Excel 2007 Training Video: Tables

Learn how to use tables to simplify layout and formatting in Excel. A transcript is available for this video.

Using tables in Excel 2007

You can create tables from existing data in Excel.

Tables now have instant formatting and filtering options.

This demo will look at how to format your spreadsheet. How to insert tables using existing data, the formatting options of those tables and also filtering.

So firstly we will look at the old way of formatting a series of data. The old adage of you must select it to affect it. So if I select the headings, I can use, on the home tab, some of the formatting options such as the bold and italic.

I can use a dropdown for the fill colours. We have got a lot of new fill colour options available. I will use the Tan background. I can change the font colour. Make that a dark colour.

There are my formatting options for my headings. For the values, I can select them, add again give them a different background colour, foreground colour, make the text red and underline them. So quickly using the home tab we have changed some formatting options on our sheet.

I'm going to press undo and go back to the way I had it before. I can click on undo once and go back step by step, or I can click the dropdown button and choose where I want to go back to. It's a good way to do multiple undo or redos.

New to Office 2007 is the ability to change a series of data to a table. With a cell in the table selected, I can go to the Insert Tab. From Insert is the option to insert a table. It will Insert a table using currently selected data. Notice that the check box My table: has headers has been clicked. This is fine, as week 1 and Sales are headers. If that wasn't the case, I could undo that. Click on OK and you can see that my table has been formatted. I have filters on my headers that we will use in a moment.

Let's say that I am not really comfortable with the colour scheme that is being used. Select the table again, and you will see on the ribbon, a new tab called table tools, and within the table tools is a design tab and I can use the option to use quick styles. Clicking on quick styles, will provide a gallery of different table styles to use.

The good thing about the gallery is that I don't have to select it and then view what the table looks like, I've got a live preview, so when I hover over one of the styles, you can see in the left hand side that a live preview is being displayed of the currently selected table style. Once I'm happy with one, I will select this one, Table style medium 2, I will click on it and the new style is applied. It just saves a lot of time, if you have a table. Rather than individually styling the cells, convert it to a table and use the quick styles tab to change the effect. The drop down arrows in the headers of the table. I've got values of 154 all the way down to 28. I only want to show days of the week where sales where over 50. To do that I click on the dropdown for sales figures, and in the dropdown box I can put in Filter, and from the number filters we have Equals, Does Not Equal, Greater than, Greater than or Equal to. The question I want is all sales figures that are over 50. I click on Greater Than, and I can see sales are Greater Than, and I can enter my value. Once I'm happy with that I'll click OK. And only Monday and Tuesday have sales figures of over 50, so those two are displayed. Notice the difference in the downward pointing arrow for Week 1 which shows no filter and Sales, which has a filter. When you hover over it, you can see sales greater than 50. If I want to show them all again I can click on the filter icon, and just click on the select all. I've just clicked on OK and you see that all the sales figures are back.

This demo looked at formatting changes using the home tab formatting options, we used bold, italic, font fill colour and font colour. We also clicked on Insert and inserted a table of the currently selected data. Once you have got a table, table tools allows you to change the formatting of your table using quick styles. And again by moving your cursor over the quick styles, the preview will be displayed. Select that preview and your table is now formatted.