December 2022
Monthly Archives: December 2022
VBA Window Split
I just found out you can split the VBA code window. See images below.
Use the small icon above the right side scroll bar.
This can be useful if you have a long block of code and need to look at separate parts together.
Thanks to Bob Umlas the Excel MVP for sharing.
You can double click the split bar to remove it.
Custom Function for Days in the Month
Excel’s EOMONTH function is great. Here is a tweak using a custom function to calculate the number of days in a month.
Excel’s New Navigation Pane
If you have the subscription version of Excel then you have access to the Navigation Pane. This allows you to navigate between sheets and see the structure of the sheets in your file.
Hence a wealth of information creates a poverty of attention.
Herbert Simon 1971
One Minute to Excel #30 – Extract Data Based on Sheet Name
Shortcut to speed up name creation
Here’s a technique I use a lot to speed up report development.
Sheet names have to be unique, so they can’t be duplicated. This makes them great for department names or states.
This short video combines a few techniques to extract from a data set based on the sheet name.
All in less than a minute.
Excel Ranking with Sequential Numbers
Excel has three functions that can rank values. Unfortunately none of them provide a way to create a sequential list of ranking numbers when there are duplicates. Luckily we can adapt the RANK function to handle duplicates and create a sequential list of ranking numbers.
The Carbon Almanac (Penguin, 2022)
Facts. Connection. Action.
With a forward by Seth Godin this is an unusual book.
Filled with FACTS about climate change, it was created by people from around the world.
The authors had different backgrounds and specialisations.
The book covers all the aspects of climate change.
There are many different problems we are facing caused by many different industries.
This book explains the problems and covers what we can do to avert a climate disaster.
It is not too late, but we don’t have much time – we need to act now!
Check out the website here.
Update 30/12/22
This book won a Data Literacy award – link below.
Excel Constant not a Constant
In Excel you can use the Go To Special dialog to find constants. These are cells that won’t change. Constants are things like labels, entered text, numbers, or dates. But there are cells that won’t change that Go To Special won’t identify as a constant.
Check out the winners of the Data Literacy awards.
Related Posts