All ways to calculate interest in Microsoft Excel - formulas and examples of use. how to add percentage to a number in excel how to add 7 percent in excel

The Microsoft Office Document Creation Suite is a popular suite of programs. Every person who typed information on a computer, compiled lists, calculated various kinds of indicators, created presentations to demonstrate them to an audience is familiar with them. Among the programs of the package, Excel deserves special attention - an assistant in carrying out various calculations. To determine% of a number, adding percentage values, it is worth knowing some of the nuances that will simplify the calculations.

Cell Format

Before entering a formula in a cell, you should set the format to "percentage". In this case, there is no need to enter additional steps. A “%” icon will automatically be placed next to the received value.

Basic Formula

In Excel, there are general rules for calculating interest. After selecting the cell format, enter the formula.

The basic formula for interest in Excel is written as follows:

The "=" sign is placed.

The cursor presses the value whose percentage is to be calculated.

An oblique dividing line is set from the keyboard.

Then the cell to which the percentage is calculated is pressed.

Calculation of percentages in Excel in a column

Sometimes it is necessary to calculate the percentages of values that are located in columns. To do this, you need to enter the basic percentage formula in Excel in the first cell of the column, then, by clicking in the lower right corner of the cell, “drag” down to calculate the desired values.

Calculating Percentages in Rows

When data is written horizontally, in rows, interest is calculated in the same way as in columns. After selecting the cell format, the percentage formula is entered into the Excel spreadsheet.

The “equal” icon is put, the cell is selected, the percentage of which needs to be calculated. Then - the line of division. The value of the cell to which you want to calculate the percentage is selected.

After that, you need to click on the lower right corner of the cell with the cursor and drag it to the side until the required values are calculated.

Calculation from the total amount

To do this, you can put the "=" sign and, highlighting the cells one by one, press the "+" sign between them. Then the Enter key is pressed.

You can also, after putting down “=”, type SUM on the keyboard, open the bracket, enter the range of cells for summation, and then press Enter.

When calculating the share of the total amount, after the introduction of the “equal” sign, in the cells whose share needs to be calculated, the “$” signs are placed in front of the row number and column letter. This icon "fixes" the relationship between them. Without this sign, interest will be calculated from other cells.

Adding percentages to a number

Before you add percentages to a number in Excel, you should familiarize yourself with the general calculation formula. Do not forget that a percentage is a hundredth of a number.

If you need to add Excel percentages to a column of values, you can enter the following formula: "=" icon, the cell to which you want to add percentages, the percentage value, the "/" sign, the same cell to which percentages are added multiplied by one hundred.

If the percentage value is placed in a separate column or cell, the formula will look different.

Before you add a percentage to a number in another column in Excel, the formula must take the form: equals sign, cell to which you want to add percentages, plus sign, cell with the percentage value with "$" signs, multiplied by the cell to which we add percentages. Provided that the latter format is set to "percentage".

If the percentages are in one column, and the values to which you want to add them are in another, then the formula that allows Excel to add percentages to a number will take the following form: equals sign, cell with value, plus sign, cell with percent, multiplied by the cell with the number. It is important not to confuse cell formats! Percentage format can only be set for cells with %.

When calculating % in Excel, remember to set the cell format for percentage values. For cells with numbers, a numeric value is used, for percentages, a percentage value. You can select it from the menu that appears after pressing the right mouse button.

In all formulas for calculating interest, the “=” sign is required. If you do not put it, the formula will not calculate.

Calculations in columns and rows are similar. Many operations are performed by "stretching" cells. To do this, enter the calculation formula and click on the cross in the lower right corner of the cell. Then use the cursor to "stretch" the value down, up or in the desired direction. The calculation will happen automatically.

There are several formulas that allow you to add percentages to a number in Excel. It depends on whether you need to add a specific value or data from another column.

If you need to add percentages of one cell, calculate a fraction of one value, use the "$" sign, which is installed in the formula bar before the line number and column letter.

Cell Format

To do this, put the mouse cursor on the desired cell. By clicking the right mouse button, a menu will appear. You must select the cell format, then percentage.

Basic Formula

In Excel, there are general rules for calculating interest. After selecting the cell format, enter the formula.

The basic formula for interest in Excel is written as follows:

The "=" sign is placed.
The cursor presses the value whose percentage is to be calculated.
An oblique dividing line is set from the keyboard.
Then the cell to which the percentage is calculated is pressed.

Calculation of percentages in Excel in a column

Calculating Percentages in Rows

When data is written horizontally, in rows, interest is calculated in the same way as in columns. After selecting the cell format, the percentage formula is entered into the Excel spreadsheet.

After that, you need to click on the lower right corner of the cell with the cursor and drag it to the side until the required values are calculated.

Calculation from the total amount

To do this, you can put the "=" sign and, highlighting the cells one by one, press the "+" sign between them. Then the Enter key is pressed.

You can also, after putting down “=”, type SUM on the keyboard, open the bracket, enter the range of cells for summation, and then press Enter.

Adding percentages to a number

Before you add percentages to a number in Excel, you should familiarize yourself with the general calculation formula. Do not forget that a percentage is a hundredth of a number.

If the percentage value is placed in a separate column or cell, the formula will look different.

In all formulas for calculating interest, the “=” sign is required. If you do not put it, the formula will not calculate.

There are several formulas that allow you to add percentages to a number in Excel. It depends on whether you need to add a specific value or data from another column.

If you need to add percentages of one cell, calculate a fraction of one value, use the "$" sign, which is installed in the formula bar before the line number and column letter.

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, margins, markups, interest on loans, returns on deposits and securities, and discounts. All these calculations are made as a calculation of a percentage of the amount.

Click on the button below to download an Excel file with examples of interest calculations in Excel:

What is a percentage?

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

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

There is no one size fits all formula for every interest calculation scenario. Below you will learn about the most popular payment methods.

How to calculate percentage of an 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, it is enough to divide one number by another. For example, we have apples that we bought for 100 rubles. in a wholesale warehouse, and we will set the retail price of 150 rubles. In order to calculate the percentage of how much the purchase price is 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 sale price:

Apply cell format D2 “Percentage”:

How to calculate the percentage of the sum of the values of 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 is the contribution of each seller to the final turnover:

For this we need:

Add a column to the table with percentage calculation;
In cell C2, insert the formula:

The $ signs fix column “B” and cell “9” so that when dragging 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 dragging the formula, the system will shift cell B9 as many cells down as you drag the formula.

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

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 sum of a table

Video tutorial:

In the example above, we had a list of sellers and their sales volume. We calculated how much each employee contributed to the final sales volume. But what if we have a list of recurring products with sales data and we want to calculate what percentage of all sales a particular product makes up?

From the list of products above, we want to calculate how much sales are 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 a function that will help you calculate Tomatoes from the list of products and sum up their sales, and then divide it by the total sales of products:

How does this formula work?

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

The syntax for the SUMIF function is:

=SUMIF(range, condition, [sum_range])

range– the range of cells by which the criteria are evaluated. The argument can be numbers, text, arrays, or references containing numbers;
condition– criteria that are checked against the specified range of cells and determine which cells to summarize;
sum_range are 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 goods, among which the function will search for the criterion we need (Tomatoes). Cell " $E$1” is specified as a criterion and indicates that we are looking for “Tomatoes”. Cell range “ $B$2:$B$8” denotes which cells need to be summarized if the desired 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 = Percent difference between two numbers

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, let's say that yesterday you had 8 apples in stock, and today there are 10 apples. Thus, the number of apples relative to yesterday has changed by 25% upwards. In the event that you had 10 apples yesterday, and today there are 8 apples, then the number of apples decreased by 20% compared to yesterday.

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

(New value - Old value) / Old value = Percentage difference between two numbers

Below, using examples, we will analyze how to use this formula for calculations.

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

How to add numbers in Excel?

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

How to add date in Excel?

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

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

Knowing this nuance, it is very easy to make 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 a year to a specified date, but to simplify this process, there is a function DATE OF(). The function syntax is as follows: DATE(year, month, day). Thus, adding several months or years to the 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 the date format.

How to add percentages in Excel?

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

When calculating in MS Excel, sometimes it may be necessary to add a percentage of a certain number to the same or another number. This technique is often used in accounting, for example, to calculate an employee's salary allowance, compare profitability indicators with different periods. All of these operations may require the ability to add percentages in Excel.

There are several ways to help realize what you have planned. Let's look at them in more detail in the context of this article.

How to add a percentage to a number in MS Excel

There are two ways to do this. Both involve using a special formula. Only in the first case, you work in one cell and, as a rule, with some specific numbers. In the second case, the work goes on with cells in which some data has already been set.
Let's consider these two cases on concrete examples.

Example 1: Calculations in a cell

Provided that you do not have a table with filled data or this data has some other form, then it is better to use this method. It is implemented as follows:

Example 2: Working with cells

If you already have a table with data filled in, then this will even be a little easier. The calculation will take place according to the same formula, only instead of numbers, cell numbers will appear there. Here is a good example:

We have a table that shows that there is such and such revenue for a certain period for such and such a product. We need to find the same revenue, but increased by a certain percentage. Select the cell that is located in the same line with the desired product. There will be a formula.
The formula in our case will look like this: "= (cell number, where the amount of revenue for the product) + (cell number, where the amount of revenue for the product) * (percentage)%". In our case, the formula looks like this: "= C2 + C2 * 20%".
You don't need to remember cell numbers. In the formula editor, when you click on the desired cell, it is inserted into the formula automatically.
Press Enter to complete the calculation.

There is one serious note to this example - if the desired percentage is located in a cell, then the numbers in these cells must be converted to the appropriate format. Consider how to bring the numbers in the percentage column to the desired format for the correct calculation:

By displaying the result of one addition in this way, you can fill in the cells for the remaining goods in automatic mode. Select the cell in which you have already calculated everything and stretch it to the remaining products using the special manipulator in the lower right part. The data for the rest of the cells will be inserted automatically in accordance with the adjacent cells and columns that appeared in the formula.

As you can see, it is very easy to add a percentage of itself to a number in the Excel working interface. If necessary, you can check the resulting result for reliability using a calculator.