Excel Formula for Duplicates Revisited
I was reviewing a blog post from last year that listed duplicates in a range. I thought I could shorten the formula using the LET function.
(more…)Handling Different Column Structures in Excel
One thing you learn in Excel is that there are so many ways to display the same data. When working with Actual / Budget / Variance one structure can be a challenge to work with. See how the FILTER function can simplify it.
(more…)XLOOKUP and the Last Entry
One major advantage XLOOKUP has over VLOOKUP is that it can search from right to left. That makes it useful for finance models where you need to identify the last month with a payment, balance, or forecast value.
(more…)First and Last Dates in Excel
The MIN and MAX functions are useful as for returning the first and last dates in a range. They have conditional versions as well MINIFS and MAXIFS which allow you to find the earliest and latest dates based on a condition.
(more…)Extracting Dates with XLOOKUP
One issue you may find with dates when extracting from a look up table is that an empty date field generates a zero. You can avoid that with a simple formula adjustment.
(more…)The only time to use SUMIF
I no longer teach the SUMIF function. I teach the SUMIFS function as it provides more solutions because it handles multiple criteria. The two functions differ in their argument sequence which can be confusing when switching between them. Rather than learning both, it is easier to learn the SUMIFS function. There is however one time when the SUMIF function is shorter and easier to use than SUMIFS.
(more…)Flexible SUMIFS Function
The SUMIFS function is a conditional sum function that allows you to handle multiple conditions. Here is a technique to handle the situation where a condition maybe blank.
(more…)Alphanumeric Sort in Excel
If you have ever sorted codes like A1 and A100 you have found that Excel’s standard sort doesn’t usually provide the required sort sequence. Here’s a solution.
(more…)Two Column Lookup in Excel – Part 1
It is common to perform a lookup based on one column. Performing a lookup based on two columns is more unusual. This is a formula technique you can use to perform a two-column look up.
(more…)Excel LET Function and Other Functions
In my previous post I created a formula that calculates the month number for a financial year. That formula used the MONTH function four times. I thought I would share a technique you can use to define functions as a variable in LET function.
(more…)Capturing Formulas in Excel
Unfortunately, it is easy to delete a formula. Of course, there is always Undo but if the file has been closed getting the formula back is difficult unless…..
(more…)Excel SEARCH and Wildcards
Excel has recently added three regex functions to handle text patterns, but did you know the old SEARCH function can use wildcards?
(more…)TRANSPOSE and TOROW Functions in Excel
When you have a vertical list that you need to make horizontal you can use the TRANSPOSE function to switch it. There is an alternative function that gives you extra options.
(more…)Excel FizzBuzz Challenge
I was recently reminded of a basic programming challenge called FizzBuzz. Here is my Excel solution.
(more…)UNIQUEIF Excel Formula
I recently had an interesting question in a webinar. Could I create a formula like UNIQUEIF. I like a challenge so here is my answer.
(more…)TRIMRANGE Syntax and INDEX and INDIRECT
The INDEX function can return a reference to a cell. However, the new TRIMRANGE syntax won’t accept it a reference. You need to use the TRIMRANGE function to use INDEX or INDIRECT. In my last post I showed how to always refer to cell A1 in a reference. The techniques demonstrated in that post won’t work with the new TRIMRANGE syntax. – see this previous blog post about the new syntax. If you try to use the new syntax, you…
Making Sure a Reference is Always A1
When you create a formula or a range that refers to cell A1 and you want it to ALWAYS refer to A1 no matter what – you have at least a couple of options.
(more…)Excel Variance Techniques
When comparing actuals to budget or this year to last year you often calculate the variance and variance %. The variance calculation can be standardised with one simple trick and another function can make sure the variance % has the right sign.
(more…)Unique Entries in a Range in Excel
Sometimes the UNIQUE function won’t return what you’re expecting when you refer to a range of cells. You can use another function to allow you to extract the unique individual values from a range.
(more…)Adding Up a Specific Column in an Excel Spill Range
If you have a 2-dimensional spill range in Excel and you need to add up a specific column here is an easy formula to use.
(more…)Excel Formula for Duplicates
I had question in a webinar recently about a formula to list duplicated values. There may be a shorter formula, but I found and shared a solution.
(more…)Convert Line Chart to Dynamic Step Chart
Excel and Chart legend and MVP Jon Peltier has blogged about how you can convert a line chart into a step chart. I have used the new TRIMRANGE functionality and range names to make a dynamic solution.
(more…)TRIMRANGE and Range Names in Excel
The new TRIMRANGE range reference structure can be applied to range names to create flexible and dynamic range names.
(more…)TRIMRANGE and OFFSET in Excel
I have seen a few posts recently suggesting that the new TRIMRANGE feature may reduce the use of the OFFSET function when creating flexible ranges. I thought that maybe the OFFSET function will be used more with the TRIMRANGE functionality.
(more…)New TRANSLATE Function
A recent update to Excel includes a new TRANSLATE function. This function enables you to translate words from one language to another.
(more…)Identifying Locked Cells in Excel
The default setting in Excel is for all cells to be locked. If you want users to input in a cell you must unlock that cell before you apply sheet protection. You can use a formula to identify locked and unlocked cells.
(more…)Excel Formula Updating Technique [Video]
A recent project required editing many formulas to insert an IF function to display the NA error in certain circumstances. Here’s how I did it.
(more…)Excel YTD Reporting Solution
Having Actuals, Budget and Variances for each month going across the page is a very common reporting construct. Here is an easy way to sum the correct YTD values as the year progresses.
(more…)Creating Sentences in Excel
If you want to automate journal descriptions or create sentences the TEXTJOIN function is your friend. It can combine words and insert the spaces for you.
(more…)Selecting a Formatted Table in a Formula
You can select a formatted table when you have a cell or range selected in the table by pressing Ctr + A. But that shortcut won’t work when creating a formula that refers to a formatted table. To select the table in a formula you must click a cell in the table and press Ctrl + Shift + Spacebar.