I’m a big detective show fan. Big. I’m realizing that it’s pretty much all I watch. Mind you, it’s not the CSI ‘normal’ detective shows – only the wacky ones will do. Monk, Psych, Sherlock (ooh… and the Mentalist – I miss you Patrick Jane!). You see the pattern, right? I like the folks that are the mad geniuses. Perhaps you can relate. Everyone likes solving problems – except not when working with Excel! With Excel, if you’re like me, you just want it to do what you want it to do and get back to the business of business (or watching detective shows).
If you’re partial to both ideas, as I am, here’s a way to combine both Excel productivity and a little fun detective work – custom number formatting. Here’s how to get to the scene of the crime: Home Ribbon Tab/ Number Section/ Dialog Box Launcher/ Format Cells Dialog Box/ Number Tab.
I lost you at Dialog Box Launcher, right? Allow me to get you a decoder ring. While in the Home Ribbon Tab, the Dialog Box Launcher is that little box with an arrow inside it that sits at the bottom right of the section titled “Number.” And… if you don’t share Monk’s impeccable pattern recognition, please notice, there is a Dialog Box Launcher at the bottom of other sections on the Home Ribbon Tab.
Other fancier ways to activate the Format Cells Box include: right click any cell and choose “More Number Formats” from the shortcut drop down list or Ctrl + 1.
Once inside the Format Cells Box – you should see a list of available pre-programmed formats starting with ‘General’ and finishing with ‘Custom.’ It’s the last two we’re going to examine more closely: ‘Special’ and ‘Custom.’
If ‘Special’ is highlighted, it leads you to a list of four pre-programmed entry fields that eliminate having to format special numbers like zip codes and phone numbers. Why does this matter? With zip codes, Excel will normally not recognize leading zeros – it deletes them. Sorry New England – you’ll just have to get your mail in one big pile. With phone numbers – excel will automatically add parentheses, spaces and dashes instead of having to type them out, which is pretty neat not only to make it look pretty but to make the list searchable since all numbers will be formatted exactly the same. No typos.
If ‘Custom’ is highlighted, it leads you to a bunch of pre-programmed entries that look like gibberish and do require a bit more background knowledge to understand. But do not be deterred. The ‘Custom’ section houses lots of neat things like suppressing entered data on screen (like invisible ink!), or showing reader friendly readouts of numerical or date and time data that is only a string of digits.
Within the ‘Custom’ section, your codebreaking skills have surely noticed the pattern that governs it, namely how many sections are separated by semicolons. These sections define how the data will be interpreted and revealed by Excel. The more sections used, the more delimited the formatting becomes. Although this column is too short to get into specifics, some sleuthing online could help one make or find a particular format desired. I’d start with something like “codes used in creating custom formats for …” or “parts of a number format string” to get started.
If you do create your own Custom Number Format, you can use it again simply by copying and pasting any cell that uses it into the new workbook and the number format will import along with the cell data. Also, Excel 2013 shares a lot in common with Excel 2010. Many of these might apply to both versions. Doing a little codebreaking up front can help your data reveal answers later, even if you don’t live anywhere near Baker Street. Try it, you’ll thank me later.
This edition of Tip of the Week was written by Melissa Beatty, a freelance trainer who has worked with Clark College’s Corporate Education Department. She can be reached at Melissa.beatty.writes@gmail.com.
