Using Auto Sum and the Function Library in Excel

The Function Library in Excel and the Auto Sum feature are two of the easiest and fastest ways by which to write formulas in Excel.  The beauty of each feature is that they write the formula for you and all you have to do is select the criteria.  This means that you do not need to have expert knowledge of all the Formula Operators that must be used in order for a formula to calculate correctly.  I highly recommend each of these features as a way to streamline workflow and reduce formula errors.

The Insert Function button and the Auto Sum button are both found on the Formulas Tab (across all versions of Excel from 2007 to 2013).  The Auto Sum feature, which appears on the Ribbon as a backward looking E also appears on the Home Tab.

Using Auto Sum

The default of Auto Sum is to capture the criteria directly above or beside the active cell.  So if you place your cursor in cell A5 and then click the Auto Sum button, Auto Sum will select Cells A1:A4 by default.  If you were to place your cursor in Cell E1 and click the Auto Sum button, Auto Sum would select Cells A1:D1 by default.  You can always click the Auto Sum button and then re-select the criteria range to customize the formula.  Once your formula is in place, simply hit the enter key on your keyboard. 

You will notice that there is a drop down arrow below the Auto Sum button.  This drop down arrow allows you to choose other functions besides the Sum feature.  Average will give you the average of a range of criteria, Count will count the number of entries and Max and Min will give you the largest and smallest numerical values in a range of data.  All of these selections are applied exactly the same way.  Simply select the function you wish to use, make sure your criteria range is correct and hit the enter key on your keyboard.

Using the Function Library

The Function Library contains all the formulas that are available within Excel.  You can access the functions in the Library either by clicking one of the category buttons in the Function Library or by searching for a function using the Insert Function button.  When you choose either one of these methods, the Function Arguments dialog box will launch.  The beauty of using this method is that it supplies all the Function Arguments necessary to write a formula and gives you an explanation of what each Function Argument represents.  All the end-user needs to do is enter the Function Arguments and click the “OK” button when done.  Excel will write the formula for you and include all the Operators necessary for the formula to calculate correctly.

Both the Auto Sum feature and the Function Library are excellent ways to quickly write accurate formulas and I highly recommend them as a Best Practice to my clients.



This entry was posted in: MS Excel Tips.
"Hawthorn Training delivers expert and up to date software training solutions for your business."