Finding the Largest Value Using Subtotals
Note: For help with these instructions see the image above.
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:
- Drag select the data in cells A2 to D12 to highlight them.
- Click on the Data tab of the ribbon.
- Click on the Subtotal button to open the Subtotal dialog box.
- For the first option in the dialog box At each change in: select Region from the drop down list.
- For the second option in the dialog box Use function: select MAX from the drop down list.
- 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.
- For the three check boxes at the bottom of the dialog box, check off:
Replace current subtotals
Summary below data
- Click OK.
- 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 on page 1 of this tutorial.
Return to Index page