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
Last night, I looked up my book “Excel Reports and Dashboards” on Amazon only to find that my first reviewer gave me 3 out of 5 stars. He gives a bunch of reasons, mostly having to do with how advanced he is and how the book is too basic for his needs. But that’s not the interesting bit. In his review he says:
“My own favorite technique — setting all of a worksheet’s columns to be very narrow and then merging cells to position the information exactly where I want it — isn’t mentioned”
Interestingly enough, I’ve been using that trick for several years now. I’ve always found this trick difficult to explain verbally, let alone in print. But I’ll give it a shot here.
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.
Susan writes me and asks if there is a way to suppress zeros in chart labels.
“I have a chart that’s dynamic in that I can choose a month that’s represented. For each month, and Sales Rep, there are up to 5 possible revenue streams, but not every Sales Rep has revenue for all revenue streams each month. Is there a way to remove the zero values from the labels in my chart?”
I want to drive traffic to this site, so I better answer her question.
Harkem (one of my 12 fans) wrote me recently and asked:
“How can I use bullet points in Excel? Much of my PowerPoint presentations are made of Excel ranges that are copied and pasted as pictures. I remove the gridlines to make it look seamless in PowerPoint. I’d like to be able to use bullet points in Excel to enhance the illusion.”
Well Harkem, you’ve come to the right place. I happen to know the answer.
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.
