5.1 Table Basics
Learning Objectives
- Understand table properties and structure.
- Format data as a table.
- Use Freeze Panes.
- Work with the Table Tools Design tab.
Organizing, maintaining, analyzing, and reporting human resources data is essentials across industries. In this chapter, we will demonstrate tabling skills by examining employee relations, payroll, benefits, and training options.
TABLE PROPERTIES & STRUCTURE
Turning a range of cells into an Excel table makes related data easier to analyze, visualize, and report. Structuring and planning table layouts are vital for data integrity. Below are guidelines to consider when designing and building a table from scratch:
OVERVIEW
Excel tables behave independently from the rest of the information on the worksheet. Excel treats the table area as a database locking the record entries together. There are several advantages of Excel treating the data independently. For example, using integrated filters and sort functions you can effortlessly drill down data based on questions and in return get results. Excel will also automatically expand the table to accommodate new data entries and allows for automatic formatting, such as recoloring of banded rows or columns.
You will also notice Excel treats formulas and calculations differently in a table, showing structured column names, along with automatically filling a calculated field to the entire table or offering quick and easy table totaling tools.
When graphing and charting table data you will also see Excel automatically adjusts of associated charts and ranges based on what the user is sorting or filtering at the time.
In industry, data is commonly stored in databases or multiple Excel files. In our example, we will work with an Excel file that has imported data from a human resources database.
FORMAT DATA AS A TABLE
Download Data file: CH5 Data
1. Open data file CH 5 Data and click on the EmployeeData sheet to make it the active sheet; save the file as CH5 HR Report.
TABLE TOOLS DESIGN TAB
This file looks a little different than previous Data files. You are looking at an Excel worksheet with the data formatted as a “Table”. Excel tables require specific tools. The Table Tools Design tab houses these specific tools used for formatting and editing tables. The Table Tools tab is considered a contextual tab; meaning the tabs appear when you are clicked in a table area. When you click out of a table, the Table Tools disappear.
Explore the table tools now. Notice the specific checkboxes to turn on table options, for example, you can choose to display banded rows or banded columns, or a total row etc. We will explore table tools in the following steps.
The data file you downloaded was already formatted as a Table. Later in the chapter, you will learn how to convert a simple Excel range into a Table. Follow the below steps to format and edit the table.
1. Click the Table Tools/Design tab on the ribbon.
Mac Users: you don’t have a Table Tools/Design tab. J