On LinkedIn recently someone posted an Excel formula solution lamenting that it was long and complex. That of course was a challenge to me to simplify it.
Here’s a technique to calculate the time differences when you aren’t sure which time is first or last. Note with standard Excel settings you cannot report negative time.
20 years ago my last article for the Accountants Weekly magazine was published. They spelled my name wrong after getting it right for all the other articles, maybe that’s why I stopped.
My second article in Accountants Weekly was published 20 years ago today and it was Top 10 Functions for Accountants.
If you need to add time to an existing time then you need to learn about the TIME function.
If you have a list of first names and last names and you want to make sure the list has no duplicates you can use a formula to confirm the names are unique.
When creating an input range you may need to validate input cells. That may mean ensuring all input cells have an entry. Here’s how.
Excel has two functions to answer these questions.
It is now easier to create a distinct count formula in the subscription version of Excel. You can also use a criteria. A distinct count only counts each value once. Duplicate entries are ignored.
The Australian Financial Year has its challenges. Working out the Quarter number based on a date has a few solutions. Here’s another one.
In a financial model you often have different types of allocations that start at different times. Creating a short formula to handle this flexibility can be a challenge. Here is one solution.
It is common to display a blank cell using the IF function and “”. A problem can arise when you want to use that IF formula in a calculation. Here is an easy way to cope.
In Excel your goal should be to have a single formula in a table column that can be copied down the whole column.
The new XLOOKUP function has the ability to spill when you select multiple columns to extract. Even when you do, it doesn’t always spill across.
When you protect a sheet in Excel many icons are turned off (greyed out), including the ever popular AutoSum icon. That’s when it pays to know keyboard shortcuts.
Let’s say you are getting inputs you can’t control and in some cases you get text and others you get numbers. You want the numbers, but you need to treat text as zero. Here’s the easy way to do that.
In budgets, forecasts, financial models and even reporting models repeating the numbers 1 to 12 can be useful. The SEQUENCE and MOD functions can make it easy and scalable.
I wrote a blog post a few years back showing how to add up numbers formatted as text. If you have the subscription version of Excel you have another solution.
I was recently working with some data that had some issues with the sign on the quantities. The quantities should have had the same sign as the associated dollar amount, but they didn’t. Here is how I fixed it.
Let’s say we need to put a prefix in front of a number to identify the period being used. Whether that be year, month or week.
If you need to confirm a number is a whole number you can use a function with a short name.
Some people like to use bullet points in text boxes. Here is a simple technique to insert bullet points based on a list of entries in an Excel sheet.
If you have the subscription version of Excel you may have the new LET function. This function lets you capture variables within a formula. Let’s see an example.
One of the frustrations with using array syntax is that you always have to type all the entries between the curly brackets. You couldn’t link to cells. Well that has all changed with dynamic arrays.
When working with weeks in Excel you may need to show the start and end date of the week in the same cell. Here’s how you can do that.
With the introduction of Dynamic Arrays in all versions of Microsoft 365 (formerly Office 365) it is now a lot easier to use the TRANSPOSE function.
The SUMPRODUCT function has been my favourite function for about 20 years. It is so flexible. Soon it will be redundant thanks to dynamic arrays.
It is best practice to use grouping to hide and unhide rows in Excel. I recently saw a technique that also displays a message.
A few years ago I wrote an article on extracting the end of quarter date from a date. I recently had a query that was related and I tweaked the previous solution to solve it.
On a recent Webinar I was asked a question about an unusual date structure that was imported. The structure dd.mm.yy was not recognised by Excel as a date. Here is formula that fixes it.
Below is an example of the date issue.
The formula in cell B2 is
As you can see the dates in column A are left aligned. That is a clue that they are not recognised as dates in Excel. Dates are right aligned.
The SUBSTITUTE function replaces the full stop between the numerals with a / and makes it look like a date.
This isn’t sufficient as the SUBSTITUTE function will return text. The *1 at the end converts the text date in to a real date that Excel recognises.
Note: Power Query can also automatically fix dates like these when it imports data.