Mally wants a simple Spreadsheet - Two (2) Columns; she asks how to enter a Value in Column A and have the Running Total over in Column B automatically fill down. Episode #1502 shows you how to use the new Table functionality to solve this. Also, in reference to the Learn Excel from MrExcel Podcast Episode #1482, Bill readdresses a few more Table Topics, such as: 'Control-T' to define your Data Set as a Table or going to 'File - Options - Formulas- 'Working With Formulas' area and deselecting "Use Table Names in Formulas"
...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, Podcast Episode 1497: Running Total AutoFill.
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen. Today’s question is sent in by Mally.
Mally just wants a really simple spreadsheet, 2 columns.
She wants to enter some value from today and then have a running total over there in the right-hand side.
So, she enters a value and she wants this formula to auto-update, enters a value once this formula to auto-update, alright, and she says, is there some way I can build this?
You know, Excel has this formula autocomplete once you get 3 items in a row but it doesn't work for a data set that's this small.
Very frustrating, but, hey, if you have Excel 2007 or Excel 2010, here's what you're going to do.
Go back up into that spreadsheet.
Do CONTROL+T. That defines it as a table.
Now, there's lots of good things about tables, and, as I mentioned a few weeks ago, in table [ unintelligible – 00:55 ], some things I don't like about tables, but it just so happens that this is one of the good things about tables.
So, here, I have a constant, here I have a formula.
We've defined a table.
Now, if you don't like those colors, choose a different set of colors, turn off the banded rows, whatever you need to do, but here's the magic.
When we go to the cell just below the table and we enter a new value for today -- 555 -- automatically, that formula over in B5 is going to update.
Isn't that a gorgeous thing?
Oh that's beautiful.
Beautiful.
So, Mally, provided you have Excel 2007 or 2010, those simple steps will make it very, very easy for you to create your running total over there in column B.
Also, Derek, Derek from the UK, in response to my table [ unintelligible – 01:42 ] rant, said, you know, you can turn off table formulas.
So, go into Excel options, So, FILE, OPTIONS, and then, under FORMULAS, USE TABLE NAMES IN FORMULAS.
So, I complain about tables because of the weird formulas we get, but you can actually turn that off.
So, if that is bugging you like it's bugging me, feel free to go through turn that off.
Thanks to Derek for that answer.
Thanks to Mally for sending her question in.
I want to thank everyone for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel From MrExcel, Podcast Episode 1497: Running Total AutoFill.
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen. Today’s question is sent in by Mally.
Mally just wants a really simple spreadsheet, 2 columns.
She wants to enter some value from today and then have a running total over there in the right-hand side.
So, she enters a value and she wants this formula to auto-update, enters a value once this formula to auto-update, alright, and she says, is there some way I can build this?
You know, Excel has this formula autocomplete once you get 3 items in a row but it doesn't work for a data set that's this small.
Very frustrating, but, hey, if you have Excel 2007 or Excel 2010, here's what you're going to do.
Go back up into that spreadsheet.
Do CONTROL+T. That defines it as a table.
Now, there's lots of good things about tables, and, as I mentioned a few weeks ago, in table [ unintelligible – 00:55 ], some things I don't like about tables, but it just so happens that this is one of the good things about tables.
So, here, I have a constant, here I have a formula.
We've defined a table.
Now, if you don't like those colors, choose a different set of colors, turn off the banded rows, whatever you need to do, but here's the magic.
When we go to the cell just below the table and we enter a new value for today -- 555 -- automatically, that formula over in B5 is going to update.
Isn't that a gorgeous thing?
Oh that's beautiful.
Beautiful.
So, Mally, provided you have Excel 2007 or 2010, those simple steps will make it very, very easy for you to create your running total over there in column B.
Also, Derek, Derek from the UK, in response to my table [ unintelligible – 01:42 ] rant, said, you know, you can turn off table formulas.
So, go into Excel options, So, FILE, OPTIONS, and then, under FORMULAS, USE TABLE NAMES IN FORMULAS.
So, I complain about tables because of the weird formulas we get, but you can actually turn that off.
So, if that is bugging you like it's bugging me, feel free to go through turn that off.
Thanks to Derek for that answer.
Thanks to Mally for sending her question in.
I want to thank everyone for stopping by.
We’ll see you next time for another netcast from MrExcel.
