Archive for the ‘Excel Number Formatting’ Category

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

Padding Numbers with Zeros

August 19th, 2009

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.

  » More: Padding Numbers with Zeros

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