For most users, Excel is a program that can work with tables and into which you can put the relevant information in a tabular form. As a rule, few people know all the features of this program, and hardly anyone thought about it a lot. And Excel is capable of performing a large number of operations, and one of them is counting numbers.
Simple operations in MS Excel
The Microsoft Excel program has a built-in super-modern calculator with a large number of functions and capabilities.
So, the first thing to know: all calculations in Excel are called formulas and they all begin with an equal sign (=). For example, you need to calculate the amount 5 + 5. If you select any cell and write 5 + 5 inside it, and then press the Enter button, the program will not calculate anything - the cell will simply say "5 + 5". But if you put an equal sign (= 5 + 5) in front of this expression, then Excel will give us the result, that is, 10.
You also need to know the basic arithmetic operators to work in Excel. These are standard functions: addition, subtraction, multiplication, and division. Excel also offers exponentiation and percentage. With the first four functions, everything is clear. Exponentiation is written as ^ (Shift + 6). For example, 5 ^ 2 would be five squared or five to the second power.
As for the percentage, if you put a% sign after any number, it will be divisible by 100. For example, if you write 12%, you get 0.12. This sign makes it easy to calculate percentages. For example, if you need to calculate 7 percent of 50, then the formula will look like this: = 50 * 7%.
One of the popular operations that is often done in Excel is the calculation of the amount. Let's say there is a table with the fields "Name", "Quantity", "Price" and "Amount". All of them are filled in, only the "Amount" field is empty. To calculate the amount and fill in a free column automatically, you first need to select the cell where you want to write the formula and put an equal sign. Then click on the desired number in the "Quantity" field, type the multiplication sign, then click on the number in the "Price" field and press Enter. The program will calculate this expression. If you click on the cell of the sum, then you can see something like this formula: = B2 * C2. This means that not any specific numbers were counted, but the numbers that were in these cells. If you write other numbers in the same cells, Excel will automatically recalculate the formula - and the value of the sum will change.
If, for example, you need to count the number of all goods that are recorded in the table, you can do this by selecting the auto sum icon in the toolbar (it looks like the letter E). After that, you will need to specify the range of cells that you want to count (in this case, all the numbers in the "Quantity" field), press Enter - and the program will display the resulting value. You can also do it manually by alternately specifying all the cells of the "Quantity" field and putting an addition sign between them. (= A1 + A2 +… A10). The result will be the same.
Today we will look at:
- a program for creating tables, which is widely used both in organizations and at home, for example, for home bookkeeping. In the process of using Excel, many users are faced with the fact that it is necessary to calculate the sum of a column. Of course, you can do it manually, but it's much easier to follow the advice given in the article.
Below we will look at several ways to help you get the sum of the cells in a column.
Method 1
Just select the cells with the mouse and then click the autosum icon. The total will be displayed in an empty cell below.
Method 2
Another variant. Click on the empty cell where the amount should be displayed, and then click on the autosum icon.
The program will highlight the area to be added, and the formula will be displayed in the place where the total will be located.
You can easily adjust this formula by changing the values in the cells. For example, we need to calculate the amount not from B4 to B9, but from B4 to B6. When you finish typing, press the Enter key.
Method 3
But what if the cells required for the summation are not one after the other, but scattered? This task is a little more difficult, but it is also easy to cope with it.
For clarity, the cells that we need to fold, we will highlight with color. Now click with the mouse cursor on the cell where the amount will be located and put the sign = ... It is with such a sign that any formula begins.
Now click on the first cell to be summarized. In our case, this is B7. The cell appears in the formula. Now put a sign + and add the rest of the cells in the same way. You will get something like the screenshot below.
Press the Enter key to execute the formula. The system will display the number with the sum of the cells.
While working in Microsoft Excel, you often need to tweak the sum in columns and rows of tables, as well as simply determine the sum of a range of cells. The program provides several tools to address this issue. Let's see how to sum cells in Excel.
The most well-known and easy-to-use tool for determining the sum of data in cells in Microsoft Excel is the autosum.
The program outputs the formula to the cell.
In order to see the result, you need to press the Enter button on the keyboard.
You can do it a little differently. If we want to add cells not of the entire row or column, but only of a certain range, then we select this range. Then we click on the "AutoSum" button already familiar to us.
The result is immediately displayed on the screen.
The main disadvantage of calculating using autosum is that it allows you to calculate a sequential series of data located in one row or in a column. But an array of data located in several columns and rows cannot be calculated using this method. Moreover, it cannot be used to calculate the sum of several cells distant from each other.
For example, we select a range of cells, and click on the "AutoSum" button.
But the screen displays not the sum of all these cells, but the sums for each column or line separately.
SUM function
In order to view the sum of a whole array, or several arrays of data in Microsoft Excel, there is the "SUM" function.
Select the cell where we want the amount to be displayed. Click on the "Insert Function" button located to the left of the formula bar.
The Function Wizard window opens. In the list of functions, we are looking for the "SUM" function. Select it and click on the "OK" button.
In the function arguments window that opens, enter the coordinates of the cells, the sum of which we are going to calculate. Of course, manually entering coordinates is inconvenient, so click on the button to the right of the data entry field.
After that, the window of the function arguments is minimized, and we can select those cells, or arrays of cells, the sum of the values of which we want to calculate. After the array is selected and its address appears in a special field, click on the button to the right of this field.
We again return to the function arguments window. If you need to add one more data array to the total amount, then we repeat the same steps mentioned above, but only in the field with the "Number 2" parameter. If necessary, in this way you can enter the addresses of an almost unlimited number of arrays. After all the arguments of the function are entered, click on the "OK" button.
After that, in the cell in which we set the output of the results, the total sum of the data of all the specified cells will be displayed.
Using a formula
The sum of data in cells in Microsoft Excel can also be calculated using a simple addition formula. To do this, select the cell in which the amount should be located, and put the "=" sign in it. After that, one by one we click on each cell, of those, the sum of the values of which you need to calculate. After the cell address is added to the formula bar, enter the "+" sign from the keyboard, and so on after entering the coordinates of each cell.
When the addresses of all cells are entered, press the Enter button on the keyboard. After that, the total amount of the entered data is displayed in the specified cell.
The main disadvantage of this method is that the address of each cell has to be entered separately, and it is impossible to select a whole range of cells at once.
View amount in Microsoft Excel
Also, in Microsoft Excel it is possible to view the sum of selected cells without displaying this amount in a separate cell. The only condition is that all cells, the sum of which should be counted, must be side by side, in a single array.
We simply select a range of cells, the sum of the data of which you need to find out, and see the result in the status bar of Microsoft Excel.
As you can see, there are several ways to summarize data in Microsoft Excel. Each of these methods has its own level of complexity and flexibility. As a rule, the simpler the option, the less flexible it is. For example, when determining the amount using an autosum, you can operate only with data lined up in a row. Therefore, in each specific situation, the user himself must decide which method is more suitable.
Microsoft Excel is used in a variety of activities, from accounting to retail sales. In this article, I will explain how to calculate percentages in Excel (Excel)... Often in the process of work, it becomes necessary to calculate the percentage of a certain amount - you cannot do without this when calculating taxes, discounts, loan payments, etc.
Calculating percentages on a calculator or “in mind” sometimes takes a lot of time, because not everyone can quickly recall formulas from the school curriculum. Using Excel allows you to complete this task in a matter of minutes, greatly facilitating the user's work. This article will help you figure out how to work with percentages in Excel, as well as perform any mathematical action with percentages.
Cell percentage format
Microsoft Office Excel is a spreadsheet consisting of cells. Each cell can be assigned a specific format. For the cell in which we will calculate the percentage, it is necessary to set the percentage format. This is done as follows.
Right click on the cell, choose Format Cells.
Specify Percentage.
I usually leave two decimal places.
To make a percentage without zeros (decimal places for an integer number), specify the number of decimal places 0.
The basic formula for finding the percentage looks like this:
Part / Integer * 100
How to calculate percentage of a number in Excel
Simple calculation - we get a percentage of one number. In cell A1, enter a number, for example 70. In cell B1, enter the second number, for example 38. The question is, what percentage is 38 out of 70? Let's set the percentage format for cell C1, in the same cell you need to write the formula:
Result - 54.29%
The formula is entered after the = sign and displayed in the formula bar. Cell A3 displays the result.
And let's do autocomplete. Thus, in column B, we will have values corresponding to 5 percent of the number in column A.
The $ signs fix cell C1. That is, by changing the value from 5% to 8% (or other), the values in column B will be recalculated automatically.
Another example of calculating percentages in Excel
So, we need to determine what percentage of the goods sold from the total number of products in the warehouse.
This requires the following steps:
- Enter the formula = С2 / D2 (number of goods sold / total number of products) in cell D2 and press Enter.
- In order not to waste time, it is recommended to use the autocomplete function - stretch the formula down as much as necessary.
- Select all filled cells in column D and set the percentage format.
- Evaluate the result:
There are four ways to choose a percentage format for a cell:
- Having selected the necessary cells, go to the context menu with the right mouse button. Please note that in this case it is possible to independently adjust the number of characters after the dot.
- Use the key combination Ctrl + Shift + 5.
- Select a format in the "home" tab on the taskbar.
- Enter a number with a% sign - the program will automatically select the required format.
Sometimes the opposite situation arises - it is necessary to determine how much the percentage of goods sold in a numerical value is. To do this, just select the cell for which you want to get the result, and multiply the percentage by an integer.
Determining the percentage of numbers
Calculating the percentage of numbers in Excel is very easy! The need to complete this task arises quite often - for example, in the case when it is necessary to assess the change in the level of sales for the past and current periods.
To understand how much sales increased in September, you need to do the following:
- Set in cell D2 the formula = (C2-B2) / B2 and press the Enter key.
- Pull D2 down the required number of lines.
- Select the received data and convert it to percentage format in any convenient way.
A positive value in column D indicates a profit, a negative value, respectively, a loss.
To visually assess the results of activities, you can make a diagram. To do this, select the column with percentages and select the type of chart in the "insert" tab.
Interest difference in Excel, how to subtract interest
Let me give you another example, similar to the previous one. Sometimes we need to calculate the percentage difference. For example, in 2017 we sold goods for 2,902,345 rubles, and in 2018 for 2,589,632 rubles.
Let's make a blank. And we will make calculations.
In cell C2, enter the formula:
This form shows the difference between the percentages. In this example, we sold the product in 2018 for an amount less than in 2017 by 10.77%. A minus sign indicates a smaller amount. If there is no minus sign, then we sold for a large amount.
If you have a lot of data, I advise.
How to calculate percentage of plan completion in Excel
The percentage of completion of the plan as a whole is calculated in the same way as I described above. But let's look at a more specific example. Namely, on a time tracking plan.
An example will be simple. An employee receives a salary of 10,000 rubles per month, depending on the percentage of days worked per month. And also the employee receives a bonus of 8000 depending on the fulfillment of the sales plan.
The corresponding percentage is multiplied by the rate and then summed up. The final amount will be the employee's monthly wages.
How to make a percentage chart in Microsoft Office Excel
How to make a pie chart in Excel showing percentage? Let's look at a simple example. Let's say we need to graphically display the share of shareholders in a company.
Let's create a simple data table.
We get the following diagram.
After adding labels, the corresponding numbers appear on the diagram.
In this menu, uncheck the Values box and set the Shares.
The percentage chart in Excel is ready.
After that, you can change the title of the chart, since the data is now displayed not in pieces, but as a percentage.
Other actions with percentages in Excel
Sometimes it may be necessary to add interest to a certain amount. A striking example of such a calculation is the calculation of the cost of goods with VAT. The problem is solved by the formula = number + (number * percentage).
To subtract a percentage of the total in Excel, you need to do the same, replacing addition with subtraction. Then the formula will look like this: = number- (number * percentage).
When performing such calculations, do not forget about the parentheses, because it depends on how accurate the result you get.
Percentage in Excel (Excel) updated: February 7, 2019 by the author: Anatoly Buzov
Excel is essential when you need to organize, process and save a lot of information. It will help automate calculations, make them easier and more reliable. Formulas in Excel allow you to carry out arbitrarily complex calculations and get results instantly.
How to write a formula in Excel
Before learning to do this, there are a few basic principles to understand.
- Each begins with an "=" sign.
- Values from cells and functions can participate in calculations.
- Operators are used as the usual mathematical symbols for operations.
- When you insert a record, the cell reflects the result of the calculation by default.
- You can see the construction in the line above the table.
Each cell in Excel is an indivisible unit with its own identifier (address), which is denoted by a letter (column number) and a number (row number). The address is displayed in the field above the table.
So how to create and insert a formula in Excel? Proceed according to the following algorithm:
Designation Meaning
Addition
- Subtraction
/ Division
* Multiplication
If you need to specify a number, but not a cell address, enter it from the keyboard. To specify a negative sign in an Excel formula, press "-". 
How to enter and copy formulas in Excel
They are always entered after pressing "=". But what if there are many calculations of the same type? In this case, you can specify one, and then just copy it. To do this, enter a formula, and then "stretch" it in the desired direction to multiply.
Position the pointer over the cell to be copied and move the mouse pointer over the lower right corner (over the square). It should take the form of a simple cross with equal sides. 
Press the left button and drag. 
Release when you want to stop copying. At this point, the calculation results will appear. 
You can also stretch to the right.
Move the pointer to an adjacent cell. You will see the same entry, but with different addresses. 
When copying in this manner, the line numbers increase if the shift is down, or the column numbers increase if to the right. This is called relative addressing.
Let's enter the VAT value into the table and calculate the price including tax. 
Price with VAT is calculated as price * (1 + VAT). Let's enter a sequence in the first cell. 
Let's try to copy the entry. 
The result is strange. 
Let's check the content in the second cell. 
As you can see, when copying, not only the price shifted, but also the VAT. And we need this cell to remain fixed. Let's fix it with an absolute link. To do this, move the pointer to the first cell and click on B2 in the formula bar. 
Press F4. The address will be diluted with a "$" sign. This is a sign of absolutely a cell. 
Now, after copying, the B2 address will remain unchanged.
If you accidentally entered data in the wrong cell, simply transfer it. To do this, move the mouse pointer over any border, wait until the mouse looks like a cross with arrows, press the left button and drag. At the desired location, simply release the manipulator.
Using functions for calculations
Excel offers a large number of functions that are categorized. You can view the complete list by clicking on the Fx button next to the formula bar or by opening the "Formulas" section on the toolbar. 
Let's talk about some of the functions.
How to set "If" formulas in Excel
This function allows you to set a condition and carry out the calculation depending on whether it is true or false. For example, if the quantity of goods sold is more than 4 packs, you should purchase more.
To insert the result depending on the condition, let's add one more column to the table. 
In the first cell under the heading of this column, set the pointer and click the "Logical" item on the toolbar. Let's choose the "If" function. 
As with inserting any function, a window will open to fill in the arguments. 
Let us indicate the condition. To do this, click on the first row and select the first cell "Sold". Next, put the ">" sign and indicate the number 4. 
On the second line, we will write "Purchase". This label will appear for those items that have been sold out. The last line can be left blank, since we have no action if the condition is false. 
Click OK and copy the entry for the entire column. 
To prevent "FALSE" from being displayed in the cell, open the function again and fix it. Move the pointer to the first cell and press Fx next to the formula bar. Insert the cursor on the third line and put a space in quotes. 
Then OK and copy again. 
Now we see which product should be purchased.
Formula text in Excel
This feature allows you to apply the format to the contents of a cell. In this case, any data type is converted to text, which means it cannot be used for further calculations. Let's add a column to format the grand total. 
Into the first cell we will enter the function (the "Text" button in the "Formulas" section). 
In the arguments window, indicate the link to the total amount cell and set the format “# rub.”. 
Click OK and copy. 
If we try to use this amount in calculations, we get an error message. 
"VALUE" indicates that the computation could not be performed.
You can see examples of formats in the screenshot. 
Date formula in Excel
Excel provides many options for working with dates. One of them, DATE, allows you to build a date from three numbers. This is useful if you have three different columns - day, month, year. 
Position the pointer over the first cell in the fourth column and select a function from the Date & Time list. 
Arrange the cell addresses accordingly and click OK. 
Copy the entry. 
AutoSum in Excel
In case you need to add up a large amount of data, Excel provides the SUM function. For example, let's calculate the amount for the goods sold.
Place the pointer in cell F12. It will calculate the total. 
Go to the Formulas panel and click AutoSum. 
Excel will automatically highlight the nearest numeric range. 
You can highlight a different range. In this example, Excel did everything right. Click OK. Pay attention to the contents of the cell. The SUM function was substituted automatically. 
When inserting a range, the address of the first cell is specified, followed by a colon and the address of the last cell. “:” Means “Take all cells between the first and the last. If you need to list several cells, separate their addresses with a semicolon:
SUM (F5; F8; F11)
Working in Excel with formulas: an example
We showed you how to create a formula in Excel. This is the knowledge that can be useful even in everyday life. You can manage your personal budget and control expenses. 
The screenshot shows the formulas that are entered to calculate the amounts of income and expenses, as well as the calculation of the balance at the end of the month. Add sheets to the workbook for each month if you don't want all tables to be on the same table. To do this, just click on the "+" at the bottom of the window. 
To rename a sheet, double-click it and enter a name. 
The table can be made even more detailed.
Excel is a very useful program, and its calculations offer almost unlimited possibilities.
Have a great day!
