Microsoft Excel has many functions that help you produce calculations, statistics, and tables. Some of these functions do not even make it into the official list in the software. DATEDIF is one of those hidden functions that can help you calculate periods of time. The result can be shown in days, months or years. We’ll explain how it works.
We often have to work with percentages in our day-to-day work and our everyday life. Excel makes this task easier by automatically applying basic or advanced formulas that allow you to calculate percentages directly in your spreadsheets. We’ll show you how to use the most important formulas in Excel to calculate percentages of totals or percentage changes such as growth and decline. You’ll also learn how to calculate proportions and totals based on percentages and how to increase and decrease numbers by percentage.
- Calculating percentages in Excel – the basics
- Calculating percentages in Excel – formulas and formatting
- Calculating the percentage of a total in Excel
- Formula for calculating a percentage change in Excel
- Calculating the proportion and the total based on a percentage in Excel
- Increasing and decreasing numbers by a percentage in Excel
Calculating percentages in Excel – the basics
In this article, we’ll give you an overview of the most important methods for calculating percentages in Excel and show you the most important formulas for doing percentages.
Microsoft Excel can help you with many basic and advanced calculations in your day-to-day work. Learn more about the SUM function in Excel if you want to easily add groups of numbers.
The word “percent” is of Latin origin and means “by a hundred”. In other words, you calculate a percentage by dividing the numerator of a fraction by the denominator and multiplying the result by 100. The basic formula for calculating apercentage looks like this:
For example, if you sell a product for $14 that costs $10.50 to manufacture, the manufacturing cost is 75 percent of the total price. We often use this formula to calculate percentages in our everyday lives. However, with Excel, calculating percentages is even easier because the spreadsheet application automatically performs several steps in the background.
Organize your data in Excel, create spreadsheets, and calculate your expenses, profits, and much more. Get the spreadsheet program as part of the Microsoft 365 package directly from IONOS and get optimum round-the-clock support for Microsoft Excel and other Office products.
Calculating percentages in Excel – formulas and formatting
When you calculate percentages in Excel, you’ll save yourself steps like multiplying by 100 because the application automatically handles the formatting for you once you apply the percent format (see below for more information). The formula for calculating percentages in Excel is as follows:
Let’s look at a practical example of how percentages are calculated in Excel: A retailer uses a spreadsheet of purchased and sold products in order to keep track of inventory. The number of products that the retailer buys is in column B and the number of products that the retailer sells in is column C. To calculate the percentages in column D of the Excel spreadsheet, enter the following formula (and then press Enter):
You can easily apply the formula to subsequent rows by double-clicking the bottom right corner of the cell (in this case: D2). Excel also automatically adds the formula as you enter more data in columns B and C.
Adding percentage formatting in Excel
However, the results may not look quite right at first: Instead of percentages, you’ll see decimal numbers in your Excel spreadsheet.
If you want Excel to automatically add the percent sign and round up to the percentage using the formatting, you have to adjust the following settings first: To quickly change the cell format to show values as percentages in Excel, go to the “Number” group (between “Alignment” and “Styles”) on the “Home” tab. Once there, simply click the percent sign to select the percent format – either for a single cell, a range of cells or an entire column or row. In this case, we calculated percentages in column D of our Excel spreadsheet by selecting the appropriate cells beforehand:
Alternatively, you can use the keyboard shortcut Ctrl + Shift + % (as shown in the screenshot above) to enable the percentage format.
Adjusting percentage formatting in Excel
If you follow the steps above, percentages are displayed without decimal places according to the default settings in Excel. However, you can change the percentage formatting in Excel simply by clicking the little arrow icon at the bottom right in the “Number” group. Alternatively, you can select a cell and right-click it to display the “Format cells” dialog box.
To adjust the percentages in Excel, you can then specify the number of decimal places you want to display. For example, to display two digits after the decimal point, select “2”. Then click OK.
Alternatively, you can choose the “Custom” category in the same dialog box to specify percentages either without a decimal place or with two decimal places.
Formats in Excel not only help you calculate percentages; you can also use them for many other processes and tasks. For example, in another article we explain how to calculate time in Excel.
Calculating the percentage of a total in Excel
In the examples above, we calculated percentages of a total. The purchases and sales are the total and the sales are the percentages. However, we can also use Excel to do more complicated calculations for finding the percentages of a total.
Calculating the percentage of a total at the end of an Excel table
Let’s say the retailer in the example above now wants to know how many products they purchased in total and what percentage of the total purchase amount is attributed to each product. Excel works out the percentage of the total that each amount represents. However, first we have to determine the total quantity of all products in cell B11. In the column to the right, we use Excel to find out what percentage of the total each product represents. To do this, we enter the following values in cell C2 or select these values with the mouse button:
The $ signs create an absolute reference to cell B11. This means that the value does not change even if the other values change according to the spreadsheet. For example, “=B3/$B$11” appears in cell C3 after the formula from cell C2 is automatically applied. Without the absolute value, which is indicated by the dollar sign, “=B3/B12” would appear in this cell and the example formula would not calculate any percentages because cell B12 is empty.
You can also define a cell as an absolute value by pressing the [F4] key on your keyboard after you have selected the cell with the mouse or entered it manually.
Calculating percentages of a total from different rows
Now let’s say the retailer has multiple rows for the same product and wants to use Excel to find out what percentage of the total that product represents. In this case, you can use the SUMIF formula, which automatically adds all values that match the criteria you have selected. Combined with the SUMIF formula, the complete formula for calculating percentages in Excel looks like this:
In our example, we entered the criteria “Pants” in cell E1. The values that we want to add are in column B and the product names for automated selection are in column A. The corresponding formula in Excel is as follows:
Here’s how it looks in the Excel spreadsheet:
Instead of specifying your criteria in a cell of your Excel spreadsheet, you can type it directly into your formula, which then reads “SUMIF(A2:B10,"Pants",B2:B10)/$B$11”. You can also use parentheses to work with multiple SUMIF functions, for example, to calculate percentages of multiple products in Excel:
Formula for calculating a percentage change in Excel
The percentage of change or percentage of growth is probably one of the formulas that you’ll use most often when you calculate percentages in Excel. Simply use the following formula to calculate the change between the original value A and the value B:
The original value A represents the value before the change. For example, if you compare sales between the two months of March and April, the number of sales in March would be the original value A and sales in April would be B. The following two examples illustrate how to calculate the percentage of growth using this formula.
Calculating the percentage of change between two columns
The retailer in our example entered the sales from March in column B and the sales from April in column C. The retailer then enters the following formula in column D to calculate the percentage of growth:
Don’t forget: After this step, you have to format the cells in column D as percentages so that the result is displayed correctly.
Calculating the percentage of growth between two rows
You may want to keep track of total monthly sales in a spreadsheet. In this case, you can calculate the percentage of growth between two rows so that you can easily see month-over-month changes in sales. In the example, we enter all monthly sales in column B and calculate the growth in column C. Start with the sales from January in cell B2 and determine the percentage of growth for February in cell C3. To do this, enter the following formula in cell C3:
Then apply the formula to the subsequent rows (starting with C4). In your Excel spreadsheet, you can see the percentage of growth as follows:
When you calculate the percentage of growth, be sure to leave the first row blank because it is not being used in the comparison. In this example, you are not comparing January to the prior month.
You can also calculate the percentage of growth in Excel in comparison to a static value that does not change. To do this, simply enter the absolute reference to the cell by pressing the F4 key and adding the $ sign as explained above.
Calculating the proportion and the total based on a percentage in Excel
Just as you can use Excel formulas to calculate percentages based on two values, A and B, you can also calculate a total or the proportions of a totalbased on a percentage.
Calculating the proportion based on the total and based on percentages
Let’s say you buy new IT equipment for your company and the total price is $1,200. This includes sales tax, which may be tax-deductible. Sales tax varies from state to state, so for the sake of illustration let’s assume that you live in Wisconsin, where sales tax is 5%. For this purpose, you simply calculate the amount of sales tax based on the percentage. In the following example, the total price is in cell A2 and the percentage is in cell B2. You need to calculate the absolute value of the sales in cell C2. The appropriate formula is:
The result: The purchase price includes $60 of sales tax.
If you don’t want to enter the percentage in a separate cell, you can also integrate it directly into the formula. Enter either “=A2*5%” or “=A2*0.05”.
Calculating the total based on the proportion and percentage
Excel also allows you to calculate a total based on the proportion and percentage. For example, let’s say you attend a multi-day workshop in another city. As a workshop attendee, you receive a special discount and pay only 70 percent of the price of your hotel room as part of your all-inclusive accommodations. You are charged $350 and are curious how expensive your stay would have been without the discount.
Enter the amount you paid in cell A2 and the percentage in cell B2. In cell C2, enter the formula for calculating the total price based on percentages. Divide your amount by the percentage:
If you entered everything correctly, you’ll see that the hotel price would have been $500 without the discount:
Increasing and decreasing numbers by a percentage in Excel
You can also use percentages to increase and decrease numbers in Excel. This is especially useful for our retailer in the previous examples. For example, if the retailer wants to increase all prices by 20 percent to compensate for increased expenses, they can use the following formula:
The retailer can then reduce the prices again during the end-of-season sale. Note that Excel also recognizes the decimal notation when calculating the percentage:
In the example, a price increase looks like this: Column A shows the product type and column B shows the current price. To calculate the new price in column C, the retailer uses the following formula:
The retailer now sees the new prices, which are 20 percent higher, in column C: