Percentages play a fascinating role in our daily lives, helping us understand and express proportions in a simple yet powerful way. From calculating discounts during shopping to analyzing data in spreadsheets, they are everywhere! Ever wondered how percentages work and why they're so essential in everyday calculations? Well, allow us to break it down for you! A percentage is simply a portion of a whole expressed as a value out of 100. Take 25%, for instance – it's the same as 0.25 or 25 divided by 100. And guess what? Excel loves percentages too! You can easily use the percent sign (%) to represent them and enter them directly into cells or calculate them using formulas. Interested? Follow this guide and learn some amazing Excel Percentage tricks and learn how to add a Percentage to a number in Excel!
Method 1: Multiplication Method
The first method we will be exploring is the Multiplication method to learn how to add a percentage to a number in excel. This method is highly popular amongst stores, as it allows you to calculate the price during sales and discounts. Let’s see how it works through an example.
We have a store called VeloMax Bikes, and we have a habit of putting our items for sale so that everyone can enjoy our products. But manually calculating the new price of each product was getting tiring, so we decided to learn an Excel Percentage Trick to help us manage our sales quickly and efficiently. Here’s how it works
Step 1: Open Excel and simply load your Dataset sheet
Step 2: To obtain the Price After Discount we will be using the basic Excel Percentage Formula; Price x (1-Discount). Simply click on the cell where you want the Discounted Price, and enter the formula accordingly.and press enter.
Step 3: The Price After Discount will be displayed on our cell D2. With the help of the cursor, we will drag and copy the formula on the remaining cells.
There are times when we have to increase the price of our items as well due to several economical and business reasons. The formula can be altered to display increments in prices as well. Here’s how:
Step 1: On the same Excel sheet, we will replace the formula with a minor tweak. The “-” before discount will be changed into “+”, this is because there will be an increase in price. This is how the new Excel Percentage Formula would look like:
Price x (1+Discount)
Step 2: Drag and copy the new formula to the remaining cells to get a new price of every item in your inventory after price increment.
Tips:
Sign- Make sure you input the correct sign in each formula.
Equals sign- Many users forget to start their excel formulas with the “=”, resulting in errors. Always start your Excel Percentage Formula with an Equals to sign.
#REF! Error: Incase you come across the “#REF!”. As you work with the formula, double-check that you inserted it properly.
Method 2: Addition/Subtraction Method
Similar to the Multiplication method, Excel offers the Addition/Subtraction Method to its users to work with percentages. Let’s learn how to add a Percentage to a number in Excel using this simple and easy method.
Step 1: Go to Microsoft Excel, and open your excel sheet that holds the dataset.
Step 2: In this method, we will be using addition to calculate the new price after an increment. The formula used is as follow:
Price+(Price x Discount)
Step 3: Simply drag and copy the formula onto the remaining cells.
Step 4: Similarly, incase of price decrease, we will be replacing “+” with the “-”.
Price-(Price x Discount)
Step 5: Press “Enter” and copy the results to the remaining cells. Prices after discount of each item will be displayed
Method 3: Adding a Column of Numbers by a Percentage
Excel offers a convenient method to work with percentages effortlessly – adding a column of numbers by a percentage. With just a few clicks, users can calculate new salaries after increments or decrements, avoiding the need to manually enter formulas. Let's go through an example to learn more.
After every year end, it is a common practice that we increase the pay of our employees at VeloMax Bikes by 5%. Manually calculating the new salaries of each employee can be pretty time-consuming. This method helped us to streamline this process, here’s how:
Step 1: Launch Excel on your system and open the file containing the dataset.
Step 2: Click on an empty cell and type the percentage value. In our case, since the salaries will be increasing by 5%, the total change will be 100% + 5%. Hence, we will be entering 105% in an empty cell.
Step 3: Copy the percentage value by using the keyboard shortcut “CTRL + C”, or simply right click and select “copy” to copy the value.
Step 4: Next, select the range of cells which contains the values which need to be increased by 5%.
Step 5: On the ribbon menu, visit the Home tab and find Paste located on the far left of the screen.
Step 6: Click on small arrow key on Paste option to open the drop down menu and select Paste Special
Step 7: In the Paste Special Dialog, select “Value” from the Paste section and “Multiply” from the Operation section. Click “OK”
Step 8: The increments will be displayed on your sheet.
Step 9: To calculate percentage decrement using this technique, simply add “-” before the percentage; Eg. -105%
Avoid Using this Faulty Technique
There are many Excel gurus suggesting new ways to make your work within excel more efficient. However, not everyone is an expert and might suggest ways that might be difficult for some users to follow.
One such method that we came across was “Using Basic Arithmetic Functions in Excel”. We suggest to not follow this method for the following the reasons:
The method involves changing percentages into decimals manually, for eg. convert 10% into 0.01 and then use it for further calculation. This manual conversion not only complicates the process, but also increases the chances of human error.
This method cannot be used for a range of cells, as it requires manual conversion of each percentage into decimals.
The Multiplication Method and the Addition/Subtraction Method are highly effective techniques for working with percentages in Excel. These two methods adapt automatically based on the cell values, hence, saves time and effort.
FAQs about Common Percentage Operations
Q1. How to apply the percentage formatting to a number?
Here is how users can apply the percentage formatting to a number:
Step 1: Select cell or range of cells where you format the value a percentages.
Step 2: Go to the Home Tab on the ribbon menu.
Step 3: Locate the % Symbol in the Number Section
Q2. How to add the percentage of a total to a number?
To add the percentage of a total in excel, follow these steps:
A group of 5 friends have a total of $600. Let’s try calculating contribution by each friend in percentages:
Step 1: Click on the cell where you want the percentage share to be displayed. Make sure the cell formatting is set to Percentages.
Step 2: Enter the formula to calculate the percentage share of each friend. Make sure to add “$”, this will make sure the cell reference does not change while copying the formula to other cells.
Step 3: Press “Enter”. Drag and copy the formula on to the other cells to get the percentage share of each friends
Q3. How to find the percentage of change between two numbers?
To calculate percentage change between 2 number, follow these steps:
Step 1: Enter the formula in an empty cell:
(New-Old)/Old
Step 2: Press “Enter” to display the results. Make sure the cell formatting is set to Percentage.
Q4. How to find the amount when you know the total and percentage?
Let's consider a scenario where a smartphone is on sale for $300, which is 20% off its original price. We want to find the original price. We need to calculate the original price; 80%.
Step 1: Click on an empty cell and enter the formula to calculate the original amount before sale.
Step 2: Press “Enter” to display the formula.
Learn Percentages - Effortless Data Management and Optimization
Understanding how to calculate percentages in Excel is useful for both daily tasks and financial choices. Excel users who know how to add a percentage to a number are better equipped to manage sales and analyze data. These calculations can be made more quickly and with less effort by using the Multiplication and Addition/Subtraction methods. It is crucial to avoid faulty techniques that may lead to errors. WPS Spreadsheet provides cutting-edge functionalities, compatibility, cloud storage, and a template store, all without cost, for those looking for a productive alternative to Microsoft Office. Experience the convenience and efficiency of WPS Spreadsheet, the perfect alternative to Microsoft Office.
