Convert to Range for Subtotaling

Read this chapter, which provides a visual tutorial on converting a table back into a list of data and then subtracting the data into "chunks."

If you have a list of data in Excel that has not been put into a table format, creating an outline to help organize the data is easy. The first image below is an excerpt of the spreadsheet used in subunit 10.1.3: Filtering Data. Even though it looks like a table, these data have not been put into a table structure because the Subtotal command, located on the Data ribbon, is still available.

Image of spreadsheet with the data tab chosen. The subtotal command is highlighted on the far right of the ribbon.

In contrast, in the image below, the Subtotal command is not available because the list of data has already been made into a table. Even though these two images look the same on the surface, they are not. Notice the other greyed-out commands that cannot be used when a list is in table format. This is not to say that a table format is less desirable - it is usually favorable, but experience will help you to know when to use a table and when not to. Often, you can create and perform mathematical calculations on a table and then use the Convert to Range command to use the Subtotal command for optimal grouping.

Image of spreadsheet with the data tab chosen. The subtotal command is highlighted on the far right of the ribbon. Arrow shows the subtotal command is greyed out and cannot be used.

Now, let's look at the Convert to Range command. Let's say I want to subtotal each product type in the table. First, I would sort the table by the Type field A–Z. Then, go to the Table Tools Design toolbar and click the Convert to Range command (see the image below).

Image of spreadsheet with the data tab chosen. The subtotal command is highlighted on the far right of the ribbon. Arrow shows tupe sorted A-Z. Open this toolbar (design under table tools) and then on the far left in the Tools grouping, choose Convert to Range.

Next, a dialog box will pop up (see image below) to check whether you want to do this. Choose Yes so the Subtotal command will be available on the Data ribbon.

Dialogue box that asks - Do you want to convert the table to a normal range? - yes or no

Now go back to the Data tab and click the Subtotal command. A dialog box will pop up, and you can decide how to show the subtotals. You will Count how many of each type (laptop, projector, etc.) are listed. For a hint, use the example on the right, noting all of the selections made in each area of the Subtotal dialog box.

Subtotal dialog box. Each drop down arrow wil give you a selection of possibilities. Here selected is: At each change in (type), Use function: Count, Add subtotal to (type), Replace current subtotals, summary below data

Below is the resulting subtotaled list. Note that each Type has a count listed for that subsection.

Image of spreadsheet with resulting subtotaled list.

This concludes our quick introduction to subtotaling.

Source: Saylor Academy
This work is licensed under a Creative Commons Attribution 3.0 License.

Last modified: Monday, August 26, 2024, 3:27 PM