Learn Excel 2010 - "Table Schmable, I Want Results!": Podcast #1482

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Dec 1, 2011.
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!
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top