It’s a good idea to check if there are actually formulas in all the cells where you expect them to be. It happens all the time that someone accidentally has overwritten a formula with a hard-coded value, and it can be difficult to spot errors like that. Fortunately, there is a very easy way to locate all the formulas in an Excel report: The Go To Special feature.
This is what a sales report might look like. We expect to find formulas in some of the columns, but it’s almost impossible to go through the report manually cell by cell:
Sometimes you have to deal with an Excel file that has a lot of hidden sheets, and Excel only lets you unhide them one by one, so it can be quite annoying. Here’s an easy trick that lets you forget that you ever had this problem. It requires VBA, but it’s very simple, and you can save it as a personal macro that works for every Excel file you open on the same computer, so you never have to do it again. I’ll explain every step below.
First, make sure you have the Developer tab in the ribbon. If you don’t have it, right-click on any of the other tabs, choose Customize Ribbon and click the Developer check-box:
Excel has had the WEEKNUM function for as long as I can remember, but it is very confusing, and you tend to get it wrong more often than you get it right. This is what it looks like when you type the WEEKNUM function:
Who’s to know that it is actually the last option (21) that’s the right one for most users?
Thankfully, a few years ago, Microsoft finally launched the new ISOWEEKNUM function, which I’ll describe in a moment. But first, let’s look at the different ways to calculate week numbers.
I wrote an article a few years ago about how you can join data from different columns, and add a comma between each part. It was quite tricky, especially if we had some empty cells, so we ended up with a long formula with SUBSTITUTE, TRIM and CONCATENATE.
If you have Excel 2019 or Office 365, there is an easier way: The TEXTJOIN function.
Here’s the same dataset that I used in the previous article, and the result we want in the column to the right:
Look at the diagrams below – they show the same numbers, but the vertical scales, the y-axis, are different. In this example we see how $1,000 grows to almost $300,000 in 50 years with a 12% yearly return.
The blue diagram has a linear scale on the y-axis, so the distance between 0 and 50,000 is the same as the distance between 200,000 and 250,000. The yellow diagram has a logarithmic scale with base 10, which means that each interval is increased by a factor of 10. Read more to find out how to do this in Excel, and why you may or may not want to use a logarithmic scale:
Benford’s Law describes the phenomenon that in a large dataset, the leading digit of each number does not occur with the intuitively expected probability of 1:9 (11.1%), but rather with a much larger probability for the smaller numbers. For more details on Benfords’s Law, you can read the Wikipedia Article about it, but keep on reading here if you want to learn how to use Excel to check if a dataset is consistent with it. It’s very easy!
For this example I have used population data for all the counties of the United States from census.gov: