April 26, 2019
Using a Formula Instead of VBA
Sometimes we get requests to provide a VBA solution to a problem. But when we look at the problem, VBA
Read MoreApril 26, 2019
Sometimes we get requests to provide a VBA solution to a problem. But when we look at the problem, VBA
Read MoreJanuary 8, 2019
I really wanted the title of this post to be “Excel Sorted Dynamic Unique List Ignoring Blanks and Errors”, but
Read MoreDecember 6, 2018
If you’re familiar with array formulas, then the simplicity of Excel Dynamic Arrays will be a breath of fresh air.
Read MoreAugust 31, 2018
The SUBSTITUTE function allows you to change text in a string, or a range of strings, but there is more
Read MoreJuly 19, 2018
It’s often difficult enough to figure out the correct Excel functions to use, but it’s significantly harder if you also
Read MoreApril 4, 2018
Excel Named Ranges is a vast topic that includes some simple techniques that we all can and should use to
Read MoreMarch 22, 2018
The Excel ADDRESS Function returns a cell address for specified row and column coordinates. Optional arguments allow you to specify
Read MoreOctober 25, 2017
We often need to extract the date or time portion from a date-time serial number for use in other functions.
Read MoreOctober 25, 2017
The Excel DATE function combines separate year, month and day values and converts them into a date serial number formatted
Read MoreOctober 25, 2017
Often, we'll have a list of month names that we want to convert to a date, or even just convert
Read MoreOctober 24, 2017
If you want to use a date in a chart label, or you want to concatenate a date with some
Read MoreOctober 19, 2017
The Excel NETWORKDAYS.INTL function (new in Excel 2010) returns the number of working days between two date serial numbers, excluding
Read MoreSeptember 19, 2017
In an ideal spreadsheet our formulas would always reference adjacent cells and columns and it would be obvious which cells
Read MoreJuly 27, 2017
If your company’s fiscal year runs in line with the calendar year then it’s easy to convert dates into quarters
Read MoreJune 1, 2017
If you share Excel files and reports with users from different countries then you might want to give them a
Read MoreMarch 22, 2017
New in Excel 2019* is the TEXTJOIN Function. It's the grown-up sibling of CONCATENATE. Both TEXTJOIN and CONCATENATE join text
Read MoreNovember 17, 2016
Last week I received the following question from André: “I need a formula that will count how many values in
Read MoreOctober 18, 2016
This tutorial is applicable to Excel 2019 onward and with a Microsoft 365 license. In Excel 2019 there are a
Read MoreOctober 18, 2016
This tutorial is applicable to Excel 2019 onward. New in Excel 2019 is the SWITCH function. It looks up a
Read MoreOctober 14, 2016
A couple of days ago I sent out a survey about the name of an argument in one of the
Read MoreOctober 7, 2016
In Excel 2007, Microsoft thought it would be a good idea to increase the number of times you can nest
Read MoreSeptember 1, 2016
There are a few ways to approach a running total formula, but Excel Tables require something special, or you're likely
Read MoreAugust 16, 2016
I’ve written about how to create dependent data validation lists before here; Excel Data Validation with dependent lists, and here;
Read MoreMay 3, 2016
At first glance the CHOOSE function isn’t very exciting and typically you have to team it up with other functions
Read MoreApril 19, 2016
Excel MROUND function is perfect for rounding numbers to the nearest multiple. For example, last week Brenda said she was
Read MoreMarch 21, 2016
There comes a time in many Excel users’ careers where we start to write incredibly complex Excel formulas to summarise
Read MoreFebruary 17, 2016
I use Dynamic Text Labels all the time, whether it’s in a Dashboard report or chart title, and even to
Read MoreJanuary 27, 2016
There are 3 ways you can transpose data in Excel (not including VBA). Download the workbook and follow along Enter
Read MoreJanuary 20, 2016
Excel Multi-cell array formulas are a single formula which returns multiple values and is entered into multiple cells. Hence ‘multi’
Read MoreJanuary 12, 2016
You'll typically find the ROW(S) or COLUMN(S) functions nested inside other functions because they make formulas more efficient to write.
Read More