In this article, I have put together a simple tutorial on how you can calculate percentages while working in your Google spreadsheet. It is nothing but a series of steps and formulas and, I hope you will benefit from my article.
So, let’s start then!
Calculating Percentages in Google Sheets: How to do Formatting
One of the easiest and common ways of calculating a percentage value in Google Sheets is formatting the cell in your spreadsheet. This does not only give you an answer in percentage, but, actually converts the value into a percentage.
This is how you can achieve this:
I have an example below where I have a list of test scores of students and, now I need to know what percentage their scores are out of a hundred.
Now, to find out the percentage that each student has scores; all I need to do is, divide the values in column B by the values in column A respectively and get those answers in column C. Afterwards, I will format the cells in column C to convert the values into percentages.
- Select cell C2 and insert the formula “=B2/A2”.
- Press “Enter” key to get the results.
- Get answers for the rest of the data, using the auto fill option.
- Select the entire column C and find the toolbar at the top of your screen which is right beneath the top menu bar.
- Select the percentage icon option (%) which converts values into percentages.
- You see the icons which look like arrows both pointed at the opposite directions from each other? You can use those icons to increase or reduce the decimal points of a value.
- You need go through this process every time you need to convert a value into a percentage.
How to Calculate Percentage Using a Common Total in Google Sheets
There could be instances where you have to find out the percentage out of a total for a data and, that total is base for your percentage calculation. This is important for when you need to know the percentage, to have an idea of the proportion out of the total.
Here is a second example for you, where I have the total amount of money spent and the money spent on every individual item which led up to the total money spent. What would I do if I needed to know the percentage spent on each item? Here is how I can achieve this:
As you can see in the case above, the total money spent is $24.1. In column C, I will find out the percentages of money spent on each item of shopping.
- Select the first cell in column C.
- I will apply the same formula that I used for my previous example. Only this time, for my denominator, I have a common value in cell B7. Therefore, this cell would be the cell for my absolute reference. And, as with the rule with absolute references; I will insert the dollar sign ($) when I give my formula.
The function of the dollar sign is to make sure that the value of the reference being used does not change when repeated for other cells. So, by using this formula, I can copy the formula for the rest of my data and the value of the total will remain the same.
- Press the “Enter” key and, get the results for the rest of the data.
- Use the toolbar to decrease the decimal places of each value.
- Apply the same percentage format to all the values in column C.
Calculating Percentages: Using a Common Total and SUMIF Function
In my earlier example, my data consisted of items which occur just once throughout the list.
There can be instances where the name of an item occurs more than one time in the data. Now suppose I needed to know the total money spent on just that item; meaning, I want to know the total proportion of money spent on a particular item which has been bought more than once.
As shown in my example, the item “Apple” occurs three times in my data. Now, consider that I need to know the total amount spent on just apple out of the total amount of money spent.
To do this, I would need to apply the SUMIF function from Google Sheets. The syntax of the formula is given below:
=SUMIF (range, criteria, sum_range) /Total
The SUMIF formula ensures that only the total amount that relates to one particular item in my data are divided by the total.
- Range – range is the range of cells which has the data.
- Criteria – criteria is that particular data which I want the summation of.
- Sum_range – sum_range is the cell range of values of my data.
So now, all I have to do is:
- Select a cell where I want my results of “Apple”.
- Insert the relevant formula to get the answer.
- Convert the cell to make the value a percentage value.
You can also extend this formula to get the same kind of results for each item; if those items appear more than once in your data as well. All you have to learn is applying this formula given below:
=(SUMIF(A2:A9,"item 1",B2:B9)+SUMIF(A2:A9,"item 2",B2:B9))/$B$8
How to Calculate Increase and Decrease in Percentage in Google Sheets
I often need to calculate the percentage increase and decrease of values and to do that, there is a very simple formula you can use.
Suppose certain course fees at a university have either increased or decreased between two years. This is how I would calculate the percentage increase and decrease if I needed to:
- Divide the difference in the two values by the first value. This is a simple formula when it comes to calculating percentage differences: (“new value-old value”/”old value”).
- Convert this answer into a percentage value using the percentage function from the top toolbar.
There could be instances, where I need to calculate the percentage change in fees of each department compared to the fees of only one department (Department A). Then the value in cell B1 would be a common value in the calculation for finding the percentage change.
In that case, the cell B1 will be our absolute reference cell instead of a relative reference cell.
The value in an absolute reference cell remains the same when the formula is copied; whereas, the value in a relative reference cell changes as the formula is applied to the rest of the data.
So now, the formula for finding out the percentage change will be:
This will ensure that the data for the “old value” does not change and is the same for the calculations for all the departments.
The final result:
Calculations with Totals and Percentages
You can also do calculations which involve percentages and totals. For instance, you may have cell A1 with a total numerical value (5,000) and cell B1 with a percentage value (35%).
You can either multiply these figures to know what is 35% of 5,000 or divide these figures to know how much is 35% of 5,000 made up of.
- Multiply – =A1*B1 (value*percentage)
- Divide – A1/B1 (value/ percentage)
It is helpful to remember that since essentially, percentage means “out of hundred”, you can also use decimal places write a percentage value. For instance, 6% can also be written as, 0.06 or, 60% can be written as 0.6. You can sue them in the same percentage calculations.
Well, I hope my tutorial on how to calculate percentage in Google Sheets helped you.
Best of luck!
Read More Google Sheet Guide Articles On: