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:
How can we calculate the grades (A-F) in Excel if we have the test results as numbers? We know that a score of 90% or higher is an A, 80-89% is a B, 70-79% is a C, 65-69% is a D and less than 65% is an F.
The first thing we should do is to organize this information in a lookup table:
If you create an Excel report for someone, it is important that you somehow visualise which cells they are allowed to change, and which cells they should not touch. The common way to do this is to use a blue font for the assumptions, i.e. the values that can be changed.
Unfortunately, this is not always enough. Inevitably, someone will try to enter values into the calculation cells as well, and when they do, the whole report might be useless. We need to protect the cells from being tampered with, and the good news is that it takes less than a minute!
Is there an easy way to locate and highlight duplicates in a list in Excel?
If you just want to remove the duplicates, the easiest way is to use the Advanced Filter or the built-in Remove Duplicates feature on the Data ribbon, but what if you want to find the duplicates in the list, keep them and highlight them with a different colour? That almost sounds like a job for a professional Excel consultant, but there’s no need for that – you can easily do it yourself! I’ll show you one easy way and one super-easy way: