Education isn’t something you can finish
Isaac Asimov (1920-1992)
Hiding Multiple Sheets in Excel Using xlVeryHidden
Flexible solution
It is easy to hide multiple sheets in Excel. Unfortunately, it now just as easy to unhide those sheets. You can hide sheets and make it harder to unhide them. You can use a setting called xlVeryHidden (no kidding) that won’t display the sheet name if you right click a sheet tab and choose unhide sheets.
The myth is that there isn’t enough time. There is plenty of time. There isn’t enough focus with the time you have. You win by directing your attention toward better things.
James Clear
Excel Has Ordinals
Woohoo, I don’t know when this happened, but you can now get Excel to extend your ordinals when you drag with the Fill Handle and use things like 1st, 2nd, 3rd and 4th etc.
Type 1st January in a cell and drag the cell down.
It seems to work with ordinals at the start rather than at the end of a text string. So January 1st doesn’t work. 1st by itself does work.
Improvement to Excel’s HYPERLINK Function
Excel’s HYPERLINK function is not easy to use. You need to know a few of its secrets to get it to do what you want. Unfortunately, you can’t replicate the HYPERLINK function in a custom function, so we can’t improve it. But the next best thing is to simplify creating the reference you need as the first argument in the HYPERLINK function.
If you can’t make a mistake, you can’t make anything.
Marva Collins
Excel Column Letter Custom Function
Sometimes Excel refers to columns using numbers. If you need to identify the column letter you can use a few functions in combination. Thanks to Marcus Small for the formula which is shorter and more elegant than my original one. You can also create a custom function.
Never give up your right to be wrong, because then you will have lost the ability to learn new things and move forward with your life.
David Burns
Percentage Movement in Excel
It is common in Excel to calculate the percentage movement or difference between two values. This may be between this year and last year or between actual and budget. There are two common issues you will face when doing this calculation. The first is handling zeros and the second is handling negatives.
Mistakes are painful when they happen, but years later a collection of mistakes is what is called experience.
Denis Waitley
FILTER Function Technique
An application I use recently updated it’s filtering options to allow you to filter by any filters or all filters. This was a useful addition to the software and I thought that I could apply the same idea to Excel’s FILTER function.
It’s good to remember that there’s much to learn from negative experiences. In science, mistakes always precede discoveries.
John C. Maxwell
Benford’s Law in Excel – Part Two
Benford’s law is used in auditing to identify data sets that may have been manipulated or adjusted. In my previous post I created a report to analyse a data set based on Benford’s Law. In this post we will create a single formula to create the report and then convert that into a custom function.
When opportunity comes, it’s too late to prepare.
John Wooden
Benford’s Law in Excel – Part One
Benford’s law is used in auditing to identify data sets that may have been manipulated or adjusted. In actual data sets when reviewing values the 1st digit of the values tends to follow a predetermined frequency. For example, roughly 30% of the values should start with a 1.
If you’re willing to consider failure as a blessing in disguise and bounce back, you’ve got the potential of harnessing one of the most powerful success forces.
Joseph Sugarman
Comparing Averages in Excel
It is common in Excel to use averages to summarise large data sets. It is also common to compare the averages across different segments. Here’s a technique you might find useful when comparing a segment against all other segments.
The most common trait I have found in successful people is that they conquered the temptation to give up.
Peter Lowe
SUBTOTAL and Dynamic Arrays in Excel – Part 4
The SUBTOTAL function in Excel is quite flexible. The single function allows you to perform 11 different calculations. In this post we will amend the custom function we have created to add an extra column plus headings.
Humility does not mean you think less of yourself. It means you think of yourself less.
Ken Blanchard
SUBTOTAL and Dynamic Arrays in Excel – Part 3
The SUBTOTAL function in Excel is quite flexible. The single function allows you to perform 11 different calculations. In this post we will create a custom function to summarise a data set.
Don’t rush, but don’t wait. Act with urgency, but release yourself from the need to achieve it on a particular timeline. When you think longer term than most, you can think bigger than most. If it takes years, start now.
James Clear
SUBTOTAL and Dynamic Arrays in Excel – Part 2
The SUBTOTAL function in Excel is quite flexible and in this second post we build an automated summary report using SUBTOTAL.
Focus starts with elimination, improves with concentration, and compounds with continuation.
James Clear
SUBTOTAL and Dynamic Arrays in Excel – Part 1
The SUBTOTAL function in Excel is quite flexible. The single function allows you to perform 11 different calculations. It can also ignore hidden rows, something that not many Excel functions can do.
What if we studied what was right with people instead of what was wrong with them?
Don Clifton
Highlighting Weekends in Excel
When you have a list of dates in Excel it can be useful to know which of those dates are weekends. You can automate a format for weekends using a conditional format.
Do not spoil what you have by desiring what you have not; remember that what you now have was once among the things you only hoped for.
Epicurus – Greek philosopher
Excel Custom Function to Return Unique Random Whole Numbers
In last week’s blog post I covered a complex formula to return unique random whole numbers. In this weeks’ post we will look at how we can convert that complex formula into a custom function.
One can resist the invasion of armies; one cannot resist the invasion of ideas.
Victor Hugo