On Friday May 10, 2013, Chandoo (Welcome to Chandoo.org - Learn Excel, Charting Online) and Rob Collie (P3 - Leading Consultants on Power BI & the MS Data Platform) - PowerPivotPro - stopped by MrExcel's place for an evening of Microsoft Excel. So, what is on the agenda For Today's Podcast? Chandoo doing his First Guest Appearance on the Learn Excel Podcast and his topic: "The Benefits of Structured Table References". Follow along with Episode #1708 as Chandoo gives us an indepth analysis of Table References and why using Structured Table References are so beneficial.
"Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. (Excel 2013 In Depth)
And
"Pivot Table Data Crunching Excel 2013", by Bill Jelen. Use Excel 2013 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours... understand exactly what's going on in your business... take control, and stay in control! Even if you've never created a pivot table before, this book will help you leverage all their amazing flexibility and analytical power. (Pivot Table Data Crunching Excel 2013)
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
"Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. (Excel 2013 In Depth)
And
"Pivot Table Data Crunching Excel 2013", by Bill Jelen. Use Excel 2013 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours... understand exactly what's going on in your business... take control, and stay in control! Even if you've never created a pivot table before, this book will help you leverage all their amazing flexibility and analytical power. (Pivot Table Data Crunching Excel 2013)
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1708 - Chandoo Structured References!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
We're in Akron, OH, and guess who is here from halfway around the world.
That's right, it's Chandoo here to do a guest appearance on the MrExcel podcast, let me turn it over to Chandoo.
Chandoo: Hey Bill, so glad to be here, and I'm totally excited to be talking to your community, and sharing one of the most time-saving tips that I came across.
OK, so this is called creating tables.
Let's say you are looking at some data like this, and this is some data that's been thrown in, so it has some regions, products, date, its customer quantity, revenue, COGS, and profit, and we have a few hundred rows of that.
Now one of the common things that happens in business world is, your data is never static, every month, every week, every day, you are adding new data.
Right, so that's one of the things.
The second thing, is any formulas or charts, or any of those things that you build on top of this, they need to keep this in mind that the data is going to grow, sometimes it might even change.
So one of the things that people you often do is, when they want to sum up the total quantity sold, they would write something like SUM of, and then let's say, it starts on E2 and goes all the way up to E564.
Right, so they would put something like E2-E564, or if I want to make sure that the new data is considered, sometimes people might even say E1000 or E10000 or E100000.
But this kind of approach is not good, because you might have something else at the bottom, like in this case we have some text.
Of course it's not in that column, but it is still there.
And the second thing is, it might just unnecessarily keep Excel busy, and this is where tables come in really handy.
So you just go into any cell inside the data and press Ctrl+T, T for table, and make sure the first row has the headers, and you check that, and then click OK.
So instantly Excel would format the whole thing as a table, and then you have this set up as a table.
Now take a minute and look at this, a table is not just a formatting thing, but even from a formatting perspective, it is really pretty looking.
Right, every other row is shaded in a different color, filters are applied on top automatically, and you can easily scan this when you are looking for information.
When you select a table, a new ribbon will appear here, this is called Design ribbon, and a table is given a name, by default table 1, 2, or 29, like that.
Let's just select this and say mySales.
Right, and then, keep in mind that the table name has to be changed from the design tab.
Now you want to sum up the total quantity, you would just write a simple formula that says SUM(mySales[ Quantity ]) and that's it!
Not only this formula would work for all the quantity values in your sales table, this formula is so easy to write.
You don't have to look at E2-E564, or any of those kind of references, you just refer to the data by its structure.
So this is what Excel is calling a structural references, as against relative, or absolute references.
So you can use this, and the moment you add any data at the end of the table, just remember that number, it's 313900, and let's go and add up a new quantity here that is for 1000, OK.
And when you press Enter, the table formatting gets extended, and your formula gets recalculated, so you would get the new number there.
So this is how easy it is to set up table formatting, and then calculate.
Not just formulas, even if you set up a chart on top of table, any additions to the table gets reflected in the chart automatically.
Right, not just charts and formulas, if you create a Pivot table from the table, then the Pivot table is connected to table.
So I'll just show you very quickly: select something and you insert a PivotTable.
You would see that the selection range for that actually refers to here as mySales.
It's not saying A1-K240 or something like that, it says "I'm going to create a Pivot from mySales table." The advantage of that is, when your data changes, all you have to do is refresh your Pivot table, you don't have to modify a source data connection, or any of that.
Apart from these, there are many, many advantages of Table, and I'm sure Bill has covered several of these in his podcasts.
But if you need to learn a little more, you could check mrexcel.com or you can even go to chandoo.org and look up some information.
So I hope you like it, and I hope to catch up with you once again sometime soon.
Bye-bye!
Bill: Alright hey, that's cool the, structured table.
I remember when, you know, tables were virtually not just Ctrl+T, but back in Excel 2003 they were Ctrl+L, and I said "This is silly, all it is, is formatting." But it's not the formatting, it's the fact that as the table grows, the formulas grow, the charts grow, the Pivot tables grow.
And even if you already have a Pivot table that's not based on the table, when you go Ctrl+T that range, and then add more data, the pivot table will automatically grow, it's retroactive.
So, a great tip.
I want to thank Chandoo for stopping by, and I want to thank everyone else for checking us out, we'll see you tomorrow for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1708 - Chandoo Structured References!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
We're in Akron, OH, and guess who is here from halfway around the world.
That's right, it's Chandoo here to do a guest appearance on the MrExcel podcast, let me turn it over to Chandoo.
Chandoo: Hey Bill, so glad to be here, and I'm totally excited to be talking to your community, and sharing one of the most time-saving tips that I came across.
OK, so this is called creating tables.
Let's say you are looking at some data like this, and this is some data that's been thrown in, so it has some regions, products, date, its customer quantity, revenue, COGS, and profit, and we have a few hundred rows of that.
Now one of the common things that happens in business world is, your data is never static, every month, every week, every day, you are adding new data.
Right, so that's one of the things.
The second thing, is any formulas or charts, or any of those things that you build on top of this, they need to keep this in mind that the data is going to grow, sometimes it might even change.
So one of the things that people you often do is, when they want to sum up the total quantity sold, they would write something like SUM of, and then let's say, it starts on E2 and goes all the way up to E564.
Right, so they would put something like E2-E564, or if I want to make sure that the new data is considered, sometimes people might even say E1000 or E10000 or E100000.
But this kind of approach is not good, because you might have something else at the bottom, like in this case we have some text.
Of course it's not in that column, but it is still there.
And the second thing is, it might just unnecessarily keep Excel busy, and this is where tables come in really handy.
So you just go into any cell inside the data and press Ctrl+T, T for table, and make sure the first row has the headers, and you check that, and then click OK.
So instantly Excel would format the whole thing as a table, and then you have this set up as a table.
Now take a minute and look at this, a table is not just a formatting thing, but even from a formatting perspective, it is really pretty looking.
Right, every other row is shaded in a different color, filters are applied on top automatically, and you can easily scan this when you are looking for information.
When you select a table, a new ribbon will appear here, this is called Design ribbon, and a table is given a name, by default table 1, 2, or 29, like that.
Let's just select this and say mySales.
Right, and then, keep in mind that the table name has to be changed from the design tab.
Now you want to sum up the total quantity, you would just write a simple formula that says SUM(mySales[ Quantity ]) and that's it!
Not only this formula would work for all the quantity values in your sales table, this formula is so easy to write.
You don't have to look at E2-E564, or any of those kind of references, you just refer to the data by its structure.
So this is what Excel is calling a structural references, as against relative, or absolute references.
So you can use this, and the moment you add any data at the end of the table, just remember that number, it's 313900, and let's go and add up a new quantity here that is for 1000, OK.
And when you press Enter, the table formatting gets extended, and your formula gets recalculated, so you would get the new number there.
So this is how easy it is to set up table formatting, and then calculate.
Not just formulas, even if you set up a chart on top of table, any additions to the table gets reflected in the chart automatically.
Right, not just charts and formulas, if you create a Pivot table from the table, then the Pivot table is connected to table.
So I'll just show you very quickly: select something and you insert a PivotTable.
You would see that the selection range for that actually refers to here as mySales.
It's not saying A1-K240 or something like that, it says "I'm going to create a Pivot from mySales table." The advantage of that is, when your data changes, all you have to do is refresh your Pivot table, you don't have to modify a source data connection, or any of that.
Apart from these, there are many, many advantages of Table, and I'm sure Bill has covered several of these in his podcasts.
But if you need to learn a little more, you could check mrexcel.com or you can even go to chandoo.org and look up some information.
So I hope you like it, and I hope to catch up with you once again sometime soon.
Bye-bye!
Bill: Alright hey, that's cool the, structured table.
I remember when, you know, tables were virtually not just Ctrl+T, but back in Excel 2003 they were Ctrl+L, and I said "This is silly, all it is, is formatting." But it's not the formatting, it's the fact that as the table grows, the formulas grow, the charts grow, the Pivot tables grow.
And even if you already have a Pivot table that's not based on the table, when you go Ctrl+T that range, and then add more data, the pivot table will automatically grow, it's retroactive.
So, a great tip.
I want to thank Chandoo for stopping by, and I want to thank everyone else for checking us out, we'll see you tomorrow for another netcast from MrExcel!
