• Unit 4: Tables

    Tables let us organize large amounts of information meaningfully and usefully when we do not necessarily want to produce a chart. Tables in Microsoft Excel have database properties that let us sort and filter the data to answer specific questions and perform calculations. In this unit, we examine how to design and use tables and create pivot tables, a special type of table that gives statistical information.

    Completing this unit should take you approximately 1 hour.

    • Upon successful completion of this unit, you will be able to:

      • create and name 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
      • create pivot tables.

    • 4.1: Creating and Formatting a Table

      In this section, we explore how to create a table in Microsoft Excel and apply some basic formatting.

      • Creating a Table Page

        This video describes how to create and format a Microsoft Excel table using a list of pets and their identifying information and how to convert columns in a worksheet into a table. Then, it explains how to do some basic formatting and how to add columns to the table.

    • 4.2: Sorting and Filtering Data

      Because tables act like 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 list of students enrolled in a large university class.

      • Filtering Data Page

        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 a table.

      • Sorting Data Page

        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 that we will discuss in detail later. Even so, 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. If your data is in a table and you have access to the table tools -> design toolbar, you cannot 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. It is a good idea to use a table to sort or filter your data the way you would like before converting it to be subtotaled.

      • Convert to Range for Subtotaling Page

        Read this tutorial on converting a table back into a list of data and then subtotalling the data into chunks. Pay attention to the commands you need in the toolbar to perform the operations.

    • 4.4: Pivot Tables and Pivot Charts

      Now that you understand tables, we can discuss pivot tables. Pivot tables summarize information in a larger table and give statistical information such as sums and averages. You can use pivot tables to help make predictions about data, including business forecasts.