Formatting Reference

Complete guide to number formats, cell styles, borders, colors, and conditional formatting in Excel.

#Number Format Categories

#Built-in Categories

#Number Format Options

#Custom Number Format Codes

#Format Code Structure

positive;negative;zero;text

Up to four sections separated by semicolons:

1. Positive numbers
2. Negative numbers
3. Zero values
4. Text

#Basic Codes

#Text and Symbols

#Color Codes

Example:

[Green]#,##0;[Red]-#,##0;0

#Condition Codes

Example:

[>=1000]#,##0,"K";[>=1]#,##0;0

#Common Custom Formats

#Date and Time Formats

#Date Codes

#Time Codes

#Common Date/Time Formats

#Font Formatting

#Font Properties

#Default Fonts

#Cell Styles

#Built-in Styles

#Creating Custom Styles

1. Home > Cell Styles > New Cell Style
2. Name the style
3. Format: Number, Alignment, Font, Border, Fill, Protection
4. Click OK

#Modifying Styles

1. Home > Cell Styles
2. Right-click style > Modify
3. Change format settings
4. All cells using style update automatically

#Borders

#Border Positions

#Border Styles

#Border Colors

• Select from theme colors
• Standard colors
• Custom RGB/HSL colors

#Draw Borders

1. Home > Borders dropdown > Draw Border
2. Select line style and color
3. Click and drag on cells

#Fill (Background)

#Solid Fill

• Single background color
• Most common fill type
• Theme or custom colors

#Pattern Fill

#Gradient Fill

1. Format Cells > Fill > Fill Effects
2. Choose two colors
3. Select gradient style:
   • Horizontal
   • Vertical
   • Diagonal Up
   • Diagonal Down
   • From Corner
   • From Center

#Alignment

#Horizontal Alignment

#Vertical Alignment

#Text Control

#Text Direction

#Indent

• Increase/decrease left margin
• 0-15 character widths
• Works with left or right alignment

#Conditional Formatting

#Rule Types

#Highlight Cells Rules

#Top/Bottom Rules

#Data Bars

Options:

• Gradient or solid fill
• Positive/negative colors
• Show or hide bar only
• Minimum/maximum values
• Bar direction

#Color Scales

Settings:

• Minimum color and type
• Midpoint color and type (3-color)
• Maximum color and type

#Icon Sets

Options:

• Show icon only
• Reverse icon order
• Custom thresholds

#Formula-Based Rules

Use formulas returning TRUE/FALSE:

=A1>100                          Value condition
=A1>AVERAGE($A:$A)               Compare to average
=ISBLANK(A1)                     Check for blanks
=MOD(ROW(),2)=0                  Alternate rows
=A1<>B1                          Compare columns
=COUNTIF($A:$A,A1)>1             Highlight duplicates

#Managing Rules

1. Home > Conditional Formatting > Manage Rules
2. View all rules for sheet or selection
3. Edit, delete, or change order
4. Stop If True option

#Rule Precedence

• Rules evaluated top to bottom
• First matching rule applies (unless continue)
• "Stop If True" prevents further evaluation

#Format Painter

#Using Format Painter

1. Select cell with desired format
2. Click Format Painter (Home tab)
3. Click or drag over target cells

#Multiple Applications

• Double-click Format Painter to lock
• Apply to multiple ranges
• Press Esc to deactivate

#What Gets Copied

• Number format
• Font properties
• Fill color
• Borders
• Alignment
• Conditional formatting

#Themes

#Theme Components

#Applying Themes

1. Page Layout > Themes
2. Choose built-in theme
3. Or customize individual components

#Theme Colors

#Clear Formatting

#Clear Options

#Keyboard Shortcuts