Difference between regular and percentage format
The first thing to start with with percentages and the actions that you can perform with them is the display format. Let's take a look at what shares or odds might look like:
- As a part of one. That is, 0.9 is 90%, 1.55 is 155%. This is a standard format that is easy to use for calculations.
- Percentage, but no “%” symbol. For example, 90 or 155. But when calculating the number will have to be divided by 100, which complicates the calculation a little and lengthens the formula.
- As numbers with a “%” sign. This option is also suitable for calculations and will be correctly perceived by the Excel program.
If the cell contains a number in the form of a fraction of one, the format can be left as standard or numeric. This type of writing also does not require any additional actions from the user. But then, when performing calculations, do not forget about the need to recalculate the value.
The third option can be set automatically if you put “%” after the number. Moreover, by default, as many decimal places will be selected as the number entered manually.
If the format has been changed beforehand, the “%” sign can be omitted for the entire area (column, row, rectangle, or group of free form). It will be placed after any number. True, by default the program selects 2 decimal places, and the numbers will look like 90.00% or 37.00%. If such accuracy is not needed in the calculations, you can put 1 or even 0 here.
1. Adding interest by simple calculation
The easiest way to subtract or add percentages in Excel is to manually enter an expression. To do this, perform the following actions:
- Place the cursor in the desired cell.
- An expression like “= 155 + 155 * 15%” is entered into the line of formulas.
- Get in the cell the result of adding the desired number of percent to the selected number.
The same actions are performed when subtracting numbers, only a minus is put instead of a plus. And it is suitable for those situations where no value changes. That is, the main number remains constant, which can be used in other calculations, and the percentage. If the values depend on the results of some calculations, or the formula must be used alone for a number of cells, it is worth using a different technique.
2. Use in formulas
It is also easy to figure out how to use Excel to add percentages to a number that is already in one of the cells. You will have to spend a little more time on this than on the usual calculation, and perform the following actions:
- Put “=” in the selected cell.
- Click on the cell from which the data for the calculation is taken – the number to which you want to add (or subtract from) a percentage.
- Put the appropriate sign – minus or plus.
- Click again on the cell with the number.
- Put the multiplication sign.
- Manually enter the percentage value and put “%”.
After pressing “Enter”, the result of the calculation will appear in the cell. And to extend the addition or subtraction formula to all column values, it is enough to select the right edge, when you hover over which the cursor becomes a cross, and drag the cell down to the desired line with the mouse. This will calculate the result for the entire column if the percentage has not changed.
3. Calculation when using a variable percentage
If both values change in the formula for adding or subtracting percentages, in such a situation there can be two ways how to add percentages in Excel. The first one involves writing an expression like “= number + number * percent%”, in which the cells will be indicated. For example, “= H5 + H5 * I5”, where H5 is a number, I5 is a percentage. By pulling the formula down, you can provide calculations for the remaining values and the proportions that are added to them.
However, if the percentage remains the same, but may vary depending on other evictions, it will not be possible to put a specific number in the formula. But you cannot use the cell number, which will change when dragging. For this method of subtracting interest, the “$” sign is used. And the formula will look different – for example, “= H5 + H5 * $ I $ 5”.
Now, when pulling down, calculations will be performed with new numbers, but with the same percentage from cell I5, and not I15. But, if the share changes and becomes, for example, 36 or 38, the results will correspond to the new changes – nothing needs to be manually corrected.
- How to Make a Table in PowerPoint: 2 Easy Ways
- How to delete a blank page in Word: several ways