Removing Greyed-Out Slicer Options in Excel
Sometimes Slicers seem to have a long memory and list entries that are no longer in the current data set. There is a setting to fix this.
(more…)Greyed Out AutoSum Icon In Excel
When you protect a sheet in Excel many icons are turned off (greyed out), including the ever popular AutoSum icon. That’s when it pays to know keyboard shortcuts.
(more…)Possible Dynamic Array Bug
Recently I have been using more dynamic array formulas and have come across an unusual situation where a date is not treated like a date. Remember dynamic arrays are currently only available in the subscription version of Excel. Have a look at the image below. You can download the example file at the button at the bottom of the post if you want to see it in practice. Cell B1 is an input cell to set the first date of…
Treating text as zero in Excel
Let’s say you are getting inputs you can’t control and in some cases you get text and others you get numbers. You want the numbers, but you need to treat text as zero. Here’s the easy way to do that.
(more…)Sequences of a Repeating Series of Numbers in Excel
In budgets, forecasts, financial models and even reporting models repeating the numbers 1 to 12 can be useful. The SEQUENCE and MOD functions can make it easy and scalable.
(more…)