Jerry has a model with input cells and formula cells. He wants to copy the model for next month and clear out all the input cells. Episode 1052 will show you how to clear out the numeric input cells.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we're gonna analyze this. Let's fire up a pivot table.
Let's see, if you can solve this problem.
Alright, welcome back to the Mr.Excel netcast.
I'm Bill Jelen.
Great question today sent it by Jerry.
Jerry has a model that's been built.
There's a lot of formulas in the model and then some input cells.
Jerry wants to make a copy of that model for next month and wants to clear out the input cells, but not the formula cells.
Alright , so first of all, to copy the sheet.
You can right click on the tab choose move or copy.
But, a faster way to go is to hold down control and drag that sheet to a new spot that'll make a copy.
Of course you might wanna rename it to be next year or something like that.
Now, the hard part is clearing out all the numeric input cells.
But, keeping all of the formula cells.
So, here's how we're going to do that.
We're going to just choose a cell and spreadsheet to control A to select all cells, you could of course also do that by clicking this box above into the left of A1.
And then I'm going to use Control+G to display the Go To dialog box, and Alt+S to choose special.
Now, in here I want to say that I want to choose all of the Constants, but only the constants that are numbers.
So, I'm going unchecked Text, Logical and Errors and click OK.
Now, it's very subtle here in Excel 2007, to see which items are selected but in fact all of the input cells are now selected.
I have a couple of options, I can press the delete key to clear those or if I just want to zero them all out.
I'm going to do 0 and control+Enter.
Also, while that's selected it might be a good time to use a cell style.
Let's, define those input cells in excel 2007.
They have cell styles built in for input calculation and so on.
So, let's go ahead and choose input that way people will know where they have to enter the data.
Great little feature built into Excel 2007 there, but, not central to Jerry's question, Jerry's question is, how to clear out all the input cells?
Again, control+A to select everything, control+G for Go To, Alt+S for special and then choose formulas, uncheck the other items and you've gotten all of the numeric input cells selected.
Oh hey, I wanna thank you for stopping by.
See you next time for another netcast from MrExcel.
Thanks for stopping by.
See you next time for another netcast form MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we're gonna analyze this. Let's fire up a pivot table.
Let's see, if you can solve this problem.
Alright, welcome back to the Mr.Excel netcast.
I'm Bill Jelen.
Great question today sent it by Jerry.
Jerry has a model that's been built.
There's a lot of formulas in the model and then some input cells.
Jerry wants to make a copy of that model for next month and wants to clear out the input cells, but not the formula cells.
Alright , so first of all, to copy the sheet.
You can right click on the tab choose move or copy.
But, a faster way to go is to hold down control and drag that sheet to a new spot that'll make a copy.
Of course you might wanna rename it to be next year or something like that.
Now, the hard part is clearing out all the numeric input cells.
But, keeping all of the formula cells.
So, here's how we're going to do that.
We're going to just choose a cell and spreadsheet to control A to select all cells, you could of course also do that by clicking this box above into the left of A1.
And then I'm going to use Control+G to display the Go To dialog box, and Alt+S to choose special.
Now, in here I want to say that I want to choose all of the Constants, but only the constants that are numbers.
So, I'm going unchecked Text, Logical and Errors and click OK.
Now, it's very subtle here in Excel 2007, to see which items are selected but in fact all of the input cells are now selected.
I have a couple of options, I can press the delete key to clear those or if I just want to zero them all out.
I'm going to do 0 and control+Enter.
Also, while that's selected it might be a good time to use a cell style.
Let's, define those input cells in excel 2007.
They have cell styles built in for input calculation and so on.
So, let's go ahead and choose input that way people will know where they have to enter the data.
Great little feature built into Excel 2007 there, but, not central to Jerry's question, Jerry's question is, how to clear out all the input cells?
Again, control+A to select everything, control+G for Go To, Alt+S for special and then choose formulas, uncheck the other items and you've gotten all of the numeric input cells selected.
Oh hey, I wanna thank you for stopping by.
See you next time for another netcast from MrExcel.
Thanks for stopping by.
See you next time for another netcast form MrExcel.
