Advanced data Formatting

Formats are changes that are made to Excel Worksheets in order to enhance their appearance and/or to focus attention on specific data in the worksheet.

There are a number of different types of formats in Excel. Such as

Cell Formats those that are applied to cells in the worksheet such as:

  • adding borders to a cell or group of cells
  • changing the background color (shading) of the cell(s)
  • changing the width of columns or the height of rows

Text or Font Formats those that are applied to data in the cells of a worksheet such as:

  • changing the font color
  • changing the alignment of data in a cell
  • changing the size of a font
  • applying bold, italics, or underline formatting

Chart Formats those that are applied to charts or graphs:

  • changing the fill or outline color of a chart
  • changing the alignment of text labels
  • adding 3-d effects
  • beveling or adding drop shadows to chart borders

How to Use Excel Custom Number Formatting

The key benefit of custom number formatting is: “It allows you to change the appearance of your data without actually changing the data value.” Please keep in mind that this is different than conditional formatting which, while allowing you to change the font, font size, text color, and highlighting, does not allow you to change the actual data that appears.

The Custom Number Formatting Syntax

All custom number formats follow a basic syntax:
<Positive Values> ; <Negative Values> ; <Zero Values> ; <Text Values>

Between each of the semicolons is where you define the formatting for each of the conditions specified.  Please note that in some special cases, these conditions are not relevant (i.e.. date and time formats) and that you don’t necessarily have to specify the condition for zeroes or text values.

Theoretically, there are an infinite number of adjustments you can make the using custom number formatting.  Rather than trying to include all of them, I will focus on explaining the most common and most useful implementations of this feature.  For the most part, I will try to leave out scenarios that can either be typed in manually or created through one of the default number format categories.

  Number of Parts     Format Syntax Used
  Three     positive format ; negative format ; zero format
  Two     positive and zero format ; negative format
  One     positive, negative, and zero format

The four parts, separated by semicolons, determine how various numbers are presented. The first part defines how a positive number is displayed, the second part defines how a negative number is displayed, the third part defines how zero is displayed, and the fourth part defines how text is displayed. If you leave out one or more of these parts, numbers are controlled as shown here:

General Displays the number with the General format.
# Holds a place for a digit and displays the digit exactly as typed. Displays nothing if 0 is entered.
0 Holds a place for a digit and displays the digit exactly as typed. Displays 0 if 0 is entered.
? Holds a place for a digit and displays the digit exactly as typed. Displays a space if no number is entered.
. (period) Sets the location of the decimal point.
, (comma) Sets the location of the thousands separator. Marks only the location of the first thousand.
% Multiplies the number by 100 (for display only) and adds the percent (%) character.
E+ e+ E– e– Displays the number in scientific format. E– and e– place a minus sign in the exponent; E+ and e+.
/ (slash) Sets the location of the fraction separator.
$ ( ) : – + <space> Displays the character.
* Repeats whatever character immediately follows the asterisk until the cell is full. Does not replace other symbols or numbers.
_ (underscore) Inserts a blank space the width of whatever character follows the underscore.
\ (backslash) Inserts the character that follows the backslash.
“text” Inserts the text that appears within the quotation marks.
@ Holds a place for text.
[COLOR] Displays the cell contents in the specified color.
[condition value] Uses conditional statements to specify when the format is to be used

 

Accounting Format Adjustments

In this view, you can also see all the custom formatting adjustments that go into creating the Accounting format.  (This is also a good exercise if you like the Accounting format, as I usually start with Accounting as a base before I make custom formatting adjustments.)

Now to explain what each of these adjustments do:

Format Description
_( Adds a left indent to the cell, so it does not ride up against the edge; the underscore value tells Excel to create a space equal to the character following the underscore
$ Special character that will show up in your output without the need to add quotation marks within the syntax
* (Please also note that the code indicated here is both an asterisk and a blank space) The asterisk tells Excel to repeat the subsequent character until the width of the cell is filled; this is why the accounting format has the dollar sign aligned to the left, the number aligned to the right, with as many spaces as necessary in between
#,##0 Displays commas in between every three digits, for all values exceeding three digits
0 Digit placeholder that displays insignificant digits (ie. digits that are zero)
0 The zeroes trailing after the first zero indicate how many decimals places you want to show; even if the final digit is zero, it will be displayed in this format
_) Adds a right indent to the cell, so it does not ride up against the edge; the underscore value tells Excel to create a space equal to the character following the underscore
“-“ Displays a text value; anything between the quotation marks will be shown
? Digit placeholder similar to 0, but does not display insignificant digits; if the value is zero, it will just be a blank space; in accounting format, because it is in the Zero section of the syntax, it will always appear as a blank space
@ Placeholder for text

Round by Thousands (0.000,,)

The most common use of custom number formatting is to round large numbers greater than 1,000.  For example, assume we want to display 1,562,000 as 1.562.

Each comma rounds three decimal places; therefore “,” puts your number in thousands “,,” is in millions and “,,,” is in billions, etc.  In front of the comma, we indicate how many decimal places we want to show.  Note that you typically want to replicate any adjustments you make on the positive side with the negative one, in case your value switches signs.  Don’t forget to include negative notation for the negative number syntax – either a negative sign or a set of enclosed parentheses.

Making this adjustment is especially helpful for charts, where a long number can add unnecessary space to your vertical axis.

Add a Unit Value ( “M” or “K”)

In the example above, after you’ve rounded your value, you may also need to add a unit indicator to the end of your new value.  To do this, we simply add the text of the new value within quotation marks in the custom number formatting syntax.

Please note, that in this newly formatted cell, I can type whatever numeric value I want without typing “M” at the end of my entry.  It will still show up in the same format as 1.562 M.

Positive Numbers Green / Negative Numbers Red

This format is useful when you have a dynamic model that will spit out both positive and negative output numbers.  In this situation, because we’re only changing the color of the text, not the values that show up, this change can also be made using Excel’s basic conditional formatting.

dd an Indent ( _) )

The accounting format already adds indents on both sides of the cell for you.  Many people like to indent their cells so that their values don’t ride up right against the cell border.

Turn Zeroes into Dashes or Blanks (“-“)

The accounting format already turns all zeroes into dashes.

Making this adjustment is very helpful when you have a large table of data with both zero and non-zero numbers.  Turning the zeroes into dashes makes it much easier to see where your non-zero data points are.

Add Parentheses to Negative Percentages ([Red](0.0%))

While Excel 2010 has a format for negative values with parentheses in both the default “Number” and “Currency” categories, it does not have one specifically for negative percentage values.  We can use custom number formatting to create our own.  Anytime you show negative values with parentheses, you should also add an indent to the positive value.  That way, positive and negative numbers line up when stacked on top of each other.

Show an Error Message

Since custom number formatting allows you to display pretty much whatever text you want, you can leverage it to display error messages when the situation applies.  For example, assume you have a model that should only output positive numbers.  Anytime a negative value appears, you can have it just display an error message rather than the negative number.

Show Leading Zeroes (000000)

Excel’s default formatting cuts off leading zeroes.  Leading zeros are usually necessary for any primary key codes that use them.

Display a Date or Time

When choosing a date format, you should always start by picking the standard date format that’s closest to what you want and then make adjustments in the custom formatting option.  Below are common formatting designations for dates and times.

Format Description
m month as a number with no leading zeroes
mm month as a number with leading zeroes
mmm three letter month abbreviation
mmmm full name of month
d day as number with no leading zeroes
dd day as number with leading zeroes
ddd three letter day of week abbreviation (Wed)
dddd unabbreviated day of week text (Wednesday)
yy two digit year
yyyy four digit year
h hour as number with no leading zeroes
hh hour as number with leading zeroes
m minutes as number with no leading zeroes
mm minutes as number with leading zeroes
s seconds as number with no leading zeroes
ss seconds as number with leading zeroes

 

Download Documents

Download Excel File

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment