Whenever you want the format code for a given number format, format a cell in the usual fashion, and then press Ctrl-1 to display the Format Cells dialog box. On the Number tab choose Custom and copy and paste the format code where you need it.
You nailed my intent exactly, thank you. Another reader at the US-based AccountingWEB.com site had the same thought as the first commenter, why use a circular reference at all? My point was to explain the concept of circular references to the uninitiated by way of a simple example. I appreciate the assist!
When I teach Excel classes I tell users Ctrl-~ as that's a more identifiable character. It's tough to remember every keyboard shortcut in Excel, so the alternative is to toggle Show Formulas on the Formulas tab of Excel 2007 and later. There's also the new FORMULATEXT worksheet function in Excel 2013 as well. But the formal name for the small apostrophe is the grave symbol. Now that's some trivia! :-)
The scrollbar splitter was removed in Excel 2013. One must click the Split command on the View menu instead, and then double-click on whichever split bar isn't wanted to remove it.
F6 does move between windows, but I prefer Ctrl-Tab. However, you can't use Ctrl-Tab in Word, and must use F6, so at least F6 gives you a consistent keyboard shortcut across the Microsoft applications.
I am happy to report that Microsoft will have to eat at least one hat, as there wasn't anything new to me on the list.
The upside/downside to Excel is that there are numerous ways to accomplish the same task. INT(NOW()) certainly accomplishes the task, but TODAY() gets it done with one function, and will be easier for most users to remember. In any case, thanks for enriching my article with a great alternative.
The bit of nuance is that you must anchor the first range in COUNTIF by way of putting a $ in front the row number, i.e. =COUNTIF($B$2:B2,B2). The $ in front of B is extraneous in this context because we're not copying the formula sideways, so =COUNTIF(B$2:B2,B2) would work just as well. By anchoring the formula, COUNTIF looks at an ever expanding range, and that's what makes it work as a numbering scheme.
Thank you for reading my article, I hope you found it helpful!
Great point on ROW(). I see users often learn a few functions in Excel and try to apply them to pretty much every task. I sometimes fall into that trap myself. I often use ROW for other purposes, but in this instance it is more effective than COUNTIF when working with large data sets. Thanks for reading, and for enriching my article!
My answers
Format code for pounds is:
"[$£-en-GB]#,##0"
which you might expand to
"[$£-en-GB]#,##0;([$£-en-GB]#,##0)"
Whenever you want the format code for a given number format, format a cell in the usual fashion, and then press Ctrl-1 to display the Format Cells dialog box. On the Number tab choose Custom and copy and paste the format code where you need it.
rjmannaca,
You nailed my intent exactly, thank you. Another reader at the US-based AccountingWEB.com site had the same thought as the first commenter, why use a circular reference at all? My point was to explain the concept of circular references to the uninitiated by way of a simple example. I appreciate the assist!
David
The small apostrophe is the grave symbol
When I teach Excel classes I tell users Ctrl-~ as that's a more identifiable character. It's tough to remember every keyboard shortcut in Excel, so the alternative is to toggle Show Formulas on the Formulas tab of Excel 2007 and later. There's also the new FORMULATEXT worksheet function in Excel 2013 as well. But the formal name for the small apostrophe is the grave symbol. Now that's some trivia! :-)
Excel 2013 doesn't have Scrollbar splitter
Great article, John...just a couple of thoughts:
The scrollbar splitter was removed in Excel 2013. One must click the Split command on the View menu instead, and then double-click on whichever split bar isn't wanted to remove it.
F6 does move between windows, but I prefer Ctrl-Tab. However, you can't use Ctrl-Tab in Word, and must use F6, so at least F6 gives you a consistent keyboard shortcut across the Microsoft applications.
I am happy to report that Microsoft will have to eat at least one hat, as there wasn't anything new to me on the list.
Duncan,
Agreed. However, most users don't/won't do this, hence my ability to make a living writing about and presenting lectures on Excel.
David
Brian,
The upside/downside to Excel is that there are numerous ways to accomplish the same task. INT(NOW()) certainly accomplishes the task, but TODAY() gets it done with one function, and will be easier for most users to remember. In any case, thanks for enriching my article with a great alternative.
David
Robjoy,
Insightful comment. I must admit I'm presently in between stages 1 and 2 after a few months of use.
David
AMHCPA,
The bit of nuance is that you must anchor the first range in COUNTIF by way of putting a $ in front the row number, i.e. =COUNTIF($B$2:B2,B2). The $ in front of B is extraneous in this context because we're not copying the formula sideways, so =COUNTIF(B$2:B2,B2) would work just as well. By anchoring the formula, COUNTIF looks at an ever expanding range, and that's what makes it work as a numbering scheme.
Thank you for reading my article, I hope you found it helpful!
David
Clever!
Duncan,
Great point on ROW(). I see users often learn a few functions in Excel and try to apply them to pretty much every task. I sometimes fall into that trap myself. I often use ROW for other purposes, but in this instance it is more effective than COUNTIF when working with large data sets. Thanks for reading, and for enriching my article!
David
Good thought on RANKING
Dick,
RANKING is a great alternative, and I appreciate the enrichment your comment adds to my article. Thanks for reading!
David