Unit 4: Tables
Tables allow us to organize large amounts of information meaningfully and usefully when we do not necessarily want to produce a chart of the information. Excel tables have database properties that allow us to sort and filter data to answer specific questions and perform calculations. In this unit, we examine how to design and use tables and create pivot tables, which give statistical information.
Completing this unit should take you approximately 2 hours.
Upon successful completion of this unit, you will be able to:
- create, name, and format a table;
- create a totals row in a table and use pre-built functions in that row;
- sort within a table;
- filter a table;
- use an advanced sort and filter;
- use subtotals; and
- explain how to manage data using PivotTables and PivotCharts.
4.1: Creating and Formatting a Table
Tables are helpful in spreadsheet programs because they have database properties, which allow you to sort and filter data. This section explores how to create a table in Excel and apply some basic formatting.
Watch this video on creating and formatting an Excel table using an example of a list of pets and their identifying information. The tutorial shows how to convert columns in an Excel worksheet into a table. Then, it explains how to do some basic formatting and add columns to the table.
This video provides key moments focusing on basic and more advanced table functions.
4.2: Sorting and Filtering Data
Because tables act as databases, we can sort and filter them to obtain the information we want in the order we want to see it. This is useful when dealing with large lists of information, such as your company's client list or a listing of students enrolled in a large university class.
Watch this video, which uses the example of a table of donors to a charity, with the amount they donated to each program, to show you how to filter data in an Excel table.
Watch this video for a tutorial on how to use the sort feature in Excel tables. This example uses a pivot table, a type of table we discuss in detail in the last section of this unit. However, the sort feature is the same as for regular tables.
4.3: Outlining Data
Outlining data allows you to organize large amounts of information into manageable chunks. We do this with lists of data, NOT tables. You cannot create outlines or use the subtotals command if your data is still in a table format. If your data is still in a table format and you have access to the table tools–design toolbar, you will not be able to access the subtotals and outlining tools. You must click on your table and use the convert to range command in the tools grouping on the table tools–design toolbar. A good idea is to use a table to sort or filter your data the way you would like before you convert it to be subtotaled.
Read this chapter, which provides a visual tutorial on converting a table back into a list of data and then subtracting the data into "chunks."
Pay close attention to the commands you need to perform the operations in the toolbar.
4.4: PivotTable and PivotChart
Now that you understand tables, we can discuss PivotTables. PivotTables provide information summaries in a larger table and give statistical information, such as sums, averages, or other statistical information. You can use pivot tables to help make predictions about data, including business forecast predictions.
Watch this video for a detailed description of PivotTables. The presenter explains when to use them, how to create one, and how to edit one to obtain your desired information.
Watch this video to learn about slicer filtering controls, how to use timelines to filter specific periods, and how PivotCharts can be used to analyze PivotTable data.
Finally, watch this video for a tutorial on how to create a Pivot Table in Google Sheets.