How to Find the Largest Values in an Excel Database or List

Excel 2007 Subtotal Feature

Ted French

Excel's Subtotal feature works by inserting the SUBTOTAL function into a database or a list of related data. Using the Subtotal feature makes finding and extracting specific information from a large table of data quick and easy.

Even though it is called the "Subtotal feature," you are not limited to finding the sum or total for selected rows of data. In addition to the total, you can also find the largest values for each subsection of a database.

This step-by-step tutorial includes an example of how to find the highest sales total for each sales region.

The steps in this tutorial are:

  1. Enter the Tutorial Data.
  2. Sorting the Data Sample.
  3. Finding the Largest Value.

Enter the Subtotal Tutorial Data

Excel 2007 Subtotal Feature

Ted French

The first step to using the Subtotal feature in Excel is to enter the data into the worksheet.

When doing so, keep the following points in mind:

  • It is important to enter data correctly. Errors, caused by incorrect data entry, are the source of many problems related to data management.
  • Leave no blank rows or columns when entering the data. This includes not leaving a blank row between the column headings and the first row of data.

For This Tutorial

Enter the data into cells A1 to D12 as seen in the image above. For those who do not feel like typing, the data, instructions for copying it into Excel, are available at this link.

Sorting the Data

Excel 2007 Subtotal Feature

Ted French

Before subtotals can be applied, your data must be grouped by the column of data you want to extract information from.

This grouping is done using Excel's Sort feature.

In this tutorial, we want to find the highest number of orders per sales region so the data must be sorted by the Region column heading.

Sorting the Data by Sales Region

  1. Drag select cells A2 to D12 to highlight them. Be sure not to include the title in row one in your selection.
  2. Click on the Data tab of the ribbon.
  3. Click on the Sort button located in the center of the data ribbon to open the Sort dialog box.
  4. Choose Sort by Region from the drop-down list under the Column heading in the dialog box.
  5. Make sure that My data has headers is checked off in the top right corner of the dialog box.
  6. Click OK.
  7. The data in cells A3 to D12 should be now sorted alphabetically by the second column Region. The data for the three sales reps from the East region should be listed first, followed by North, then South, and last the West region.

Finding the Largest Value Using Subtotals

Excel 2007 Subtotal Feature

Ted French

In this step, we will use the Subtotal feature to find the highest sales amount per region. To find the highest or largest value, the Subtotal feature uses the MAX function.

For this tutorial:

  1. Drag select the data in cells A2 to D12 to highlight them.
  2. Click on the Data tab of the ribbon.
  3. Click on the Subtotal button to open the Subtotal dialog box.
  4. For the first option in the dialog box At each change in: select Region from the drop-down list.
  5. For the second option in the dialog box Use function: select MAX from the drop-down list.
  6. For the third option in the dialog box Add subtotals to: check off Total Sales only from the list of options presented in the window.
  7. For the three checkboxes at the bottom of the dialog box, check off:
    Replace current subtotals
    Summary below data
  8. Click OK.
  9. The data table should now include the highest sales total for each region (rows 6, 9, 12, and 16) as well as the Grand Max (the highest sales total for all regions) in row 17. It should match the image at the top of this tutorial.