The Calculator/Validator

The most simple yet extremely useful analytical tool in Excel is the calculator. Once you have discovered the calculator you do not look at a spreadsheet in the same manner. You can find any subtotal even if the creator of the spreadsheet has not calculated it. You can validate any of the subtotals that are calculated on one sheet using data on the same sheet or another sheet.

The Calculator before Excel 2007

Select two or more  cells with numbers in them and take a look at the bottom of your screen, you will see . It is the sum of the selected cells. If you right click on , you will see this:

you can select this functionality to show the sum, the count, the average.... or nothing at all. If you chose to count non-empty cells just select any cells (numerical or not) and the result appears at the bottom of your screen.

When will you use this tool?

- You will first use the calculator to validate certain totals in a report before issuing it. You make sure that formulas work correctly.

- You can rapidly calculate some significant totals in a set of data that doesn't show any totals. You can count the number of values or entries. When you discover the database functionalities  and the SUMPRODUCT function you can automate your report and validate the results using the filters and the calculator.

- You will also use the calculator to calculate some totals that do not exist in reports that are designed by others. For example you can calculate quarterly subtotals from a report showing only monthly numbers or calculate easily "Year to Date" .

- When you discover the comparative analyses in lesson 2 you can look at a report and its raw data on the same screen. The raw data can be in the same workbook, in a different workbook or even in a different program. While showing the data and the report on a split screen you can validate  the totals in the report using the calculator in both views.

- You can use the calculator to analyse the data dynamically and then decide that such analysis is worthy of a special automated report and create it. The calculator then becomes a report designing tool.

The Calculator since Excel 2007

In Excel 2007 the different values are always visible in the status bar right below the screen. For example, open a new workbook and enter the value 33 in cells A1 and A2. Then enter the value John in B1, B2 and B3.

If you then select cells A1 and A2 the calculator will show the following results: The average of the selected value is 33, there are two values selected, there are 2 numerical values selected. The minimum value within the selection is 33, the maximum value is 33 and the sum of all values is 66

If you then select cells B1 to B3 the calculator will show the following results: There are 3 values selected.

Finally, if you select cells A1 to B3 the calculator will show the following results: The average of the selected value is 33, there are five values selected and two of them are numerical values, the minimum value within the selection is 33, the maximum value within the selection is 33 and the sum of all values is 66

Learn more on Excel
See the list of small downloads

There are  five sections in this website

Section 1: About Databases: 4 lessons
In this section we review the basic notions on databases and evaluate Excel as a database. You will then see how Excel can be used downstream from most centralized databases and data management programs. Finally we will discuss the "upgrade" from Excel to Access..
Section 2: Excel and External Data: 7 Lessons
There are many ways to get data into your Excel Workbooks and there are many ways to easily extract data from all the databases in your organization and the Internet. This section is centered on querying data
Section 3: Analyzing Data with Excel: 8 lessons
Once you have the data in the right formats within your Excel workbook it is time to use the powerful analytical tools of Excel including the database functionalities (sort, filter, subtotals), the integrated calculator, the comparative analysis tools and the mysterious and not so complicated pivot tables. 
Section 4: Reporting with Excel: 7 lessons
The report is a communication tool and you can not accept to compromise on its presentation. Excel is the best reporting tool allowing you to easily develop popular dashboards and to share these reports with multiple colleagues.
Section 5: Other Functionalities in Excel: 3 lessons
In this fifth section you will review the useful CTRL and ALT keystrokes, the conditional formatting, the drop-down lists and other interesting functionalities.