The most essential Excel feature every assistant should use

As a former PA turned technology instructor, Vickie Sokol Evans has worked with every type of document, spreadsheet, presentation and database you can imagine over the past 25 years, and seen some major improvements in the technology since the dos-based version of Word.

We have the capability to get more things done in less time, but with the speed at which technology changes, it is becoming increasingly impossible to prioritize what new technology we need to focus on so that the learning curve is shorter and more relevant to our role.

So I began developing a “Tips in Minutes” series of technology books and classes to shed light on some of the many time-saving ways you can produce professional documents, spreadsheets and presentations with minimal effort.

Hands down, the award for best feature ever introduced in Microsoft Office is Excel Tables. No other feature has made my jaw drop so low, turned my pupils into little hearts, nor made me proclaim to the world that I want to marry it. Excel Tables, you are the love of my life. How did I ever exist without you?

Below is an excerpt from my book “100 Tips in 100 Minutes using Windows 7 & Office 2010” that highlights quick tips for using Tables in Excel. If you learn only one new feature this year, make it Excel Tables. It just may change your life.

Note: The Table feature is available in Excel 2007 and Excel 2010 for PC, as well as Excel 2011 for Mac.

Microsoft Excel 2010 – Manage your data with minimal effort

You are Gromlock, the Data Enterer. All puny data quails before your wrath. Your use of the 9-key number pad is legend among your allies and your enemies, and finally your quest for the ultimate power to format data has a table is at long last come to an end. No data will pass by you without being managed to its fullest. All hail Gromlock, the Data Enterer, the greatest data warrior our time has ever known.

One of the most valuable and time-saving features first introduced in Excel 2007, is Format as a Table, which can be applied to any list to create not only a professional look, but provide you with a dozen other benefits you won’t want to live without. It’s perfect for managing financial data, contact lists, inventory, sales data, gift lists etc.

Here are some of the benefits you receive when formatting your lists as tables:

• Filters are automatically included
• Remove duplicates easily
• Instantly format the table
• Add a total row using a toggle button
• Calculated columns
• View table headings when you scroll
• Easily rearrange columns
• Easily select columns
• Auto-expands to include new rows and columns
• Delete rows in your table without affecting surrounding rows in other tables
• You can name your table to keep your data organized, to use as a shortcut and to use as a reference for PivotTables and Charts
• And more!

As you can see, there are numerous benefits to formatting your list as a table but there are also a few limitations. You will need to convert your table back to a range by clicking the Convert to Range button on the Table Tools tab in the Tools group if you want to do the following:

Use the Subtotals feature

Transpose the data

Tip #50  Format a list as a Table

Before you can reap the many benefits of a table, you must first format your list as a table.

To format your list as a table

1. Select the range of data in your list you wish to format as a table or click in any cell of your table and press [Ctrl]+[A], which should select your entire table as long as you do not have any empty rows or columns.
2. On the Home tab, in the Styles group, click the Format As Table button to launch the table styles dropdown menu. Select the first one in the list to get started. You can always change it later.
3. Make sure the range of data you want to format as a list is correct and that it has the correct setting for My table has headers. Then click Ok.

Figure 1. Create Table dialog box

Tip #51  Expand the table as you type

Once your list is formatted as a table, you can use many of the features of an Excel table, such as Table AutoExpansion. As you add text to a column or row next to a table, Excel will automatically include that column or row in the table. In most cases, this is extremely convenient and when it is not, you always have the ability to stop expanding.

To expand the table as you type

Start typing content in the first column that is not part of your table. As soon as you press [Enter] or [Tab] or click out of the cell, Excel adds that new column to the defined table.

To decline Table AutoExpansion

As soon as Excel expands the table to include the new column or row, you will see the AutoCorrect Options Tag next to your column as seen in Figure 2. Click the Options Tag and select Stop Automatically Expanding Tables and the new data you typed will not be included with the table.

Figure 2. AutoCorrect Options Tag for Automatically Expanding Tables

To turn off AutoExpansion altogether

1. Click the Options Tag as seen in Figure 2. The AutoCorrect dialog box opens.
2. On the AutoFormat As You Type tab select Control AutoCorrect Options…
3. Uncheck the second option “Include new rows and columns in the table” and click Ok.

Figure 3. AutoCorrect Dialog Box to stop auto-expanding tables

Tip #52  Create a calculated column with minimal effort

With your data in a table, you can create a calculation in any cell and Excel will automatically copy that calculation to every cell in that column within the table without missing a beat. When necessary, you still have the option to stop the Autocalculate feature. Note: this does not work unless your list is formatted as a table.

To create a calculated column in your table

1. Click in any cell of the column you wish to add the calculation.
2. Add the calculation in that cell by creating a formula or function and click [Enter] or [Tab]. Every cell in that column should now have the calculation you just created.

To decline Table AutoCalculation

As soon as Excel adds the calculation to every cell in the column, you will see the AutoCorrect Options Tag next to your column as seen in Figure 4. Click the Options Tag and select Stop Automatically Creating Calculated Columns. The new calculation you typed will only apply to the current cell and will not be added to every cell in that column.

Figure 4. AutoCorrect Options Tab for Automatically Creating Calculated Columns

To turn off auto-calculations altogether

1. Click the Options Tag as seen in Figure 4 and on the AutoFormat As You Type tab select Control AutoCorrect Options…
2. In the AutoCorrect dialog box as seen in Figure 5, uncheck the third option “Fill formulas in tables to create calculated columns” and click Ok.

Figure 5. AutoCorrect Dialog Box to stop creating calculated columns

Tip #53  Instantly add a total row to your table

When you’re ready to add a row at the bottom of your table to count rows in your table or sum a column, you can rely on your mouse clicking skills, not your math skills, to do all the work for you.

To add a total row to your table

1. With your list now formatted as a table, click anywhere in the table to activate the Table Tools contextual tabs.
2. On the Design tab, in the Table Style Options group, click the Total Row option.
3. Click in any cell within your new total row and click the dropdown arrow to select the function you want in that column.
4. To turn off the total row, go back to the Design tab, in Table Style Options group and uncheck the Total Row option.

Tip #54  Use Autofilter to find data

Filtering allows you to easily find and work with a subset of records in your table. It shows the records that meet your criteria and hides the records that do not. You can even filter on multiple columns.

When you format your list as a table, filter buttons are automatically added to the header row saving you time from having to insert them yourself.

To find data using Autofilter

1. With your data formatted as a table from the previous step, click one of the dropdown arrows in your header row.
2. Use any one of a combination of filters to find your data.

Tip #55  Add style to your table

You do not have to be a graphic designer or have an eye for color to turn your table of data into a professional and eye catching report that people will actually read. Let Excel do all the work for you using table styles and to save even more time, rely on gallery previews in Excel 2010 to show you what the table will look like before you apply the style.

To add style to your table

1. With your list formatted as a table, click inside your table so that the Table Tools contextual tab is displayed.
2. On the Design tab, in the Table Styles group, mouse over any one of the Table Styles to preview a table style.
3. Click the More button on the ribbon, as seen in Figure 6, to display more table style formats.

Figure 6. Table Styles More button

4. When you find the one you like, simply click on the style to apply.

Tip #56  Select all cells in a column with ease

Selecting a range of cells can certainly be tricky at times, especially if it involves scrolling to get to the end of the range. Never fear, use this one click method to select all the cells within one column and nothing more (or less).

To select all cells in a column with ease

1. With your list formatted as a table, select the first cell in your column, which is the column name.
2. Click the bottom border of the column name, which selects the entire column of data.

Tip #57  Remove duplicate records

To remove duplicate records in your table

1. With your list formatted as a table, click inside your table so that the Table Tools contextual tab is displayed.
2. On the Design tab, in the Tools group, click the Remove Duplicates option to launch the Remove Duplicates dialog box as seen in Figure 7.

Figure 7. Remove Duplicates dialog box

3. If you want Excel to look for duplicate rows by evaluating the data in every column, click Ok. Otherwise, use the checkboxes to identify which columns Excel should examine for duplicates.