“Auto Calculation”

Whether we are working with a large or small “Spreadsheet”, sometimes it can be useful to know the sum or the average of certain rows or columns (range) in our “Spreadsheet”.

To do so, we can use a “Formula” or we can use Excel’s “Auto Calculation” feature at the bottom of the spreadsheet.

For example, to find out the sum and average of a range of cells, just select that “Range” in the “Worksheet”, and the average, count and sum amounts are shown automatically in the status bar (bottom of the spreadsheet).

By default, the “Average”, the “Sum” and “Count ” are selected.

When right-clicking the status bar, a pop-up menu is displayed containing six (relevant) choices of which three are selected by default. To add or delete, check or uncheck the boxes next to the choices.

 

 

 

“Range” names

If you do not know what a “Range” is, and how to select it, Lesson 15.

OK, now that our “Range” is selected, we can give an appropriate name in the “Name box”, and click Enter.

Or you can use the “Formulas” tab (1) in the “Ribbon” and click the “Define Name” (2) button.

This opens a “New name” dialog box, which we aptly named (3) “Range”. “Range” names can not contain spaces and “Range” names must start with a letter. You can use numbers, but the range name MUST begin with a letter.

In the second box (4) “Scope”, we choose where we want to use this “Range” i.e, either in the entire “Workbook”, or just in a “Worksheet”.

In the “Comment box:” you can optionally insert your description about the “Range” you just identified.

The bottom box is already completed with the “Range” area: the cells which make up the range.

Click OK.

A faster way to do the same is to include a title when selecting the range. For example, if you would select C3 to C13, and click the “Create from selection” button, then the “Excel” “Range”, suggests the name from the values of our cell, in the above case “Jan”.

 

When you click on the downward pointing arrow to the right of the “Name box”, you see that the newly entered name will appear. Select it and the “Range” is assigned to the name selected in the “Worksheet”.

 

Navigating between the different “Range” names

To navigate between the different “Range” names in our “Workbook”, we use the downward pointing arrow, right next to the name box and click on the desired “Range”.

You can also find a range by clicking on the “Find and Select” button in the “Home” tab of the “Ribbon” and select “Go to …”.  In the dialog that appears, select the “Range” name and click OK.

 

“Name Manager”

To delete “Range” names , click the “Name Manager” button in the “Formulas” tab of the “Ribbon”.

Select the “Range” name in the “Name Manager” dialog and click the “Delete” button.
Choose Yes to confirm.

 

In the “Name Manager” dialog box, we can change our “Range”.

To set the “Range” name, click “Edit” button. This shows the “Edit Name” dialog where we can adjust the settings.

Click the OK button when you are satisfied.

 

The last option in the “Name Manager” dialog box is the “Filter”

The options in the filter are self explanatory.

 

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats