Stories in data: Sums and rates

Topic Progress:

Click on the numbered tabs to navigate this lesson.

Calculating sums, percentages and ratios

Calculating totals and how they relate to a dataset as a whole will be one of the most common tasks that you will have to perform in Google Sheets. To learn how, make a copy of this Sheet at this link in your Google Drive. It shows the population of Nigeria as estimated by the National Bureau of Statistics in 2016.

Adding rows together

To get the total population, we can add a row by writing the word “Total” in cell A39. Now, in the next cell (B39), start typing the formula =SUM. As you type, you will see that Sheets will start to recommend ways to complete the formula. In this case, it suggests =SUM(B2:B38) – ie. adding together all of the values vertically from B2 down. You’ll see the suggested range is highlighted. 

You can either enter this formula manually or just press Tab to automatically complete it.

You can use CRTL+B to bold the text in the total row.

Calculating percentages

Now that we know the total population, we want to understand the size of the population in each region as a percentage of the whole. Finding a percentage means dividing the regional population by the total population, and multiplying by 100.

Percentage = (Regional population/Total population) x 100

In Sheets we do this by starting in the region at the top of the list (Lagos), and cell C2. Type =B2/B$39. 

The $ sign is very important for the next step.


Copy the formula and format it

Now place your mouse cursor over the large dot in the corner of cell C2 until it turns into a cross. Drag this cross all the way down the column to cell C39. You should see the formula copy into each cell. You will see that the first reference – B2 – updates to the current row number in each new cell. The $ sign in B$39 prevents the second cell reference from automatically changing.

Turning numbers into percentages

In Sheets it’s not necessary to multiply the result of that calculation by 100 to express it as a percentage. You can change the data format by clicking on the letter at the top of column C to select the whole column, then going to the Format menu and selecting Number>Percent. 

Calculating ratios

What if you want to describe the population of Lagos in a way that is more meaningful to readers? Instead of using percentages, we can use ratios. Try this:

In cell D2, type =B$39/B2. We are dividing total population by the population of Lagos. You will get the result 11.33999686. You can round this down by clicking on the top of column D and selecting Format>Number>###. The ‘###” represents a sequence of numbers without a decimal place. 

What we can now say is that one in eleven Nigerians live in Lagos.

Drag that formula down across all the cells in the same way that we did for percentages. 

Recap and test

From your new sheet, you should be able to answer these simple questions.