17 Top Tips for Creating Excel Lists or Excel Tables

Many people tend to put their shopping list, contact list, project task list, etc. in Excel. Presented in this article advanced Excel tips and tricks are some helpful Excel tips which will aid you in learning how to get good at Excel by making your lists friendlier. Moreover, if you want to learn more about excel excersices read our article "Write My Excel Exercises".

Top Tips How to Become Good at Excel

1. How to create a Data List in Excel

In order to manage all the Excel list functions effectively and create a list, you need to follow this guideline:

• Go across the top row of header labels. Then use such categories as labels: Start Date, Task, Inventory Item, etc.
• Make sure your list, together with the header row, does not touch adjoining filled cells in the worksheet. The list must also include some empty cells all around it.
• Remember that you should not let empty rows or columns divide your data list into two as it can also split the list.

2. What to choose: Data List or Excel Table?

Since Excel 2007, Excel tables are considered to be useful tools for those, who want to manage their lists. I knew so many Microsoft Excel tips and tricks for creating lists that I used to think I Tables could be of little use for me personally. Though after the first try to use them, I finally realized how useful they really are.

By changing your data list into an Excel Table, you benefit from:

1. If you add or delete data the Table gets expanded or contracted automatically.
2. Note that created charts get expanded automatically once you add more data.
3. Tables receive names automatically. Though if you want, there's a possibility to modify the name.
4. You also have a chance to apply some complex formatting from other galleries of layouts designed by professionals.
5. While working with table calculations, you should remember that like a column total, they do not include hidden by filtering rows. Thus, the results go in accord with visual displays.

3. How to change a Data List into Excel Table

For changing a data list into Excel table, you need:

• Choose a cell which can also include headers.
• Find the Insert tab and click Table (/press Ctrl+T).
• You will then find the Create Table dialog box which usually appears with the automatically selected range.
• In case your Table consists of a row of top header labels, you may choose My table has headers.
• Press Okay.

4. Use Table Tools Design Tab for Managing Excel Table

Try to select anywhere in the Table. Did you notice a new tab in the row? There you can find the Table Tools Design tab which consists of numerous handy tools which can help you to resize your Table, rename it, create a Pivot Table, apply any available predesigned format to it, etc. Your work with a Table can mostly be done by using either the Data tab or the Table Tools Design tab.

Best Custom Writing Service

• We'll write any essay from scratch according to your instructions
• All papers are plagiarism free
• Prices start from only 11.99/page
• Placing an order takes 3 minutes

Call now Start chat Order now

5. Rename Your Table

Creating an Excel Table, you may find your table already named, like Table2, Table3, etc. You can also use names like that when accessing your functional Excel or Pivot Table.

You can create a descriptive name by:

• Selecting a cell in your Table.
• Entering a new name for the Table (go the Table Tools Design tab - Table Name field).

6. From Excel Table Back to List

Change your Excel Table to a list by:

• Clicking within your Excel Table.
• Clicking Convert to Range, which you can find in the Tools group on the Table Tools Design tab.

You can also use a shorter way to do it: just right-click within a Table, choose Table - Convert to Range.

7. Make it Easier with Gallery of Predefined Table Formats

To ease your life, you can choose the predefined Table formats which consist of alternating rows, header formatting, and some other for creating lists. However, you need to know some advanced excel tricks here. First, for changing your list into a Table, you need to click inside the list and then press Ctrl+T. Then to choose a Table format (go to Table Styles in predefined formatting gallery). After that, you can change your Table to a list. After clicking the Convert to Range tool (the Table Tools Design tab - the Tools group), tool features will be deleted, but the formatting will be left on the list.

8. Create Charts which Auto-Expand with Excel Tables

Tables of a data source for creating charts usually have the same vertical layout and are arranged in the same way as regular data for a chart. The advantage of using a Table while holding your chart data is that when you add new data in the last row of your Table, it expands automatically together with the chart.

9. How to Modify Header, Bold Columns, and Filter Buttons Fast

To change the header row, bolded and banded column, and filter buttons, you need to select and clear check boxes in the Table (go to the Style Options group). Check boxes are used as:

• Header Rows for showing/hiding header labels.
• Total Rows for adding total rows and results.
• Banded Rows for helping with formatting.
• First Columns for Bold data of the first columns.
• Last Columns for Bold data in the last columns.
• Banded Columns for formatting columns with alternated colors.
• Filter Button is for showing/hiding the filter arrows.

10. How to Create Custom Predefined Table Formats in the Gallery

Another advantage of Tables usage is that a predefined gallery of table formats can profoundly ease your life. Go to the Table Styles group - the Table Tools Design tab and find the predefined gallery. Then select a cell and click the preferable predefined style.

You can even create a predefined style on your own and make it match your corporate color identity. Many companies have a marketing identity which serves to specify company's name, logo, logotype, and colors. Colors used in the identity package usually have color codes which you are able to enter in Excel.

11. Use Filter Arrows for Aligning your Data

When you create a Table filter, you can notice arrows in the right side of the header labels; they are added there automatically. However, arrows usually push header labels to the left and, as a result, the header labels and data are not right aligned and the whole alignment looks clumsy.

For aligning the right edge of the data with the right edge of the header label:

• Select the column of the data excluding the header label.
• Go to the Home tab - Alignment group and click the Increase Indent tool - your data and header must be now right aligned.

12. Some Useful Shortcut Keys for Excel Lists and Tables

By just remembering a few of the following shortcut keys, you can make your movements within your Excel list or Table easier:

1. Moving up/down - Ctrl+Up/Down
2. Moving left/right - Ctrl+Left/Right
3. Selecting and move - Shift+Ctrl+Arrow
4. Selecting entire list/Table - Shift+Ctrl+* (asterisk)
5. Selecting row - Shift+Spacebar
6. Selecting column - Ctrl+Spacebar

13. Built-In and Drop-Down Lists for Your Data Entry

You have to create neither a drop-down nor Data Validation list. Your Excel list can display its entry. To see it just right-click on the cell where you aim to add any data and choose Pick in Drop-Down List.

14. Use a Drop-Down List for Entering Totals, Averages, and Counts

Go to the Table Style Options group and on the Table Tools Design tab select a cell within your Table, then choose the Total Row check box. Now you can enter column calculations in the bottom of Table.

By selecting a cell in the last row, for instance, F463, and clicking the drop-down arrow to the right, you'll be able to turn on and off a calculation at the bottom of each column. If you want to select a different type of calculations or just leave it blank - choose None.

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

When you scroll down a huge Excel Table or list, header labels can be lost by scrolling off the top of the screen. To stop it, you need to freeze the title row. To do so, you need, firstly, put the list of the header in the area on the screen where you work. Then, click the row number of the row which will select the whole row under the titles. And, finally, go to the View tab - the Window group and click Freeze Panes.

The list will scroll while the header labels remain frozen. To unfreeze the window you can by selecting the row under the labels and choosing View - Freeze Panes - Unfreeze Panes.

16. Using Table References for Formulas

In a newly created Excel Table, a name is usually applied to the table, as well as to each of its columns. All the names are pretty much the same as names for cell references. When you add a formula to a total column, for instance, you always notice the Table and the column name, but they are not cell references.

17. How to Remove Filter Arrows in Table Headings

If you desire to have a cleaner appearance and make it easier for other users to filter/sort and remove the filter arrows, you should first select within the needed cells of your Table, then go to the Table Style Options, and on the Table Tools Design tab, find the Filter Button. Click on it and everything will be cleared.