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.
I did a post on the Pie in Pie chart a few weeks back, but it can be a bit clunky to use. I thought about using a few dynamic array functions to make it a bit more interactive and flexible.
Like me, you have no doubt seen some colourful Excel files. One task that colour is used for is to mark rows so they can be reviewed. In general, please avoid applying colours manually when marking rows for review. Instead use a code in a separate column.
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…..
When working with financial years and depreciation you often have the situation where the asset being purchased has been purchased during the year and not at the start of the year. Here’s a function that calculates fixed declining balance depreciation and handles part year depreciation.
