When someone mentions " get a percentage in Excel " in general we can be frozen by not knowing how to perform this action because as a general rule we can make mistakes, but we know that an error, however minimal in Excel , will result in an erroneous value that has nothing to do with the expected result..
We should not always work in a financial area to handle percentages, so knowing the various ways to obtain them is ideal for better data management in Microsoft Excel. In this case using the latest version released 2019.
Something to keep in mind is that, although the formulas are a global standard, in some cases the calculated results of these formulas and some Microsoft Excel 2019 functions may differ between computers that use x86 (32 bit) or x86-64 (634 architecture) bits) and a computer with Windows RT based on the ARM architecture
We will learn various options to calculate percentages in Microsoft Excel 2019..
1. How to calculate a value when a total and a percentage are available in the Excel 2019 sheet
This method implies having in our Excel sheet 2019 the value of an element and the percentage that will be applied to it. This is useful if we have a product for sale and we want to apply a certain percentage to obtain the total cost of sale.
In this case we have a vehicle in cell A2 and in cell B2 the percentage to be applied to it is entered, so that in the cell where we want to obtain the total cost of sale we will enter the following formula:
= A2 * (B2 / 100) + A2
Note
This same formula applies to adjacent cells.
2. How to calculate the percentage having a quantity and a total in the Excel 2019 sheet
This method involves having an already defined amount, as well as an expected total of results. This is practical when we want to know the percentage of the expected total of some activity.
Step 1
For this example, in cell A2 we have entered the number of visitors while in cell B2 the expected total has been entered, for this, in the cell where we want to obtain the results we execute the following formula:
= (A2 / B2) * 100
Step 2
These values ​​are multiplied by 100 to show an integer value, otherwise the value would be shown in decimals (0.88, etc.), as we see the total percentage is displayed with decimals to the right of the number. To display this result with the percent sign (%), we select the cells where the results are, right click on them and select the Format cells option:
Step 3
In the window that will appear, we will go to the Percentage section and in the field Decimal positions select 0:
Step 4
Click on accept and the percentage will be represented in the respective cell.
3. How to calculate the percentage increase between two numbers in Excel 2019
Thanks to this method it will be practical and simple to compare the increase between two values, this facilitates actions associated with sales, projections and others.
For this example, we assume that we handle the sales of 2017 in cell A2 and the sales of 2018 in cell B3, so that the formula for calculating this increase is as follows:
= (B2-A2) / A2
Something to keep in mind is that the final value can be both positive and negative based on the data generated:
Excel 2019 is responsible for analyzing the difference between the two numbers to later divide this result by the first value in order to determine the final percentage.
4. How to calculate the percentage of a number in Excel 2019
It is a practical and very useful option to calculate the percentage of a certain number. For this, just run the following syntax:
= number% * percentage
For example:
= 100% * 58
5. How to calculate the percentage as a proportion in Excel 2019
The percentage as a proportion is a formula to determine the size of a sample or result and thus allow Excel 2019 to yield the final result.
In this example we have a series of active users in cell A2 and in cell B2 the total number of available users, so the formula to be used is as follows:
= A2 / B2
Note
It is important that the cell where the results are applied is the percentage format.
For this type of format we have two options:
- First, click on the% icon located in the Start menu, Number group.
- Second, we can right click on the cell, select Format Cells and in the Percentage section define the number of decimals.
6. How to multiply an entire column by a defined percentage Excel 2019
Step 1
This is a useful option when we have a series of numbers and we want to multiply them all by a defined percentage. For this we have the following values ​​that will be multiplied by 10%.
Step 2
To achieve this, we select the cell where the percentage is, in this case cell B1 and use the Ctrl + C keys to copy them. Next we select the range of values ​​where this value will be applied, in this case the range A1: A5 and click on the Paste icon located in the Clipboard group and there we will click on Paste Special:
Step 3
In the pop-up window we activate the Values ​​and Multiply boxes:
Step 4
As a result, we will obtain the percentages of the numbers based on the value we have entered:
7. How to change a quantity by percentage Excel 2019
Using this option it will be possible to reduce or increase the values ​​based on the defined percentage, to calculate the quantity, we will use a formula to subtract or add a percentage as the case may be.
For example, in this case we want to reduce monthly values ​​by 20% in column B, for this we execute the following:
= B2 * (1-C2)
In case we want to add that amount, we will execute:
= B2 * (1 + C2)
In this formula, the number 1 is the equivalent of 100%, the values ​​within the parentheses are calculated first, so that the value of C2 is subtracted or added from 1, in order to obtain 75%. And then this result will be multiplied by cell B2 in order to achieve the final result..
8. How to calculate the percentage of different Excel 2019 cells
This is a very practical option since it will be possible to perform calculations of different cells to obtain a final percentage, this will allow us to identify the percentage that a given object uses of a global total.
In this case we have a series of operating systems in column A, their value in column B and we want to know what percentage a certain system has, for this we will do the following:
- In cell B8 we have made the total sum of the products with the SUM function
- In cell E1 we will enter the system to filter
- In cell E2 we enter the formula to execute
The formula includes the SUM function.SI and is as follows:
= SUM IF (A2: A7; E1; B2: B7) / $ B $ 8
As we can see, the selected system occupies 40% of the total products. Thus, we have checked the different solutions that Excel 2019 offers us to carry out the integral calculation of percentages and thus administer this segment in a much more complete way.