Excel Database Subtotals

NOTE: If Excel recognizes your set of data as a database you can sort the data

In Excel there is a SUBTOTAL function and  there is also a subtotal functionality. Here is the functionality.

IMPORTANT NOTE: When you want to see the subtotals by clients you must first sort the database by clients.

Click anywhere in the database and go to the menu "Data/Subtotals..." and this window appears.

In the first text box a list of the names of your fields appear (the words in the title cells of each of you columns). Select the field (column) for which you want a subtotal (by client, by product, by date or other). In the second text box select the kind of subtotal that you want (sum, count, average or other). In the list box, add a check for all the fields that you want subtotaled. Click on "OK" .

Once you have activated the functionality Excel calculates the subtotals (be patient if you have thousands of records (rows)) and at the end of the process you see your records, the subtotals and the grand total. On  the left of the spreadsheet you will see this:

If you click on "1" you see only the grand total, on "2" you see the grand total and the subtotals and on "3" you see everything.

To deactivate the subtotals functionality click within the database, go to the menu "Data/Subtotals.." and select

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.