Did you know you can sum a column in Excel with one click?!
As brokers and underwriters, time is a precious commodity. We need results fast!
Because time is of the essence, we put together this guide on the 5 fastest ways to do a Sum in Excel
One Click – Status Bar
If you need to add an entire column, by far the fastest way to sum a column is to click on the letter of the column with the numbers you want to sum. That’s it!
The results are displayed below on the Excel Status Bar. The status bar will display Average, Count and Sum.
Click on the column letter to sum the column. The Average, Count and Sum are displayed on the status bar.
This method also applies to rows. Click on the row to Sum the numbers entered across a row.
Note that this method is sensitive to Data Filters. The results displayed on the status bar will reflect the data visible when filters are applied. We applied a filter to select only Crime policies on the above data. See how the results have changed:
We applied a Filter to the Policy Type. Now the status bar shows only the results for the visible data.
But what if you want to copy the numbers or display them in as numeric digits? Don’t worry, we have a few other tricks to show you!
The Traditional way to Sum and why you’ll never use it the same way again:
The SUM Function
The most common method to sum a column in excel is to endlessly scroll to the bottom of the column you want to sum. Then enter =Sum( and then select the range using your mouse with the numbers that you want to sum and press enter, like this:
The traditional way to sum is by entering =Sum( and then selecting the desired range. Forget it, this takes too long!
While this method gives you results, it can be time consuming if you have lots of data. Here are some tips to speed it up:
AutoSum
-
To navigate to the end of the column you want to sum, start by clicking anywhere in the column you want to sum. In this case we clicked on the header:
Click anywhere on the column you’d like to sum. In this case we selected the header.
2. Use Ctrl+Arrow Down to instantly get to the last row of the range.
Use Ctrl + Arrow to quickly navigate in the desired direction. This shortcut will take you to the end of the data range in no time.
3. After placing the cursor on the cell below, you can easily Sum by clicking on AutoSUM icon.
AutoSum button under the Home tab of the Ribbon
Alt=
Alternatively, if you liked the feeling of using Ctrl+Arrow Down, press the keys ALT and =, you will instantly have applied the SUM formula. Watch out, using keyboard shortcuts is addictive!
Applying Sum to more than One Column
What if you would like Sum applied to all columns in a spreadsheet? Simple:
Convert data into Table
1. Click on the data range you’d like to Sum – anywhere is fine.
2. Using your keyboard, press Ctrl and T. You will be asked to confirm the data range you would like to convert into a table. Confirm the selection.
3. Click on “Total Row” in your table design.
Be sure to click on Total Row on the Table Design tab of the ribbon.
4. Once you have clicked on Last Row, you have access to a drop down menu to apply a function to each column in the last row. Select Sum as needed.
An added benefit of this method is the ability to manipulate your data by applying filters. This feature can be combined with Filters to only add up visible cells.
And there you have it, 5 fast ways to Sum in Excel!
If you liked using the keyboard and shortcuts to apply Sum function, be sure to check out our Excel Course where we cover expanding selections and filling in Formulas using shortcuts.
Try out the methods above: read sum on the Excel Status, use Sum Function, use AutoSum, use ALT= or format your data as a Table. See which method works best for you and your every day.
If you have any questions, be sure to email us. Happy Learning and Excel with Excel!
