Excel Formatting Live Preview

One of the new features in Excel 2007 is live preview when you select a different font or theme on the Ribbon.

For example, here’s how my worksheet looks now:

The default font for the cells is Calibri, and I’ve added bold in the first row.

I’m not sure if the Calibri font is the best choice, so I’d like to see how the data would look in a different font.

Select a Different Font

I select column H, then click the drop down arrow for Fonts on the Ribbon’s Home tab. The live preview shows the selected cells in each font as I point to it in the list.

If I find a font that I like better, I can click on it to change the font for the selected cells. None of the other cells are affected.

Select a Different Theme or Theme Font

In most cases, I wouldn’t just change the font in one column or a few cells. To keep the workbook from looking like a ransom note, I’d usually change the font for the entire workbook.

In Excel 2007 I can do this by choosing a new Theme or by selecting a different Theme Font.

1. On the Ribbon’s Page Layout tab, click the drop down arrow for Themes or for Fonts, then point to one of the options.
2. The live preview affects all the cells that use the Calibri font, instead of just the selected cells.
3. Click on one of the options and all the cells with Calibri font are changed.

Formatting Converted Workbooks

Slowly, I’m converting some of my old Excel files, like the price list shown below, to Excel 2007 format.

When I use the Theme or Theme Fonts drop down list, the live preview doesn’t work. The data is still shown in Arial Narrow, which was the default font in Excel 2003.

To make this worksheet fit the current theme, I’ll change the fonts to Calibri and Cambria:

1. Click the Select all button at the top left of the worksheet, to select all the cells on this worksheet.
2. Then, on the Ribbon’s Home tab, select the Calibri font.
3. To format the first row as Headings, select Row 1 and apply the Cambria font.

Now, any cells that are formatted with one of the Theme fonts will show a live preview when selecting a different Theme or Theme Font.

Merge Styles Tip

John Walkenbach added this formatting tip in the comments below:

“Here’s a way to quickly make an XLS workbook look like an 2007 workbook:

Open your XLS workbook, then press Ctrl+N to open a new workbook. Go back to your XLS window and activate the Merge Styles dialog (click the bottom of the scroll bar in the Styles group, then choose Merge Styles). Select the new workbook you opened, click OK, and confirm your choice.

All cells that have the “Normal” style will now be formatted in the default style for Excel 2007. In addition, the cells will respond to theme changes.”

_________________________