How to add percentages to a column in Excel. How to add percentages to a number

09.07.2023

Drivers

In Excel, you can perform the most complex calculations with various units of data, be it finance, time or ordinary numbers. Each case has its own nuances, and then we will focus on how to add Excel numbers, date (day, month, year) and percentage.

How to add numbers in Excel?

You can add numbers in Excel by writing a formula for adding two cells with data, or immediately writing the numbers themselves in the formula, or adding a number to a cell. The method of writing a formula that uses the addresses of cells with values is more universal, since it can subsequently be applied to other data and not be rewritten when the values change, which can simply be written in the corresponding cell.

How to add a date in Excel?

The easiest way to add a date in Excel is to add to the date written in the cell a value corresponding to the number of days being added.

To make this whole process more understandable, you need to have an idea of how date values are stored in Excel. And the date in Excel is stored as a numeric value corresponding to the number of days from January 1, 1900 to the date value specified in the cell. That is, if you write a number in a cell "1" and assign a date format to the cell, the cell will display the value 1.01.1900 .

Knowing this nuance, it is very easy to create a formula and add any number of days to the date in the cell.

It is much more difficult in Excel to add a month or year to a specified date, but to simplify this process there is a function DATE OF(). The syntax of the function is as follows: DATE(year;month;day). Thus, by adding several months or years to a date, you don’t have to think about the number of days in a particular month or year, since everything will be calculated automatically.

Let's try using this function in Excel to add five years, six months and thirty-one days to our date. In this case, the format of the cell with the result will be automatically converted to date format.

How to add percentages in Excel?

You can add percentages to a number in Excel using the classic formula for calculating percentages; you just need to write the corresponding formula correctly.

Percentage formula in Excel

In Excel, you can apply the “Percentage” format to a cell, and depending on whether it is applied to the cell in which percentages are written or not, the type of formula will depend. The only difference is that in such a cell the decimal number is automatically written as a percentage, and there is no need to use division and multiplication by one hundred in the formula.

For example, we need to get a percentage of a number and we have a common format applied to the cells. In this case, the formula is written as it was taught in school, divide by one hundred and multiply by a percentage.

You can also simplify the notation a little by using a percentage sign in the formula, perhaps this will be more convenient for someone. The initial value cell is multiplied by the percent value cell, followed by a percent sign.

If the Percentage format is applied to a cell containing a percentage value, then the formula will look like multiplying two cells.

How to subtract or add percentages to a number in Excel?

You can subtract percentages in Excel or add percentages to a number by simply writing the formula correctly, taking into account the format applied to the cells. For example, formulas for subtracting percentages from a number can be like those in the pictures below.

The formula for adding percentages to a number in Excel will look similar.

In this article I will tell you about a very simple and common task in Excel - how to add percentages in excel using formula.

Applying percentage addition to a number

The described task occurs very often. For example, in economics, you need to add 3% to the wages of team workers.

As can be seen from the figure, cell D9 contains the formula known to us. Press “enter” and change the cell format to “Numeric” (if necessary). After this, we will extend the formula to the remaining cells in the column. As a result, we will get the following result.

The previous methods of adding percentages in Excel can be called “clumsy” and are used more often by those who have little practice with the application. This is due to the fact that there is The recommended way to add percentages is to use the percentage symbol (%).

Let's say we have a small tablet, in the first column we have a number written, and in the second, how many percentages need to be added. We will write the result in the third column.

In the third column we need to write the formula H3+H3*I3%, please note that we multiply cell H3 by cell I3%. I hope everyone saw the percentage symbol.

In fact, this method no different from what was described earlier. The developers just thought it would be intuitive.

Interest rates are everywhere in the modern world. Not a day goes by without using them. When purchasing products, we pay VAT. Having taken out a loan from a bank, we repay the amount with interest. When reconciling income, we also use percentages.

Working with percentages in Excel

Before starting work in Microsoft Excel Let's remember school math lessons where you studied fractions and percentages.

When working with percentages, remember that one percent is a hundredth (1% = 0.01).

When performing the action of adding percentages (for example, 40+10%), we first find 10% of 40, and only then add the base (40).

When working with fractions, do not forget about the basic rules of mathematics:

Multiplying by 0.5 is equal to dividing by 2.
Any percentage is expressed as a fraction (25%=1/4; 50%=1/2, etc.).

We count the percentage of the number

To find a percentage of a whole number, divide the desired percentage by the whole number and multiply the result by 100.

Example No. 1. There are 45 units of goods stored in the warehouse. 9 units of goods were sold in a day. How much of the product was sold as a percentage?

9 is a part, 45 is a whole. Substitute the data into the formula:

(9/45)*100=20%

In the program we do the following:

How did this happen? Having set the percentage type of calculation, the program will independently complete the formula for you and put the “%” sign. If we set the formula ourselves (with multiplication by one hundred), then there would be no “%” sign!

Example No. 2. Let's solve the inverse problem. It is known that there are 45 units of goods in the warehouse. It also states that only 20% have been sold. How many total units of the product were sold?

Example No. 3. Let's try the acquired knowledge in practice. We know the price for the product (see picture below) and VAT (18%). You need to find the VAT amount.

We multiply the price of the product by the percentage using the formula B1*18%.

Advice! Don't forget to extend this formula to the remaining lines. To do this, grab the lower right corner of the cell and lower it to the end. This way we get an answer to several elementary problems at once.

Example No. 4. Inverse problem. We know the amount of VAT for the product and the rate (18%). You need to find the price of a product.

Add and subtract

Let's start with the addition. Let's look at the problem using a simple example:

We are given the price of the product. It is necessary to add VAT percentage to it (VAT is 18%).
If we use the formula B1+18%, then the result we will get is incorrect. This happens because we need to add not just 18%, but 18% of the first amount. As a result, we get the formula B1+B1*0.18 or B1+B1*18%.
Pull down to get all the answers at once.
In case you use the formula B1+18 (without the % sign), then the answers will be obtained with “%” signs, and the results will not be what we need.
But this formula will also work if we change the cell format from “percentage” to “numeric”.
You can remove the number of decimal places (0) or set it at your discretion.

Now let's try to subtract the percentage from the number. Having knowledge about addition, subtraction will not be difficult at all. Everything will work by replacing one sign “+” with “-”. The working formula will look like this: B1-B1*18% or B1-B1*0.18.

Now let's find percentage of all sales. To do this, we sum up the quantity of goods sold and use the formula B2/$B$7.

These are the basic tasks we accomplished. Everything seems simple, but many people make mistakes.

Making a chart with percentages

There are several types of charts. Let's look at them separately.

Pie chart

Let's try to create a pie chart. It will display the percentage of sales of goods. First, we are looking for percentages of all sales.

Afterwards, your diagram will appear in the table. If you are not satisfied with its location, then move it by pulling it outside the diagram.

bar chart

For this we need data. For example, sales data. To create a histogram, we need to select all numerical values (except the total) and select the histogram in the “Insert” tab. To create a histogram, we need to select all numerical values (except the total) and select the histogram in the “Insert” tab.

Schedule

Instead of a histogram, you can use a graph. For example, a histogram is not suitable for tracking profits. It would be more appropriate to use a graph. A graph is inserted in the same way as a histogram. You need to select a chart in the “Insert” tab. Another one can be superimposed on this graph. For example, a chart with losses.

This is where we end. Now you know how to rationally use percentages and build charts and graphs in Microsoft Excel. If you have a question that the article did not answer, . We will try to help you.

In almost every field of activity we are faced with the need to be able to calculate percentages. Most often, we need interest calculation skills when calculating VAT, margin, markup, loan interest, return on deposits and securities, and discounts. All these calculations are made as a percentage of the amount.

Click the button below to download Excel file with examples of percentage calculations in Excel:

What is percentage?

Percent (lat. per cent - per hundred) - one hundredth part. Marked with a “%” sign. Denotes a share of something in relation to the whole. For example, 25% of 500 rubles. means 25 parts of 5 rubles. each, that is, 125 rubles.

The basic formula for calculating the percentage of the amount looks like this:

There is no universal formula that fits every interest calculation scenario. Below you will learn about the most popular ways calculation.

How to calculate percentage of amount in Excel

Video lesson:

(Part/Whole) * 100 = Percentage (%)

But if you use the “Percentage” cell format, then to calculate the percentage of a number you just need to divide one number by another. For example, we have apples that we bought for 100 rubles. in a wholesale warehouse, and the retail price will be 150 rubles. In order to calculate the percentage of the purchase price from the retail price, we need:

Create a table with prices and add a column to calculate the percentage:

In cell D2, enter a formula that calculates the percentage of the purchase price from the sales price:

Apply cell D2 “Percentage” format:

How to calculate the percentage of the sum of values in an Excel table

Video tutorial:

Let's imagine that we have a list of sellers with the sales volume for each and the total amount of all sales for all sellers. Our task is to determine what percentage each seller contributes to the final turnover:

For this we need:

Add a column to the table with percentage calculation;
Insert the formula into cell C2:

The $ signs fix column “B” and cell “9” so that when extending the formula to all rows of the table, Excel automatically substitutes the sales volume of each seller and calculates the % of total sales. If you do not put the “$” signs, then when you stretch the formula, the system will shift cell B9 as many cells down as you stretch the formula.

Extend the formula to all table cells corresponding to the rows with the names of the sellers:

In the example above, we stretched the formula and got the values in the form of fractional numbers. In order to convert the received data into percentages, select the data with the left mouse button and change the cell format to “Percentage”:

How to calculate the percentage of multiple values from the table sum

Video tutorial:

In the example above, we had a list of sellers and their sales volume. We calculated what contribution each employee made to the final sales volume. But what if we have a list of recurring products with sales volume data and we need to calculate what percentage of all sales a particular product represents?

From the list of products above, we want to calculate the sales volume of tomatoes (they are written in several rows of the table). For this:

To the right of the table we indicate the product (Tomatoes) for which we want to calculate the share in sales:

Change the format of cell E2 to “Percentage”;
In cell E2, insert a formula with the function that will help calculate Tomatoes from the list of products and sum up their sales volume, and then divide it by the total sales volume of goods:

How does this formula work?

For calculations we use the formula. This function returns the sum of the numbers specified as arguments that meet the criteria specified in the formula.

The syntax for the SUMIF function is:

=SUMMIF(range, condition, [sum_range])

range– the range of cells by which the criteria are evaluated. Arguments can be numbers, text, arrays, or references containing numbers;
condition– criteria that are checked against a specified range of cells and determine which cells to sum;
sum_range– summed cells. If this argument is not specified, then the function uses the argument range as sum_range .

Thus, in the formula =SUMIF($A$2:$A$8,$E$1,$B$2:$B$8)/B9 we indicated “ $A$2:$A$8” as a range of products among which the function will search for the criterion we need (Tomatoes). Cell “ $E$1” is indicated as a criterion and indicates that we are looking for “Tomatoes”. Cell range “ $B$2:$B$8” indicates which cells need to be summed if the required criterion was found.

How to calculate percentage difference in Excel

For example, to calculate the percentage difference between two numbers A and B, use the formula:

(B-A)/A = Difference between two numbers as a percentage

In practice, when using this formula, it is important to determine which of the numbers you need is “A” and which is “B”. For example, imagine that yesterday you had 8 apples in stock, and today you have 10 apples. Thus, the number of apples you have compared to yesterday has changed by 25% upward. If you had 10 apples yesterday, and today you have 8 apples, then the number of apples compared to yesterday has decreased by 20%.

Thus, the formula to correctly calculate the percentage change between two numbers looks like this: