In, 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.

(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.

Search our Formatter by Zapier help documentation, resolve common errors. You can manually enter one by clicking on the dropdown and select 'Use Custom. You can create an activity formatter for any audited table. Before you begin Role required: admin Procedure Navigate to System UI > Formatters. Enter a name for the formatter, such as Activities (task).

Click for larger image. For the neophytes, here are what those icons mean, from left to right: Currency 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. Increase Decimal Add decimals. Since I’m not [overly] neurotic or a scientist, I very rarely use this option.

Decrease Decimal 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. Click for larger image.

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. Date Formatting 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.

Following a few simple principles, you can construct your own formatting options. We’ll use August 3, 2012 in our examples below: m: 8 mm: 08 mmm: Aug mmmm: August d: 3 dd: 03 ddd: Fri (because it fell on a Friday) dddd: Friday yy: 12 yyyy: 2012 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. Click for larger image.

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. Play populous the beginning. 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: [BLACK] [GREEN] [RED] [BLUE] [CYAN] [MAGENTA] [WHITE] [YELLOW] 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.