Posts Tagged ‘Number Formatting’

Forcing Trailing Negatives

October 5th, 2009

Argento writes and asks:

"How can I produce numbers with minus symbol at the end of the number?"

In other words, Argento wants to show his numbers with trailing negatives. That is to say, instead of showing -125, he needs to show his numbers as 125-.

I'm not an accountant, so I never really understood why anyone would want to see trailing negatives. To me, it just looks as though you keep forgetting to finish writing your math problem.

Nevertheless, you can force trailing negatives easily with a custom number formatting.

  » More: Forcing Trailing Negatives

Suppressing Zeros in Chart Labels

September 14th, 2009

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.

  » More: Suppressing Zeros in Chart Labels

Using Bullet Points in Excel

September 4th, 2009

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.

  » More: Using Bullet Points in Excel

Handling Different Data Types with One Chart

August 12th, 2009

Ahh…data types; Currency, Number, Percent. What a joy to have such a diverse set of numerical variations designed to make your life hell.

Anyone who has had to handle varying numerical formats knows how tedious it can be to build a reporting structure that can handle all formats.

If you've ever built one chart for Percents, one for Currencies, and one for Numbers, then you're reading the right blog post.

Today, I'll to show you a technique that allows one chart to accurately show any data type.

  » More: Handling Different Data Types with One Chart

Finding a More Acceptable Green

August 3rd, 2009

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!

» More: Finding a More Acceptable Green

Forcing Number Formatting in a Pivot Table

July 20th, 2009

Every now and then, I have to deal with a situation where a normalized dataset makes it difficult to build an appropriate pivot table.

Here's an example:

» More: Forcing Number Formatting in a Pivot Table

Convert Numbers to Military Times in Excel

June 17th, 2009

In his last desperate act before taking a handful of Ambien, M. DeRevere asks:

How can I program a cell to accept time formulas without putting the (:) colons between the numbers… if you could format the cell to accept just number such as 2300 and it formats it as 23:00 it would save me sooooo much time?… HELP

Well hold off on the pills M.  Because I'm rockin the Excel skillz , and I happen to know the answer.

M's question revolves around "military" time (the 24 hour clock).  For you civilians out there, 23:00 hours is 11:00 pm in military time.  The trick is to apply some custom number formatting.

  » More: Convert Numbers to Military Times in Excel