How to Calculate Percentage of Sales in Excel (5 Suitable Methods)

Calculating the Percentage of sales is a very important issue for companies to keep a track record of their business. Microsoft Excel is a very good tool to store any data and calculate various types of percentages from that data.

Here in this article, I am showing how to calculate different types of percentages of sales from the data stored by a business group.

Table of Contents hide

Download File

How to Calculate Percentage of Sales in Excel

1. Calculating Percentage of Sales of Each Item with Respect to the Quantity Produced

Method 1: Calculating Percentage of Sales by Directly Entering the Formula

Method 2: Calculating Percentage of Sales by Taking Help from Excel Toolbar

2. Calculating Percentage of Sales of Each Item with Respect to the Total Sale

Method 1: Calculating the Percentage by Directly Entering the Formula

Method 2: Calculating the Percentage by Using Another Cell as Absolute Cell Reference

3. Calculating Percentage of Sales of Each Item Maintaining a Specific Criteria

4. Calculating the Increased or Decreased Percentage of Sales of Each Item

5. Calculating the Target Number of Sales for Achieving a Specific Sales Percentage

Conclusion

Download File

How to Calculate Percentage of Sales in Excel

Let us have a look at the data set first. We have the sales record of January 2021 of a company named Sunflower group. We have three columns A, B, and C containing the Item Name, Quantity Produced, and the Number of Sales respectively.

1. Calculating Percentage of Sales of Each Item with Respect to the Quantity Produced

First of all, let us assume we want to find out the percentage of sales of each item with respect to the number of quantities produced.

You can execute this in two ways.

Firstly, by directly entering the percentage formula (Number of Sales/Quantity Produced*100) in the cell.

And secondly, by only entering the ratio in the cell and taking help from Excel Toolbar.

Method 1: Calculating Percentage of Sales by Directly Entering the Formula

First, select the first cell of the column where you want to get the percentages. Here I have named column D as the Sales Percentage and selected the first cell of it, D4.

Then write down the direct percentage formula = C4/B4*100. Then click Enter. You will find the sales percentage noted there. Look at the image below.

Now to fill all the remaining cells of the column with the percentages, move your mouse cursor on the rightmost bottom corner of the cell and drag the Fill Handle (The small Plus(+) sign) through the column. Or double-click on the Fill Handle.

The formula will be copied down to the whole column and every cell will have the respective Sales Percentage. Like cell D5 will have = C5/B5*100, cell D6 will have = C6/B6*100, and so on. Look at the image below.

Method 2: Calculating Percentage of Sales by Taking Help from Excel Toolbar

You can Calculate the Percentage of sales of the items in an easier way by taking help from Excel Toolbar. Like the previous method, select the first cell of the column and enter the formula there. But in this case, enter the dividing formula only. Do not multiply it by 100. Here I have again selected cell D4 and written =C4/B4.

Like the previous method, drag the Fill Handle (the small Plus(+) sign) from the rightmost bottom corner of the cell through the whole column. Or just double-click it. You will find all the cells of the column filled with the same formula. Like the cell D5 has =C5/B5 C6 has =C6/B6 and so on.

Then select the whole column. You can do it by either selecting the first cell and dragging the mouse over the rest cells or by selecting the first cell and pressing Ctrl + Shift + Down Arrow.

Now in the Excel Toolbar, go to the Number Option from Home tab. Home>Number.

Click it. You will find several options, From the options, choose Percentage (%). You will find all the cells containing the required Sales Percentages.

2. Calculating Percentage of Sales of Each Item with Respect to the Total Sale

Now suppose you want to find out the sales percentage of each item with respect to the total sales. How can we achieve that? Here I am showing two easy ways.

Method 1: Calculating the Percentage by Directly Entering the Formula

Here we will use the SUM function of Excel. Using the SUM function is very easy. It takes a range of cells as an argument and gives their numerical sum as output.

Go to the first cell of the column where you want to have the sales percentages. Then put a formula like this, Number of Sales / Sum of Number of Sales.

Here I have created a new column E namely Percentages w.r.t Total Sales and selected the first cell of it, E4. Then put the formula =C4/SUM($C$4:$C$10)

Note: You have to write the Absolute Cell References of the cells $C$4:$C$10. Because when we drag the Fill Handle to copy the formula through the column, we want this to be fixed, not to increase.

Then drag the Fill Handle to copy the formula through the whole column.

Next, select the whole column and choose the Percentage(%) option from the Home>Number option from Excel Toolbar. Just like the step shown in method 2 of section 1.

You will have the whole column filled with the required percentages.

Method 2: Calculating the Percentage by Using Another Cell as Absolute Cell Reference

In lieu of directly using the SUM function in the formula, you can use another cell to note down the sum. And then use the Absolute Cell Reference of that cell in the formula. In this case, I have used the cell C12 to note down the sum of total sales =SUM(C4:C10)

Then I use the Absolute Cell Reference of this cell in column E. E4=C4/$C$12. And then dragged the Fill Handle to fill the rest of the cells.

Next I selected the whole column and chose the Percentage(%) option from the Home>Number option from the Excel Toolbar. Just like the step shown in section 1.2. All the cells were filled with the required Sales Percentage.

3. Calculating Percentage of Sales of Each Item Maintaining a Specific Criteria

If we look at the dataset again, we will find that there are items with different numbers of quantities produced.

For example, Full Sleeves produced 1200 pieces, Half sleeves produced 1000 pieces, T-Shirts produced 1800 pieces, and so on.

Now if the Chief of the company wants to know the Sales Percentage of each item which was produced more than a specific number, for example, 1400, Then what to do?

Quite simple. We will use Excel’s IF function here. The IF function takes three arguments, a criterion, a value if the criterion is true, and a value if the criterion is false. Here I select the first cell of a new column, column F, and set the function =IF(B4>1400,C4/B4,"N/A"). It means if C4 (Quantity Produced) is greater than 1400, it will have the value C4/B4 (Number of Sales / Quantity Produced). Otherwise, it says N/A (not applicable).

Now I drag the Fill Handle, then select the whole column and choose Percentage(%) from the Home>Number menu. The whole column will be filled with the required Sales Percentage where necessary and N/A elsewhere.

4. Calculating the Increased or Decreased Percentage of Sales of Each Item

Now let us assume we have another column in the dataset, which contains the number of sales of each item one year earlier, January 2020.

Now the Chief of Sunflower Group wants to make an analysis on the effect on sales numbers due to the COVID-19 pandemic, and therefore, know the increase or decrease of the percentage of sales of each item between January 2020 and January 2021. So, how to do that?

Very easy. Take a new column, select its first cell and enter a formula like this =(Number of Sales in January 2020 – Number of Sales in January 2021) / Number of Sales in January 2020. Here I am taking a new column E, selecting its first cell, and entering a formula =(D4-C4)/D4.

Now I drag the Fill Handle, then select the whole column and choose Percentage(%) from the Home>Number menu. The whole column will be filled with the required increase or decrease percentage. Here a positive number means a decrease in percentage and a negative number increase of percentage.

5. Calculating the Target Number of Sales for Achieving a Specific Sales Percentage

Finally, the Chief of Sunflower Group takes a strict decision. At any cost, the percentage of sales must reach a specific value, let’s say 95%. He wants the target number of sales of each item to reach that specific value. Now, how can he get that?

The required formula will be, Target Number of Sales = Quantity Produced * Percentage

So, take a new column, select its first cell and enter this formula. Here I am taking a new column F, select its first cell and enter a formula =B4*95%

Now I drag the Fill Handle or double-click it. The whole column will be filled with the required total number of sales.

Conclusion

By following the above methods, one can calculate various types of sales percentages for one’s business and also carry other operations on it. This is really a very useful tool in our daily work schedule. Do you know any other option? Let us know in the comment section.

How to Calculate Percentage of Sales in Excel (5 Suitable Methods)

Download File