Excel and Pivot Tables

PowerSchool High School

Excel and Pivot Tables PowerSchool High School Class of 2009

Contents Introduction ......................................................................................................................................................................... 3 Creating a Pivot Table ....................................................................................................................................................... 4 Lab 1 – Create a Pivot Table ................................................................................................................................... 4

Using Filters ........................................................................................................................................................................ 6 Lab 2 – Using Filters ................................................................................................................................................ 6

Moving Fields between Rows & Columns ................................................................................................................... 7 Lab 3 – Moving Fields.............................................................................................................................................. 7

Viewing Multiple Data Calculations ............................................................................................................................... 8 Lab 4 – Multiple Data Points ................................................................................................................................... 8

Grouping Data ..................................................................................................................................................................... 9 Lab 5 – Grouping Data Points................................................................................................................................. 9

Creating a Pivot Table Chart ......................................................................................................................................... 11 Lab 6 – Pivot Table Chart ...................................................................................................................................... 11

Drilling into Data from the Pivot Table ....................................................................................................................... 12 Lab 7 – Drilling into the Data................................................................................................................................. 12

Formatting the Pivot Table ............................................................................................................................................ 13 Lab 8 – Formatting the Pivot Table ...................................................................................................................... 13

PowerSchool High School Jun2009

2

Introduction Are you constantly being asked questions about your PowerSchool data that you know is in there but aren’t sure how to get out? Do you want to see trends and patterns at a glance? Then PivotTable reports are for you. They enable you to create new views of PowerSchool data in seconds. Hundreds or thousands of pieces of information swing into place, revealing the meanings behind the data. I know that pivot tables are a daunting feature of Excel but I’m confident that this course will teach you how to harness the power of pivot tables. Once mastered, you will be seeing your data in an entirely new light. During this course we will cover the following:        

Creating a pivot table Using Filters to limit what you see Moving fields between rows and columns Viewing multiple data calculations – showing data as count and % of total Grouping data Creating a Pivot table chart Drilling into data from the pivot table Formatting Pivot Table Reports


3

Creating a Pivot Table A PivotTable report is an interactive way to quickly summarize large amounts of data. Use a PivotTable report to analyze numerical data in detail and to answer unanticipated questions about your data. A PivotTable report is especially designed for:  









Querying large amounts of data in many user-friendly ways. Subtotaling and aggregating numeric data, summarizing data by categories and subcategories, and creating custom calculations and formulas. Expanding and collapsing levels of data to focus your results, and drilling down to details from the summary data for areas of interest to you. Moving rows to columns or columns to rows (or "pivoting") to see different summaries of the source data. Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data to enable you to focus on the information that you want. Presenting concise, attractive, and annotated online or printed reports .

The 1st thing you need is a question to be answered. How many females are taking Auto tech? What class has the largest concentration of ELL students? What program has the largest number of students failing? Once you have the question you will need to use the DDE, or an ODBC connection to extract the data from the PowerSchool tables. Then the fun begins. Lab 1 – Create a Pivot Table Question: How many Males and Females attend the district in each grade level? Steps:

1. Open excel 2. Open the provided text file accepting all defaults a. File

Open


Enrollment-pivot.txt

Finish

4

3. Highlight any cell in the data Excel 2007

Excel 2003

Insert tab



Data Drop down tab



Select Pivot Table and Pivot Chart Report

Click Pivot Table 

Click

Click

4. You now have 4 areas to work with a. Report Filter b. Row labels c. Column labels d. Value or Data By dragging fields into these areas Excel creates an aggregate view of the data 5. Drag Grade_level field to Row Labels 6. Drag Gender to column labels 7. Drag gender to value or data area 8. Your 1st pivot table is complete

9. Double-click the Sheet1 tab and name it Enrollment 10. Drag Program to the row area and place it above (left) of grade 11. How many programs have students in 14 th year?


5

Using Filters As you continue to add information to your pivot table it may start to get overwhelming. You can limit what you see based on filters. Each row or column field can be limited to see individual or selected values. Lab 2 – Using Filters Question: How many programs have students in the 14 th grade? List them in order of largest concentration to smallest. Steps:

1. Start with the pivot table created in Lab 1 – Enrollment tab 2. Use the drop down

next to the field you’d like to filter (grade_level) Excel 2007

Excel 2003

3. Select 14 to limit the list to just programs with students in their 14 th year 4. How many programs have students in the 14 th grade? 5. To sort by number of students, place your cursor in the grand total column and click

NOTE: Grand totals and calculations will adjust to reflect the new values


6

Moving Fields between Rows & Columns To change the view or layout of the data you can move fields between the rows and columns and still maintain the information. Lab 3 – Moving Fields Steps:

1. Start with the pivot table from Lab 2 – Enrollment tab 2. Remove the filter from the grade_level 3. This is a simple drag and drop. Let’s try a few a. Grab Gender from the Column header and drag it to the row area below (after) Grade_level b. Move Gender to the top (left) of the row area c. Move Grade_level to the column area d. Move Gender back to the column area under (right) of grade_level.


7

Viewing Multiple Data Calculations There are times when you need to see not only the count of but also the % it makes to get the bigger picture. This lab will let you do just that. Lab 4 – Multiple Data Points Question: How many seniors are female and what percentage of seniors is that? What is the overall % of males to females?

Steps:

1. Start with the pivot table from Lab 3 2. Remove Program form the row area 3. Move the grade_level to the row area 4. Drag the Gender from the field list to the data or value area 5. You should see 2 columns with the same inforamtion 6. Right click on the 2 nd column of data and choose Value Field Setting


8

7. Click the show values as tab and use the drop down next to Normal to select % of Row

8. Click Ok 9. Since you chose % of row and grade level is the row field, you are seeing the % of males and females in each grade level. Which grade level has the highest level of females? 10. Repeat steps 6 and 7 but this time select % of column. What are you seeing now? 11. Remove grade_level from the row area and drag ethnicity into the row area. Which gender has a more diverse makeup? 12. Repeat steps 6 and 7 and change back to % or row. Which ethnic group has a higher female population?

Grouping Data At times you may want to look at one data point in comparison to multiple others. For example if you wanted to compare the number of Caucasian students against the rest of your student population you could us the grouping feature. Lab 5 – Grouping Data Points Question: Which programs have the highest and lowest diversity? Steps:

1. Use the steps in lab 1 to create a new pivot table


9

2. Start by dragging ethnicity into the row and value areas

3. Highlight all ethnic codes but W – Right click and select Group

4. In the row area, you will now have 2 ethnicities, the original and the grouped one.

Drag the original one out of the row area leaving just the grouped one 5. Click on the Group1 and change the name to Diverse 6. Double-click the Sheet2 Tab and name it Diversity 7. Drag Programs into the row area above (left) of the ethnicity 8. Move Ethnicity(2) from the row area to the column area (under the ∑ Values) 9. Right click in the column area – choose Value Field Settings – Show Values as - % of Row 10. Right Click in the pivot table and choose Pivot Table Options

11. Under the Totals and Filters tab – remove the



from Show grand totals for rows – click OK

12. Visually find the most and least diverse programs.


10

Creating a Pivot Table Chart At times you may want to look at one data point in comparison to multiple others. For example if you wanted to compare the number of Caucasian students against the rest of your student population you could us the grouping feature. Lab 6 – Pivot Table Chart Steps:

1. Start with the pivot table report created in Lab 5 – Diversity tab

2. Click the Options tab under PivotTable Tools 3. Click PivotChart 4. In the Insert Chart – select the stacked cylinder column chart

5. By default in 2007 the chart will be added to the selected worksheet. 6. Right click on the blue border of the chart and click move chart 7. In the Move Chart window – click New sheet and name the sheet CH-Diversity – Click OK

8. From the chart view it is easier to see programs lacking in diversity PowerSchool High School Jun2009

11

Drilling into Data from the Pivot Table The pivot table is great for giving analysis and big picture views. Once we have the area of concern pinpointed, it would be nice to be able to drill in and get back to the specifics. This lab will show you how to do just that. Lab 7 – Drilling into the Data Question: I am the Special Ed counselor for all the automotive related programs and I would like to be able to identify the students in these programs with learning disabilities and get their specific information.

Steps:

1. Using Lab one create a new pivot table. 2. Drag programs into the row area 3. Ctrl-click on Auto collision, Auto Tech Adv, Diesel & Equipment and Lawn, Cycle & Marine Mechanics to select just the auto related programs – then right-click one of the highlighted programs and select Group 4. Drag the original Programs field from the row area 5. Use the drop down arrow next to Row labels to filter on Group1 6. Drag primary disability to the row and value areas 7. There are 72 students identified with a learning disability in these 4 programs. 8. To get back to the specifics – double-click on the number 72 in the pivot table 9. A new sheet will open with a copy of the data for these 72 students. You can now use additional pivot tables to identify teachers, sessions, etc…


12

Formatting the Pivot Table Let’s face it, while they have been extremely useful in getting information, they don’t look that great and are sometimes hard to read. That’s where formatting comes into play. Lab 8 – Formatting the Pivot Table Steps:

1. Using the steps in Lab 1 create a new Pivot Tabe 2. Drag Teacher, expression, course Name and gender to the row area. 3. Drag grade_Level to the column area 4. Drag gender to the Value (data) area 5. For 2007 users, Right-click within the pivot table and choose PivotTable Options a. Click the Display tab and



the box in front of Classic PivotTable Layout

6. Right-click the column headers for expression, course name and gender and remove the front of subtotal column



in

7. Click the header for Expression and change it to Period 8. Select Colmun B and on the Home Tab – under alignment - choose to center the column 9. Repeat step 8 for gender and grade columns 10. Highlight columns A thru D and double click to the left of “A” to automatically widen the columns

11. Highlight columns E thru H – Right click on E and choose Column Width – set to 8 12. Click the header for Grand Total – on the Home Tab click 13. With Cursor in the Pivot Table – Click Design under the PivotTable Tools tabs 14. Click the in PivotTable Styles to open up all the styles – Pick one from the top 2 rows of the Medium area 15. Highlight the Gender column (D) 16. On the Home tab choose Conditional Formatting – New Rule


13

17. Select Format only cells that contain and enter the following

18. Click

Set font to Bold and color to Pink

19. Repeat steps 16 thru 18 for value = M and set color to Blue

20. Highlight the cells E5 – G5 21. On the Home tab choose Conditional Formatting – New Rule 22. Select Use a formula to determine which cells to format – type the following

23. Click

Set font to Bold and color to Pink

24. Repeat steps 21 thru 23 for value = M and color to Blue 25. Click the 26. Click the

and copy this formating fromE6 – G269 to see the results (


Print – Print Preview)

14

Anatomy of a PivotTable Report – Excel 2007

Report Filter A field from the source data that you assign to a page ( or filter) orientation in a PivotTable report. For example, grade is a page field. You can use the Year field to display summarized data for only 2003, only 2004, and so on. 1

2 7

Values field A field from the source data that contains values to be summarized. For example, Count of studho is a data field.

For most types of source data you can choose how to summarize data (for example, by sum, average, or count). A data field usually summarizes numbers, but it can also summarize text. For example, you can count the number of times a specific text entry (such as Yes or No) appears in a field. 3

Column field A field from the source data that you assign to a column

orientation in a PivotTable report. For example, ethnic is a column field. 4

Item A subcategory of a row, column, or page field. For example, the ethnic field contains the following items: AA and L. The cela04 field contains these items: FB, BB, BA, PR, AD

5

3

1

Row field A field from the source data that you assign to a row orientation in a PivotTable report. For example, cela04 is a row field. Data area The cells in a PivotTable report that contain counted data. For example, the value in cell C5 counts the number of Latino students who performed at the Far Below Basic level on the 2004 CST ELA (42). 6

5

7

Field List List of all possible fields from the original data.

8

PivotTable Tools Provides quick access to editing and customizing your PivotTable.

2

4


6

15

Anatomy of a PivotTable Report – Excel 2003

1 2

3 5

4

6 7

8

1

2

Page field

A field from the source data that you assign to a page ( or filter) orientation in a PivotTable report. For example, grade is a page field. You can use the Year field to display summarized data for only 2003, only 2004, and so on.

Data field

A field from the source data that contains values to be summarized. For example, Count of studho is a data field. For most types of source data you can choose how to summarize data (for example, by sum, average, or count). A data field usually summarizes numbers, but it can also summarize text. For example, you can count the number of times a specific text entry (such as Yes or No) appears in a field.

3

Column field A field from the source data that you assign to a column orientation in a PivotTable report. For example, ethnic is a column field.

4

Item

A subcategory of a row, column, or page field. For example, the ethnic field contains the following items: AA and L. The cela04 field contains these items: FB, BB, BA, PR, AD

5

Row field

A field from the source data that you assign to a row orientation in a PivotTable report. For example, cela04 is a row field.

6

Data area

The cells in a PivotTable report that contain counted data. For example, the value in cell C5 counts the number of Latino students who performed at the Far Below Basic level on the 2004 CST ELA (42).

7

Field List

List of all possible fields from the original data.

8

Tool Bar

Provides quick access to editing and customizing your PivotTable.


16

Excel and Pivot Tables

Recommend Documents