18 Top Tips for Creating Excel Lists or Excel Tables

Excel

People love putting their To Do List, Contact List, and Project Task List in Excel. These tips will show you how to make your lists even more friendly and useful by converting them into an Excel Table.

Click here to learn how to use Excel AutoFilter or Excel advanced filter with your lists or Excel Table. The Excel advanced filter gives your powerful filtering ability and the ability to extract column reports.

If you are experienced using data lists or Excel Tables you may want to check Tips 7, 13, 14, and 18 for more advanced tips.

You can apply predefined formats to Excel tables.

Use a drop-down list to select the summary calculation in an Excel Table's last row.

Do you know someone who works with Contact Lists, Inventory Lists, To Do or Project Task Lists? Help them work smarter and faster by forwarding this article to them.

Click here to learn how to create a checklist in Excel.

Click here to learn how to change a list with dates into an Excel Gantt chart or advanced Excel Gantt chart to show task completion by time.

“In the beginner’s mind there are many possibilities, but in the experts, there are few.”
Shunryu Suzuki (1905-1971)

1. Creating a Data List in Excel

To use Excel’s list management capabilities, create your lists so,

  • Header labels go across the top row. Use categories as labels, such as, Inventory Item, Start Date, Task, etc.
  • The list, including header row, must not touch adjacent filled cells in the worksheet. The list must have a “moat” of empty cells around it.
  • Do not let empty rows or columns bisect the data list as this will split the list.

For most list management commands,

1. Select a cell within the list.

2. On the Data tab you can sort, filter, extract (inside Advanced Filter), remove duplicates, and more.

Or

2. On the Insert tab you can create a chart, Excel Table, Pivot Table, and more.

2. Should You Use a Data List or an Excel Table?

Excel Tables, available since Excel 2007, are a powerful way to manage lists. Personally, I knew so many tips and tricks for lists that I thought I didn’t need Tables. When I finally used them I saw how useful they are. This is a heads up. Use Excel Tables!

Changing your data list into an Excel Table gives you the following advantages,

  • Adding or deleting data automatically expands or contracts the Table.
  • Charts created from a Table automatically expand as you add data.
  • Tables are automatically named, like a range name. You can modify the name if you want.
  • Complex formatting can be applied from predefined galleries of professionally designed layouts.
  • Tables can use the same Slicers for filtering as those used in Pivot Tables.
  • Table calculations, like a column total, do not include rows hidden by filtering so the results agree with the visual display.
  • Tables use automatically generated reference names for column and row calculations making formulas easier to understand.

When should you keep your data in a normal, non-Table, list? If you are using a data list as a source for a Pivot Table or database analytics, then you don’t need to create an Excel Table. Otherwise, an Excel Table is a good way to go.

3. Changing a Data List into an Excel Table

To change a list into an Excel table,

1. Select a cell inside a list. Your list may or may not have headers.

2. On the Insert tab, click Table or press Ctrl+T.

3. The Create Table dialog box appears with the entire range automatically selected.

4. If your Table has a row of header labels at the top, then select My table has headers.

5. Choose Ok.

When you first create an Excel Table it tries to find the table boundaries.

When you first create an Excel Table it tries to find the table boundaries.

Your Excel list becomes a Table with all cells and headers in the Table selected.

A shortcut for changing a list into a table is pressing Ctrl+T.

Notice that default formatting is automatically applied. (You can change the default Table format. See Tip 10 to learn how to create a new format and set it as default.) The Excel Table is setup to use filters, but you can turn those off with a single click. (See Tip 9) And, the Table has a name applied to it, similar to a range name, that can be modified.

4. Use the Table Tools Design Tab to Manage Your Excel Table

Select anywhere within the Table and a new tab appears on the ribbon. The Table Tools Design tab has tools that will help you resize the Table, name it, create a Pivot Table from it, apply predesigned formats to it, and much more. Most of your work with a Table will be done from either the Table Tools Design tab or the Data tab.

Use the Table Tools Design tab to work with Excel Tables.

Use the Table Tools Design tab to work with Excel Tables.

5. Naming Your Table

When you create an Excel Table, the table is automatically named with a name like Table1, Table2, and so on. Like a range name, you can use these names to refer to the Excel Table in functions or Pivot Tables.

To create a more descriptive name,

1. Select a cell inside the Table.

2. On the Table Tools Design tab, at the far left side of the ribbon, is the Table Name field. In this field, enter a new, more descriptive name for your Table.

Alert: This name includes only the data range. It does not include the header cells.

To select or go to your Excel Table, press F5, select the Table’s name, then click Ok.

Use these guidelines for Table names,

  • Start the name with a letter, underscore, or backslash (\).
  • You can use numbers and special characters.
  • Do not use blanks in Table names.
  • Use 255 characters or less.
  • Do not use names that could be confused as Excel cell references, for example, B12.
  • Table names are not case sensitive so INVENTORY, inventory, and Inventory are all the same.

6. Changing an Excel Table Back to a List

You can change an Excel Table back to a list.

1. Click within the existing Excel Table.

2. On the Table Tools Design tab, in the Tools group, click Convert to Range.

A shortcut is to right-click within a Table and choose Table, Convert to Range.

7. Using the Gallery of Predefined Table Formats on a Plain List

You can use the predefined Table formats which include header formatting, alternating rows, etc. on lists as well, but it takes a simple trick. Change your list to a Table by clicking inside the list and pressing Ctrl+T. Now, select a Table format from the predefined formatting gallery in Table Styles. Then, change the Table back into a list. On the Table Tools Design tab, in the Tools group, click the Convert to Range tool. The Tool features will be removed, but the formatting will remain on your list.

8. Creating Charts that Auto-Expand Using Excel Tables

Tables that are a data source for charts must have the same vertical layout and arrangement as normal data used for a chart. The advantage of using a Table to hold chart data is that adding new data to the last row in the Table automatically expands the Table and the chart.

9. Quickly Modifying Header, Bold Columns, and Filter Buttons

Design options give you the ability to turn on or off Excel Table features.

Quickly modify the Table format and filter display from the Table Style Options group.

You can quickly change header rows, bolded columns, banded columns, and filter buttons by selecting or clearing check boxes in the Table Style Options group. The checkboxes do the following,

Header Row

Show or hide header labels

Total Row

Add a total row and result at the bottom of the table

Banded Rows

Format rows in alternating clear and colored bands

First Column

Bold data in the first column

Last Column

Bold data in the last column

Banded Column

Override alternating rows and format columns with alternating colors

Filter Button

Show or hide the filter arrows on header labels

10. Create Custom Predefined Table Formats in the Gallery

One of the great advantages of using Tables is applying formats from a predefined gallery of table formats. The predefined gallery appears on the Table Tools Design tab, in the Table Styles group. Just select a cell in your Table, then click the predefined style you want.

You can apply predefined formats to Excel tables.

You also can create your own predefined styles. You may want to do this to match your corporate color identity. Most enterprises have a marketing identity that specifies the exact usage of the corporate name, logotype, logo, and colors. The colors in the identity package will have color codes you can enter in Excel.

To create and save a new Table style with the custom format you want,

1. Select a cell in your Table.

2. On the Table Tools Design tab, in the Table Styles group, click on New Table Style at the bottom of the gallery to open the New Table Style dialog box.

Modify predefined formats or create new formats you can apply to Excel Tables.

Modify predefined formats or create new formats you can apply to Excel Tables.

3. In the Name edit box, enter a name to appear in the gallery.

4. Select from the Table Element list the parts of the Table you want to format, then click Format and make your formatting changes.

5. Select the Set as default table style for this document check box, if you want your custom format to be automatically applied when you create new Excel Tables in this workbook.

The custom Table formats you create appears at the top of the predefined styles gallery under the label Custom. To delete your custom style, right click the style’s icon in the gallery and choose Delete.

You can base your new style on an existing style. To modify an existing style, display the gallery, right-click on the style you want to modify, then click Duplicate. The New Table Style dialog box will open ready for you to create a new style based on the predefined style you duplicated. The modified duplicate will appear as a new style icon under Custom in the gallery.

11. Aligning Data with Filter Arrows

Creating a Table automatically adds filter arrows to the right side of the header labels. The arrows push header labels to the left making the alignment look awkward because header labels and data are not right aligned.

To align the right edge of your data with the right edge of the header label,

1. Select the column of data, but do not include the header label.

2. On the Home tab, in the Alignment group, click the Increase Indent tool until the data and header are right aligned.

12. Moving and Selecting in Excel Lists and Excel Tables with Shortcut Keys

Use these shortcut keys to move quickly through your Excel list or Table. If the starting cell is an empty cell it will move until it reaches a filled cell. If the starting cell is a filled cell it will move until it reaches an empty cell.

Move up/down

Ctrl+Up/Down Arrow

Move left/right

Ctrl+Left/Right Arrow

Select and move

Shift+Ctrl+Arrow

Select entire list/Table

Shift+Ctrl+* (asterisk)

Select row

Shift+Spacebar
(selects entire worksheet row in a list or a row within the Table)

Select column

Ctrl+Spacebar
(Selects entire worksheet column in a list or a column within the Table)

13. Use Built-In Drop-Down Lists for Data Entry

You do not have to create a drop-down list or Data Validation list for Excel lists or Tables. Right-click in the cell where you want to enter data, choose Pick from Drop-Down List. The list will show existing entries for that column.

Enter data in an Excel Table with a right-click.

Enter data in an Excel Table with a right-click.

14. Enter Totals, Averages, Counts, and More Using a Drop-Down List

Use a drop-down list to select the summary calculation in an Excel Table's last row.

Add column calculations to the bottom of Table by selecting a cell in the Table, then on the Table Tools Design tab, in the Table Style Options group, select the Total Row check box. This turns on column calculations for the last row of the table.

You can turn on and off the calculation at the bottom of each column by selecting the cell in the last row, F630 in the example, then clicking the drop-down arrow to the right. Select None to leave a blank or select the type of calculation you want displayed.

The great thing about these calculations is that they reflect only the numbers displayed after filtering so the displayed data and calculated results agree.

15. Freeze Panes to Show Header Labels as You Scroll the Table or List

When you scroll down a long Excel Table or list, the header labels scroll off the top of the screen. To keep the header labels on screen, freeze the title row so only the data below it scrolls,

1. Position the list so the header row is where you want it on screen as you work.

2. Click the row number of the row under the title row. This selects the entire row under the titles.

3. On the View tab, in the Window group, click Freeze Panes, Freeze Panes.

The header labels are now frozen, but the list will scroll.

To unfreeze the window, select the row under the labels, and choose View, Freeze Panes, Unfreeze Panes.

16. Using Table References in Formulas

When you create an Excel Table, Excel applies a name to the table and to each column. These names are just like the range names used as cell references. When you enter a formula, for example, totaling a column, you will see the Table name and the column name rather than the cell references. To learn more about Table references, check the article Using structured references with Excel tables.

17. Removing Filter Arrows in Table Headings

If you want a cleaner appearance or you don’t want to make it as easy for users to filter or sort, then remove the filter arrows. Select inside the Table, then on the Table Tools Design tab, in the Table Style Options, clear the Filter Button check box.

18. Filtering with Data Slicers

Slicers make it easy to filter Excel Tables.

The Slicers that make it so easy to filter data in a Pivot Table can also be used with Excel Tables.

To add Slicers, select a cell inside the Table, then on the Table Tools Design tab, in the Tools group, click the Slicer tool. Select the Slicers you want to appear, then click Ok.

Drag each Slicer, like the two shown in the figure, where you want them. You can align corners with cell edges by holding down the Alt key as you drag a corner.

Tips on Filtering and Extracting with Simple and Advanced Queries

Excel has both simple and very advanced filtering and extracting capabilities for lists and Tables.

Click here to learn how to use Excel AutoFilter or Excel advanced filter with your lists or Excel Table. The Excel advanced filter gives your powerful filtering ability and the ability to extract column reports.

Share the power...

Leave a Comment:

Leave a Comment: