1 / 14

Lesson 7

Lesson 7. Working with Tables, PivotTable, PivotCharts. 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

jacoba
Download Presentation

Lesson 7

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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.

More Related