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.
In the past I have written about using a conditional format to highlight formulas. I thought I would create a macro to apply the conditional format easily.
I just found out that Excel’s in-cell drop-downs via Data Validation allow wildcards characters that match an entry in the list. Wildcards combinations that don’t match are rejected.
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.
Custom number formats allow you to add text to numbers and still use those numbers in calculations. There is a workaround if you need to display the number and the text on separate lines within the cell.
Check boxes are supposed to be used to allow multiple selections. But since they are now so easy to insert, you might want to use them to allow a single selection. Here’s one way to do that.
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.
Sometimes you like to point to a cell from the cell on the left using something like ===>. Here is an easy way to automate the length of the arrow based on the width of the cell.
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.
Excel has a couple of functions that will generate a random number. But the problem is they are volatile. The number changes when Excel calculates. Here’s a technique to generate a random number that doesn’t change.
When you protect a sheet in Excel there is a setting to stop users from selecting locked cells. This means when the sheet is protected, they can’t use the keyboard or mouse to select a locked cell. But you can still select a locked cell in a protected sheet – see how.
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.
