How to use Excel to validate a dataset according to Benford’s Law

Posted on November 11, 2020 by Audun Danielsen

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 to handle parts per million, basis points and per mille in Excel

Posted on November 9, 2020 by Audun Danielsen

Excel has built-in functionality to handle percent (%), but there is no automatic way to calculate parts per million, basis points, permyriad or per mille in Excel. It is easy to calculate though, but let’s start with the definitions:

And here’s how to apply it:

How to create a Refresh All button in Excel

Posted on July 14, 2020 by Audun Danielsen

It’s easy to forget to refresh your Pivot Tables when you add new source data. Here’s an effective solution to that problem: Add a Refresh button in your Excel report!

This is very easy, and only takes a minute:

There are 3 steps:

How to calculate prices and make them end with a certain number in Excel

Posted on June 9, 2014 by Audun Danielsen

When you calculate prices you usually know the cost price and the desired profit percentage, so all you have to do is to add the profit to the cost price in order to calculate the sales price. However, the price list might not look as nice as you want:

Join cells in Excel and remove excess commas between elements

Posted on January 21, 2014 by Audun Danielsen

EDIT: If you are using Excel 2019 or Office 365 you can use the TEXTJOIN function to solve this problem. Click here for the new article:

How to Join Text from Several Cells in Excel using TEXTJOIN

Unclean data can cause a lot of problems in Excel. In this post I will show how you can join data from different columns with a comma between them. That’s the easy part. The problem occurs when you have empty cells in your data, like in the table below. The result of the first row looks fine, but if you look at the rows with empty cells, you get too many commas:

EasyExcel_41_1_Join with concatenate and trim

How to Calculate Grades in Excel

Posted on September 4, 2013 by Audun Danielsen

How can we calculate the grades (for example A-F) in Excel if we have the test results as numbers? In the example below, 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:

EasyExcel_39_1_Calculate grades in Excel

Easy-Excel.com

Find Easy Solutions to Your Excel Problems!

Category Archives: Easy Tricks