ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Creating and using tables in Excel 2007

Updated on September 7, 2012

Introduction

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.

Ensure Excel 2007 is configured correctly to auto correct Tables.
Ensure Excel 2007 is configured correctly to auto correct Tables. | Source

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 and deleting Tables in Excel 2007

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

A table in Excel 2007.
A table in Excel 2007. | Source

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 and columns to an existing table

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.

Showing how to add columns and rows to an Excel 2007 table.
Showing how to add columns and rows to an Excel 2007 table. | Source

Using formulas in tables

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

=[

Using table column headers for formulas in Excel 2007.
Using table column headers for formulas in Excel 2007. | Source

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.

Working with Previous versions of Excel

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.

Printing tables

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.

Linking a table to a Pivot table or Pivot Chart

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

Conclusion

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

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)