Education isn’t something you can finish
Isaac Asimov (1920-1992)
Education isn’t something you can finish
Isaac Asimov (1920-1992)
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
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.
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
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
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
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 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 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
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
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
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.
The SUBTOTAL function in Excel is quite flexible and in this second post we build an automated summary report using SUBTOTAL.
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.
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.
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.