Well there’s a theme we haven’t heard in a while the old where is it Wednesday theme test question sent 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 and formulas. Now I don’t know many people that use this but when we chose that, we’re going to be able to build form those by using the column headings equal profit divided by revenue.
Amazingly that would work and you would copy it down and what we do is look at the data to the left. Figure out where those headings were and figure out that we were dividing 8100 by 1404—called natural language formula in form. As he said, where is that Excel in 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 as table so choose one of these tables formats, click okay.
Alright now that we have that, now you have your new heading GP percent and that column now becomes part of the table. We can now type equal, open left square bracket, and choose profit. Right square bracket, divided by a left square bracket and choose revenue. Click the right square bracket, I’ll press enter, it puts the right formula in, copies it all the way down. So a bit more of a hassle, you have to use the square brackets, of course you only have to turn on the option for—except labels and formulas provided you format as a table, it’s just going to naturally work.
So it’s kind of there, kind of not there, kind of the same, kind of not the same. If you’re working between Excel 2003 and 2007, it’s just going to be a disaster. Give it up, wait until everyone has Excel 2007 and switch over to this. There you go, thanks for stopping by we’ll see you next time for another net cast from Mr. Excel.
Well thanks for stopping by, we’ll see you next time for another net cast on Mr. Excel.
Transcription by:
Scribe4you Transcription Services