Calculating Percentage and Discount Percentage in Excel
Calculating percentages (%) on the total and discount percentage are two common mathematical problems in many fields. To calculate such percentages, you don’t need to use Excel functions. So, how can you calculate percentages quickly and accurately? This article will guide you on how to calculate percentage values quickly and accurately.
1. Calculating the percentage ratio of each element in a total
Step 1: Apply the calculation
This is one of the most common ways to calculate percentages that users often encounter. That is to calculate the ratio of one part in the total. You can see it more clearly in the example table below.
A total consists of 3 elements A, B, C, and we calculate the percentage of each part A, B, C.
To get the percentage of A, divide the element A by the total. When you apply this formula, you have calculated %A.
Then you can copy the calculation formula down by selecting the cell containing the calculation, then placing the mouse pointer in the bottom right corner. Next, click and drag down or simply double-click. Then you will get the result as shown below:
Next, you perform the same calculation for the percentage for column B and C. You can record the first formula for these two columns. However, if there are too many elements in the total, this method takes quite a lot of time. Therefore, we will teach you how to copy the formula horizontally.
First, select the cell containing the formula and place the mouse cursor right after the total denominator that needs to be kept fixed.
Then press F4 once to fix this parameter. That means when copying the formula, the parameter cell containing this total will be fixed, not changing horizontally like the numerator. Please refer to the image below for a better understanding.
After copying horizontally as above, you only need to copy vertically to complete the task. The method is the same as above, and you will get the result as follows:
Step 2: Round to Percentage
After completing the calculations, the results will be in decimal format, not the familiar percentage format. Therefore, the next step is to convert them to percentages.
First, highlight the entire range of data that needs to be converted to percentages. Then, go to the Home tab, look in the Number section, and select Percentage.
You will see the percentage displayed with two decimal places below. After selecting, the result will be similar to the image below.
2. Calculate Discount Percentage
This is also one of the real-life applications of percentages that we often encounter. In the example below, we will calculate the selling price of items after a discount.
You can apply the following formula:
Selling price = Actual price – (Actual price x discount percentage)
So, according to the example below, we will have the formula = C2 – (C2*D2). After entering the formula, you can calculate the selling price of items. Copy the formula down to the rows below to get the results as shown in the image below.
In conclusion, we have introduced two basic methods for calculating percentages in Excel. These skills are essential for anyone working with data analysis or financial calculations. By following the steps outlined in this guide, you should now be able to easily calculate percentages and convert decimal numbers to percentages in Excel. We hope this has been helpful and wish you success in your future endeavors.