Creating and using tables in Excel 2007

Tables are a new feature in Excel 2007. In previous versions of Excel they were referred to as a list. When you are first introduced to tables, it is likely that you will be a little underwhelmed. Initially, a table just looks like a range of data to which a filter has been applied. This is certainly not the case; the table in Excel 2007 has some features that are very useful indeed.

Once you create a table, Excel 2007 knows to add data added below or to the right directly to your table. When working within a table, you can create formulas using column headers and Excel will even auto fill entire columns with formulas for you. Finally, it allows your pivot tables or charts to be dynamically updated when you add new rows or columns.

Before beginning to work with tables in Excel 2007, there are a couple of settings that you need to check first. Click on the Office button in the top left and then select Excel Options. Click on Proofing, then AutoCorrect Options and finally the AutoFormat As You type Tab. Ensure the options are the same as those below.

Let’s begin by creating a table in Excel 2007 and then continue with a more detailed examination of the advantages of using tables over normal ranges.

Creating a table

To create a table, select the data you wish to be included in the table and click on the Insert tab and then the Table button in the Tables group. If your table has column headers, leave the box My table has headers ticked, otherwise clear it and click OK.

Excel will now create your table. At this point, we can change the formatting of the table to meet your own requirements. The Table Style Options group within the Table Tools Design tab has a number of options that you can select or deselect depending on how you want your table to look. Most of these options will improve the readability of your table (for example banded rows or columns, which changes the colour of adjacent rows or columns to make data easier to read).

Using the Table Styles group, you can change the colours used in your table which is particularly useful if you want your table to pop out at the readers of your spreadsheet. The below picture illustrates my table

Deleting a table

To delete a table while keeping the data, select a cell in your table, right click and select Table, Convert to Range.

NOTE: The cells will retain the formatting applied to them by your table, to roll back the cell formatting, firstly select the cells that previously made up your table and then select the Clear button on the Home tab and select Clear Formats.

Adding rows to a table is very straightforward. Simply add a value to the next row and Excel includes it in your table.

Excel 2007 has provided another way to add rows and columns. For the eagle eyed, when you create a new table, the cell at the bottom right corner of the table has what looks like a blue triangle in the corner of the cell (this is illustrated in the picture below). You can drag this to add columns or rows to your table quickly and easily.

Excel 2007 has added some really cool features for using formulas within tables. Now that I have my table, I have decided I want to calculate the total number of hits for my hubs that did not happen in the last thirty days. I create my formula in the normal fashion and I end up with the formula as below:

=Table1[[#This Row],[Total]]-Table1[[#This Row],[30 Days]]

Excel 2007 has introduced a new way of creating formulas within tables that is really rather cool. Rather than completing a formula in the standard way, type in

=[

Excel will then give you a list of your column headers that you can use to create your formula (as shown above). In my case I want to use Total. Type in the closing bracket to give you the following.

=[Total]

I want to show the total minus the hits from the last thirty days, so I use the minus sign then another open square bracket

=[Total]-[

I then select 30 days from the drop down and close the formula with a closed square bracket which gives me the following

=[Total]-[30 Days]

I am sure you will agree that this is a much more readable and understandable formula than the one that we started with.

Once you press enter, you will notice another cool feature of tables in Excel 2007. Excel 2007 auto fills formulas for entire columns.

Previous versions do not use tables. To send data containing tables to users of previous versions convert the table to a range. This is done by selecting a cell in your table, right clicking and select Table, Convert to Range.

When working with tables, Excel 2007 gives you the option to print just a table. To do so, click the Office button and then select Print. In the section Print what, select Table.

NOTE: If a table is not selected before clicking print, the option will be greyed out.

When you link a table to a pivot table or chart, they will automatically increase or decrease their ranges as rows or columns are added or removed. Pivot tables allow you to summarise information from ranges and tables efficiently and quickly and are very powerful and extremely flexible. They are particularly valuable when you have data that is constantly changing or updating. I have hubs that cover pivot tables and pivot charts in greater detail.

http://robbiecwilson.hubpages.com/hub/Creating-Pivot-Tables-in-Excel-2007

http://robbiecwilson.hubpages.com/hub/Creating-Configuring-and-Using-Pivot-Charts-in-Excel-2007

Tables are an excellent new addition to Excel in 2007. They add a number of new features, a number of which allow you to work with your data more efficiently. After reading this hub, I do hope that you will use tables rather than normal data ranges in your work to take advantage of the new features on offer. Many thanks for reading this hub; I hope you enjoyed reading it as much as I enjoyed writing it. Please feel free to leave any comments you may have below.

I also have a number of other hubs on aspects of Excel 2007, covering everything from Conditional Formatting to creating charts and graphs. I have an Index hub which also covers how I successfully transitioned from Excel 2003 to 2007 as well as outlining my other Excel 2007 hubs which can be found here

http://robbiecwilson.hubpages.com/hub/How-to-adjust-to-Excel-2007-from-previous-versions-as-well-as-step-by-step-guides-to-many-functions-in-Excel-2007