Percentage formula in Excel

What is Percentage?

A percentage is a number denoted in terms of the fraction of 100. The symbol of percentage is ‘%’ and is displayed after a number. For example, 10 percent or 10% is written as 10/100. The percentage has no units.

The percentage has made the calculations even easier. It is sometimes difficult to represent a part infraction, such as one-twelfth, two-third, etc. But, it is easy to represent the fraction in percentage. Hence, the percentage is used everywhere in our daily life.

For example,

The fraction value 2/5th in percentage is written as 2/5 x 100 = 40%.

Percentage Formulas

A formula is an equation that performs the calculations. The calculations by humans are not accurate and quick. The calculations in excel are similar to the calculator. The percentage formulas are discussed below:

Selling price is the price at which the product is sold, while cost price is the price at which the seller purchased the product. Profit percentage is the profit earned from the sale of a product.

Loss is incurred when the cost price is more than the selling price. It means that the seller sold the product at lesser prices compared to the price they purchased.

We can also use the profit % formula to find the loss percent. The difference is that it will come in negative.

Where,

A and B are the commodities whose difference percentage is to be calculated.

Average is calculated as the sum of all the commodities divided by the number of commodities. We can also call it as a mean.

Let’s first discuss some common features of excel.

• Data Sorting
  We can easily sort the data in excel by just few clicks.
• Built-in formula
  Excel has various built-in formulas that are visible on the tool bar present at the top. We can also compute the formulas on the formula bar.
• Data filtering and different charts It also the ability of data filtering and different charts, such as pie charts, bar charts, etc.

Excel provides us various ways to use the percentage formula, such as difference between two values, total percentage, percentage decrease, percentage increase, etc., as discussed above. We will discuss all the related examples for a better understanding.

Let’s first consider the steps to open an excel file on the computer if anyone does not know.

Steps to open an excel file

Step 1: To open the excel file on your personal computer or desktop, right click on the home page -> select New -> Click on Microsoft Office Excel Worksheet, as shown below:

Step 2: Specify the suitable name for the file and click on the new created icon of the Excel present on the home page.

Step 3: Excel is now ready for use.

Percentage formula

Excel has a built-in percentage symbol present on the tool bar. It is generally resent at the center of the toolbar, as shown below:

To convert a decimal value into a percentage, specify the decimal value in an excel block -> click on the block -> click on the percentage symbol (%) present on the toolbar. The corresponding decimal value will be converted into percentage value.

Or

Click on the block containing decimal value and press Ctrl + Shift + %. The corresponding decimal value will be converted into percentage value.

Examples based on the percentage formula

Let’s discuss some examples of using percentage formula in excel with various methods.

Example 1: Calculating the total percentage of five students in three subjects.

The data of 5 students in three subjects, English, Mathematics, and Science is shown below:

The steps to calculate the percentage of these three students are as follows:

Step 1: Click on the first block of the percentage column, as shown below:

Step 2: Drag the mouse on the formula bar and type ‘= (click on the column number D + click on the column number E + click on the column number F)/300.’

It will be specified as ‘=(C:C+D:D+E:E)/300%.’ Here, C:C, D:D, and E:E are the automatic specified name of the columns.

The percentage symbol will automatically convert the data value into the percent. We can also write the above formula as :

The formula of percentage is: ‘==(C:C+D:D+E:E)/300* 100’

It will appear on the formula bar, as shown below:

Step 3: Press Enter. The percentage value on the first block of the discount column will appear.

Step 4: Click again on the first block of the percentage column -> click on the bottom-right corner of that block, as shown below:

Step 5: Drag that point to the fifth block of the same column (Percentage), as shown below:

The percentage value will be computed automatically. Similarly, we can calculate the percentage of many students of a class by a single click.

We can also use the direct method to calculate the percentage.

Example 2: To calculated the discount percentage.

The cost price and selling price of the three products (sugar, wheat, and maize) is shown below:

We need to compute the discount percentage. We can insert the data under any column. The steps are as follows:

Step 1: Click on the first block of the Discount % column, as shown below:

Step 2: Drag the mouse on the formula bar and type ‘= (1 – click on the column number E -click on the column number D)/ (again click on the column number D) * 100.’ It will be specified as ‘(= (E:E-D:D)/D:D*100).’ Here, E:E and D:D are the automatically specified name of the columns.

The formula of discount percentage is:

It will appear on the formula bar as the image shown below:

Step 3: Press Enter. The percentage value on the first block of the discount column will appear.

The formula will also appear on the specified block, but as soon as we press Enter, it will be replaced by the corresponding value.

Step 4: Click again on the first block of the percentage column -> click on the bottom-right corner of that block, as shown below:

Step 5: Drag that point to the third block of the same column (Discount %), as shown below:

The percentage value will be automatically computed.

Similarly, if there are multiple numbers of data rows, we can scroll upto the desired data. It means that we can compute percentage of hundred of data just by a single click. We are not required to calculate the percentage of different products separately using the calculator.

Example 3: To find the difference percentage between the two commodities.

Here, we will find the percentage difference between the two commodities. The data is shown below:

The steps to compute the percentage are as follows:

Step 1: Click on the first block of the ‘Difference %’ column, as shown below:

Step2: Drag the mouse on the formula press and type ‘= (Click on the column D/Click on the column C – 1) *100.’ It will appear on the formula bar as ‘= (D:D/C:C – 1) *100.’ We can also write it as ‘= (Click on the column D – Click on the column C)/Click on the column C x 100.’

The formula of discount percentage is:

Note: The percentage symbol in front of the difference value will not appear by default.

In case, we want the percentage symbol to appear in front of the difference% values, follow the below steps:

Right click on the difference% values -> select the Format cells option -> Click on the Percentage option on the left side of the window -> and click Ok.

The percentage symbol will appear. The percentage option selected here will automatically convert the difference value into the percentage.

‘= (D:D/C:C – 1)’

So, we need not to multiply the difference value by 100 to convert it into percentage.

The formula on the formula bar will appear as:

Step 3: Press Enter. The percentage value on the first block of the Difference% column will appear.

The formula will also appear on the specified block, but as soon as we press Enter, it will be replaced by the corresponding value.

Step 4: Click again on the first block of the percentage column -> click on the bottom-right corner of that block, as shown below:

Step 5: Drag that point to the second block of the same column (Difference %), as shown below:

The second value of the difference percentage is negative (-1.97%). It means that the price of graphics card has decreased as compared to the previous year. Hence, excel is very useful to quick predict the profit, loss, increase or decrease of price as compared to the overall data.

Example 4: To find the profit percentage and a loss percentage of the seven products.

The data of seven products is shown below:

The steps to compute the percentage are as follows:

Step 1: Click on the first block of the ‘Profit/Loss %’ column, as shown below:

Step2: Drag the mouse on the formula press and type ‘= (Click on the column D/Click on the column C – 1) *100.’ It will appear on the formula bar as ‘= (D:D/C:C – 1) *100.’ We can also write it as ‘= (Click on the column D – Click on the column C)/Click on the column C x 100.’

The formula of discount percentage is:

If the percentage value is positive, it means there is a profit. If it is negative, the seller has incurred a loss.

The formula on the formula bar will appear as:

Step 3: Press Enter. The percentage value on the first block of the Profit/Loss% column will appear.

The formula will also appear on the specified block, but as soon as we press Enter, it will be replaced by the corresponding value.

Step 4: Click again on the first block of the percentage column -> click on the bottom-right corner of that block, as shown below:

Step 5: Drag that point to the seventh block of the same column (Profit/Loss %), as shown below:

The above table shows the profit/loss percentage of the seven products. But, how can we conclude which product was sold at a profit and which at a loss? So, let’s discuss it.

If the profit/loss percentage is positive, the product was sold at a profit, and if the percentage is negative, the product was sold at a loss. Thus, we can conclude that the keyboard and bulb were sold at a 5.6 and 5 percent loss. Similarly, pen, jar, lamp, mouse, and speaker were sold at a profit of 60, 27.3, 23.1, 25, and 20.8 percent, respectively.