Last Updated on
Knowing how to calculate percentages in Excel can be useful for many reasons and can save you valuable time and energy in your business calculations.
In this article, we will look at some ways of working with percentages in Excel. We will also show you how to calculate the percentage of any value within an excel cell.
Why Might You Need To Calculate Percentage In Excel?
Percentages are used to measure things such as:
- Sales performance
- Costs per unit sold
- Profit margins
- Productivity
Converting each of these elements to a percentage makes it easier to see and understand data at a glance, and offers a good insight into your overall business.
Calculating Percentage In Excel
To begin with, let’s take a look at how to calculate a percentage using the standard formula. This is done by entering the following formula into a cell:
100*(A1/B1) where A1 represents the numerator (the part that you want to divide by 100), and B1 represents the denominator (the whole).
For example, if you wanted to find the percentage of sales made last month, you would enter the following formula into a new cell:
100*(Sales-Previous Month)/Sales
If you wanted to find the average cost per item produced, you could use the following formula:
100*(Total Cost – Previous Month Total Cost)/Total Cost
There are two main problems with this method though. The first problem is that there is no easy way to copy or paste this formula from one cell to another.
Secondly, when you do change the values in the cells, the percentage changes too. To solve both of these issues, you can use the Percent function instead.
The Percent Function
The percent function allows us to work with percentages without having to use the standard formula. It does this by allowing us to specify what the numerator and denominator should be.
In order to use the percent function, we must first select the cells containing the numbers we wish to convert. Then we simply type % into the formula bar and press Enter.
For example, say we have a column of figures representing our previous month’s sales.
We then want to know the percentage increase over the previous year. We would enter the following formula in a new cell:
%((Sales-Previous Year Sales)/Previous Year Sales)
We now have a percentage on the left-hand side of the equation, and the figure we are comparing against has been entered on the right-hand side.
This means that we can easily compare the two, and determine whether they are similar or not.
If we were to continue with the same example, but this time we had a column of costs, we could calculate the percentage decrease over the previous year by typing the following formula:
%((Previous Year Costs)-Current Year Costs)/Previous Year Costs
In addition, if we wanted to find out the percentage of profit for the current month, we could use the following formula.
%((Monthly Profit)/(Sales-Previous Monthly Profit))
This will give us a percentage on the left, and the number we are comparing against on the right.
Final Thoughts
Learning how to calculate percentages in Excel is a useful skill that can save you considerable time, as well as make it easier to read and analyze your data as required.
By using the formula, or the Percent function, you can convert your raw data into easy-to-use percentages, and this is a fast, easy process.
