Being able to display your data as percentages and calculate changes is a very useful skill for all Google Sheets users to develop.
In Google Spreadsheets, it is very easy to analyze your data and calculate percentages with your data.
In this tutorial, I will show you how to format as a percent, calculate the percentage of a total, and calculate percent change in Google Sheets.
Table of Contents
How to Format as Percent
If your data is already a percentage but it is not displaying as percentages, you can easily add the percentage symbol to your cells by formatting it as a percent.
Here’s how this is done:
- Highlight the cells that you want to change to a percentage
- In the toolbar menu select the Format as percent option
This will let Google Sheets know that your data is supposed to be a percentage so it will take your data and multiply it by 100 and add the percentage sign
How to Calculate Percentage of Total
Next, I’m going to show you how to calculate the percentage of a total in your spreadsheet.
In this example, I have a sheet that contains data with sales revenue per T-Shirt color.
I want to analyze the data and with my spreadsheet calculate what percentage of the total sales each T-shirt color is.
The formula to calculate the percentage of total revenue for each T-shirt color would be:
Sales Revenue of T-shirt Color/Total Revenue * 100
So the first thing we need to do is get the total revenue by using the SUM function to sum together all the sales.
Here is how to calculate the percentage of total with this example:
1. Use the SUM function to sum together all the sales revenue. In the example, I will use “=SUM(B2:B6)” to get the total sales revenue
2. Take your sales revenue of each t-shirt color and divide it by the total revenue. In this example, I use “=B2/$B$7”.
3. Copy and paste this same formula down to the other rows that you need to calculate for.
4. Highlight all the cells and select the Format as percent tool
You have now just calculated the percentage of sales that each T-shirt is contributing to the total revenue.
This will make it easier for your to analyze your dataset and understand your numbers.
How to Calculate Percent Change
Another useful calculation that you may want to make is to calculate the percentage change.
This will allow you to see the percentage increase or decrease over time.
To learn how to do with we will use the same data as the previous example, except this time we will add in another month of sales so we can calculate the change from month 1 to month 2
The formula to calculate this percent change in this example would be:
Month 2 Sales Revenue – Month 1 Sales Revenue/ Month 1 Sales Revenue * 100
Here are the steps to do this calculation in Google Sheets:
- In the cell where you want to calculate the percentage change, enter the formula above but with your cell references. In this example, it is “=(C2-B2)/B2”. Make sure you have the parenthesis in your formula so you are doing the right order of operations with your math
2. After you have entered the formula press Enter on your keyboard and then copy and paste the formula down to the other rows
3. Next you will need to highlight all of the cells where you are calculating the percentages
4. In the top toolbar menu select Format as percent
5. You have now calculated the percentage of sales increase or decrease over time by comparing month 1 to month 2.
This should now allow you to analyze and compare your data easier so you can see how you are performing over time.
This is just one example, but you can apply this calculation to many different scenarios.
In this tutorial, I have shown you a couple of different ways to calculate percentages depending on the data you are trying to view.
There are other things you might want to calculate, but using these examples as a starting point should help you have a grasp on how to do other percentage calculations.
Understanding how to analyze and compare data in your spreadsheets is a powerful skill that can help you make data-driven decisions.
So make sure to take the time to master this, because it will be very useful if you are a regular spreadsheet user. Be sure to watch the video if you are still needing help with this.