19 Ex Data

Using the Excel database features Objectives: • • • •

Sorting data Filtering data Using Subtotals Pivot Tables

CS&E 101 ExData

A Database vs. a Spreadsheet Use a Database for: ●

●

●

Large amounts of data Multiple tables of related information Complex querying (multidimensional)

Use a Spreadsheet for: ●

●

●

Small to medium amounts of data 1-3 Tables of related information Simple sorting, filtering, and subtotaling

Often analysts use both tools to solve a problem, keeping track of data in a database application and copying the relevant information for further analysis in Excel CS&E 101 ExData

Excel Provides up to 3 Levels of Data Sorting Use the toolbar buttons to Sort a single field Use the Data/Sort menu to sort by up to 3 fields

Sort by customer & quantity snack potato chips cheese puffs nuts cheese puffs potato chips pretzels potato chips pretzels potato chips cheese puffs nuts

QTY 12 10 15 9 5 4 30 11 8 5 5

customer ab foods ab foods nt foods nt foods nt foods nt foods xy foods xy foods xy foods xy foods xy foods

CS&E 101 ExData

Excel’s Autofilter tool allows the user to specify criteria in multiple fields •Select from Menu - Data Filter – Autofilter •Each field can have a Customized Filter •Filters in multiple fields acts like an AND Snack potato potato potato potato potato

Qty chips chips chips chips chips

12 5 5 8 30

Customer ab foods nt foods xy foods xy foods xy foods

CS&E 101 ExData

Advanced Filters allow the user to create OR relationships in multiple fields or to vary criteria based on calculations •Select from Menu - Data Filter – Advanced Filter •Setup your filter range and input the criteria. Criteria in the same row acts as AND, criteria on adjacent rows acts as OR Snack potato chips

Qty

Customer nt foods

Snack potato chips

Qty

Customer 12 ab foods

pretzels

5 nt foods

potato chips

5 xy foods

potato chips

30 xy foods

CS&E 101 ExData

Excel can automatically create Sub-Groups – to perform sums, counts, averages etc 1. Sort by your Group field • • • • • •

Select Data/Subtotals menu Choose your Group field Select a function Select a Subtotal field Click OK to calculate Repeat the procedure to calculate a 2nd aggregate function

CS&E 101 ExData

Data-Subtotals are available in 3 Levels

CS&E 101 ExData

Pivot Table, a Multidimensional Summary ●

Select Data – Pivot Table and PivotChart Report from the menu to run the Pivot Table Wizard

●

Select Microsoft Excel list or database and Pivot Table Specify data range – including titles Select the Layout button to specify your columns rows and table data

●

●

CS&E 101 ExData

The Resulting Pivot Table Data summarized by snack by customer: sum qty snack cheese puffs nuts potato chips pretzels Grand Total

customer ab foods nt foods xy foods Grand Total 10 9 5 24 0 15 5 20 12 5 43 60 0 4 11 15 22 33 64 119

•Data can also be displayed as a Pivot chart. •Both Pivot tables and charts can be used in Excel and in Access. •If the data is later modified, the button to update the values CS&E 101 ExData