In this episode, more about Ctrl+T tables. See how they can improve VLOOKUP, Charts, and Data Validation. Episode Recap:
In the last episode, Ctrl+T made the pivot table source expand
This also helps VLOOKUP and Charts and Data Validation
Although it is slightly different in each
Create your VLOOKUP then make the table a Ctrl+T table
Remarkably the VLOOKUP formula will rewrite itself
Build a chart. Make the source data a Ctrl+T table. Add new months.
For Data Validation Source: Make it a table and then name the range without the heading
Use the named range as the Validation Source
Also mentioned in the episode: FORMULATEXT function for showing a formula
In the last episode, Ctrl+T made the pivot table source expand
This also helps VLOOKUP and Charts and Data Validation
Although it is slightly different in each
Create your VLOOKUP then make the table a Ctrl+T table
Remarkably the VLOOKUP formula will rewrite itself
Build a chart. Make the source data a Ctrl+T table. Add new months.
For Data Validation Source: Make it a table and then name the range without the heading
Use the named range as the Validation Source
Also mentioned in the episode: FORMULATEXT function for showing a formula
Transcript of the video:
Learn Excel for MrExcel Podcast, Episode 2002 – CTRL T Helps VLOOKUP I'm podcasting this entire book, go ahead and subscribe to the playlist, top right hand corner, there's an "i" up there and welcome back to the MrExcel netcast.
I'm Bill Jelen.
So yesterday's podcast we talked about how CTRL T makes your Pivot Table Data automatically grow.
The other really amazing thing, here, is I have a VLOOKUP.
So there's the VLOOKUP and you're seeing the FORMULA over here thanks for the FORMULA TEXT FUNCTION.
I love FORMULA TEXT.
It was brand new in Excel 2013.
It lets me show you the FORMULA and the results side-by-side.
Okay and you can see that this FORMULA is pointing to a Table here that's one, two, three, four, or five rows, , but there's something missing.
So A106.
Alright now here's the amazing thing I'm going to take this Table.
This little VLOOKUP Table here.
I'm going to do CTRL T, to make it into a real Table.
My Table has headers and then, I'm going to come here and type A106, the missing item, outside of the range, and it's $88 and did you see that that?
The FORMULA automatically rewrote itself to now go down through row F10.
It didn't rewrite itself to refer to the Table, using Table nomenclature, , but it it simply worked .
Here's another example where CTRL T makes things better.
Here's a chart, January through April, here's the Data, I'm going to CTRL T the Data and notice in all these cases the VLOOKUP, the chart, it was all there, just from a regular range and now when I add new Data, so here's May and we'll give it 15,000 it automatically grows.
Alright, and when I look at the chart series, because I'm fascinated about how this works, the the chart series doesn't get rewritten in Table nomenclature, but it simply says, oh hey this is a Table we're going to extend from row five to row six.
And here's another one.
I picked this one up, this one's not in the book, this is a bonus.
I picked this up at an amazing conference in Lucerne, Switzerland, called Trainer Tage.
That's German for Trainer Days.
These are, the the Trainer Tage Team, I was lucky enough to speak there for two years, Tanya Kuhn puts us on, and saw this amazing trick.
So we want to have a Data Validation List and we might be adding more things to the end of the Data Validation List.
So, here's my list.
I'm going to CTRL T to make it into a Table and then I'm going to very carefully name everything except for the heading.
So I'll call it MyList ENTER.
Right, so we just created a name there and then here we're going to go to Data and then it's drop-down, choose Data Validation.
We're going to allow a List and the source is going to be =MyList ENTER.
Alright, so now, what we should expect to see is Apple threw Fig would be there.
Beautiful.
Alright, but then when I come along and I type a new item, that End Of Table Marker is going to move down to the bottom of row 8, and remarkably, it's going to be in he list.
Right, these are all great amazing side benefits of using Tables.
Alright now, of course I'm going to ask you to buy my book, but before I do that, I should give credit to Zach Barresse and Kevin Jones who wrote THE book on Excel Tables.
Right, if you need to learn anything about Tables, or just see all the amazing stuff that comes about when you use Tables, check out this book from Zach and Kevin.
Alright yeah, and then of course, I wish you to buy my book, so much knowledge in the palm of your hand.
All of the tips from all of August and September podcasts.
Right there.
10 bucks is an e-book, 25 bucks is a print book.
Click the I in the top right hand corner.
Alright, so a recap here.
In the last episode we use CTRL T to make the Pivot Table Source expand.
It also helps VLOOKUP and Charts and Data Validation.
It's slightly different in each, but you know, even after the VLOOKUP and the Charts are set up, you can, after the fact, make it into a Table and the VLOOKUP and the charts will expand.
So create your VLOOKUP and then make the Table, the VLOOKUP Table of CTRL T Table and the FORMULA just rewrites itself.
It's so cool.
Or build a chart and then make it into a CTRL T Table and as you add new Data the Chart will automatically expand for Data Validation.
Right now, this is from Tanya in Switzerland, make it a Table and then name the range without the heading, and then used the name range as the Validation Source.
I also mentioned the Form Of The Text Function.
Alright now, when I ask people to send in their favorite tips, Tables were popular.
Alright, Peter Albert, Snorri Iceland, Nancy Federici, Colin Michael, James Mead, KR Patel, Paul Payden and then a bunch of people suggested using OFFSET to create expanding ranges for dynamic charts.
Charlie, Don, Francis and Cecilia.
Tables now do the same thing in most cases, so you don't need OFFSET anymore.
So, I actually took their ideas and threw them out, and put Tables in instead, but I still appreciate them sending their ideas in.
I'm Bill Jelen.
So yesterday's podcast we talked about how CTRL T makes your Pivot Table Data automatically grow.
The other really amazing thing, here, is I have a VLOOKUP.
So there's the VLOOKUP and you're seeing the FORMULA over here thanks for the FORMULA TEXT FUNCTION.
I love FORMULA TEXT.
It was brand new in Excel 2013.
It lets me show you the FORMULA and the results side-by-side.
Okay and you can see that this FORMULA is pointing to a Table here that's one, two, three, four, or five rows, , but there's something missing.
So A106.
Alright now here's the amazing thing I'm going to take this Table.
This little VLOOKUP Table here.
I'm going to do CTRL T, to make it into a real Table.
My Table has headers and then, I'm going to come here and type A106, the missing item, outside of the range, and it's $88 and did you see that that?
The FORMULA automatically rewrote itself to now go down through row F10.
It didn't rewrite itself to refer to the Table, using Table nomenclature, , but it it simply worked .
Here's another example where CTRL T makes things better.
Here's a chart, January through April, here's the Data, I'm going to CTRL T the Data and notice in all these cases the VLOOKUP, the chart, it was all there, just from a regular range and now when I add new Data, so here's May and we'll give it 15,000 it automatically grows.
Alright, and when I look at the chart series, because I'm fascinated about how this works, the the chart series doesn't get rewritten in Table nomenclature, but it simply says, oh hey this is a Table we're going to extend from row five to row six.
And here's another one.
I picked this one up, this one's not in the book, this is a bonus.
I picked this up at an amazing conference in Lucerne, Switzerland, called Trainer Tage.
That's German for Trainer Days.
These are, the the Trainer Tage Team, I was lucky enough to speak there for two years, Tanya Kuhn puts us on, and saw this amazing trick.
So we want to have a Data Validation List and we might be adding more things to the end of the Data Validation List.
So, here's my list.
I'm going to CTRL T to make it into a Table and then I'm going to very carefully name everything except for the heading.
So I'll call it MyList ENTER.
Right, so we just created a name there and then here we're going to go to Data and then it's drop-down, choose Data Validation.
We're going to allow a List and the source is going to be =MyList ENTER.
Alright, so now, what we should expect to see is Apple threw Fig would be there.
Beautiful.
Alright, but then when I come along and I type a new item, that End Of Table Marker is going to move down to the bottom of row 8, and remarkably, it's going to be in he list.
Right, these are all great amazing side benefits of using Tables.
Alright now, of course I'm going to ask you to buy my book, but before I do that, I should give credit to Zach Barresse and Kevin Jones who wrote THE book on Excel Tables.
Right, if you need to learn anything about Tables, or just see all the amazing stuff that comes about when you use Tables, check out this book from Zach and Kevin.
Alright yeah, and then of course, I wish you to buy my book, so much knowledge in the palm of your hand.
All of the tips from all of August and September podcasts.
Right there.
10 bucks is an e-book, 25 bucks is a print book.
Click the I in the top right hand corner.
Alright, so a recap here.
In the last episode we use CTRL T to make the Pivot Table Source expand.
It also helps VLOOKUP and Charts and Data Validation.
It's slightly different in each, but you know, even after the VLOOKUP and the Charts are set up, you can, after the fact, make it into a Table and the VLOOKUP and the charts will expand.
So create your VLOOKUP and then make the Table, the VLOOKUP Table of CTRL T Table and the FORMULA just rewrites itself.
It's so cool.
Or build a chart and then make it into a CTRL T Table and as you add new Data the Chart will automatically expand for Data Validation.
Right now, this is from Tanya in Switzerland, make it a Table and then name the range without the heading, and then used the name range as the Validation Source.
I also mentioned the Form Of The Text Function.
Alright now, when I ask people to send in their favorite tips, Tables were popular.
Alright, Peter Albert, Snorri Iceland, Nancy Federici, Colin Michael, James Mead, KR Patel, Paul Payden and then a bunch of people suggested using OFFSET to create expanding ranges for dynamic charts.
Charlie, Don, Francis and Cecilia.
Tables now do the same thing in most cases, so you don't need OFFSET anymore.
So, I actually took their ideas and threw them out, and put Tables in instead, but I still appreciate them sending their ideas in.
