H2Eratesper100

How to Show Percentages as Rates Per 100,000

by with No Comment statisticstips & tricks

When displaying data, using percentages isn’t always optimal, especially if you’re dealing with a very tiny number. In those cases, it may be more effective to display data as a rate per 100,000 or per 1 million, depending on how small of a number you have. Below, I’ll show you how you can move between percentages and rates.

Converting between percentages and rates

To convert percentages into rates, it’s as simple as multiplying the percent by the population you’re trying to calculate a rate for. Some common examples are calculating rates per 10,000, per 100,000, or per 1 million.

Let’s start with a simple stat: there are approximately 50 million Microsoft 365 subscribers in the world. Out of a global population of 7.9 billion people, that is 0.6% of the total population. Let’s frame this a different way, as a rate. To do this, I can multiply that percentage by 100,000, which returns a value of 633. That tells us that for every 100,000 people, 633 of them have Microsoft 365 subscriptions. You can multiply this by 10 to say that for every 1 million people, more than 6,300 will be subscribers.

Now let’ do the reverse. The odds of winning the Powerball jackpot are approximately 1 in 292 million. To convert this into percentages, we’ll need to divide 1 by 292,200,000. The result is a very tiny value of 0.0000000034. As you can see, this isn’t very helpful in using this as a percentage. And this is why using a rate is more appropriate.

Calculating 1 per a larger base

If you’re working with that incredibly small value, you can convert that into a rate of 1 per some larger number. All you need to do is calculate the inverse. To do that, take 1 and divide it by the value. In the above example, it would be 1/0.0000000034, which would return a value of 292 million.

Creating a quick template

If you have some small percentages you want to convert into percentages, you can create a quick template to help you determine which rate you may want to use. In some cases, you may not want to just use 1 per x but instead x per 100,000, or some larger figure. You’re communicating the same value, it’s just a matter of how you decide to do it.

Below, I’ve collected some data showing the percentage of dog owners in the world, the percentage of people who have green eyes, and the percentage of people with red hair:

Table showing percentage of dog owners, people with green eyes, and people with red hair.

I’m going to create four additional columns, one to calculate the inverse (1 per x), rate per 10,000, rate per 100,000, and per 1,000,000. For the last three columns, all you need to do is to multiply the percentage by those base numbers. And here’s what the results will look like:

A quick way to check these results is by calculating the percentages. 60,000/1,000,000 is equal to 6%, 20,000/1,000,000 is 2%, and 15,000/1,000,000 is 1.5%.

The advantage of using the larger population number is that your results will stand out more and can be easier to visualize on a chart:

Rates per population showing the occurrence of dog owners, people with green eyes, and people with red hair.

If you liked this post on How to Show Percentages as Rates Per 100,000, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Compounded Annual Growth Rate Calculator

by with No Comment calculatorinvestingstatisticsstocks

If you don’t want to calculate compounded annual growth rate yourself, you can do it quickly and easily with this free calculator:




Calculating Compounded Annual Growth (CAGR)

Percent Change
Number of Years



[WP-Coder id="1"]

presentation-1454403_640

How to Make a Correlation Matrix in Excel

by with No Comment advancedcorrelationsstatistics

To find correlations between data points is useful when you’re trying to find a pattern or any sort of relationship. Below, I’ll show you how you can quickly do a correlation matrix as well as how to do a calculation if you’re only looking at two data sets to compare.

Step 1: Enabling the Data Analysis Add-on

One of the biggest challenges in creating a correlation matrix is just finding where the option to calculate the correlations is. In order to access it, you need to first enable the Data Analysis add-on.

To do this, you have to get to the Excel Options. This will vary depending on which version of Excel you have, but in newer versions, you go to the File tab and select the Options button at the bottom of the page. Once there, you’ll want to select the Add-ins option.

excel options

From there, you’ll have a list of all the Add-ins available. Then, next to the Manage button at the bottom, click on the Go button (highlighted in yellow).

excel add-ins

After clicking the button, you’ll have a list of all the Add-ins that you can install.

excel add-ins data analysis

Click on the checkbox next to the Analysis Toolpak and then click OK.

Step 2: Running the Correlation Add-in

Now, if you go onto the Data tab, you should see off to the right, a button for Data Analysis, next to the Outline group.

data analysis group

Clicking on the Data Analysis button will give you a lot of different options, but for this example, we’re just going to use the Correlation option.

data analysis correlation

Step 3: Selecting the Ranges to Evaluate

Next, you’ll be asked to select your Input Range. This is where you’ll enter the ranges that you want to compare. You can select either rows or columns. In most cases, you’ll probably leave the default, which is columns. You’ll want to select the columns you want to compare and specify if the label is in the first row.

Once you’ve selected your data along with where you want to output the data (I usually leave the default, which is New Worksheet Ply), then click on OK.

If you don’t have numbers in all your columns, you might see the following error come up:

correlation error

To fix this, you’ll need to look for any blank cells that might be in your data. If you have any if formulas that have a result of “”, then those will cause a problem as well. Either way, your data will need to be cleaned up to ensure that only numbers are in the range that you want to calculate correlations on.

Once you’ve cleaned it up, depending on how many columns you selected, you should end up with something that looks like this:

correlation matrix excel

Step 4 (Optional): Apply Conditional Formatting to the Correlation Matrix

Although the matrix is technically complete, this is not an easy way to identify significant correlations, especially if you’re looking at several columns. This is where conditional formatting can help us.

What I’ll do is setup formatting so that anything between 0.7 and 0.99 shows up as green, and anything that is between -.1 and -.99 will be red to indicate a negative correlation. Now the matrix looks a bit easier to read since I can focus on areas of high or negative correlations:

correlation matrix conditional formatting excel

For a detailed look at how to do conditional formatting, refer to this post.

Recreate a Correlation Matrix Using a Formula

That’s how you can create a correlation matrix in Excel, but what if you just want to look at the correlation between two pairs of data sets? In that case, you can use the CORREL function.

Back to my data set, I can use the CORREL function and select two data sets.

correl function

After hitting enter, it tells me the correlation of the two columns is 0.61. The one limitation of this is that you can only compare two data sets at a time. However, you don’t have to go through data analysis feature and can use this to put the correlation results in any way that you want.