Excel: Analyze PowerSchool Data

(1)

PowerSchool University 2012

Excel: Analyze

PowerSchool Data

Trainer Name

Trainer/Consultant

(2)

Agenda

• Welcome & Introductions

• Organizing Data with PivotTables

• Displaying Data with Charts

• Creating Dashboards

• Time for Review

(3)

Using Filters

(4)

Filtering Options

• Sort Ascending

• Sort Descending

• Show Top 10

• Custom Filter

• Select by the Specific Data

• Show All

(5)

Now It’s Your Turn

Complete hands-on activity 1:

Analyzing Data with Filters

(6)

Organizing Data with

PivotTables

(7)

What a PivotTable Requires

• Many rows of data

• Non-unique values in the column of data to be analyzed (in order for Excel to count a value, it requires multiple records with that value)

• No empty rows or unlabeled columns

(8)

The Anatomy of a PivotTable

Excel 2011 (Mac) Excel 2007 (Windows)

(9)

Creating a PivotTable

Select the PivotTable function Select the data to summarize

Select a location for the PivotTable Select fields and data elements

Define the summary

(10)

• Excel 2011 (Mac) – Data tab or Data menu

• Excel 2007 (PC) – Insert Tab

Excel 2007 (Insert tab) Excel 2011 (Data tab or Data menu)

Select the PivotTable function

(11)

Select the data to summarize and make sure the range is correct

Excel 2011 Excel 2007

(12)

1. Select New Worksheet 2. Click OK

Excel 2007 (PC) Excel 2011 (Mac)

Select location for the PivotTable

(13)

• Excel 2011 (Mac) – PivotTable Builder

• Excel 2007 (PC) – PivotTable Field List

Select fields and data elements

(14)

Sum (default)

Count

1. Right-click a Values item cell, and select:

• Excel 2011 (Mac) – Field Settings

• Excel 2007 (PC) – Value Field Settings 2. Select Count

Define the summary

(15)

Use the Filter Field

• Filter the data based on a grouping criteria

• The data changes as you select a different value

(16)

Show Pages

• Select Show Report Filter Pages - Excel 2010 (Mac) – Right-click a

Values cell; choose from menu - Excel 2007 (PC) – Click the

PivotTable Tools tab; click the Options menu

• When you click OK, each value in the Filter area becomes its own worksheet

(17)

Stacking Filter Fields

• Put the main group on top and the sub-group on the bottom

• Now you have two different levels of filtering

(18)

Copy a PivotTable

• It's like copying the worksheet

• The advantage is that you display the same data multiple ways

(19)

Change the Form of the Values Items

1. Right-click a cell in the Values area 2. Click Field Settings

3. Change “Show Data as” settings

• Excel 2011 (Mac) – Number

• Excel 2007 (PC) – “Show Values as” tab

(20)

Now It’s Your Turn

Complete hands-on activities 2-11:

Working with PivotTables

(21)

Displaying Data

with Charts

(22)

Quick Chart Creation

1. Select the data to chart 2. Create the chart in a new

worksheet

• Excel 2011 (Mac) – Charts tab > (Select chart type)

• Excel 2007 (PC) – Insert tab

> (Select chart type)

(23)

Changing Chart Colors

1. Right-click the chart portion you want to change 2. Choose Format Data Series from the menu

3. Adjust color, line style, or other options as desired

(24)

Create a Combination Chart

Create a chart with grades, absences, and tardies Select the Absence Data Series, and select Format Choose to plot the series on a secondary axis

Change the series Chart Type to Line

Repeat steps 2-4 for tardies

(25)

Create the chart

(26)

• Right-click and select Format Data Series

• Excel 2007 (PC) – Chart Tools Section > Format tab > Format Selection

Select one of the Absence data columns

(27)

Select Secondary Axis

(28)

• Excel 2011 (Mac) – Right-click and select Change Series Chart Type

• Excel 2007 (PC) – Chart Tools Section > Design Tab > Change Chart Type

Excel 2011

Excel 2007

Change Chart Type

(29)

(30)

Create a PivotChart

Select a cell in the PivotTable

• Excel 2011 (Mac) – Charts tab > (Select chart type)

• Excel 2007 (PC) – PivotTable Tools > Options tab >

PivotChart

(31)

Use the PivotChart

• The PivotChart has some of the same interactivity as the PivotTable

• The PivotChart extracts data from the PivotTable

(32)

Now It’s Your Turn

Complete hands-on activities 12–14:

Displaying Data with Charts

(33)

Creating a Dashboard

(34)

Create a Dashboard of Pie Charts

(35)

Now It’s Your Turn

Complete hands-on activities 15–17:

Creating a Dashboard

(36)

Time

Review for

(37)

A B C D

1 2 3 4

Filter area

Field Settings

Show Pages

Values area

Where the column meets the row

One worksheet for each page field value

Filter data in a PivotTable

Changes how data displays

Matching Questions

(38)

A B C D

1 2 3 4

Filter area

Field Settings

Show Pages

Values area

Where the column meets the row

One worksheet for each page field value

Filter data in a PivotTable

Changes how data displays

Matching Answers

(39)

A B C D

1 2 3 4

Column Chart

Pie Chart

Line Chart

Combination Chart

Compare parts of a whole

Compare similar values

Show two related, different data points

Show a trend over time

Matching Questions

(40)

A B C D

1 2 3 4

Column Chart

Pie Chart

Line Chart

Combination Chart

Compare parts of a whole

Compare similar values

Show two related, different data points

Show a trend over time

Matching Answers

(41)