How to Calculate Percentage of Sales in Excel (4 Examples)

Percentages are one of the most important types of mathematical operations in our lives. Most people understand this operation and use it in their lives. Sales percentages are similar types of operation which helps us to understand how the sales of different commodities of the product are going on. If you are curious to learn how you can calculate the percentage of sales in Excel, then this article may come in handy for you. In this article, we discuss how you can calculate the percentage of sales in Excel with elaborate explanations.

How to Calculate Percent of Sales in Excel: 4 Examples

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. Below, you have an example following which you would be able to calculate the percentage of sales in Excel.

Steps

• To begin with, select the cell E5 and enter the following formula:

This formula will calculate the percentage of sales and add a percentage sign with it.

• Then drag the Fill Handle to cell E11.
• We now have the sales percentages of the products in the range of cells E5:E11.

How to Calculate Percentage of Sales in Excel: 4 Suitable Examples

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 Number of Sales respectively.

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

You can Calculate the Percentage of sales of the items more easily by taking help from the Excel Toolbar.

Steps

• We have the Sales value of how much quantity is produced and the number of sales in the range of cells A4:D11.

• 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 E5 and entered the following formula:

• Then drag the Fill Handle to cell E11.

• Then select the whole column and then go to Home > Number group > Select Percentage.

• Then we will notice that the range of cell E5:E11 is now filled with the sales percentage values.

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

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.

Steps

• 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.
• Then selected the cell E5 and put the formula:

• Then drag the Fill Handle to cell E11.

• Then go to the Home tab > Press Number group > Select Percentage from the dropdown.

• Now you can see that the range of cell E5:E11 is now filled with the sales percentages with respect to the Total Sales value.

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

If we look at the dataset again, we will find that there are items with different numbers of quantities produced. Now if the Chief of the company wants to know the Sales Percentage of each item that was produced with more than a specific number, for example, 1400, Then we need to follow the below example. We are going to use the IF function in this example.

Steps

• Select the cell E5 and enter the following formula:

• Then drag the Fill Handle to cell E11.
• We can see that the values in cell E11 are in Number format.
• We need to change this number format to the Percentage format.

• Select the whole range of cell E5:E11.
• Then go to the Home tab > Press Number group > Select Percentage from the dropdown.
• After pressing the Percentage command, you will notice that the sales values are now showing in the percentage format instead of the Number format.

4. 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. After this, you need to follow the below example to achieve that specific percentage value.

Steps

• Select the F5 and enter the following formula:

After entering this formula, we can see that the value of the target sales is now showing in cell F5.

• Then drag the Fill Handle to cell F11.
• Now we can see that the range of cell F5:F11 is now showing the target sales value.

How to Calculate Sales Percentage Increase or Decrease

Now the Chief of Sunflower Group wants to analyze 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

Steps

• 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.
• For this, we selected cell F5 and enter the following formula:

After entering the formula, we will notice that the Increased value of sales is now showing in cell F5.

• Then drag the Fill Handle to cell F11.
• We will see that the range of cell F5:F11 is now filled with the percentage changed value of sales in each month.

How to Calculate Monthly Sales Percentage

In the previous method, we calculated the percentage value of sales with various criteria. Now we are going to calculate the percentage of the monthly sales growth rate. This parameter will help us to have an insight into how the sales performance is going from month to month.

Steps

• Select the cell D6 and enter the following formula:

• Then drag the Fill Handle to cell D16.
• Now we can see the monthly percentage change of the sales value in the range of cells D5:D16.

• As we can see that the percentage values are actually in number format, we need to reformat it to Percentage format.
• Go to the Home tab > Number group > select Percentage from the drop-down menu.

• After selecting the Percentage format, we can see that the range of cell D5:D16 is now filled up with the sales percentage values.

💬 Things to Remember

• The output will always have to be in percentage format, so we need to reformat the output from Number to Percentage every single time.
• When calculating the percentage change, beware of the formula. Always remember to subtract the previous value from the later value and then divide this subtraction value by the previous value. Doing anything else will result in a faulty outcome.

Download Practice Workbook

Conclusion

To sum it up, the issue of how we can calculate the percentage of sales in Excel using 4 separate examples.

For this problem, a workbook is available to download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciated.

<< Go Back to Sales | Formula List | Learn Excel