Daily posts of Excel tips…and other stuff
Archive for the ‘Formatting’ Category.
In New Zealand, we format our dates dd/mm/yy. However, the United States format as mm/dd/yy.
When I see a date by itself, like 12/01/09, I ask “is this the 12th of January or the 1st of December”?
When I started working with Oracle RDBMS, a habit I picked up was to format dates dd-mon-yyyy.
This avoided the dd/mm [...]
Nothing wrong with this sheet, right?
Until you get to the end…
The teeny-tiny vertical scrollbar handle is usually a give away. Hundreds of rows tacked onto the end of the list to give the illusion that the whole column has been formatted.
The trouble with formatting columns as per the above image is that it causes the [...]
It is possible to get a cell to display one thing, but store another.
Create a Custom Format by right-clicking the cell, select Format Cells…, then from the Number tab select Custom.
In the Type box use whatever text you want displayed, but be sure to enclose it in “double-quotes”.
I admit, this is a pretty dirty trick, [...]
Excel has a Text format that can be applied to cells. When a cell is formatted as Text, anything you type in the cell will appears in the cell. That may seem obvious, but it applies to numbers and formulas too. For instance, if you type a formula in a cell formatted [...]
If you type a number into an unformatted cell in Excel, that number is stored in the Double data type. When you format that number, you show it in a specific way, but you don’t change the number. For instance, if you format the number 1 as a Comma style, you get 1.00. [...]
A couple of my golf buddies and I were debating the handicaps of a course we regularly play. Each hole of a course is handicapped 1 through 18 with 1 being the hardest hole and 18 the easiest. Handicaps are used to allow golfers of differing abilities to complete equitably. Because my [...]
In order to perform my most common formatting operations, I’m going to try to use a userform with only those operations on it. These 16 buttons should cover about 95% of the cell formatting I do.
I set the Ctrl+1 shortcut, which normally shows the Format Cells dialog, to show my userform instead. Now [...]
I like the Comma style. It’s by far my favorite. What I don’t like is how hard it is to apply that style with the keyboard. In 2003, all you have to do is:
Alt + O + S + DownArrow + UpArrow x 4 + Enter
Unless you’re me. If you’re me, [...]
