Chapter 4
Section 4.2 – Table Basics
Learning Objectives
- Understand table structure.
- Plan, create, and edit a table.
- Freeze rows and columns.
- Sort data in a table.
Download and open FILE: CH 4.2
This section reviews the fundamental skills for setting up and maintaining an Excel table. The objective used for this chapter is the construction of tables in a multi-sheet file to keep track of two cities’ national weather data for the month of January. Organizing, maintaining, and reporting data are essentials skills for employees in most industries.
Figure 4.7 shows the completed workbook that will be demonstrated in this chapter. Notice that this workbook contains three worksheets. The first worksheet lists average weather for January in Portland, Maine. The second sheet lists average weather data for January in a very different climate – Portland, Oregon. The third sheet adds a weekly column to the Portland, Oregon data so that it can be subtotaled by week.
Creating a Table
When data is presented in long lists or columns, it helps if the table is set up well. Here are some rules of data-entry etiquette to follow when creating a table from scratch:
- Whenever you can, organize your information using adjacent (neighboring) columns and rows.
- Start the table in the upper-left corner of the worksheet and work your way down the sheet.
- Do not skip columns and rows just to “space out” the information. (To place white space between information in adjacent columns and rows, you can widen columns, heighten rows, and change the alignment.)
- Reserve a single column at the left edge of the table for the table’s row headings or identifying information.
- Reserve a single row at the top of the table for the table’s column headings.
- If your table requires a title, put the title in the row(s) above the column headings.
Following these rules will help ensure that the sorts, filters, totals, and subtotals you apply to your table with give you the desired results.
With these rules in mind, we will begin working on the Portland ME worksheet in the National Weather workbook. Notice that the data is in adjacent columns and rows. The upper-left corner of the table is in A5 and the titles are above the column headings in Row 5. Since the set-up of our data looks good, we are ready to turn our data range into an Excel table:
- Open data file CH4.2 and save a file to your computer as CH4 National Weather.
- Click on A5 in the Portland ME sheet.
- Click the Table button in the Insert tab of the Ribbon.
Figure 4.8 will appear on your screen.
- Make sure “My table has headers” is checked. Click OK.
- Click in A5 again.
- Adjust your columns widths so that you can see the complete headings in row 5 with the filter arrows showing. The filter arrows are the down-arrow buttons that will appear in row 5 when you create your table. We will learn how to use these to sort and filter later in this chapter.
After this, your spreadsheet will look like Figure 4.9.
Notice that