helpful handout subtotal report

8/7/2019 Helpful Handout Subtotal Report

1/8

Hepful Handout: Creating a Subtotal Report

Copyright 2005 PPCT.ca Page 47 of 106

5 Data Analysis Tools

Excel has a number of data analysis tools. Data can be analyzed numerically and byusing text entries as a basis for analysis also. In this segment of the module we will usefive of Excels analysis tools; the Subtotal Report, Pivot Tables, Groups and Outlines,The Solver, and a Histogram.

5.1 Creating Subtotals

The Excel Subtotal Report gives users the opportunity to organize their data into logicalgroups and then perform some analysis based on the content of the group(s). As part ofthis activity, Excel also provides the ability to group data items that are similar. Thisgives users great flexibility and control over what is being displayed at any time on theExcel window.

For our example, we will reexamine our Employee List database and organize it so thatit is grouped by Department and then, within each department, by Gender.

This will allow us to summarize the total salary cost of each department and then displaythe average salary of females compared to males within each department.

1) Click File.

2) Click Recently Accessed file 1Validation.xls.


2/8

Helpful Handout: Creating a Subtotal Report


3) Click cell B3. (Any cell in the data area will be fine.)

4) For this feature to work correctly, the data must be sorted into the correct grouping.Click Data | Sort.

5) Enter the Sorting Criteria as shown below.

6) Click OK.7) Click Data | Subtotals.


3/8



8) Click the drop-down for At each change in.

9) Click Department.10) Click the drop-down for Use function.

11) Click Sum.12) In the Add subtotal to area, click a checkmark into the Annual Salary box.

Remove any other checkmarks that may be showing.

13) Click OK.


4/8



14) Excel will calculate the total salary for each department.

15) Click Data | Subtotals.

16) Click the drop-down for At each change in.

17) Click Gender.18) Click the drop-down for Use function.

19) Click Average.


5/8



20) Ensure that the checkmark for Add Subtotal to is still showing in the Annual Salarybox.

21) Click to remove the checkmark from Replace current subtotals.

22) Click OK.23) Excel will now show the new analysis.


6/8



5.2 Groups and Outline

Once the data has been organized by sorting into the required order and there aresuitable subtotals and grand totals, Excel can detect this structure and provide a

grouping of similar data. This allows the user to display various levels of detail for eachgroup or the data as a whole.

1) Click the button labeled 1 in the upper left corner of your window.

2) Excel collapses the details of the report to its lowest level of detailthe GrandAverage and Grand Total.

3) Successively click buttons 2, 3 and 4 to see increasing levels of detail.


7/8



4) Click the button labeled 2 to reduce the details again.

5) Click the plus sign button shown below.

6) Excel will expand only the details in that region of the data.

7) Click the minus signbutton shown below.

8) Excel collapses the details again.


8/8



9) To remove the subtotals click Data | Subtotals.

10) In the Subtotals dialogue box click Remove All.

34) Click Close Window.

35) Click No.

helpful handout subtotal report

Documents