Don't Fear The Spreadsheet: The Excel Series for Beginning Excel Users with Questions.
Bob asks: "My Database contains different currency types; I set up a conversion filter to show British Pounds...but the Formula is AutoFilling all the way down. Why and what can I do to stop this because I am getting the wrong conversion figures?"
Following along with Bill in Episode #1658 to learn why this is happening and what to do to correct the issue.
If you are just starting out in Excel or you want to go back and find the things that you missed when you started using Excel, then "Don't Fear the Spreadsheet" is the 'Beginner Oriented' Excel How-To book for you. Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen. Check out "Don't Fear the Spreadsheet". Don't Fear the Spreadsheet
Bob asks: "My Database contains different currency types; I set up a conversion filter to show British Pounds...but the Formula is AutoFilling all the way down. Why and what can I do to stop this because I am getting the wrong conversion figures?"
Following along with Bill in Episode #1658 to learn why this is happening and what to do to correct the issue.
If you are just starting out in Excel or you want to go back and find the things that you missed when you started using Excel, then "Don't Fear the Spreadsheet" is the 'Beginner Oriented' Excel How-To book for you. Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen. Check out "Don't Fear the Spreadsheet". Don't Fear the Spreadsheet
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast.
Episode 1658 Why is Excel AutoFilling my Formulas?
Today's question send it by Bob.
Bob says. Hey, I have a database today.
It has information in USD, GBP, EUR, and CHF.
And he says, I created a new column to convert everything to pounds.
And he said, here's what I did. I went to the drop down here and chose just US dollars.
And I entered in my formula for =.664* the amount.
He says, it's flipping me out, this automatically fill down, and what's even worse, is it didn't just fill down to the cells I could see, it fill down to all of the cells.
So, my grand plan of being able to filter to just US dollars, put in a formula and then another filter to CHF and a formula and another filter to EUR, and put in a formula, is not working at all.
So, I'm going to undo, undo and undo.
All right so, you know, the first thing we want to do, is immediately, after it does that fill, well first, first, Bob.
Here's what's happening. Someone had went through and made this into an official table, using Home, Format As Table or Ctrl+T, and that's why you're getting that behavior.
All right so, that's why it's happening and it doesn't happen most of the rest of the time.
*.664 As soon as, you press Enter, it fills down.
Ah, right then, you have to look for this little drop down here.
It's called AutoCorrect Options.
Open that drop down and say, Stop Automatically Creating Calculated Columns.
And now, you will be able to copy this down to the visible cells.
Even, I don't know if that's going to work.
Let's see. Did it work? Did it work? Did it work?
It'll go to CHF instead of US dollars.
Yeah, beautiful! Okay then, CHF is .705*amount.
Copy that down.
And then switch over to Euros, where the formula is, .864* Or I'll start with an equal sign, all right.
times the amount.
Copy that down, and okay, using that approach, you will be able to, come up with all of your formulas, that you're looking for.
Well, we should've done GBP which is just a, copy it over. Right. Hang on.
All right, and after a few different filters, you now have your column that has GBP.
And everything is good, all right.
So, this was the question that Bob asked.
Why is this happening? Why is the formula AutoComplete happening?
And how do I turn it off?
The other method to have turned it off, is once you're inside the formula, once you're inside the table.
Come over here to Table tools Design, and there's something called Convert to Range and that will turn that feature off permanently, for this data set.
Although, Bob said that someone else set this up.
So, when this goes back to the person who set it up, they're gonna be hacked off, that we converted it back to a range.
But maybe, he's never sending it back, and then, you know, screw those people.
We're going to just Convert to Range.
Yes.
All right, and now we can do things either way.
All right.
So, you know we started out the podcast.
we use DON'T FEAR THE SPREADSHEET That's your sign, that this is a podcast episode, that is designed for beginners.
I'm sure, a lot of you, who are regulars to the podcast, are out there screaming, that there is a much much better way to go.
And I agree with you, there is a much better way to go.
But I didn't want to overwhelm Bob today.
We will overwhelm Bob tomorrow.
So stop back for tomorrow's episode 1659.
and I'll show you the great way, that we can leave that formula AutoComplete on.
Instead, we'll write one formula, that solves our problem of every single currency.
Well hey, I want to thank you for stopping by.
Will see you next time, for another netcast from MrExcel.
Learn Excel from MrExcel podcast.
Episode 1658 Why is Excel AutoFilling my Formulas?
Today's question send it by Bob.
Bob says. Hey, I have a database today.
It has information in USD, GBP, EUR, and CHF.
And he says, I created a new column to convert everything to pounds.
And he said, here's what I did. I went to the drop down here and chose just US dollars.
And I entered in my formula for =.664* the amount.
He says, it's flipping me out, this automatically fill down, and what's even worse, is it didn't just fill down to the cells I could see, it fill down to all of the cells.
So, my grand plan of being able to filter to just US dollars, put in a formula and then another filter to CHF and a formula and another filter to EUR, and put in a formula, is not working at all.
So, I'm going to undo, undo and undo.
All right so, you know, the first thing we want to do, is immediately, after it does that fill, well first, first, Bob.
Here's what's happening. Someone had went through and made this into an official table, using Home, Format As Table or Ctrl+T, and that's why you're getting that behavior.
All right so, that's why it's happening and it doesn't happen most of the rest of the time.
*.664 As soon as, you press Enter, it fills down.
Ah, right then, you have to look for this little drop down here.
It's called AutoCorrect Options.
Open that drop down and say, Stop Automatically Creating Calculated Columns.
And now, you will be able to copy this down to the visible cells.
Even, I don't know if that's going to work.
Let's see. Did it work? Did it work? Did it work?
It'll go to CHF instead of US dollars.
Yeah, beautiful! Okay then, CHF is .705*amount.
Copy that down.
And then switch over to Euros, where the formula is, .864* Or I'll start with an equal sign, all right.
times the amount.
Copy that down, and okay, using that approach, you will be able to, come up with all of your formulas, that you're looking for.
Well, we should've done GBP which is just a, copy it over. Right. Hang on.
All right, and after a few different filters, you now have your column that has GBP.
And everything is good, all right.
So, this was the question that Bob asked.
Why is this happening? Why is the formula AutoComplete happening?
And how do I turn it off?
The other method to have turned it off, is once you're inside the formula, once you're inside the table.
Come over here to Table tools Design, and there's something called Convert to Range and that will turn that feature off permanently, for this data set.
Although, Bob said that someone else set this up.
So, when this goes back to the person who set it up, they're gonna be hacked off, that we converted it back to a range.
But maybe, he's never sending it back, and then, you know, screw those people.
We're going to just Convert to Range.
Yes.
All right, and now we can do things either way.
All right.
So, you know we started out the podcast.
we use DON'T FEAR THE SPREADSHEET That's your sign, that this is a podcast episode, that is designed for beginners.
I'm sure, a lot of you, who are regulars to the podcast, are out there screaming, that there is a much much better way to go.
And I agree with you, there is a much better way to go.
But I didn't want to overwhelm Bob today.
We will overwhelm Bob tomorrow.
So stop back for tomorrow's episode 1659.
and I'll show you the great way, that we can leave that formula AutoComplete on.
Instead, we'll write one formula, that solves our problem of every single currency.
Well hey, I want to thank you for stopping by.
Will see you next time, for another netcast from MrExcel.
