15 Subtotals Information and Practice

A large range of data can be summarized according to criteria supplied. In a large range of data, groups can be established within that data. For example, if the spreadsheet had data several cities each of the cities could be subtotaled for comparison with each other.

To accomplish subtotaling, the spreadsheet data must be first converted to a table then converted to a range. The next step is to use the Subtotal Next button in the Outline group and complete Subtotal Dialog Box.

The steps are:

A. Start with a spreadsheet with data that needs to be sorted. In this example, the months are sorted by the Type and Category are not.

B. Create a new sheet tab called Subtotal

C. Copy the original spreadsheet into the Subtotal tab created in #2.

D. Remaining in the Subtotal tab, select the entire table.

E. Click INSERT tab, and look for the Tables group. Click on the Table icon

F. A Create Table dialog box appears.

G. Notice the range is recorded using absolute references.

H. Be sure that there is a checkmark in front of “My table has headers.

I. Click Ok.

J. The data is now in a table and usually the rows are changed to a variegated color.

K. Notice, also, that a new tool bar has appeared at the top of screen called Table Tools, with a tab for DESIGN.

L. Look for the Tools group, and click on Convert To Range.

M. A question comes up whether to convert the table to a normal range. Answer YES.

N. Click in any cell in the table.

O. Click the DATA tab. Then look for the Outline group and choose Subtotal.

P. A dialog box appears that looks like this:

Q. At the change in: there is an arrow by which you can chose any heading.

R. Use function: SUM , COUNT, AVE, MAX, MIN are some of the choices

S. Add subtotal to: Click where the subtotal should appear.

T. Be sure that the other two items are checked as shown.

U. Click OK.

V. In the Practice Exercise, the values to use will be given to you.

W. The result appears as follows. (The entire worksheet is not shown.) Notice the subtotals are divided by TYPE, and the Sales are totaled for that type.

Practice Exercise : Sales Rep

1. Open the file called Sales Rep and save it to your Desktop or storage device.

2. Create a new Sheet called Subtotal. Copy the original spreadsheet and paste it to the new Subtotal sheet. ( Follow instructions for Letter B-D in the Information and Practice section page 1).

3. Continue to follow Letters E- O on Pages 1-2.

4. Starting with Letter P, complete the information for the Subtotal dialog box as follow:

• At each change in: choose .
• Use function: choose
• Add subtotal to: choose
• Make sure the other two boxes are checked.
• Click OK

5. The result should appear as follows: