Format in Excel

Follow these best practices when setting up Excel tables for linking, to create a consistent and clean output in Microsoft Word and HTML.

Before you Begin

Review and Set the Normal Style in Excel.

With the Entire Worksheet Selected:

  • Match Font (Times New Roman, 10 pt)
  • Select No Fill - banding will be achieved using Table Banding in DM Word
  • Bottom Align
  • Turn Left Align on then off
  • Turn Wrap Text on then off
  • Unmerge cells
  • Set Row Height (12.75)

Focusing on Columns and Content:

  • Move all line items into one column
  • Insert separate “gutter” columns (for $ and %), and blank spacing columns
  • Ensure () are in the same cell surrounding the value
  • Delete extra columns and ()
  • Delete extra rows - in anticipation of banding
  • Format columns:
    • $ gutter and spacing columns Column Width (1 or less), Left Align, Format Cell (General)
    • Value columns Column Width (10)
    • % gutter columns Column width (1.5), Right Align, Format Cell (General)

Formatting Headings

  • Ensure each heading is above the correct values
  • Split headings into multiple rows for consistent display
  • Change Font Size (8 pt) and Bold
  • Set Row Height (10.5)
  • Merge and Center headings over value and gutter columns
  • Customize:
    • Format dates using Custom and “mmmm dd,” or “yyyy”
    • Add thick bottom borders below heading

Formatting Values

  • Change dashes to value “0”
  • Including gutter columns apply a thin line bottom borders or a double line bottom borders to appropriate value cells
  • Apply Custom Format to remove the dollar sign and to align values and decimals
  • Custom format with em-dash (—):
    • _(* #,##0_);_(* (#,##0);_(* "—"_);_(@_)
    • _(* #,##0.00_);_(* (#,##0.00);_(* "—"??_);_(@_)
    • _(* #,##0_)%;_(* (#,##0)%;_(* "—"??_)%;_(@_)
    • _(* #,##0.0_)%;_(* (#,##0.0)%;_(* "—"??_)%;_(@_)
    • * note: "—" is entered by pressing Alt+0151

Formatting Labels

  • Select labels in left column
    • Left Align
    • Wrap Text
    • Change format to General or Text
  • Indent appropriate rows with Increase Indent button located in the Alignment cluster
  • Apply Bold/Borders to appropriate cells
  • Use one row for each label
    • Auto Fit row height to show entire label