I was checking out an old Excel book Excel Outside The Box by long time Excel MVP Bob Umlas and noticed he used the N function in his SUMPRODUCT functions. I then realised why. It converts text to a zero. That gets around an issue with adding up ranges that contains text, thanks again Bob.
“Everything not saved will be lost.”
Nintendo “Quit Screen” message
A New Month of Sundays
One of my more popular posts involved counting the number of Sundays between two dates. With dynamic arrays that becomes easier, and we can create a custom function.
It’s so hard to forget pain, but it’s even harder to remember sweetness. We have no scar to show for happiness. We learn so little from peace.
Chuck Palahniuk (Novelist)
May the Macros Be With You
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 2024Can’t push objects off the sheet Error in Excel
I 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.
Deeds will not be less valiant because they are unpraised.
Source: Aragon in The Return of the King
J.R.R. Tolkein
Dynamic List of Weekday Dates in Excel
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.
Padding Entries in Excel
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.
Introduction to Dynamic Arrays Webinar Recording
Learn about the new way to create formula and functions in Excel. This webinar recording from April 2024 will get you started.
Significant Digits in Excel
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.
Only those who risk going too far can possibly find out how far one can go.
T. S. Elliot
Excel Dynamic Arrays in April
April is Dynamic Array month.
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.
Excel Range Name Conventions
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.
Excel VBA – Please Don’t Do This
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 VBA Mod Operator Problem
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.
What matters in life is not what happens to you but what you remember and how you remember it.
Writer and Nobel Prize winner Gabriel García Márquez
Does an Excel range contain sequential numbers?
I have created a custom function to check if a range has sequential numbers. The range doesn’t have to be sorted.
I have long understood that climate change is not only an environmental issue – it is a humanitarian, economic, health, and justice issue as well.
Environmental activist, Frances Beinecke
Excel VBA and the # Symbol
The # symbol is used in Excel sheets for hyperlinks and dynamic arrays. It is also used in VBA for date definitions.
Colour Constants Updated
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.
VBA Color Constants
Simplify using basic colours
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.
Be prepared to appreciate what you meet.
A Fremen proverb from Dune by Frank Herbert.
Extracting Initials in Excel – Part 2
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 – Part 1
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.
Working with Shapes and Other Graphic Objects in Excel
Shapes can be frustrating to work with in Excel until you find out there are two types of selections with shapes.
Excel, Laptops and Function Keys
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.
Excel Sheet Names and Emojis
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.
AAA all the way
I started my own weekly cash flow forecast file way back in 2010 and it just ticked over to column AAA!
Excel PivotTable Subtotal Label Trick
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.