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

Percentage of Sales in Excel.

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.


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.

Dataset in Excel


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.

First cell of a column selected in Excel

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.

Formula written in Formula Bar in Excel

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.

Dragging through a column by Fill Handle


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.

Formula written in formula bar in Excel

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.

Dragging through Fill Handle in Excel

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.

Selecting a whole column in Excel

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

Number menu in Excel Toolbar

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

A formula copied to whole column in Excel


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.

Formula written in formula bar in Excel

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

Dragging through Fill Handle in Excel

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.

A Formula copied to whole column in Excel


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)

An absolute cell reference in Excel

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.

Formula with an absolute cell reference in Excel

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).

Formula written in formula bar in Excel

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.

A formula copied to whole column in Excel


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. 

A dataset in Excel

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.

Formula written in formula bar in Excel

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.

A Formula copied to whole column in Excel


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%

Formula written in formula bar in Excel

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

Formula copied to all cells in a column in Excel


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. 


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo