Excel sheets can be large, and you may have multiple tables on a sheet. When a filter is applied it can affect copying and pasting in the rest of the sheet. To know if a filter is in place check the Data tab.
Excel can now use regex patterns in formulas. You might think that the REGEXEXTRACT function would simplify extracting numbers and letters, but it is actually the REGEXREPLACE function that simplifies the process.
SUMIFS is great for summing values based on multiple conditions. But when one of those conditions needs to accept multiple options, such as two or more states, combining SUM with SUMIFS can provide a flexible solution.
Using the From Folder option in Power Query allows you to import all the files from the selected folder and all its subfolders. What if you didn’t want the subfolders? There is a solution.
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.
