Lesson 7

1. Lesson 7 Working with Tables, PivotTable, PivotCharts

2. Learning Objectives • Understanding PivotTables • Layout Area in a PivotTable • Create a PivotTable • Modify a PivotTable • Create a PivotChart • Create a Table • Table Information • Table Definitions • Format a Table • Maintaining Data in a Table • Sort and Filter Data in a Table

3. Table Information • Table Features allow for data to be inserted, deleted and modified easily to verify accuracy. • Data in Tables can be sorted or filtered to display data that meets a specific criteria. • Advanced data features allow detailed data to be summarized and represented in easy to read PivotTables and PivotCharts.

4. Table Definitions • Excel Table (previously known as a list) • Can manage and analyze data in table independently of data outside of the table. • You can : filter, add totals, format or publish • Can convert table back to regular range of data • Field & Field Name • Column is a field • Column Heading is the Field Name • Record & Record Name • Row is a Record • Row Heading is the Record Name

5. Create a Table • It is important to plan how the data will be used and the field names prior to creating a Table. • To Create a Table • Select the range of cell that are to be included in the table. • Insert Ribbon / Tables Group / click on Table • Click on “My Table has headers” if you included the column headings in the selected range.

6. Format a Table • To Create & Format • Select the data that you want to format • Home Ribbon / Styles Group / Format as a Table • Select the style that you want for your table • This both creates and formats the table. • To Format an existing Table • Select the range of data in the table • On the Design Ribbon / Table Tools • Select the style you want • You can also select: • Header & Total Rows • Banded Rows • 1st & Last Columns • Banded Columns

7. Maintaining Data in a Table • Adding data into a table is as known as adding records. • Use of the Tab Key: • Tab to move horizontally across the columns • Tab in the last column to create and move to next row (record)

8. Sort Data • Use Sort & Filter to organize the data in a table. • Sort is • to arrange data logically • Alphabetically or numerically • Sort Fields • Click on Sort and then in the Sort <field name> dialog box select the type of sort that you want.

9. Filter Data • Filter by: • Specific labels or values • Top or bottom 10 values • Use Report Filters to filter data in a PivotTable report. • Examples: product line, time span or geographic region • Use Slicers • Slicers provide buttons that you can click to filter PivotTable data. • Slicers also indicate the current filtering state. MS Training - Tables: http://office.microsoft.com/en-us/excel-help/use-excel-tables-to-manage-information-RZ102252956.aspx?CTT=1

10. Understanding & Using PivotTables • A PivotTable report is: • useful to summarize, analyze, explore, and present summary data • an interactive way to quickly summarize large amounts of data. • Value Fields • Use summary functions in value fields to combine values from the underlying source data. • Category Field • It is afield that displays data groups in a single column or row in the PivotTable.

11. Layout Area of the PivotTable • Notice the 5 sections of the layout area. • Choose fields • Filters • Column Labels • Row Labels • Value Fields

12. Creating a Pivot Table & Pivot Chart • See Video: • How to Create: • GCF Learn Free Pivot Tables part 1: • http://www.youtube.com/watch?v=lEPmBuyeIxs • GCF Learn Free Pivot Tables part 2: • http://www.youtube.com/watch?v=y4z6GEnAp3E&feature=related • More how to & what is new in 2010 • http://www.metacafe.com/watch/5958465/excel_rev_up_pivot_table_2010_podcast_1340/ • Series of How To on Pivot Tables and Charts: • http://www.dummies.com/how-to/content/the-essentials-of-excel-2010-pivot-tables-and-pivo.html Note: Videos need to be tested to ensure they work at school and are still valid.

13. Modifying a PivotTable • Use the filter section to hide the data that you do not want to include in your PivotTable • Note: The filter will apply every time the PivotTable is refreshed or updated. • You must remember to refresh your PivotTable to update any data changes that have been made since the PivotTable was created.

14. Understanding and Using Pivot Charts • A PivotChart enables you to create a “picture” of your PivotTable or PivotTable Report. • You can automatically create a PivotChart report when you first create a PivotTable report. • You can create a PivotChart report from an existing PivotTable report. • You can use what you learned creating standard charts with creating PivotCharts.