What is 'Alt+DEF' good for? Today, in Episode #1450, Christopher and Sheila both wrote in with different problems that required the same solution. Both had formulas that were not working. Both happened to have numbers or dates stored as text. The beloved Alt+DEF shortcut came to the rescue both times - And Bill shows us how it works!
...This blog 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 blog 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 1450 – Formulas Not Working Hey, welcome back to the MrExcel netcast.
I’m Bill Jelen. Well, I got two questions in the last week. The answer to both questions was exactly the same.
Let's take a look. First question is from Christopher. Christopher has a column of numbers and he puts an =SUM function in here. And the sum function... He was actually using =SUBTOTAL. It doesn't add up.
Another one. This was Sheila. Sheila had a really cool date formula – cooler than mine.
And the date formula wasn't working. She was doing some combinations of ANDs and ORs and she said, “Hey, this just isn't working.
Can you take a look?” And in both cases, the situation or was that the dates and numbers were not stored as dates and numbers. They were stored as text. Now for dates, hold down Ctrl and press... Well, on the US keyboard, the tilde key. We're actually looking for the grave accent, the backwards apostrophe for French. And you'll see that when that doesn't change to a zero number, a real date would change to a zero number like that one. When that doesn't change the zero number, you know that that is not stored as data. It is stored as text.
Now for the numbers, you really can't tell in that method. I'll press Ctrl grave accent again to go back. All right. So my trick here is to choose this whole column of numbers, not the formula, and do Alt+D E F, and the number source works. Same thing here. And choose your column dates, Alt+D E F. Wrong format. And the thing will start to work.
What is Alt+D E F? Well, that's something I learned right here in the podcast a long, long time ago. I don’t remember who it was.
He sent in, that was a faster way to convert a column of text and restored his numbers to numbers. I use it all the time. Any time that my VLOOKUPS aren't working, I suspect that there's some number stored as text. Rather than go through and use the little green arrow checking triangles, a fast, fast way to go.
So thanks to Christopher and Sheila for sending those in. Thanks to you for stopping by. We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel, Episode 1450 – Formulas Not Working Hey, welcome back to the MrExcel netcast.
I’m Bill Jelen. Well, I got two questions in the last week. The answer to both questions was exactly the same.
Let's take a look. First question is from Christopher. Christopher has a column of numbers and he puts an =SUM function in here. And the sum function... He was actually using =SUBTOTAL. It doesn't add up.
Another one. This was Sheila. Sheila had a really cool date formula – cooler than mine.
And the date formula wasn't working. She was doing some combinations of ANDs and ORs and she said, “Hey, this just isn't working.
Can you take a look?” And in both cases, the situation or was that the dates and numbers were not stored as dates and numbers. They were stored as text. Now for dates, hold down Ctrl and press... Well, on the US keyboard, the tilde key. We're actually looking for the grave accent, the backwards apostrophe for French. And you'll see that when that doesn't change to a zero number, a real date would change to a zero number like that one. When that doesn't change the zero number, you know that that is not stored as data. It is stored as text.
Now for the numbers, you really can't tell in that method. I'll press Ctrl grave accent again to go back. All right. So my trick here is to choose this whole column of numbers, not the formula, and do Alt+D E F, and the number source works. Same thing here. And choose your column dates, Alt+D E F. Wrong format. And the thing will start to work.
What is Alt+D E F? Well, that's something I learned right here in the podcast a long, long time ago. I don’t remember who it was.
He sent in, that was a faster way to convert a column of text and restored his numbers to numbers. I use it all the time. Any time that my VLOOKUPS aren't working, I suspect that there's some number stored as text. Rather than go through and use the little green arrow checking triangles, a fast, fast way to go.
So thanks to Christopher and Sheila for sending those in. Thanks to you for stopping by. We'll see you next time for another netcast from MrExcel.
