Daily posts of Excel tips…and other stuff
Archive for the ‘Formatting Cells’ 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, [...]
Hi all,
I’ve just published a new article on my website. Here is the introduction:
“This article explains how you can use styles to ease maintenance of your spreadsheet models.
Microsoft has made it very easy to dress up your worksheets with all sorts of fill patterns, borders and other frills. Because formatting of cells is often done [...]
It’s college football bowl season once again. I have a spreadsheet I use to help me identify winners and losers (for entertainment purposes only).
The four yardage columns are yards per game for rushing offense, rushing defense, passing offense, and passing defense, respectively. I’m trying to identify teams that have better defenses (because defense [...]
Here’s what my (partially obfuscated) desktop looks like:
I put information I need on my desktop so I’m a Windows+M key combination away from what I need. The Excel file sits in my XLStart directory so the calendar gets updated every day.
VBA from Excel to Desktop
In the Workbook_Open event, I have
Private Sub Workbook_Open()
[...]
Stu asks:
Is there a number format that I can use that will display numbers in the thousands without actually using a formula? So, 20,000,000 in sales in a cell would actually be displayed as 20,000.
Good question, and yes there is.
The number format #,##0, works because of the comma at the end.
I love the Comma style. I use for almost all of the numbers in my spreadsheets. I have been using the Number style because I could use Control+Shift+1 to apply that format easily. I doesn’t dash-out the zeros, though. Microsoft at Home says that Alt+apostrophe will take me right to the [...]
