ExcelStyle

Task Factory ›› Data Flow Components ›› Destinations ›› Excel ››
Parent Previous Next


Excel Destination Column Styles


The most common and useful implementations of Excel format codes are:

Format Code

Description

General

General number format

#

Digit placeholder that represents optional digits and does not display extra zeros.

0

Digit placeholder that displays insignificant zeros.

?

Digit placeholder that leaves a space for insignificant zeros but doesn't display them.

@

Text placeholder

. (period)

Decimal point

, (comma)

Thousands separator. A comma that follows a digit placeholder scales the number by a thousand.

\

Displays the character that follows it.

" "

Display any text enclosed in double quotes.

%

Multiplies the numbers entered in a cell by 100 and displays the percentage sign.

/

Represents decimal numbers as fractions.

E

Scientific notation format

_ (underscore)

Skips the width of the next character. It's commonly used in combination with parentheses to add left and right indents, _( and _) respectively.

* (asterisk)

Repeats the character that follows it until the width of the cell is filled. It's often used in combination with the space character to change alignment.

[]

Creates conditional formats.

The following characters can be included in Excel custom format codes without the use of backslash or quotation marks:

Symbol

Description

+ and -

Plus and minus signs

( )

Left and right parenthesis

:

Colon

^

Caret

'

Apostrophe

{ }

Curly brackets

< >

Less-than and greater than signs

=

Equal sign

/

Forward slash

!

Exclamation point

&

Ampersand

~

Tilde


Space character

Excel Number Format

Excel number format consists of 4 sections of code separated by semicolons in the following order:

POSITIVE; NEGATIVE; ZERO; TEXT

Example:

In this example, positive numbers will display 2 decimal places and a "thousands separator." Negative numbers will appear the same as positive numbers but enclosed in parenthesis. Zeros will be replaced as dashes, and the text will be displayed in the magenta color.

Digit and Text Placeholders


Code

Description

Example

0

Digit placeholder that displays zeros.

#.00 - will display 2 decimal places.

A cell that contains 1.2 will be displayed as 1.20.

#

Digit placeholder that represents optional digits (does not display extra zeros.)

#.## - displays up to 2 decimal places.

1.2 will be displayed as 1.2

1.236 will be displayed as 1.24

?

Digit placeholder that leaves a space for insignificant zeros on either side of the decimal point but doesn't display them. (Often used to align numbers in a column by decimal point.)

#.??? - displays a maximum of 3 decimal places and aligns numbers in the column by decimal point.

@

Text placeholder

0.00; -0.00; 0; [Green]@ - applies the green font color to text values.

Rounding Numbers

Microsoft Excel separates thousands by commas if a comma is enclosed by any digit placeholders - pound sign (#), question mark (?) or zero (0). If no digit placeholder follows a comma, it scales the number by thousand, two consecutive commas scale the number by million, and so on.

Example:

General

#,###

#,

#.00,

#.00,,

50

50


.05

.00

500

500

1

.50

.00

5000

5,000

5

5.00

.01

55500

55,500

56

55.50

.06

555500

555,500

556

555.50

.56

Text and Spacing Formatting

To display both text and numbers in a cell, do the following:

To add a single character, precede that character with a backslash (\).

To add a text string, enclose it in double quotation marks (" ").

Example: Numbers that are rounded by thousands and millions - add \K and \M to the format codes, respectively:

To display thousands: #.00,\K

To display millions: #.00,,\M

General

#.00, \K

#,###.0, \K

#.00,, \M

#,###.0000,, \M

50

.05 K

.1 K

.00 M

.0001 M

500

.50 K

.5 K

.00 M

.0005 M

55500

55.50 K

55.5 K

.06 M

.0555 M

5555000

5555.00 K

5,555.0 K

5.56 M

5.5550 M

5555000000

5555000 K

5,555,000.0 K

5555.00 M

5,555.0000 M

Temperatures

#"°F" displays Fahrenheit (Example: 85°F)


#"°C" displays Celcius (Example: 23°C.)


Display Leading Zeros

Users can add the number zero (0) as a placeholder to indicate the number of leading zeros.

Example: Adding 00000 to the style will display the number 55 as 00055



Percentages


Add the percent sign (%) to the style format box.


Example: #% will display the number 0.08 as 8%.



Fractions


For decimal numbers to appear as fractions, add the forward slash (/) to the style format box.


Example: #/# will display the number .5 as 1/2.



Scientific Notation


To display numbers as scientific notation, add the capital letter E to the style format box.


Example: 00E+00 - displays 1,500,500 as 1.50E+06.


Indents


Some users prefer a cell's contents not position against the cell's border. To add an indent, use the underscore (_).


_(  adds indent from the left border.


_) adds indent from the right border.



Font Color


Adding the color surrounded by square brackets ([ ]) will format the font to the specified color. (Note: the color code must be the first item in the section. Also, only the following colors can be used - Black, Green, White, Blue, Magenta, Yellow, Cyan, and Red.)


Example: [Blue] $#,##0.00 will display the number 25.3 as $25.30



Repeat Characters


To repeat a specific character so that it fills the column width, use the asterisk (*) before the character to be repeated.


Example: *- will make the cell with the number 555 appear as |------------555|



Alignment


To align numbers left in a cell, type an asterisk and a space after the number code. Example: "#,###* " (Exclude the double quotes when adding the format.)



Custom Number Formats Based on Conditions


To apply conditions that must be met for a custom style, enclose the condition within square brackets.


Example: [Red] [<100] ; [Blue] [>=100] will display numbers as 49 and 184



Custom Date



Format

Description

Example (January 1, 2018)

m

Month number without a leading zero

1

mm

Month number with a leading zero

01

mmm

Month name in short form

Jan

mmmm

Month name in full form

January

mmmmm

Month as the first letter

J

d

Day number without a leading zero

1

dd

Day number with a leading zero

01

ddd

Day of the week in short form

Mon

dddd

Day of the week in full form

Monday

yy

Last 2 digits of the year

18

yyyy

All 4 digits of the year

2018

Custom Time

Format

Description

Example (January 1, 2018)

h

Hours without a leading zero

0-23

hh

Hours with a leading zero

00-23

m

Minutes without a leading zero

0-59

mm

Minutes with a leading zero

00-59

s

Seconds without a leading zero

0-59

ss

Seconds with a leading zero

00-59

AM/PM

Periods of the day

AM or PM


For more Excel Style information, please refer to this blog post here.