Excel is not only used to create tables. Its main purpose is to calculate numbers using formulas. It is enough to enter new values in the cells and the system will automatically recalculate them. However, in some cases, the calculation does not occur. Then, you need to find out why Excel does not calculate the amount.

The main causes of the malfunction

Excel may not calculate amounts or formulas for many reasons. The problem is often how wrong formula, as well as in system settings books. Therefore, it is recommended to use a few tips to find out which one is suitable in this particular situation.

Changing cell format

The program displays incorrect calculations if the specified formats do not match the value, which is in the cell. Then the calculation will either not be applied at all, or it will produce a completely different number. For example, if the format is text, then the calculation will not be performed. For the program, it's only text, not numbers. Also, there may be a situation where the format does not correspond to reality. In this case, the user will not be able to correctly insert the calculation, and Excel will not calculate the sum and will not calculate the result of the formula.

To check if the case is really in the format, go to the tab " home". First, you need to select an unverified cell. This tab contains format information.

If you need to change it, just click on the arrow and select required from the list. After that, the system will make a new calculation.

The list of formats in this section is complete, but without descriptions and parameters. Therefore, in some cases, the user cannot find the right one. Then, it is better to use another method. Just as in the first variant, select cell. After that, right-click and open the command "".

IN open window located full list formats with description and settings. Just select the one you want and click on OK».

Turn off the "Show formulas" mode

Sometimes the user may notice that instead of a number, the calculation itself is displayed and the formula in the cell is not considered. Then, you need to disable this mode. After that the system will output the final result calculation, not expression.

To disable the " Show formulas", you should go to the appropriate section" Formulas". Here is the window Dependencies". It is in it that the required command is located. To display a list of all dependencies, click on the arrow. From the list select " Show» and disable this mode, if it is active.

Errors in syntax

Often, incorrect display of the result is a consequence syntax errors. This happens if the user entered the calculation on his own and did not resort to the help of the built-in wizard. Then, all cells with errors will not produce a calculation.

In that case, you should check correct spelling each cell that produces an incorrect result. You can overwrite all values using the built-in wizard.

Enable formula recalculation

All calculations can be written correctly, but if the cell values change, the recalculation does not occur. Then it can be turned off. automatic calculation change function. To check this, go to the " File", then " Options».

In the open window, go to the tab " Formulas". Here are the calculation options. It is enough to check the box on the item " Automatically” and save the changes so that the system starts recalculating.

The program may perform a full calculation, but instead of a ready value, an error is displayed and the column or cell may not sum up the numbers. Depending on the displayed message, you can judge which malfunction has occurred, for example, division by zero or wrong format.

In order to double-check the syntax and correct the error, go to the " Formulas". The dependencies are the command that is responsible for the calculations.

A window will open that displays the formula itself. Here, click on " Calculate' to perform an error check.

Other errors

Also, the user may encounter other errors. Depending on the cause, they can be corrected accordingly.

Formula does not stretch

Stretching is necessary when several cells must do the same calculations with different meanings. But sometimes this does not happen automatically. Then, you should check that the function is installed automatic filling, which is located in the parameters.

The sum of cells is incorrectly calculated

The amount is also considered incorrect if the ledger there are hidden cells. The user does not see them, but the system performs the calculation. As a result, the program displays one value, and the real amount should be different.

The same problem occurs if values with decimal places. In this case, they need to be rounded up in order for the calculation to be performed correctly.

Formula is not calculated automatically

In the open window, go to setting up automatic recalculation and check the box on the corresponding command. After that, you need to save the changes.

Graphs and charts (5)
Working with VB project (12)
Conditional formatting (5)
Lists and ranges (5)
Macros (VBA procedures) (64)
Miscellaneous (41)
Excel bugs and glitches (4)

Excel doesn't count correctly. Why?

Often when calculating the difference of two cells in Excel, you can see that it is not zero, although the numbers are the same. For example, cells A1 and B1 contain the same number 10.7, and in C1 we subtract another from one:

And the strangest thing is that we don't end up with 0! Why?

The reason is obvious - cell format
First, the most obvious answer: if there is a comparison of the values of two cells, then you need to make sure that the numbers there are really equal and not rounded by the format of the cells. For example, if you take the same numbers from the example above, then if you select them - right mouse button - Format cells-tab Number-select the Numeric format and set the number of decimal places to 7:

Now everything becomes obvious - the numbers are different and were just rounded by the cell format. And of course they cannot be equal. In this case, it would be optimal to understand why the numbers are the way they are, and only then make a decision. And if you are sure that the numbers really need to be rounded to tenths, then you can use the ROUND function in the formula:
=ROUND(B1 ,1)-ROUND(A1 ,1)=0
=ROUND(B1,1)-ROUND(A1,1)=0
There is also a more radical method:

Excel 2007: office button -Excel options -Advanced -
Excel 2010: File -Options -Advanced -Set precision as displayed
Excel 2013 and above: File -Options -Advanced -Set precision as displayed

This will write all the numbers on all sheets of the workbook exactly as they are displayed by the cell format. This action it is better to perform on a copy of the book, because it brings all numerical data in all sheets of the book to the form as they are displayed on the screen. Those. if the number itself contains 5 decimal places, and the cell format is set to only 1, then after applying this option, the number will be rounded to 1 decimal place. However, this operation cannot be undone unless the workbook is closed without saving.

The reason is software
But often in Excel you can observe a more interesting "phenomenon": the difference between two fractional numbers obtained by the formula is not equal to exactly the same number written directly into the cell. For example, write the following formula in a cell:
=10,8-10,7=0,1
the result should be the answer TRUE. But in fact it will FALSE. And this example is not the only one - this behavior of Excel is far from uncommon in calculations. It can also be found in a less explicit form - when calculations are based on the value of other cells, which are also calculated by formulas, etc. But the reason is the same in all cases.

Why are seemingly identical numbers not equal?
Let's first understand why Excel considers the above expression to be false. After all, if you subtract the number 10.7 from 10.8 - in any case, you get 0.1. So somewhere along the way something went wrong. Let's write in a separate cell the left side of the expression: \u003d 10.8-10.7. 0.1 will appear in the cell. Now select this cell - right mouse button - Format cells-tab Number-select the Numeric format and set the number of decimal places to 15:

and now you can see that in fact the cell is not exactly 0.1 , but 0.100000000000001 . Those. in the 15th significant digit, we have a "tail" in the form of an extra unit.
And now we will figure out where this "tail" came from, because both logically and mathematically it should not be there. I will try to tell very briefly and without unnecessary abstruseness - if you wish, you can find a lot of them on this topic on the Internet.
The thing is that in those distant times (this is approximately the 1970s), when the PC was still something of an exotic, there was no single standard for working with floating point numbers (fractional, if simple). Why this standard at all? Then what computer programs they see numbers in their own way, and fractional ones in general with the status "everything is complicated." And at the same time, the same fractional number can be represented in different ways and operations with it can also be processed. Therefore, in those days, the same program, when working with numbers, could give a different result on different PCs. It is not an easy task to take into account all the possible pitfalls of each PC, so at one fine moment the development of a single standard for working with floating point numbers began. Omitting various details, nuances and interestingness of the story itself, I will only say that in the end it all resulted in IEEE754 standard. And in accordance with its specification, in the decimal representation of any number, errors are allowed in the 15th significant digit. Which leads to inevitable errors in the calculations. Most often, this can be observed precisely in subtraction operations, because. it is the subtraction of numbers close to each other that leads to the loss of significant digits.
You can also learn more about the specification itself in the Microsoft article: Results of floating-point arithmetic operations in Excel may be inaccurate
This is precisely the reason for this behavior of Excel. Although in fairness it should be noted that not only Excel, but all programs based on this standard. Of course, a logical question arises: why did they adopt such a buggy standard? I would say that a compromise was chosen between performance and functionality. Although there may have been other reasons.

Much more important is something else: how to deal with it?
In fact, no way, because. this is a software bug. And in this case, there is no other way out, how to use all sorts of patches like ROUND and similar functions. At the same time, ROUND should be used here not as it was demonstrated at the very beginning, but a little differently:
=ROUND(10.8 - 10.7;1)=0.1
=ROUND(10.8-10.7,1)=0.1
those. in ROUND we should put the "buggy" expression itself, and not each of its arguments separately. If you put each argument, then this will not give an effect, because the problem is not in the number itself, but in how the program sees it. And in this case, 10.8 and 10.7 are already rounded to one digit and it is clear that rounding each number separately will not give any effect at all. You can, however, get out the other way. Multiply each number by a certain value (say by 1000 to remove 100% decimal places) and then subtract and compare:
=((10,8*1000)-(10,7*1000))/1000=0,1

I would like to believe that at least someday Microsoft will be able to defeat the described feature of the IEEE754 standard, or at least make a patch that will perform simple calculations no worse than a 50-ruble calculator :) Did the article help? Share the link with your friends! Video lessons

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; background-color:#333333; opacity:0.6; filter:a lpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

In this article, we will look at what AutoSum is and consider the most effective ways use Autosums in Excel. You will learn how to use autosum in excel, you will see how to calculate autosum in excel column or row with AutoSum buttons, sum only the visible cells, the entire selected range vertically and horizontally at a time, and find out the most common reason why AutoSum in Excel gives 0.

The most common feature that most users use Microsoft Excel, is the SUM function. Not surprisingly, Microsoft Excel decided to add a dedicated button to the Excel ribbon that automatically inserts the SUM function.

In fact, AutoSum in Excel automatically enters a formula to sum the numbers on your worksheet. In this article, we will cover the following information:

Where is AutoSum located in Excel

AutoSum button can be found in two places on the Excel ribbon.

HOME tab --> Editing group --> « AutoSum»:

AutoSum in Excel - AutoSum Button in Excel

Tab " FORMULA" --> group " Function Library» --> « AutoSum»:

AutoSum in Excel - AutoSum button in the Formulas tab

Now where is AutoSum in Excel You know.

How to do an autosum in Excel

Whenever you need to autosum a single range of cells, be it a column, a row, or multiple adjacent columns or rows, you can use Formula in Excel AutoSum For automatic creation the corresponding SUM formula.

To use AutoSum in Excel, follow these 3 simple steps:

Select the cell next to the numbers you want to sum:

To sum a column, select the cell immediately below the last value in the column.
To sum a row, select the cell to the right of the last number in the row.

AutoSum in Excel - Select the cell after which you want to calculate the sum

Click AutoSum button on the tab " HOME" or " FORMULA".

The sum formula appears in the selected cell, and the range of cells to be summed is highlighted (B2:B6 in this example):

AutoSum in Excel - Click the AutoSum button on the Home and Formulas tab (in this case, on the Formula tab)

In most cases, Excel chooses the correct range for the grand total. In the rare case that the wrong range is selected, you can correct it manually by entering the correct range in the formula, or by dragging the cursor over the cells you want to sum.

To sum multiple columns or rows at once, select multiple cells at the bottom or right of your table, respectively, and then click AutoSum button or use . For getting additional information see paragraph .

Press the Enter key to complete the formula.

You can now see the calculated sum in the cell and the SUM formula in the formula bar:

AutoSum in Excel - Press the Enter key to complete the SUM formula

Let's explore the reasons why autosum in excel gives 0.

AutoSum in Excel does not count

There are times when AutoSum not working in Excel, that is autosum in excel gives 0. The most common reason why AutoSum not working in Excel are numbers formatted as text. At first glance, these values may look like normal numbers, but Excel treats them as text strings and is not included in the calculations.

The most obvious indicators of numbers formatted as text are their left alignment and the little green triangles in the top left corner of the cells. To fix such text numbers, select all problematic cells, click the warning sign, and click Convert to Number.

AutoSum in Excel - Convert number in text format to number

Numbers may have text format for various reasons, for example, they may be imported from an external source. Now you know the reason why AutoSum in Excel does not count your data and the solution to this issue.

AutoSum in Excel hotkeys

If you are one of those Excel users who prefer to work with the keyboard rather than the mouse, you can use the following combination AutoSum hotkeys in Excel to sum cells:

AutoSum in Excel - AutoSum in Excel hotkeys

Holding down the Alt key and pressing the equal sign (=) inserts the SUM formula in the selected cell(s) in the same way as pressing AutoSum buttons on the ribbon, and then you press the Enter key to enter the formula.

How to use AutoSum with other functions

Besides summing cells, you can use the AutoSum button to insert other functions such as:

AVERAGE - returns the average (arithmetic mean) of numbers.

COUNTER - counts the number of cells with numbers.

MAXIMUM - returns the largest value.

MINIMUM - returns the smallest value.

All you have to do is select the cell where you want to paste the formula, click the AutoSum dropdown arrow, and select desired function from the list.

For example, this is how you can get the largest number in column B:

AutoSum in Excel - Using the AutoSum button to insert other formulas (Max in this case)

If you select "Other Functions" from the "AutoSum" drop-down list, Microsoft Excel will open the "Insert Function" dialog box, where you can find the desired formula in the list or use the search.

How to use AutoSum in Excel for visible cells only

You already know how to use AutoSum in Excel for a general column or row. But did you know that you can use this function to sum only visible cells, for example in a filtered list?

If your data is organized in an Excel spreadsheet (which can be easily done by pressing the keyboard shortcut ctrl+t) and you have filtered your data by applying one of the filter options, pressing AutoSum buttons inserts the SUBTOTAL formula, not the SUM formula, as shown in the following image:

AutoSum in Excel - Using AutoSum for Visible Cells

The 3rd, 6th, 9th and 12th rows were hidden in the original table. In this case, using AutoSum and the SUBTOTAL function, only the visible cells were summed.

Now you know how to use AutoSum in Excel to automatically sum cells, reasons why autosum in excel gives 0, . But you might want to learn a few time-saving tricks that could make your work even more efficient.

How to use AutoSum for more than one cell at a time

If you want to sum the values in multiple columns or rows, select all the cells in which you want to insert the SUM formula, and then click the AutoSum button on the ribbon or .

For example, you can select the cell range B7:D7, click AutoSum, and as shown in the following image, the values in each of the three columns are summed individually:

AutoSum in Excel - AutoSum Multiple Columns at Once

How to sum selected cells vertically and horizontally

To summarize only certain cells in a column, select those cells and click AutoSum button. If you want to calculate the sum of the selected cells by column, then select the cells and click AutoSum.

AutoSum in Excel - Sum selected cells vertically across columns

If you want to sum cells by row, select the cells you want to sum and one empty column on the right. Excel will sum the selected cells horizontally and insert the SUM formula into the empty column included in the selection:

AutoSum in Excel - Sum selected cells horizontally across rows

To sum cells across columns and across rows, select the cells you want to sum plus one empty row below and one empty column to the right, and Excel will automatically calculate the sum of the selected cells vertically and horizontally:

AutoSum in Excel - Sum selected cells to columns and rows

Now you know, how to do autosum in excel, Why autosum in excel gives 0 and how to fix it and also how to use AutoSum Hotkeys in Excel and much more. In addition to our resource, there is another article that discusses various ways, .

Have you encountered the problem that Excel does not calculate the formula? If yes, then you should probably divide this problem into two main reasons:

The first option - you entered a formula, it is displayed, but not considered.

The second option - you entered a formula, it is considered, but an error is displayed.

Excel reads the formula but throws an error

How to be here. First, I suggest reading about the errors -. In the same article, a solution to many problems is proposed. To deal with the case when there is an error in the calculations, there is a special tool - Calculate formula (Formulas - Section Dependencies of formulas - Calculate formula)

If you select this command, a special window will open, with which you can execute any formula step by step. For example

Click Calculate and step by step you will have the results of the calculation. This is very handy when you have 10 or more conditions or nested formulas!

The formula doesn't count. Other cases:

I also met with cases when the formula is not calculated, because the formula view mode was accidentally turned on (Task ribbon - Formulas - Formula Dependencies section - Show formulas)

Quite often gives an error

As the hint suggests, the following errors can be made. But most often this error occurs if you put a bracket in the wrong place - check if all the brackets are in the right place - they are highlighted for this.

VALUE() function

A formula with such a function is also used to convert text to a number. More

Share this article with your friends, leave comments and good luck with spreadsheets!

Share our article on your social networks:

Today I encountered a situation where excel formulas not working when dragging, i.e. there were two columns and it was necessary to calculate the difference of the first two columns in the third column. As a result, a formula is made and stretched down to the end, but the result from the first cell is copied to the remaining cells.

1 option

It is likely that you have disabled automatic recalculation of the sheet, to do this, simply press the key F9

2 Option for all versions of Office

Check that the cell format is not text (because of this, calculations and, accordingly, formulas may not work)

PS to fix this, select the cells and select format General (see picture Cell Format)

3 Option for Office 2010

Check the formula calculation options in the settings.

To do this, click on the FILE-> OPTIONS-> FORMULA menu and in the "Calculation Options" menu, see where you have a tick (see the picture it should be like this)

4 Option for Office 2007/2010

For Office 2007 the path is Formula->Calculation Options-> and tick the box Automatic, except for data tables.(see picture below)