Convert to Range for Subtotaling
Read this tutorial on converting a table back into a list of data and then subtotalling the data into chunks. Pay attention to the commands you need in the toolbar to perform the operations.
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. Take a look at Figure 1. Although it looks like a table, these data have not actually been put into a table structure because the Subtotal command, which is located on the Data ribbon, is still available to use.
Figure 1
In contrast, in Figure 2 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 so that you can use the Subtotal command for optimal grouping.
Figure 2
Now, let's look at the Convert to Range command. Let's say you want to subtotal each type of product in the table. First, you 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 Figure 3).
Figure 3
Next, a dialog box will pop up (see Figure 4) to check whether or not you really want to do this. Choose Yes so that the Subtotal command will then be available on the Data ribbon.
Figure 4
Now go back to the Data tab and click the Subtotal command. A dialog box will pop up (see Figure 5) and you can decide how to show the subtotals. You will Count how many of each Type (laptop, projector, etc) is listed. For a hint, use the example on the right, noting all of the selections that were made in each area of the Subtotal dialog box.
Figure 5
Figure 6 shows the resulting subtotaled list. Note that each Type has a count listed for that subsection.
Figure 6
Source: Saylor Academy
This work is licensed under a Creative Commons Attribution 4.0 License.