# PRDV252: Intermediate Excel

Unit 10: Tables   Tables are an important feature of Excel®. They are used to store large pieces of data, usually on an ongoing basis. The data can then be sorted and filtered to answer specific questions. Excel® tables have database properties. Once a table has been created in Excel®, there are database functions that can be used to do math on different categories within the table. Pivot Tables and Charts will also be introduced. Once you have a lot of data in one place, you can use Pivot Tables and Charts to manipulate it to answer business questions.

Completing this unit should take you approximately 3 hours.

☐    Subunit 10.1: 1.25 hours

☐    Subunit 10.2: 0.75 hours

☐    Subunit 10.3: 1 hour

Unit10 Learning Outcomes
Upon successful completion of this unit, you will be able to: - explain how to create and name a table; - create a Total’s row in a table and use prebuilt functions in that row; - describe how to sort within a table; - describe how to filter a table; - describe how to use an advanced Sort and Filter; and - explain how to use subtotals.

10.1 Creating a Table   - Web Media: The Saylor Foundation’s “Creating a Table” Link: The Saylor Foundation’s “Creating a Table”(YouTube)

Instructions: Click on the link to watch a video on creating tables. Once you have watched the video, try creating a table in your own Excel® program. Tables are an important feature of Excel® and something you should become comfortable using.

Completing this assignment should take approximately 15 minutes.

10.1.1 Formatting a Table   - Web Media: GCFLearnFree.org’s “Excel® 2010: Formatting Tables” Link: GCFLearnFree.org’s Excel® 2010: Formatting Tables (HTML)

Instructions: Read through the four pages and watch the short video on page 2. If you have Excel® 2010, on page 4 you can download the practice document they give you and follow the directions to practice the skills you just learned.

Completing this assignment should take approximately 20 minutes.

10.1.2 Sorting Data   As you saw in the previous section, it is easy to sort and filter data once the data are put into a table format. Let’s look a little closer at ways to sort and filter data or lists. The techniques that you will learn here can be used on a table or just a list of information you have in Excel®.

• Web Media: GCFLearnFree.org’s “Excel® 2010: Sorting Data” Link: GCFLearnFree.org’s Excel® 2010: Sorting Data (HTML)

Instructions: Read through the five pages and watch the video on page 2. If you have Excel® 2010, then download the practice workbook they provide on page 5 and try sorting the data as they showed in the lesson. You can also use the Data tab Sort commands as shown in the lesson to do advanced sorts on the table columns.

Completing this assignment should take approximately 20 minutes.

10.1.3 Filtering Data   Filtering can be done on a data set or a table to give you control over what information is shown on your spreadsheet. It allows you to narrow down your data in different ways.

• Web Media: GCFLearnFree.org’s “Excel® 2010: Filtering Data” Link: GCFLearnFree.org’s Excel® 2010: Filtering Data (HTML)

Instructions: Read through the five pages and watch the four-minute video on page 2. Filtering can be used on just a list or on data that has been put into a table format. You’ll notice in the example that the column headings have names; this makes sorting and filtering much easier and is a good technique to use with data lists or tables. If you have Excel® 2010, download the practice workbook they provide on page 5 and follow the steps they give you to practice filtering. Note that you have to have done the reading, not just watched the video, to complete the filtering correctly because the reading differs from the video.

Completing this assignment should take approximately 20 minutes.

10.2 Outlining Data   Outlining data helps to organize large pieces of information into manageable chunks on screen. This can be done to lists of data, NOTtables. You cannot create outlines or use the Subtotals command if your data are still in a table format. This cannot be stressed enough. If your data are still in a table format and you have access to the Table Tools - Design toolbar, then you will not have access to Subtotals and the outlining tools. You must click on your table and use the Convert to Rangecommand in the Tools grouping on the Table Tools - Design toolbar. It is often a good idea to use a table to sort or filter data the way you would like before you convert it to be subtotaled. You may need to think about why you are subtotaling it and how the levels should be grouped. This will make more sense after you have read through the material. This idea of sorting then outlining may take some practice but it is a good skill to master.

• Reading: The Saylor Foundation’s “Convert to Range for Subtotaling” Link: The Saylor Foundation’s “Convert to Range for Subtotaling” (PDF)

Instructions: Read the short explanation about converting a table to a list so that the Subtotal command can be utilized.

Reading this material should take approximately 15 minutes.

• Web Media: GCFLearnFree.org’s “Excel® 2010: Outlining Data” Link: GCFLearnFree.org’s Excel® 2010: Outlining Data (HTML)

Instructions: Read through the six pages and watch the video on page 2. If you have Excel® 2010, you can download the practice document they give you on page 6 and follow the directions to practice the skills you just learned.

Completing this assignment should take approximately 30 minutes.

10.3 PivotTable and PivotChart   Now that you understand about tables, let’s talk about PivotTables, sometimes called PivotTable Reports. If you have a large amount of data that you can sort and filter, you may still want to be able to manipulate it further to answer business questions or predict future scenarios like sales or marketing events. PivotTables can help managers answer or give a best-guess scenario.

• Web Media: GCFLearnFree.org’s “Excel® 2010: Creating PivotTables” Link: GCFLearnFree.org’s “Excel® 2010: Creating PivotTables” (HTML)

Instructions: Read through the seven pages and watch the two videos on page 2. If you have Excel® 2010, you can download the practice document and follow the directions on page 7 to practice creating a PivotTable and PivotChart. There is a lot of information here so go slowly and really practice these skills. Like charts, PivotTables changed a lot with Excel® 2007 and newer. If you are using an older version of Excel®, most of this information will be different and you will need to use your help feature to learn about PivotTables in your version of Excel®.

Completing this assignment should take approximately 30 minutes.

• Web Media: Daniel Norris’ “Intermediate Excel® (1/2) - Pivot Tables” Link: Daniel Norris’ Intermediate Excel® (1/2) - Pivot Tables(YouTube)

Instructions: Watch the video. There is no practice document with this exercise, but you may want to create your own data to practice with or try opening another spreadsheet from an earlier lesson in the class and create a PivotTable and PivotChart for it. Employees with PivotTables and PivotCharts experience are seen as an asset in the office.

Completing this assignment should take approximately 30 minutes.

`````` Instructions: Log into your Saylor Foundation School account in