May is Macros month.
You can learn how to save time and effort by replacing repetitive or time consuming tasks with a macro. Macros make delegation easier.
Four live Excel webinars to get you started with macros. The first one is free. Over 5 hours of training.
Buy the Essential Macros Bundle for AU$60 and be registered for all the live May sessions (see below for dates) plus get access to the online courses including bonus macro content.
Essential Macros Bundle 2024I recently had an error pop up when working with a client’s file. The client’s file had a macro that would hide most of the columns on the sheet, but it had started to generate an error message.
Creating a Dynamic list of dates in Excel is pretty easy now with the use of the SEQUENCE function. Creating a dynamic list of weekday dates is a little bit more complex.
Another post inspired by the book 101 Ready-to-Use Excel Formulas by Michael Alexander and Dick Kusleika. This one is Formula #22 and covers padding entries with zeroes.
Learn about the new way to create formula and functions in Excel. This webinar recording from April 2024 will get you started.
This post is inspired by the book 101 Ready-to-Use Excel Formulas by Michael Alexander and Dick Kusleika. Formula #10 allows rounding to a certain number of significant digits. This post shows how to convert that formula into a custom function.
This month I ran 4 new live one hour webinars dedicated to all things Dynamic Arrays.
Dynamic Arrays change the way you create and maintain formulas in Excel. They expand Excel’s capabilities and make it even more flexible.
All four webinars are now online courses that you can buy as part of the Dynamic Arrays 2024 Bundle for AU$60. PLUS you get access to future Dynamic Array sessions this year.
As new Dynamic Array courses are added to the Bundle the price will rise during the year. Buy now to get the best deal. Use the button below to see more details and buy the Bundle.
Note: you need the subscription version of Excel to use Dynamic Arrays.
You can name parts of a spreadsheet and then use the name in formulas and other Excel features. Using a naming convention make things much easier to follow and adapt in the long run. I will share some suggestions for naming ranges.
I recently read an Excel VBA book that recommended using numbers instead of VBA constants for message boxes. Please don’t do that. Here’s why.
Excel has a MOD function that returns the remainder after a division. Excel VBA also has the Mod operator. Unfortunately, they return different results when it comes to negatives.
The # symbol is used in Excel sheets for hyperlinks and dynamic arrays. It is also used in VBA for date definitions.
Thanks to Rick Rothstein MVP for sharing a LinkedIn article he wrote a while back about colour constants. He gave me permission to share the contents in this post. Making your own colour constants.
In Excel colours are colors. If you want to used basic colours you can use the built-in VBA color constants. This simplifies the code and makes it more descriptive.
In the previous post we looked at extracting initials when we only had a first name and a last name in this post, we will look at handling more than two names.
Extracting initials in Excel can be challenging. That’s because the names can be separated by different characters and there can also be more than two names. Some new functions in Excel can simplify the extraction of initials.
Shapes can be frustrating to work with in Excel until you find out there are two types of selections with shapes.
Laptop keyboards tend to re-purpose function keys to handle other features. Often the default for the function keys is the laptop features. You then must press the Fn key to access the software function key options. Look out for a FnLock option.
Did you know you can use Emojis on the sheet tabs? The font on sheet tabs is small, so some emojis may not be that effective, but simple emojis can be.
I started my own weekly cash flow forecast file way back in 2010 and it just ticked over to column AAA!
I just learned a new trick for labelling subtotal rows in a PivotTable. Hat tip to Ken Puls MVP of Excel Guru for this tip.
When making entries in Excel there are a few keyboard shortcuts worth knowing. These can save you time and effort.
If you want to hide sheets and then stop people unhiding them, here is yet another technique.
It is easy to hide a sheet, you right click the sheet tab and select Hide. Unfortunately, it is just as easy to unhide that sheet once hidden. If you want to hide sheets and make it harder to unhide them, then these macros may help.
Following on from last week’s post on a single adjustment formula this post will share a more robust solution for including or excluding adjustments.
One of the advantages with Excel is that you can usually handle exceptions. In this post I examine a way to handle exceptions or adjustments without using the IF function.
Excel Sparkline charts don’t have a horizontal axis. Here is a technique that creates one in the cell above or below the Sparkline. This works best for the Column Sparkline.
I use the thin all borders format a lot. But there are times when I need to use the thin outline (outside) borders. This border is not as straight forward to apply to a range.
SUMIFS can use wildcard characters, but the wildcards only work on text-based codes.