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.
When you link to an empty cell Excel it displays as zero. The same happens when you filter a list using the FILTER function. Blank cells display as zeroes. Here’s how to stop that.
In the previous post I showed how to use XLOOKUP to do a two-column lookup. See the link below for the previous post. This time we will see how to do the two column lookup using Power Query.
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.
Merging in Power Query is a useful feature that automates and replaces XLOOKUP functions. But please make sure the look up table doesn’t have duplicates.
Using formatted tables in Excel is best practice. If you regularly overwrite data in a formatted table here is a quick way to clear the data before pasting in updated data.
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.
Excel is built to work with calendar years. Financial years pose a few issues for correctly identifying year and month numbering. I wanted to create some formulas that would work with all financial year ends. Here they are.
