Once you have defined an Excel 2007 table, Excel will usually automatically sense when you enter data in adjacent cells and extend the table to include the new cells. Episode 420 takes a look at extending tables, using the Table Tools Design ribbon, and new selection options when you have a table.
This blog is the video netcast companion to the upcoming book, Excel 2007 Miracles Made Easy. Download a new two minute video every Tuesday and Thursday to learn one of the tips from the book!
This blog is the video netcast companion to the upcoming book, Excel 2007 Miracles Made Easy. Download a new two minute video every Tuesday and Thursday to learn one of the tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, we have 19 days until Excel 2007 debuts on January 30th 2007.
Today's a 2007 Thursday, I want to keep talking about tables.
In episode 418 we started talking about tables and how to define a table.
Once you have a table to find, there's a couple of cool tricks we can do.
If you need to add more data to the table, I'm going to come down to the first blank row and type a new record.
6/30/2007, you'll see that as I type those new rows, they automatically become part of the table.
That's one cool functionality.
We always talk about how the total formula at the bottom of the table tends to not include the new rows.
Well, as long as you add new rows immediately below the table, Excel will automatically extend the formulas to point at the table.
There's also a little icon here in the lower right hand corner of the table, that you can use to automatically extend the table to include more rows or even more columns, if we drag outwards.
So most of the time, the table intelligence will automatically extend the table, if for any reason it doesn't, you can use the icon to drag.
Another interesting thing is: usually if we hover over the heading for column B, we'll select the entire column.
But now if I move down just a bit, there's a new mouse pointer that has the tiny arrow.
When I click this the first time, I select just the data cells in the table, not the heading.
A second click here will select the data cells and the heading.
So this could be a really handy thing, if you need to format all of, let's say, the numbers on the table, but not the heading, that new selection criteria.
When a table's present on the worksheet, you've selected one of the cells, there is a new ribbon that appears, it's called Table Tools Design.
This has a whole bunch of features that you can use with the table, for example last week I talked about the remove duplicates, there's an icon here that will Remove Duplicates.
You can Summarize with a PivotTable, Export the table to a website or to some other external field.
There's also a whole gallery here of table styles.
When we define the table, you're able to choose which table style you wanted.
It's interesting here though, that we can specify certain conditions that should appear or should not appear.
For example, if I choose Banded Columns and then look at the table styles, you'll see that some of the table styles actually support Banded Columns.
Every other column having an alternating color.
If I don't want Banded Columns, I can turn that off, turn off Banded Rows and now the styles change a little bit, showing me the different styles that are available.
So there's a whole bunch of built-in styles here, that really go far beyond the old auto format tricks that we had in Excel 2003.
If for some reason one of these styles isn't enough for you, you can actually customize the style and say create a green bar format, where every stripe is two rows instead of one, or anything like that.
We'll talk about that in a future netcast.
Hey, thanks for stopping by, we'll see you tomorrow for another netcast from MrExcel.
Well, we have 19 days until Excel 2007 debuts on January 30th 2007.
Today's a 2007 Thursday, I want to keep talking about tables.
In episode 418 we started talking about tables and how to define a table.
Once you have a table to find, there's a couple of cool tricks we can do.
If you need to add more data to the table, I'm going to come down to the first blank row and type a new record.
6/30/2007, you'll see that as I type those new rows, they automatically become part of the table.
That's one cool functionality.
We always talk about how the total formula at the bottom of the table tends to not include the new rows.
Well, as long as you add new rows immediately below the table, Excel will automatically extend the formulas to point at the table.
There's also a little icon here in the lower right hand corner of the table, that you can use to automatically extend the table to include more rows or even more columns, if we drag outwards.
So most of the time, the table intelligence will automatically extend the table, if for any reason it doesn't, you can use the icon to drag.
Another interesting thing is: usually if we hover over the heading for column B, we'll select the entire column.
But now if I move down just a bit, there's a new mouse pointer that has the tiny arrow.
When I click this the first time, I select just the data cells in the table, not the heading.
A second click here will select the data cells and the heading.
So this could be a really handy thing, if you need to format all of, let's say, the numbers on the table, but not the heading, that new selection criteria.
When a table's present on the worksheet, you've selected one of the cells, there is a new ribbon that appears, it's called Table Tools Design.
This has a whole bunch of features that you can use with the table, for example last week I talked about the remove duplicates, there's an icon here that will Remove Duplicates.
You can Summarize with a PivotTable, Export the table to a website or to some other external field.
There's also a whole gallery here of table styles.
When we define the table, you're able to choose which table style you wanted.
It's interesting here though, that we can specify certain conditions that should appear or should not appear.
For example, if I choose Banded Columns and then look at the table styles, you'll see that some of the table styles actually support Banded Columns.
Every other column having an alternating color.
If I don't want Banded Columns, I can turn that off, turn off Banded Rows and now the styles change a little bit, showing me the different styles that are available.
So there's a whole bunch of built-in styles here, that really go far beyond the old auto format tricks that we had in Excel 2003.
If for some reason one of these styles isn't enough for you, you can actually customize the style and say create a green bar format, where every stripe is two rows instead of one, or anything like that.
We'll talk about that in a future netcast.
Hey, thanks for stopping by, we'll see you tomorrow for another netcast from MrExcel.
