Hello dear friends, thank you for choosing us. In this post on the solsarin site, we will talk about “ how to put percentage formula in excel 2013“.
Stay with us.
Thank you for your choice.
Excel is a great tool for performing mathematical operations on data that you have entered into your cells. These operations typically occur with the help of a formula, such as this subtraction formula.
One of the operations that you can perform on your data is to calculate a percentage of one cell value compared to another. This is accomplished by dividing one number by another number to generate a percentage.
Learning how to use a percentage formula in Excel
Learning how to use a percentage formula in Excel not only allows you to provide additional information about your data to people viewing the spreadsheet, it can also compare your data in a way that makes it more digestible. These formatting changes, combined with some usablity features like this one that freezes rows at the top of the page, can make Excel much easier to read.
Our tutorial below will show you how to use this formula, as well as how to change the format of the cells containing the percentage so that they display with a % symbol behind them.
How to Use a Percentage Formula in Excel 2013
- Open your Excel file.
- Click in the cell where you want the formula.
- Type =XX/YY into the cell, but use cell locations instead.
- Copy and paste the formula into other cells as needed.
- Right-click on formula cells, then choose Format Cells.
- Select Percentage, then click OK.
Our article continues below with more information and pictures of these steps.
How to Create a Percentage Formula in Excel 2013
The steps in this article were performed in Microsoft Excel 2013, but will work in other versions of Excel as well. Note that we are performing this task with a formula, so the percentage displayed in the chosen cell will change if you modify the cells that are being used to calculate that percentage.
Open your spreadsheet in Excel 2013.
Click inside the cell where you wish to display the calculated percentage.
Type the percentage formula into the cell. The formula is =XX/YY but replace XX with the cell containing the first value for the percentage, then replace YY with the cell containing the second value for the percentage.
Click and hold on the bottom-right corner of the cell, then drag it down to select the rest of the cells for which you wish to calculate a percentage.
This action applies the entered formula for those additional cells as well, but it updates automatically to calculate the percentages for the cells in each relative row.
Select the cells displaying the percentage, then right-click on one of the selected cells and choose the Format Cells option.
Select Percentage from the column on the left side of the window, choose the number of decimal places you wish to display, then click the OK button.
You should now see your displayed percentages in the cells.
As with any Excel formulas that use cell locations, the percentage formula is referencing the cell rather than the data contained within it. If you change a value in one of the cells that is used in the formula, then the percentage will update, too.
While this guide focuses primarily on how to use a percentage formula in Excel when you need to determine a percentage from cell data, you can also determine a percentage from two numbers, or from one number and a cell location. So the formula =4/10 or =4/A1 would also work.
How Excel handles percentages
Although formatting numbers as percentages is straightforward, the results you get after you apply the format may vary, depending on whether the numbers already exist in your workbook.
- Formatting cells that already contain numbers If you apply the Percentage format to existing numbers in a workbook, Excel multiplies those numbers by 100 to convert them to percentages. For example, if a cell contains the number 10, Excel multiplies that number by 100, which means that you will see 1000.00% after you apply the Percentage format. This may not be what you expected. To accurately display percentages, before you format the numbers as a percentage, make sure that they have been calculated as percentages, and that they are displayed in decimal format. Percentages are calculated by using the equation amount / total = percentage. For example, if a cell contains the formula =10/100, the result of that calculation is 0.1. If you then format 0.1 as a percentage, the number will be correctly displayed as 10%. To learn more about calculating percentages, see Examples of calculating percentages.
Formatting empty cells If you apply the Percentage format to cells, and then type numbers into those cells, the behavior is different. Numbers equal to and larger than 1 are converted to percentages by default; and numbers smaller than 1 are multiplied by 100 to convert them to percentages. For example, typing 10 or 0.1 both result in 10.00%. (If you don’t want to display the tw.
Display numbers as percentages
To quickly apply percentage formatting to selected cells, click Percent Style in the Number group on the Home tab, or press Ctrl+Shift+%. If you want more control over the format, or you want to change other aspects of formatting for your selection, you can follow these steps.
- On the Home tab, in the Number group, click the icon next to Number to display the Format Cells dialog box.
- In the Format Cells dialog box, in the Category list, click Percentage.
- In the Decimal places box, enter the number of decimal places that you want to display. For example, if you want to see 10% instead of 10.00%, enter in the Decimal places box.
Tips for displaying percentages
- To reset the number format of selected cells, click General in the Category list. Cells that are formatted with the General format have no specific number format.
If you want negative percentages to stand out—for example, you want them to appear in red—you can create a custom number format (Format Cells dialog box, Number tab, Custom category). The format should resemble the following: 0.00%;[Red]-0.00%. When applied to cells, this format displays positive percentages in the default text color and negative percentages in red. The portion that follows the semicolon represents the format that is applied to a negative value.