Using Excel's AutoFormat Feature

Improve readability and save time with the AutoFormat

Quickly Format Worksheets with Excel's AutoFormat Options
© Ted French
by Ted French
A spreadsheet expert who is a master at Excel and Google Sheets.
Updated October 13, 2018

One way to simplify the job of formatting a worksheet in Excel is to use the AutoFormat option.

Formatting is not done just to make a worksheet look good. The choice of background color, font style, font size, and other formatting options can make data easier to read, and the most important information in the spreadsheet is easier to see, all while giving the spreadsheet a professional look.

Main Formatting Areas

There are 17 AutoFormat styles available in Excel. These styles affect six main formatting areas:

  • Number formatting
  • Borders
  • Fonts
  • Patterns and background color
  • Alignment
  • Column and row size

How to Add AutoFormat to the Quick Access Toolbar

Although accessible through menu options in earlier versions, AutoFormat has not been available on any of the tabs of the ribbon since Excel 2007.

To use AutoFormat, add the AutoFormat icon to the Quick Access Toolbar so that it can be accessed when needed.

This is a one-time operation. After it is added, the icon stays on the Quick Access Toolbar.

  1. Click on the down arrow at the end of the Quick Access Toolbar to open a drop-down menu.

  2. Choose More Commands from the list to open the Customize the Quick Access Toolbar dialog box.

  3. Click on down arrow at the end of the Choose commands from line to open a drop-down menu.

  4. Choose All Commands from the list to see all the commands available in Excel in the left pane.

  5. Scroll through this alphabetical list to find the AutoFormat command.

  6. Click on the Add button between the command panes to add the AutoFormat button to the Quick Access Toolbar.

  7. Click OK to complete the addition.

Applying an AutoFormat Style

To apply an AutoFormat style:

  1. Highlight the data in the worksheet that you want to format.

  2. Click on the AutoFormat button on the Quick Access Toolbar to bring up the feature's dialog box.

  3. Click on one of the available styles.

  4. Click OK to apply the style and close the dialog box.

Modify an AutoFormat Style Before Applying It

If none of the available styles is quite to your liking, they can be modified either before or after they have been applied to a worksheet.

  1. Click on the Options button at the bottom of the AutoFormat dialog box.

  2. Deselect any of the six formatting areas such as font, borders, or alignment to remove these formatting options from all of the available styles.

  3. The examples in the dialog box window update to reflect the changes.

  4. Click OK to apply the modified style.

Modify an AutoFormat Style After Applying It

Once applied, a style can be further modified using Excel’s regular formatting options located—for the most part—on the Home tab of the ribbon.

The modified AutoFormat style can then be saved as a custom style, which makes it easier to reuse with additional worksheets.