Display data in table

To place information from our “Worksheet” into a “Table”, we first place the cursor (1) in the “Range” that we want to include in the “Table”, then we select the “Insert” (2) tab in the “Ribbon” and click the “Table “(3) button. This opens the “Create Table” (4) dialog box with the “Range” already selected. This selected “Range” is identified by a broken line (5) in the worksheet. This window also has a checkbox where we choose to have headers for our “Table”. Check the “My Table has Headers” checkbox , if required. If this is not the case, then ignore this checkbox.

Click OK when you are satisfied.

The extra “Design” (1) tab

Once we add a “Table”, an additional “Design” tab will appear a the top of the Ribbon. This “Design” window is divided into five sections where we can set different options for our “Table”.

In the first section, the “Properties” section, we give our “Table” a name. When we name a Table, we can address it in a “Formula”. More on that later.

Under the “Table” name we find the “Resize Table” option. With this button we can change the “Range” that is included in our “Table”.

In the second section, the “Tools” section, we find three options.

The first option is “Summarize with PivotTable”. More about PivotTables in a later lesson.
The “Remove Duplicates” option will delete duplicates from our “Table”. By “duplicate”, we mean a duplicate word with respect to the columns you selected in the list (1). By default, all such duplicates are selected. This means that all values in all columns for each record will be compared, if we search for duplicates.

If you wish to only look for duplicates in the “Name column”, unselect all the checkboxes for the rest of the column names. When clicking the OK button (2), a second dialog box (3) will appear listing the number of duplicates found. If you click the OK button again (4), the duplicates are deleted.

The third option “Convert to Range” will transform our “Table” back to a “Range”.

We will continue with this in the next lesson.

Attention!

If you want “Excel” to select the entire “Range”, then there should be no blank rows or blank columns among the data range. Empty cells are ok. For example, if there is an empty column between the “Date” and “Amount” columns, then “Excel” will only take the “Name”, “Region” and “Date” columns into its search.
You can solve this by deleting the blank columns and rows or you can manually select the “Range”.

Tip:

If you have multiple empty rows to remove in your document, you do not need to remove them one by one. Select all data in your “Worksheet”, and click the “Sort” button in the “Ribbon”. This puts all the empty rows at the bottom, where you can remove them all at once.

 

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats