How to Calculate Percentage in Google Sheets

Expressing quantities in percentage always help give a better sense of proportion.

It helps us visualize the quantity better in our heads and is great for making comparisons.

It’s no wonder that most common quantities like sales tax, discounts, bank interest rates, etc. are commonly expressed in percentage.

In this tutorial, we will show you how to use the Google Sheets percentage format to make it easier for you to calculate and display percentages.

What is Percentage?

The term percent or percentage means one part of a hundred.

Instead of expressing a ratio as a fraction of a random number, we usually express it in fractions of one hundred.

In this way, we get a common baseline, helping us to clearly see the difference in the quantity proportions.

Why use Percentage in Google Sheets?

The percentage format in Google sheets is one of the most useful number formats.

It helps you express values in percentage, by not only adding a percentage sign next to it, but also by converting the number to a percentage value.

So if you have a fractional number like, say 0.15 in a cell, formatting it with the percentage format automatically converts it into 15%.

Fractal Number in a cell

All you need to do is select the cell, and click on the ‘%’ button from the Google Sheets toolbar.

Click on the percent icon in the toolbar

Coming to the question of why we use percentage in Google sheets, take a look at the images below:

Notice that the Value Added Tax percent is formatted differently for each sheet. The VAT is displayed as a fraction of the first image, while the same value is displayed as a percentage in the second one.

VAT as fractal

VAT as percentage value

You would agree that the second spreadsheet is much easier to read than the first.

Seeing how helpful it is to calculate percentages, we are going to discuss how you can calculate percentage in different practical situations, like:

  • calculating the Percentage of proportion
  • calculating the Percentage of total
  • calculating the Percentage change

There are a number of other situations in which the percentage can be applied, but we are only going to concentrate on these three in this tutorial.

How to Calculate Percentage of a Proportion

The first application of percentages is in calculating the percentage of a proportion. In other words, what portion of a task belongs to or does not belong to a certain category.

For example, in the table below, we have the sales target allotted for each employee in Column B, and the sales made by them are given in Column C.

Dataset for converting numbers to percenatges

Let us say we want to calculate what percent of their allotted target each person has achieved. The general formula for this situation would be:

Sales Made / Allotted Target * 100

To apply this general formula to the above dataset, we need to follow the steps given below:

  1. Select cell D2 (where the first row’s result will be displayed).
  2. Type the formula =C2/B2Formula to calculate proportion
  3. Press the ‘Format as percent’ button (%) from the toolbar. Alternatively, you could navigate to Format->Number->Percent from the menu bar. This will convert the result of the formula to percentage and display a ‘%’ sign next to the result.Click on the percent icon to get 50%
  4. You should see the result in the second row of column D.
  5. Drag down the fill handle to copy the formula to the rest of the cells in column D.Drag and copy the formula to all cells in the column
  6. If you see the results include decimal places, you can choose to remove the decimal so you now have the percentage rounded up to the nearest whole number.Remove the decimals

You now have a list of percentages of sales made by each employee. You will find it is easier to see who has been most efficient in achieving their targets.

How to Calculate Percentage of Total

Next, let us look at a situation where we have the sales made by each employee (assuming that they all have a combined target).

Dataset to Calculate Percentage of Total

We want to know what percentage of the total sales each employee has made.

The general formula for a given employee would be:

Sales made by the employee / Total sales * 100

The total sales made by all employees can be calculated using the SUM function, as follows:

=SUM(B2:B6)

To apply this general formula to the above dataset, we need to follow the steps given below:

  1. First, calculate the total sales made. For this, we can use the SUM function and display the result in cell B7. The formula for the total will be =SUM(B2:B6).Calculate the SUM of all the values
  2. Next, select cell C2 (where the first row’s result will be displayed).
  3. Type the formula =B2 / $B$7Formula to Calculate Percentage of Total
  4. Press the ‘Format as per cent’ button (%) from the toolbar. Alternatively, you could navigate to Format->Number->Percent from the menu bar. This will convert the result of the formula to percentage and display a ‘%’ sign next to the result.
  5. You should see the result in the second row of column C.Click on the % icon in the toolbar
  6. Drag down the fill handle to copy the formula to the rest of the cells in column C.Copy the formula for all the cells
  7. If you see the results include decimal places, you can choose to remove the decimal so you now have the percentage rounded up to the nearest whole number.Remove the decimals if you want

You now have a list of percentages of total sales made by each employee.

You will find it is easier to see which employee contributed the most or least to the total sales. You can even use this to gauge their performance over a period of time.

We will also take a look at a situation like this in the next section.

Explanation of the Formula

You will notice that we used more or less the same basic formula as the previous case, but with a very slight difference. We used an absolute reference to the total sales made (cell B7).

The dollar sign ($) is used to specify that a reference is absolute, so it does not change when you copy the formula to the other cells.

We do this to ensure that every cell value of column B gets divided by the same cell value (in B7). Thus, every new result in column C will get calculated based on the sum in cell B7:

=B2/$B$7

Note: If we sum up all the percentages in column C, we should get a total of 100%, which obviously makes sense.

Sum of all percentages in 100

How to Calculate Percentage Change

Once you have the percentage of sales of an employee calculated for every month or week, it then becomes easy to monitor their performance.

You can easily see if their percentage increases or decreases.

Let us now take a situation where we have the sales made by employees in week 1 and in week 2.

Dataset to Calculate Percentage Change

We want to know what percentage change (improvement or decline) there has been in each employee’s sales.

The general formula for this situation would be:

(New Value - Old value) / Old value * 100

To apply this general formula to the above dataset, we need to follow the steps given below:

  1. Select cell D2 (where the first row’s result will be displayed).
  2. Type the formula =(C2-B2)/B2Increase or decrease in decimals
  3. Press the ‘Format as per cent’ button (%) from the toolbar. Alternatively, you could navigate to Format->Number->Percent from the menu bar. This will convert the result of the formula to percentage and display a % sign next to the result.
  4. You should see the result in the second row of column D.Click on the percent icon in the toolbar
  5. Drag down the fill handle to copy the formula to the rest of the cells in column D.Copy and paste the same formula on all the cells
  6. If you see the results include decimal places, you can choose to remove the decimal so you now have the percentage rounded up to the nearest whole number.Remove the decimals by clicking twice

You now have a list of percentage increase/decrease in each employee’s sales over a one-week period.

You will find it is easier to see, analyze, compare, and trace their performances over the course of a few weeks, months, or even years.

In this tutorial, we showed you how to calculate percentages in Google Sheets.

We agree there are a host of other situations where percentage would need to be calculated, but we assume these three examples would be enough to give you a basic idea of the possibilities you have, thanks to the ‘Format as percent’ feature.

We encourage you to apply this small convenience to your daily data processing needs and hope it helps you get your work done at least a small percent quicker.

Other Google Sheets tutorials you may like: