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
Copyright © 2021 Certent