In our daily life, we often need to calculate savings percentages in Excel. Especially when there are a lot of sales going on, we get a lot of discounts on various products. By using Excel, we can easily calculate the savings percentage of any product. In this article, we will learn 3 simple methods to calculate savings percentages in Excel. So, let’s start the article and explore these methods.
How to Calculate Savings Percentage in Excel: 3 Simple Methods
This section of the article will teach you three simple ways to calculate savings percentages in Excel.
Not to mention that we have used the Microsoft Excel 365 version for this article; you can use any other version according to your convenience.
1. Utilizing Percent Style Button
Utilizing the Percent Style button is one of the easiest ways to calculate savings percentage in Excel. Let’s say, we have the “Discounted Price of ABC Store” for some of their Products. Our goal is to calculate the savings percentage. Let’s follow the steps mentioned below to do this.
Step 01: Calculate Fraction of Discount
- Firstly, create a new column named Savings Percentage as shown in the following image.
- After that, enter the following formula in cell E5.
=(C5-D5)/C5Here, cell C5 refers to the cell of the Price column, and cell D5 indicates the cell of the Discounted Price column.
- Then, press ENTER.
As a result, a fraction of the discount for Smartphone will be available on your worksheet.
- Now, use the AutoFill option of Excel to get the rest of the outputs.
Step 02: Convert to Percentage Format
- Firstly, select the cells of the “Savings Percentage” column.
- Then, go to the Home tab from Ribbon.
- Following that, click on the Percentage Style option from the Number group.
Consequently, you will have the savings percentages for various “Products” as marked in the following picture.
2. Applying Keyboard Shortcut
Applying the keyboard shortcut is another smart way to calculate savings percentages in Excel. For instance, let’s say we have “Monthly Cost Data” of a person for 2 months. Our aim is to calculate the savings percentage by applying a keyboard shortcut. Let’s use the steps outlined below to do this.
Steps:
- Firstly, follow the procedures mentioned in Step 01 of the 1st method to get the following output.
- Following that select the cells of the “Savings Percentage” column and press the keyboard shortcut CTRL + SHIFT + %.
There you go! Your savings percentages will be available as demonstrated in the image below.
3. Using Number Format Feature
Using the Number Format option is one of Excel’s most efficient ways to calculate savings percentage. Let’s explore the steps to this in the following section.
Steps:
- Firstly, follow the steps used in Step 01 of the 1st method and you will have the following outputs as marked below.
- After that, select the cells of the “Savings Percentage” column and go to the Home tab.
- Then, click on the Number Format option from the Number ribbon group.
As a result, the Format Cells dialogue box will open on your worksheet.
Note: You can also use the keyboard shortcut CTRL + 1 to open the Format Cells dialogue box directly.
- Now, in the Format Cells dialogue box, choose the Percentage option.
- After that, you can add how many decimal places you want in the percentage format by clicking the increase and decrease buttons.
- Finally, click OK.
Therefore, you will have the savings percentage as demonstrated in the following picture.
How to Calculate Cost Percentage in Excel
While working in Excel, we often need to calculate the cost percentage. By calculating the cost percentage, we can understand what percentage of the total expenditures we are spending in each “Category”. We can easily identify if we are overspending or not. Using Excel, we can calculate the cost percentage by following some simple steps. Let’s follow the instructions outlined below to do this.
Steps:
- Firstly, use the following in cell D5.
=C5/SUM($C$5:$C$9)Here, cell C5 refers to the cost of “Food” in January month, and the SUM function will return the summation of the range of cells $C$5:$C$9.
- Following that, hit ENTER.
Subsequently, you will have the following output in cell D5, as marked in the image given below.
- Now, you can use the AutoFill feature of Excel to have the remaining outputs.
- After that, select the cells of the “Cost Percentage” column.
- Next, go to the Home tab from Ribbon.
- Finally, click on the Percentage Style button.
Consequently, you will have the cost percentages against each “Category” as shown in the image below.
How to Create an Annual Cost Savings Calculator in Excel
An annual cost savings calculator can help us keep track of our expenses. Excel is quite a powerful software. In Excel, we can create an annual cost-saving calculator by following some easy steps. Let’s say we have the “Unit Cost Data” as our dataset. In the dataset, there are 2 options for expenses. We will calculate the annual cost savings between the 2 options. Let’s follow the steps to achieve this.
Steps:
- Firstly, create a table as demonstrated in the following image.
- After that, select the cells of the “Time Unit” column and go to the Data tab from Ribbon.
- Then, click on the Data Validation option from the Data Tools group.
- Now, choose the List option from the drop-down, in the Allow field.
- After that, click on the box of Source and select cells of range B18:B23.
- Finally, click OK.
As a result, a drop-down list will be available in each cell of the “Time Unit” column.
- Now, select the options demonstrated in the image below in the cells of the “Time Unit” column.
- Following that, enter the formula given below in cell F7.
=D7*INDEX($I$6:$J$11,MATCH(E7,$I$6:$I$11,0),2)Here, cell D7 indicates the cell of the Qty column, the range $I$6:$J$11 indicates the cells of the Time Unit List table, and cell E7 is the cell of the Time Unit column.
Formula Breakdown
- MATCH(E7,$I$6:$I$11,0) →The MATCH function gives us the location of a lookup value in a row or column.
- E7 → It is the lookup_value argument.
- $I$6:$I$11 → It indicates the lookup_array argument.
- 0 → It is the [match_type] argument.
- Output → 5
- INDEX($I$6:$J$11,MATCH(E7,$I$6:$I$11,0),2) → It becomes INDEX($I$6:$J$11,5,2). The INDEX function returns values from multiple ranges.
- $I$6:$J$11 → It is the array argument.
- 5 → This refers to the row argument.
- 2 → It is the [column_num] argument.
- Output → 12.
- D7*INDEX($I$6:$J$11,MATCH(E7,$I$6:$I$11,0),2) → It becomes D7*12.
- Output → 12.
- Then, hit ENTER.
As a result, the “Annual Qty of TV Subscriptions” will be available in cell F7 as shown in the following picture.
- Now, drag the Fill Handle up to cell F11 to obtain the remaining outputs.
- Now, use the following formula in cell G7 to calculate the “Annual Cost”.
=C7*F7Here, C7 and F7 cells refer to the starting cells of the “Unit Cost” column, and “Annual Qty” column respectively.
- Subsequently, press ENTER.
As a result, the annual cost of “TV subscriptions” will be displayed in cell G7.
- After that, drag the Fill Handle up to cell G11 to get the annual cost for the rest of the categories.
- Then, enter the following formula in cell G12.
=SUM(G7:G11)Here, the range of cells G7:G11 indicates the cells of the “Annual Cost” column, and the SUM function will calculate the “Total Cost” by adding these up.
- Next, hit ENTER.
Consequently, you will have the “Total Cost” in a year as shown in the image below.
- Now, follow the same steps and complete the table for Option 2 as shown in the image below.
- At this stage, create the “Annual Cost Savings” table as marked below.
- Following that, use the formula given below in cell D27.
=ABS(C27-C28)/MAX(C27:C28)The ABS function returns the absolute value of a number, and the MAX function gives us the maximum number from a range of numbers.
- Then, press ENTER.
Consequently, you will have the cost savings percentage as demonstrated in the following picture.
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.
Download Practice Workbook
Conclusion
So, these are the most common & effective methods you can use anytime while working with your Excel datasheet to calculate savings percentage in Excel. If you have any questions, suggestions, or feedback related to this article you can comment below.
Related Articles
- How to Calculate Error Percentage in Excel
- How to Calculate Cumulative Percentage in Excel
- How to Calculate Mean Percentage Error in Excel
- How to Calculate Remaining Shelf Life Percentage in Excel
- How to Calculate Percentage of Completion in Excel
- How to Calculate Absenteeism Percentage in Excel
- How to Calculate Productivity Percentage in Excel
- How to Calculate Variance Percentage in Excel
- How to Calculate Accuracy Percentage in Excel
- How to Calculate Grade Percentage in Excel
- How to Calculate Win-Loss Percentage in Excel
- How to Calculate SLA Percentage in Excel
- How to Calculate Percentage of Budget Spent in Excel
- How to Calculate Utilization Percentage in Excel
