Reduce Excel Formula Length
When you create formulas that refer to other sheets Excel typically includes the name of the current sheet when you return to the current sheet and refer to a cell.
VBA to Clear a Filter
Using Excel's built-in filtering can speed up your VBA code. It is important if you are applying filters that you clear any existing filters before you apply a new filter. Otherwise the existing filters will usually affect a new filter you apply. The line of code below will remove filters on Sheet1 (Sheet1 is the sheet code name that you see on the left side of the VBA screen - it may not be the sheet tab name). If Sheet1.FilterMode…
An Avo Chart
I wonder if we can get one of these on Power BI? Saw this on the website below and liked it - I also like Avocados. Avocado article on the ABC site
Instant Format in Excel
You may know the two keyboard shortcuts below for currency and percentage. But what you may NOT know is a technique that has been around since the early versions of Excel. The technique allows you to automatically apply these two formats after you type an entry. It you type $1000 into a cell and press Enter. Excel will automatically apply the $ format to the cell. The $ sign will not display in the Formula Bar - see below. If…
Automatically Populating a Column with Text in a Formatted Table
Here’s the problem, we have four separate tables with the same layout. They hold four different metrics: Actuals, Budget, Forecast and Last Year. A column called Type is used to hold the metric name. We need to populate the Type column. (more…)
Deleting Duplicated Names in Excel
When you copy a sheet that contains range names you usually end up making a duplicate of those names at the Worksheet level. I have written a macro that removes all duplicated sheet-based range names in a file.
(more…)