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.
Monthly Archives: January 2021
Greyed Out AutoSum Icon In Excel
Get around a sheet protection issue
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.
Possible Dynamic Array Bug
Values issue
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 the sequence.
Cell E1 creates sequential numbers using the SEQUENCE function.
Row 2 has the dates and is formatted as a date.
Row 3 is the problem. If I try calculate the number of calendar days in the month using DAY and EOMONTH functions it gives a value error as if the entry in E3 is not a date.
If I multiply the E2# reference by 1 it fixes the issue as per row 4, but I shouldn’t have to do that.
I have had this is other cases where a number is not treated a number until you multiply it by 1 or perform a calculation with it.
Not sure if anyone else has seen this?
If you want to learn more about dynamic arrays I have a free Webinar Recording plus a pdf manual at the link below.
Free Introduction to Dynamic Arrays Course
Related Posts
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.
On Writing Well (Harper Collins)
This is a classic that I have just discovered. The book was written many years ago, but has been updated.
It focuses on non-fiction writing and has lots of great guidelines plus great advice.
My favorite piece of advice was – if you are having difficulty with re-writing a sentence, try deleting it and see what the effect is.
The basic tenet of the book is that writing is re-writing. No one gets it right first time, so be prepared for re-writing and re-writing.
I know from personal experience that I can improve existing writing – but a blank page is hard to edit.
It has specialist sections on most popular types of non-fiction writing. I skipped a few that I don’t write about e.g. Sport.
Highly recommend.
If you are interest in fiction writing check out Stephen King’s (yes THE Stephen King) classic – On Writing.
Sequences of a Repeating Series of Numbers in Excel
MOD and SEQUENCE used together
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.
Talking To Strangers (Penguin Books, 2020)
Written in his typical storytelling style, Malcolm Gladwell examines a police incident to see why and how we struggle to understand and relate to strangers.
Its not as straight forward as you think.
The book covers a lot of ground in many different areas. It is interesting and confronting.
If you liked his previous books then this one is worth reading.