Excel4Business - Our Blog

Excel Tips and Tricks from our Experts

Expert Excel Help - Beginning Excel; SUM, AUTOSUM and SORT

Microsoft Excel is a versatile spreadsheet tool to help you manage and perform quick and accurate calculations. In setting up your tables in Excel, you need to be aware of a few things.

The table below is a generic example. As you can see, we have clearly marked out our header and highlighted the cells. Down the left hand side, we have listed some key data, in this case the names of sales staff.

Clicking in a cell within the Salesperson range will allow us to automatically sort the names by alphabetic order. On the main toolbar, go to the Editing panel and click Sort & Filter; choose option A-Z. Notice how Excel automatically understands the limits of the range and avoids including the header in the sort.

Excel has other 'intelligent' features such as the ability to know which figures to include in a basic SUM request. The SUM function simply adds together the amounts of specified cells. Having selected the first cell under the Y-T-D header (Year To Date), we then go to the Formulas tab on the main toolbar and click on AutoSum; this two step process is shown below:

The second step requires copying down the SUM formula so that all other totals are received. Do this by selecting the result in F4, use Ctrl C to copy, then use the mouse to select the empty cells in the Y-T-D column. Finally, use Ctrl V to paste the formula into those cells:

We can then use Sort & Filter again to find the most successful sales person (this time use Z-A as this also represents highest to lowest):

Finally, we can format the row underneath all of our figures, and by selecting all the relevant cells and clicking on AutoSum again, we receive all totals.

This blog is designed to give you a simple introduction. To learn more, contact our Excel gurus today.