Summarize a whole table in Excel without writing a formula

 Instead of typing a SUM formula, an easier way to go is to use the shortcut Alt + Equal sign (=)


But did you know that you can use this shortcut for a whole table?

If you have a table like the one below, select all the values plus one extra row and one extra column:


And use the shortcut:

Alt + Equal sign (=)

Instantly, Excel inserts all the formulas and calculates the sums:



Popular posts from this blog

SUM across multiple sheets in Excel

Image
If you have a report in Excel with multiple identical sheets, for example one sheet per month, that you want to add together in a separate sheet, there are a few different ways to do it. The most common way is to simply type an equal sign, click on B2 in the ‘Jan’ sheet, type a plus sign, click on B2 in the ‘Feb’ sheet, and so on. Then copy down and across. This takes a long time, and it’s easy to make mistakes when you write formulas like that. A better way to do it is to use this formula: =SUM(Jan:Dec!B2) (and copy down and across) With this formula, Excel looks at all the tabs from Jan through Dec and picks up the value in B2 in all of them. Note that you only need an exclamation mark after the second sheet reference. You have to be careful when you use this approach, though. If you insert or move another sheet between the ones you want to refer to in the formula, Excel assumes that you want to include that too. On the picture below you can see what might happen if you move a phone ...
Read more