Today Bill brings us the second Podcast in the "Things that I Learned at Trainertage" Series. [To see the first episode in this new series: "Dynamic Range Without OFFSET" Podcast #1632]
"Ctrl+T Tables; Relative or Absolute"
Today is another trick from Trainertage 2013: Dietmar Gieringer was showing how to make a Table Reference Absolute using INDIRECT. I was surprised that a Table Formula is Absolute when copied across the Columns. Follow along With Episode #1635 as Bill shows us both styles.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
"Ctrl+T Tables; Relative or Absolute"
Today is another trick from Trainertage 2013: Dietmar Gieringer was showing how to make a Table Reference Absolute using INDIRECT. I was surprised that a Table Formula is Absolute when copied across the Columns. Follow along With Episode #1635 as Bill shows us both styles.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
Excel tricks that I learned at Trainertage.
Hey, this is episode 1635.
A Ctrl + T table, is it relative or absolute?
As I mentioned I was over in Lucerne, Switzerland doing some sessions at their Trainertage trainer days and this was a lot of fun.
In fact, I just got to check out the video.
They put up their first vimeo.com/58590184 and I clearly was having a lot of fun here if-- you know, I said I spoke slow English.
If you want to see my slow English, I was in slow English mode when they shot that video.
Just a great time made some great friends.
I'm going to show a lot of the tricks that I learned at Trainertage here on the podcast over the next several weeks, you know, maybe a couple per week.
So this is from Dietmar Gieringer.
Dietmar-- actually everyone there is using Ctrl + T tables far more often than I ever used them and so just watching them do things-- Dietmar was talking about something else but I was kind of amazed when I saw that this particular trick worked.
So, you know, the relative nature of formulas-- this is not a table.
This is just a regular range.
That format B7 to B11, when I drag it to the right it automatically changes to point to C7 to C11 and then D7 to D11.
We just take this for granted but Dietmar doesn't have a regular range.
He changed this to a table.
Ctrl + T and so, you know, now we have the situation where they're not going to build regular formulas for me.
They're going to build that weird table nomenclature.
So =AVERAGE(and so we select that range =AVERAGE(Table2[ Jan ]) and now it's basically hard-coded in there that it wants January and I said oh well this is not going to work when he drags it to the right but I was very surprised that sure enough, yes, the January changes to February, February changes to March.
That works great, all right.
So even though they're essentially hard-coding the name of the month in the table nomenclature for a formula, it works as a relative formula that works fine but then Dietmar’s point is well what if we actually don't need months that are in order?
What if we need to call out specific months?
Then we can use INDIRECT.
So here I'm using one of the new Excel 2013 formula texts =FORMULATEXT(B2).
That way I can see that formula at the same time.
Of course, I can see the formula here =AVERAGE(Table1[ Jan ]) but I can't build a formula over in G2 while this cell is selected.
So FORMULATEXT() lets me continue to see the formula that's in B2 when I'm somewhere else.
So we're going to use =AVERAGE(INDIRECT( all right and then we're going to build something that looks like that word.
So =AVERAGE(INDIRECT(“Table1[ “&G1&” ]” and that's it.
That builds our INDIRECT.
We’ll close the INDIRECT, close the AVERAGE.
=AVERAGE(INDIRECT(“Table1[ “&G1&” ]”)).
Note there the color of the parentheses changed in Excel 2013.
I think that's a great improvement.
So let's see if this works.
We’ll select June here and our average is 2269.2.
Yes, that's great.
All right and then we'll copy across so this should be pulling December 2698.
2698, yes, beautiful.
So a great way using table nomenclature you get relative references automatically but when you need something a bit more absolute like we always want to pull June, August, or December cool trick they're using INDIRECT to build a table reference that points to a specific column.
Again, hey, my thanks to Dietmar.
We learned a lot.
Dietmar’s new book comes out next week.
I actually have gone out to amazon.de and just at amazon.de search for Excel Dietmar D-I-E-T-M-A-R.
There it is right there.
Microsoft Excel 2010.
I cannot tell you what this title means but I looked through it, I ordered it for myself because just from the screenshots in the book there are so many good ideas that I'm going to be able to use.
So available at amazon.de whether you speak English or Deutsche, great book coming out next week.
All right, hey if you have a chance to be in Lucerne, Switzerland let's do trainer days January 17th through 21st 2014.
Thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Hey, this is episode 1635.
A Ctrl + T table, is it relative or absolute?
As I mentioned I was over in Lucerne, Switzerland doing some sessions at their Trainertage trainer days and this was a lot of fun.
In fact, I just got to check out the video.
They put up their first vimeo.com/58590184 and I clearly was having a lot of fun here if-- you know, I said I spoke slow English.
If you want to see my slow English, I was in slow English mode when they shot that video.
Just a great time made some great friends.
I'm going to show a lot of the tricks that I learned at Trainertage here on the podcast over the next several weeks, you know, maybe a couple per week.
So this is from Dietmar Gieringer.
Dietmar-- actually everyone there is using Ctrl + T tables far more often than I ever used them and so just watching them do things-- Dietmar was talking about something else but I was kind of amazed when I saw that this particular trick worked.
So, you know, the relative nature of formulas-- this is not a table.
This is just a regular range.
That format B7 to B11, when I drag it to the right it automatically changes to point to C7 to C11 and then D7 to D11.
We just take this for granted but Dietmar doesn't have a regular range.
He changed this to a table.
Ctrl + T and so, you know, now we have the situation where they're not going to build regular formulas for me.
They're going to build that weird table nomenclature.
So =AVERAGE(and so we select that range =AVERAGE(Table2[ Jan ]) and now it's basically hard-coded in there that it wants January and I said oh well this is not going to work when he drags it to the right but I was very surprised that sure enough, yes, the January changes to February, February changes to March.
That works great, all right.
So even though they're essentially hard-coding the name of the month in the table nomenclature for a formula, it works as a relative formula that works fine but then Dietmar’s point is well what if we actually don't need months that are in order?
What if we need to call out specific months?
Then we can use INDIRECT.
So here I'm using one of the new Excel 2013 formula texts =FORMULATEXT(B2).
That way I can see that formula at the same time.
Of course, I can see the formula here =AVERAGE(Table1[ Jan ]) but I can't build a formula over in G2 while this cell is selected.
So FORMULATEXT() lets me continue to see the formula that's in B2 when I'm somewhere else.
So we're going to use =AVERAGE(INDIRECT( all right and then we're going to build something that looks like that word.
So =AVERAGE(INDIRECT(“Table1[ “&G1&” ]” and that's it.
That builds our INDIRECT.
We’ll close the INDIRECT, close the AVERAGE.
=AVERAGE(INDIRECT(“Table1[ “&G1&” ]”)).
Note there the color of the parentheses changed in Excel 2013.
I think that's a great improvement.
So let's see if this works.
We’ll select June here and our average is 2269.2.
Yes, that's great.
All right and then we'll copy across so this should be pulling December 2698.
2698, yes, beautiful.
So a great way using table nomenclature you get relative references automatically but when you need something a bit more absolute like we always want to pull June, August, or December cool trick they're using INDIRECT to build a table reference that points to a specific column.
Again, hey, my thanks to Dietmar.
We learned a lot.
Dietmar’s new book comes out next week.
I actually have gone out to amazon.de and just at amazon.de search for Excel Dietmar D-I-E-T-M-A-R.
There it is right there.
Microsoft Excel 2010.
I cannot tell you what this title means but I looked through it, I ordered it for myself because just from the screenshots in the book there are so many good ideas that I'm going to be able to use.
So available at amazon.de whether you speak English or Deutsche, great book coming out next week.
All right, hey if you have a chance to be in Lucerne, Switzerland let's do trainer days January 17th through 21st 2014.
Thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
