ExcelInsider Team
If you are the owner of a large retail store, you might have a huge variety of items for sale. Not every item is sold in the same amount. To understand the trends and whether you need to remove some items in the future to boost sales of more profitable items, you must calculate the percentage of sales. In this article, we will learn how to calculate the percentage of sales in Excel.
➤ Enter the following formula in the C2 cell to calculate one sales value/item’s percentage of sales, and autofill the rest of the column to calculate all of them.
=B2/SUM($B$2:$B$13)
➤ Replace B2 with the current sales value, and B2:B13 with the whole range of sales values.
While calculating the percentage of sales is a simple calculation, Excel provides us with numerous ways to do it. To make full use of this flexibility, learn all the methods we are going to provide in this article.
Determine the Percentage of Sales with Excel SUM Function & Basic Calculations
We have a table with 12 items and their sales data. We will find the percentage of sales for each item in the table. The formula we will use for this method is the simplest one in this article, and it works for this dataset with no downsides.
➤ First, add a new row to calculate the total sales. In the B14 cell, enter the following formula:
=SUM(B2:B13)
➤ Now, add another column for Percentage.
➤ Write the following formula in the C2 cell and autofill till C14:
=B2/$B$14
➤ However, the percentages are shown in decimal. To change that, select C2:C14 and press the following keys together:
Ctrl + Shift + %
➤ Now the percentages show up as expected.
Note:
Instead of using the cell reference of $B$14 for dividing, you can write SUM($B$2:$B$13) instead. The new formula to fill would be =B2/SUM($B$2:$B$13) in that case. However, this is a slower method as Excel calculates the same value again and again.
PERCENTOF Function to Calculate the Percentage of Sales in Excel
Excel provides a function to calculate the percent of a total amount. We can use that function to calculate the percentage of sales. In this case, we are using the dataset modified from the previous method with the total amount in the B14 cell already. Follow the steps below to calculate the percentage:
➤ Enter the following formula in the C2 cell and autofill till C14:
=PERCENTOF(B2,$B$14)
Note:
Like the previous method, the $B$14 cell can also be replaced by SUM($B$2:$B$13) here as well.
Calculating the Percentage of Sales from a Table
In the previous methods, the data were formatted like a table, but they were not in a table according to Excel’s definition of a table. If we convert the data range to a table, we can use table-related references instead. That way, even if we add more data later, the calculation will automatically be done.
➤ Select the full data range, and press Ctrl + T .
➤ Press OK to create the table.
➤ Now, either of the following formulas in the C2 cell, and the rest will automatically be filled:
=[@Sales]/SUM([Sales])
=PERCENTOF([@Sales],[Sales])
➤ Select C2:C13, and go to the Home tab of the ribbon. In the Number group, select the percentage (%) icon to convert the decimal cells to percentage ones.
Using Pivot Table to Show Percentage of Calculation in Excel
Pivot tables in Excel can automate a lot of simple calculations. We can use that to calculate the percentage of sales as well. Follow the steps below to calculate the percentage of sales in Excel:
➤ Select the data range, and go to the Insert tab on the ribbon. Now, select PivotTable and press OK in the new window.
➤ Select the items from the PivotTable Fields panel.
➤ Right-click on a sales value, and select Show Values As > % of Column Total.
➤ Now the pivot table should show the percentage of sales.
Frequently Asked Questions
How to calculate the sales formula in Excel?
It depends on the sales type, as there are a lot of factors to consider. Usually, if you have sales data from different outlets, for example, you will use the SUM function to calculate the sales. Here’s an example of how to do that in Excel:
=SUM(A1:A10)
This formula adds the sales from the A1:A10 range.
What is the formula for total sales??
The total sales can be calculated using the following formula in Excel:
=A2*B2
Here, A2 is the cell where the number of units sold is recorded, and B2 contains the price of each unit. Excel will return the total sales revenue after you press Enter. For example, if you have 100 in the A2 cell, representing the fact that you sold 100 units of product, and B2 contains $5 as the price of each unit, Excel will return $500 as the total sales revenue.
How to get the percentage of sales vs target?
The formula for calculating the percentage of sales vs target is as follows:
=A2/B2
Here, A2 is the actual sales, and B2 is the sales target. However, this will give you the result in decimals. You have to press Ctrl + Shift + % in the results cell to convert the answer to a percentage.
How to calculate sales percentage off?
If you are giving a percentage off the sales, here is the formula to calculate the final price:
=A2*(1-0.2)
In this formula, we are giving a 20% discount on the product’s price. The A2 cell contains the product’s price, and 0.2 is the discount percentage written in decimal.
How to calculate sales percentage vs last year?
To calculate the sales percentage against last year, you can use the following formula:
=(B2-A2)/A2
Here, B2 represents the sales of the current year, and A2 represents the sales of the previous year. Change the results cell format to percentage; otherwise, the result will show up as decimals.
Wrapping Up
In this article, we have learned four methods of calculating the percentage of sales in Excel. Each of the methods shines in its own merit, and you must choose the one that fits your purpose the best. The practice Excel book contains all the methods so that you can see them in action by yourself. Stay tuned for more Excel tutorials.
