How To Format Numbers In Millions and Thousands

• Select the Data that you would like to change from a standard format to thousands or millions.  In our case, we will select the 3rd column and then the 4th column and apply different formatting to each of them.

 

  

 

• Select all of your data in the 3rd column (at this point it’s still displaying as the entire number in millions), and then type CTRL + 1.  This will bring up the formatting screen.  You’ll want to go down to custom and then on the right you’ll now be able to put in a custom format type.

$#,##0,, “M”

Once the custom number format has been typed in you can click OK.  I’ll leave at least one link at the bottom of the post to a tutorial that explains the syntax for the custom number format.  I will quickly say however that adding a comma after the zero will change our value to thousands and 2 commas after the zero to millions.

 

 

• Select all of your data in the 4th column and then type CTRL + 1.   This will bring up the formatting screen.  You’ll want to go down to custom and then on the right you’ll now be able to put in a custom format type:

$#,##0, “K”

Once the custom number format has been typed in you can click OK.

 

 

Here is the Result:

 

How to Conditionally Format and Add Colors to Values Using Custom Number Formatting

•  The first thing you’ll want to do with your data is highlight it.  We’re going to highlight the column and values that we would like to format.

• Now that we’ve highlighted our data set, type CTRL + 1.  This will bring up our formatting screen.  From here we are going to select the custom format type for numbers under the category section.  From there we are going to put in a new custom number type.  We will have three different sections to our number format which will be separated by semi-colons. Here we go.

1) This first section will only affect numbers greater than or equal to $50,000.  We are going to make the text green and we’ll also make sure that the text is displayed in thousands with a suffix of K – to identify the number in thousands.

[green][>=50000]$#,##0,”K”;

2) The second section will only affect numbers that are between $-49,999 and $49,999. We are going to display these numbers in standard/regular format (not in thousands), with a dollar sign and we’ll use yellow to indicate that the month over month change was not great but was not something to be overly concerned about.

[Yellow][>=-49999]$#,##0;

(3) The third section will affect numbers that are less than $-50,000. Here we are going to want to put our numbers in red and for the fun of it we’ll add parentheses to indicate that the numbers are negative.  Red from a sales standpoint will mean that our revenue decreased too much month over month and that something needs to be done.

[Red]($#,##0,”K”);

   Here is the Result:

How to Incorporate Symbols Into Your Custom Number Format for Data Tables and Charts

• The first thing you’ll want to do is to insert the symbols that you’ll want to use into a cell anywhere so that you can copy them into your type field on the format screen.

• Next step will be to copy (select one arrow and type CTRL +C) the arrows individually as you use them within the sections of your custom number format type.  Paste the up arrow to the positive number format section and the down arrow beside the negative syntax section.

• Now that we’ve pasted the symbols into your new custom number format type we will hit OK and we’ll see that our data range that we applied the formatting to is now updated with the arrows in the data set (depending on where the values lie).

   Here is the Result in the Data Table and The Chart:

How to Turn Numbers into Words and Then Use Icon Conditional Formatting In Excel

• The first step of the process is to input a 1, a 0 and a -1 into three different cells as shown here.

• The second step of the process will be to now highlight the cells with the three different values and type CTRL + 1 to bring up the formatting screen.  We’ll now be able to assign each of the values a word using the custom number formatting type.  Positive numbers, negative numbers and then zeros will each display with a different word.  The syntax setup here is positive numbers; negative numbers; zero; text (here we are not going to use the text portion/section of the custom number format but we’ll see how we can use this in our next Trick).  Below you’ll see we’ve applied the word “PASSED” to positive numbers, “FAILED” to negative numbers and “ON HOLD” to zero value numbers.

Note: Any numbers that are greater than 1 will show up as “PASSED” and any numbers that are less than -1 will show up as “FAILED”.

Here’s what our table now looks like:

• Now we can highlight our set of data in our table and we can assign conditional formatting icons to our data because each cell has a number behind it but is simply displaying a word (this is kind of like a light switch:  ON=1 switch OFF = 0).  Here we are going to have the value of 1 be in green (“PASSED”), value of 0 will be in yellow (“ON HOLD”) and a value of -1 will be in red (“FAILED”).

Step 1  – Select the data that you would like to apply the conditional formatting to.

Step 2 – Select the conditional formatting option from the home tab and then select the stop lights.

Step 3 – Update your light value association.  A value of 1 or greater will be green, a value of 0 will be yellow and a value of anything less than zero will be red.  Make sure you change the type from percentage to number.  Once you are done hit OK.

  Here is the Result:

How to Use The Text Section of Custom Number Formatting as Data Validation

• The first step of the process will be selecting the data that we would like to apply the data validation to.  Here it will be the values next to each of the project #s.

• Once we’ve selected the data, hit CTRL + 1 to bring up our formatting screen.  Now that the formatting screen is up, we are going to add the text “Please Enter  A Number” after the 3rd semicolon in our syntax.

• Click OK once you’ve added the text section to the syntax for our custom number format type.  Below you’ll see the results.

  Here is the Result:

Sources And Other Resources

• Excel Dashboards and Reports – by Michael Alexander (Author), John Walkenbach (Author)

• Microsoft Office – Create a Custom Number Format

• The 1 Thing You Need to Learn Before Using Excel Custom Number Formatting – The Syntax

• Excel Custom Number Formatting: How To Conditionally Format Text Fields With Icon Sets Using Number Formatting

• A Comprehensive Guide To Number Formats In Excel

• Chandoo – All Articles On Custom Number Formatting