Working with Tables Pivot Tables and Pivot Charts
Working with Tables, Pivot. Tables, and Pivot. Charts Lesson 7 – Microsoft Excel 2010
Learning Goals The goal of this lesson is for students to successfully use tables and table tools in a worksheet. The student will learn to create a table and format the table as well as work with Pivot. Table and Pivot. Chart to enhance the appearance of data.
Learning Objectives �On completion of this lesson, students will be able to do the following: �Create a table �Format a table �Maintaining Data in a table �Sort and Filter data in a table �Understanding Pivot. Tables �Create a Pivot. Table �Modify a Pivot. Table �Understand Pivot. Charts �Create a Pivot. Chart
Create and Format a Table �What is a table? �A table is a range of cells that hold data, with each row corresponding to a single occurrence of an entity. �What is a field? �A column of data within a table. �What is a record? �A row of data within a table.
Creating a Table �Step 1 – Plan! You have to know how you are going to use the data so that you can build the table correctly. �Step 2 – Come up with appropriate field names (they will serve as your header row) �Step 3 – Type your data �Step 4 – Select the data and choose “Format as Table” from the Styles group on the Home tab or “Table” on the Tables group on the Insert tab.
Navigating and Formatting the Table �Move around the table using arrow keys or the tab key. �You can still freeze rows or columns to assist with viewing. �Rename a table using the Properties Group on the Table Tools Design Ribbon. �Format a table using the Table Style Options Group on the Table Tools Design Ribbon.
Maintaining Data in a Table �Adding data into a table is also considered adding records into the table. �After data is keyed into the last field of the table, pressing the Tab key on the keyboard will create a new record in the table. �Use the Find & Select Command in the Editing Group on the Home Ribbon to search for specific data in a table.
Sort and Filter Data in a Table �Sorting and filtering data allows you to see the data that you need more easily. �Sort Fields – The fields that you use in the sort. �Primary Sort Field – The first field that is sorted. �Secondary Sort Field – The field that is used to sort if there are duplicates in primary sort.
Understanding Pivot Tables & Charts
Understanding & Using Pivot. Tables �A Pivot. Table 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 a field that displays data groups in a single column or row in the Pivot. Table.
Layout Area of the Pivot. Table �Notice the 5 sections of the layout area. �Choose fields �Filters �Column Labels �Row Labels �Value Fields
Creating a Pivot Table & Pivot Chart �See Video: �How to Create: �GCF Learn Free Pivot Tables part 1: � http: //www. youtube. com/watch? v=l. EPm. Buye. Ixs � GCF Learn Free Pivot Tables part 2: � http: //www. youtube. com/watch? v=y 4 z 6 GEn. Ap 3 E&feature=relat ed �More how to & what is new in 2010 �http: //www. metacafe. com/watch/5958465/excel_rev_up_pivot_tab le_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
Modifying a Pivot. Table �Use the filter section to hide the data that you do not want to include in your Pivot. Table �Note: The filter will apply every time the Pivot. Table is refreshed or updated. �You must remember to refresh your Pivot. Table to update any data changes that have been made since the Pivot. Table was created.
Understanding and Using Pivot Charts �A Pivot. Chart enables you to create a “picture” of your Pivot. Table or Pivot. Table Report. �You can automatically create a Pivot. Chart report when you first create a Pivot. Table report. �You can create a Pivot. Chart report from an existing Pivot. Table report. �You can use what you learned creating standard charts with creating Pivot. Charts.
Test Your Knowledge Answer Key: 1. TRUE 2. TRUE 3. FALSE The Pivot. Table option will not be available otherwise. 4. TRUE 5. TRUE True/False 1. Excel creates an associated Pivot. Table with each Pivot. Chart. 2. A Pivot. Table is an interactive table that enables data grouping. 3. Information does not need to be defined as an Excel table prior to creating a Pivot. Table. 4. Excel Tables enable the user to filter data easily. 5. Items on a Pivot. Table may be expanded or collapsed based upon the desirable view.
Test Your Knowledge Answer Key: 1. B (Not linked) Multiple Choice 1. b. not linked d. imported 2. A (Relationshi ps) C (Refresh) 3. 4. B 5. (Pivot. Chart) A (Filter) The data in a Pivot. Table is ____ directly to worksheet cells. a. linked Pivot. Tables are useful because they show _____ among data. a. relationships b. links d. values 3. a. update c. Refresh The _____ is a graphic representation of the data in a Pivot. Table. a. Option Chart b. Pivot. Chart d. Embedded Chart 5. c. changes Which button will update the Pivot. Table and Pivot. Chart information? b. recalculate d. Link 4. c. copied c. Inserted Chart _____ is one way to manipulate data in a
Vocabulary Words Please add the following vocabulary terms to your list of terms and definitions. • Table • Field • Record • Sort Field • Primary Sort Field • Secondary Sort Field • Pivot. Table • Pivot. Chart • Filter
Practice What You Have Learned Complete the following assignments. When complete, save and submit. Assignments Excel Lesson 7 – Exercise 1 Excel Lesson 7– Project 1
- Slides: 18