While working on a report, I decided to use some awesome custom number formatting.
This screenshot shows how I expertly apply a custom format so that any number greater than 1000 is colored green.
At this point, I’m thinking this is going to be great!
Some of the tables I encounter in my daily operations are….how can I put it….uglier than a bag of lips.
Seriously man, with all the buzz around visualizations today, it’s almost impossible to find anyone talking about table design. Yes – One of the most underestimated endeavors in Excel is table design.
I’ve always felt that how a table is designed has a direct effect on how well an audience absorbs and interprets the data in that table. Unfortunately, the act of putting a table of data together for consumption is treated benignly by most. Maybe it’s because it’s such a common task, often done in a hurry, that we don’t spend the extra time to apply some sound design principles.
In this post, I’ll show you how easy it is to apply a handful of table design best practices.
Here’s some breaking news……Formatting fonts white in order to hide values is not clever. It’s annoying.
Here is an example.
» More: Finding Ghost Cells in Excel
You can find lots of techniques to achieve the alternate shading effect in Excel. Most of them involve some sort of conditional formatting trick.
About a year ago, someone from one of my workshops taught me a trick to quickly apply alternate shading without a lot of hassle.
I’m a pretty lazy guy, so I naturally got excited.
» More: Lazy Alternate Shading
Any self-respecting Excel expert will tell you that the ‘Merge and Center’ functionality is an abomination. The only benefit I can think of is the justification you can offer when you launch your office shooting spree.
“It was that message again” (eye twitching). “Can not change that part of merged cells” . Your temporary insanity plea will go right through.
A better option is to apply the ‘Center Across Selection’ formatting option. Center Across Selection essentially gives the same visual effect as ‘Merge & Center’, but without actually merging the cells.
In his last desperate act before taking a handful of Ambien, M. DeRevere asks:
How can I program a cell to accept time formulas without putting the (:) colons between the numbers… if you could format the cell to accept just number such as 2300 and it formats it as 23:00 it would save me sooooo much time?… HELP
Well hold off on the pills M. Because I’m rockin the Excel skillz , and I happen to know the answer.
M’s question revolves around “military” time (the 24 hour clock). For you civilians out there, 23:00 hours is 11:00 pm in military time. The trick is to apply some custom number formatting.
