Michael wonders what happened to the old Excel 2003 Natural Language Formulas feature. It is out of Excel 2007, but replaced with something similar. Episode 1117 shows you now.
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, well there's the theme we haven't heard in a while, the old Where Is It Wednesday theme!
Today’s question sent in by Michael, Michael, is asking about a feature, an obscure feature that was in Excel 2003.
If we go into Tools, Options, on the Calculation tab, Accept labels in formulas!
Alright, now I don't know many people that use this, but when we chose that, we're able to build formulas by using the column headings, =Profit/Revenue.
Amazingly, that would work, and you would copy it down.
And what it would do is look at the data to the left, figure out where those headings were, and figure out that we were dividing 8100/14004.
It's called Natural Language Formula, and he said “Well where is that in Excel 2007?” Bad news, it's gone, they took it out!
Now they replaced it with something similar, not quite as cool.
Maybe what we have to do is take this data in Excel 2007, and by the way you have to save the file as a 2007 file or it's not going to work.
And then Format is Table, so choose one of these table formats, click OK alright.
Now that we have that, you have your new heading, GP%, and that column now becomes part of the table.
We can now type =[ and choose Profit ]/[ and choose Revenue ], I'll press Enter, it puts the right formula and copies it all the way down.
So a bit more of a hassle, so you have to use the square brackets.
Of course you don't have to turn on the option for Accept labels and formulas, provided you format as a table, it's just going to naturally work.
So kind of, they're kind of not, they're kind of the same, kind of not the same.
If you're working between Excel 2003 and 2007, just going to be a disaster, just yeah, give it up, wait ‘till everyone has Excel 2007, and switch over to this.
There you go, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Today’s question sent in by Michael, Michael, is asking about a feature, an obscure feature that was in Excel 2003.
If we go into Tools, Options, on the Calculation tab, Accept labels in formulas!
Alright, now I don't know many people that use this, but when we chose that, we're able to build formulas by using the column headings, =Profit/Revenue.
Amazingly, that would work, and you would copy it down.
And what it would do is look at the data to the left, figure out where those headings were, and figure out that we were dividing 8100/14004.
It's called Natural Language Formula, and he said “Well where is that in Excel 2007?” Bad news, it's gone, they took it out!
Now they replaced it with something similar, not quite as cool.
Maybe what we have to do is take this data in Excel 2007, and by the way you have to save the file as a 2007 file or it's not going to work.
And then Format is Table, so choose one of these table formats, click OK alright.
Now that we have that, you have your new heading, GP%, and that column now becomes part of the table.
We can now type =[ and choose Profit ]/[ and choose Revenue ], I'll press Enter, it puts the right formula and copies it all the way down.
So a bit more of a hassle, so you have to use the square brackets.
Of course you don't have to turn on the option for Accept labels and formulas, provided you format as a table, it's just going to naturally work.
So kind of, they're kind of not, they're kind of the same, kind of not the same.
If you're working between Excel 2003 and 2007, just going to be a disaster, just yeah, give it up, wait ‘till everyone has Excel 2007, and switch over to this.
There you go, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
