Easy To Advanced Uses Of Cell Formatting In Excel
In my post on table formatting, I demonstrated how to transform your static data into a simple yet sexy database in a matter of seconds. If you don’t know how to use table formatting, go read that post and then come back. Otherwise, your data will look like one of those housewives who goes to […]
In my post on table formatting, I demonstrated how to transform your static data into a simple yet sexy database in a matter of seconds. If you don’t know how to use table formatting, go read that post and then come back. Otherwise, your data will look like one of those housewives who goes to grocery store with curlers and a moo moo sporting red lipstick.
Enough chatter. Let’s jump in. If you want to follow along, you can download the Excel file. (Just click the Download button in the upper-right corner of your browser window. Don’t ask me why Dropbox sticks it up there. Developers …)
Standard Number Formatting
You would think everyone would use number formatting; it’s so rudimentary. But I can’t tell you how many times I see decimals in charts that should be formatted as percentages or no thousandths separators (a pet peeve of mine) or two decimal places filled with 0’s because the number didn’t need decimals.
These newbie mistakes are uncalled for because Excel placed several number formatting options front and center in the Number section of the Home tab:
For the neophytes, here are what those icons mean, from left to right:
Convert to currency. This option will left-align the dollar symbol and right-align the number. Technically, this is referred to as Accounting formatting. (If you want the currency symbol to be flushed up against the number, don’t use this icon; use the Currency option in the drop-down menu above this row of icons.)
You can also choose different currencies from the drop-down menu to the right of the currency icon.
Convert decimals to a percentage.
This is not a comma; it’s a thousandths separator. And if you have a number greater than 9999 you should use it. (I usually use it for numbers greater than 999.) One weird quirk with this button is it adds two decimal places whenever you apply it. Most of the time they’re unwarranted.
If you don’t have decimals, you should ditch the decimals because they just add noise. But that’s what the next icons are for.
Add decimals. Since I’m not [overly] neurotic or a scientist, I very rarely use this option.
Lob off unwanted decimals. I use this all the time. PSA: Please — for the love of all that is holy and measurable — get rid of decimals in chart axes.
Select the axis, press Ctrl-1 (Mac: Command-1) to bring up the formatting options, and adjust it in the Number section. Please.
More Ribbon Options
Both PC and Mac offer more number formatting options from the ribbon. All you have to do is click the drop-down menu above the icons we just looked at.
(Yeah, guess who Microsoft loves more.)
These options are pretty lame. The only one I use on occasion is Fraction. When you see the flexibility you have with the custom number formatting, you’ll snub your nose at these bourgeois offerings.
Custom Number Formatting Options
Okay, this is where it starts to get fun. At the risk of sounding cliche, your options are nearly endless once you learn how to rock the Custom option.
Pro Tip: To open formatting options, just select the cell(s) you want to format and press Ctrl-1 (Mac: Command-1). This actually works for anything in Excel: charts elements, images, etc.
Excel gives you quite a few options to choose from under Number > Date in the Format Cells dialog (which, again, you get to by pressing Ctrl-1 or Command-1 on the Mac). But I’m fond of the format that looks like Aug 13 (no year), which Excel doesn’t offer. No worries. Following a few simple principles, you can construct your own formatting options.
We’ll use August 3, 2012 in our examples below:
ddd: Fri (because it fell on a Friday)
So here are some examples of how you can show Aug 3, 2012:
Aug 3, 2012: mmm d, eye
Friday, August 3, 2012: dddd, mmmm d, yyyy
Fri. 08.03.12: ddd. mm.dd.yy
Formatting Positive & Negative Numbers
I’m always looking for new ways to make data pop when I have to use tables (like if there’s too much data to shoehorn into a chart). A great example of this is webmaster tools data.
If you run a report like the Search Queries report from Google Webmaster Tools (GWT) — by navigating to Traffic > Search Queries — and click the “With Change” option, as shown below, you’ll have a sea of positive and negative numbers in your export.
Sure, in the GWT interface they’re all colorful, but once you get them into Excel, they transform into the ugly duckling of data, and nothing stands out. But Excel gives you the option to dictate formatting for positive, negative numbers, and even 0.
To format these numbers, you just need to remember to follow this format: formatting for positive number; formatting for negative number; formatting for 0.
To use colors, Excel has a handful built in:
Note: These aren’t case sensitive.
If you’re a diva and those options are too constricting, Excel offers 56 colors in the form of [color X]. I actually find the [green] option too light, so I use [color 10] instead. You can find the full spectrum of colors in the helpful chart on this site.
We’ll experiment with a GWT Search Queries report from the SEER Interactive site (the agency I work for). Here’s a sample of a column that has positive and negative numbers:
To format these, the steps are simple:
Step 1: Select the column you want to format.
Step 2: Pull up the Format Cells dialog and navigate to Number > Custom.
Step 3: In the Type field enter the following formula (of sorts):
This just tells Excel, in addition to the colors, make the numbers percentages with no decimals. If you want one decimal place, just add it to the number, e.g., 0.0%. Two decimals, make it two, e.g., 0.00%. If you didn’t want percent, drop the % sign, e.g., 0.0 would translate to something like 4.7. You can also add things like currency symbols.
Here’s a screenshot of what your spreadsheet will look like after applying custom formatting to a Search Queries report:
One more thing I’ll say about formatting numbers with color is you can use conditional formatting to format positive and negative numbers, but you shouldn’t. Conditional formatting is ultra cool, and I’ll be covering it in my next post. But it’s a processor hog.
So the litmus test I follow — to keep my spreadsheets as light and agile as possible — is if it’s a static number, I use number formatting. However, if it’s formatting is conditional on another factor, I use conditional formatting.
Okay, let’s try that again in plain English …
Let’s say you run a rank report from your fave tool, and you want the current rank to be red if the keyword has fallen in rankings and green if it’s moved up.
Since the color of the current rank is conditional on the rank of the previous day, week, or month (most importantly, a value from another column), you would need to use conditional formatting. In the case of the webmaster data, all of the values are static, so we were able to use custom number formatting.
Adding Text To Number Formatting (Yes, It’s Still actually A Number)
Let’s say you don’t want negative signs, parentheses, or colors. Instead, you want positive numbers to append “Profit” to a currency value, “Loss” to a negative currency value, and “Break Even” (with no number) if it’s 0.
Your formula would look like this:
$0″ Profit”;$0″ Loss”;”Break Even”
Note: You have to include a space inside the quotes, or it’ll look like $4583Loss. No grinding on the dance floor please.
If you look at the formula bar, you can see Excel sees it as a number value, not text. Another clue that it’s a number is it’s right-aligned. Text is always left-aligned in Excel.
Square brackets aren’t just useful for formatting positive and negative numbers; they can also be used to format conditions. We’re also going to use another handy function Excel offers. You can use leaders, just like you can do in Microsoft Word, by setting a character to repeat. It’s easier to see it in action and then explain it.
So let’s say we want to write a formula that specifies that for numbers less than 1000, we want the word “Low” to be left-aligned, with the number right-aligned; for numbers greater than 2000, we want the word “High” to be left-aligned, with the number right-aligned; and numbers in between to say “Medium” with the number right-aligned.
Here’s what the formula would look like:
The last condition basically says, “And for everything else, left-align the word ‘Middle’, por favor.”
And here’s what it would look like when you’re finished:
And how did we get those extra spaces? Easy. Just throw an asterisk into your formula; then whatever character you put after it (in this case a space) will be repeated to fill up the cell. If we had put a period after it, it would have filled the space with periods.
Even though I’m not a big fan of tabular data, there are quite a few things you can do to give your data a makeover and make it actionable, even within the confines of a table. Keep in mind (as you learned in the table formatting post) you can sort and filter by color once you apply any kind of color formatting to your data.
So if you wanted to see all query terms that moved up in rankings in your GWT Search Queries report before those that fell, you can sort by putting the green numbers at the top, then the red, then the black.
To learn even more advanced uses of custom number formatting, check out this custom number formatting guide on the Microsoft site.
Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.