I just finished four days of DataPig workshops with Jon Peltier and Dick Kusleika. As always, everyone who attended had a blast and learned a ton of new tips and tricks. If you’ve never been to one, you’re seriously missing out.
.
Here’s an example of why I love these events.
» More: Forcing Carriage Return in Custom Date Format
I just got back from a whirlwind week of live training events. The most exciting one was the live Xcelsius training we did in Miami. We had a blast, as we always do at our live events. I was on my game – of course – and everyone walked away with coffers full of new techniques.
.
During my live training events, I have gotten into the habit of using a trick I learned from John (a Senior VP at Ernst &Young) a few months ago.
With this trick, I can increase the font of just the Formula bar. Like this:
Last week, Elaine asked:
“I have project numbers that are both numeric and text which follow a parent and child relationship. The problem is when I sort by project number, all the numbers are sorted at the top, and all the text at the bottom. How do I sort, but keep parent projects with the child projects?”
So Elaine would like to see something like the table shown here – all projects sorted so that the numeric parent stays with the textual children.
» More: Sorting Numbers and Text Together
So I’m teaching this Dashboarding class with Jon Peltier, when he mentions that if you format your dates with 5 m’s (“mmmmm”) you get the first letter of the month name.
What?! Do you know how long I’ve been typing (J, F, M, A, M, J, J, A, S, O, N, D)? It’s beyond me how I never found this nifty trick.
Here’s the deal:
This may sound trivial, but one of the more overlooked features in Excel is the ability to remove all formatting with a few clicks. I know what you’re thinking – “Out of Blog ideas already DataPig”? Yes, but that’s not the point.
I can’t tell you how many times I’ve seen competent analysts take a table like this one and clear the formatting one component at a time. That is to say, they typically make the cells white first. Then they remove borders. Then they clear any font and number styles.
For those of you who just came from behind the mountains: a tooltip is a brief explanatory text which appears when you hover your cursor over a button or other control without clicking.
In an Excel spreadsheet, the nearest thing to a tool tip is the Comments functionality.
The good thing about comments is that they have been around a long time, so most people know what they are and how to use them. The bad thing about comments is that they have been around a long time and they just look and feel outdated.
No I’m not talking about the Bernie Madoff method of accounting.
Side Note: Does anyone else here thinks it hilarious that the phonetic pronunciation of Bernie Madoff’s last name is ‘made off”? As in: “he made off with other people’s money”. But I digress.
Some databases require a specific number of characters in certain fields. For example, a field called Transaction Number could require 10 characters. In these cases, a number is typically padded with zero. So transaction number 6546 would be entered into the database as 0000006546.
This is all fine and dandy, but if you’re entering data in Excel, padding a number with zeros can be tedious. So here’s a simple formatting trick you can use to automatically pad numbers.
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
