Today Yari is frustrated that his Formulas pointing into a Table (with a capital T) will not copy across the Columns. Yari wants totals and he isn't getting them. In Episode #1482, Bill explains why Yari isn't getting his totals and then shows us two methods that will display totals for us.
...This episode 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!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This episode 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!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Episode 1482 – Table Schmable.
All right, today's question is sent in by Ari.
Ari has done some statistical analysis, and it came back from the tool in a table.
You know, one of those new things that Excel 2007 introduced for us – nice formatting, does the filter dropdowns for you, but the other annoying thing with tables is, when you build a formula that points to a table, you get this table nomenclature, and you can't copy that.
So if we want to copy that across the columns, we're not going to get revenue, cost of goods sold, and profit.
We're going to get nothing on here.
This is useless.
I need to be able to copy this formula across, all right.
Well hey, what you have to do is, you have to not let Excel build the reference for you.
You have to actually come here and say, =SUM(E7:E569.
And that will get you a formula that you can copy, all right.
So it's a little bit of a hassle.
You have to go back old school and build it, and without using the mouse or the arrow keys, but a good way to go.
The other thing you could do before you enter those formulas, you know, what if we just make it not be a table anymore?
So come out here and disable Table Design, and then Convert to Range.
We want to convert it to a normal range?
Yes, we do.
The formatting stays and now when we build a formula, =SUM(E3:E569.
We get, of course, a reference that will copy across.
So I'm not sure exactly why it comes back as a table.
If it does not need to be a table, let's just convert it back to a range, and then the formulas will work straight out of the box.
Hey, I want to thank for stopping by.
I’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel, Episode 1482 – Table Schmable.
All right, today's question is sent in by Ari.
Ari has done some statistical analysis, and it came back from the tool in a table.
You know, one of those new things that Excel 2007 introduced for us – nice formatting, does the filter dropdowns for you, but the other annoying thing with tables is, when you build a formula that points to a table, you get this table nomenclature, and you can't copy that.
So if we want to copy that across the columns, we're not going to get revenue, cost of goods sold, and profit.
We're going to get nothing on here.
This is useless.
I need to be able to copy this formula across, all right.
Well hey, what you have to do is, you have to not let Excel build the reference for you.
You have to actually come here and say, =SUM(E7:E569.
And that will get you a formula that you can copy, all right.
So it's a little bit of a hassle.
You have to go back old school and build it, and without using the mouse or the arrow keys, but a good way to go.
The other thing you could do before you enter those formulas, you know, what if we just make it not be a table anymore?
So come out here and disable Table Design, and then Convert to Range.
We want to convert it to a normal range?
Yes, we do.
The formatting stays and now when we build a formula, =SUM(E3:E569.
We get, of course, a reference that will copy across.
So I'm not sure exactly why it comes back as a table.
If it does not need to be a table, let's just convert it back to a range, and then the formulas will work straight out of the box.
Hey, I want to thank for stopping by.
I’ll see you next time for another netcast from MrExcel.
