Using Excel's AutoFormat Feature

Improve readability and save time with Excel's built-in table formats

Quickly Format Worksheets with Excel's AutoFormat Options
© Ted French

Formatting makes a worksheet look good. The choice of background color, font style, font size, and other formatting options makes data easier to analyze. It also makes important information easier to see, while giving the spreadsheet a professional look. One way to simplify the job of formatting a worksheet in Excel is to use the AutoFormat option.

Instructions in this article apply to Excel 2019, 2016, 2013, 2010; and Excel for Office 365.

Understand the 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

Add AutoFormat to the Quick Access Toolbar

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 AutoFormat is added, it stays on the Quick Access Toolbar.

  1. Select the Quick Access Toolbar dropdown arrow.

    Screenshot of the quick-access toolbar dropdown menu
  2. Choose More Commands to open the Customize the Quick Access Toolbar dialog box.

    Screenshot of the Customize the Quick Access Toolbar dialog box
  3. Select the Choose commands from dropdown arrow.

    Screenshot of the Choose commands from dropdown
  4. Choose All Commands to display all the commands available in Excel.

    Screenshot of All Commands in the Choose commands from dropdown
  5. Scroll through the alphabetical list and choose AutoFormat.

    Screenshot of AutoFormat selected in the Choose commands from dropdown
  6. Select Add.

    Screenshot of AutoFormat added to the Quick Access Toolbar
  7. Select OK to add the AutoFormat icon to the Quick Access Toolbar.

Apply an AutoFormat Style

To quickly apply an AutoFormat style to a table:

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

    Screenshot of highlighting a table to format in Excel
  2. Go to the Quick Access Toolbar and select AutoFormat.

  3. In the AutoFormat dialog box, choose a style.

    Screenshot of selecting an AutoFormat style
  4. Select OK to close the dialog box.

  5. The new style is applied to the table.

    Screenshot of an AutoFormat applied to a selected table.
  6. To apply a different style, select any cell in the table and select AutoFormat.

Modify an AutoFormat Style Before Applying It

If you don't like any of the available styles, modify a style before it is applied to a worksheet.

  1. In the AutoFormat dialog box, select Options.

    Screenshot of AutoFormat formatting options
  2. In the Formats to apply section, clear the check boxes for the formats that you don't want to use in the table.

    Screenshot of adjusting AutoFormat formatting options
  3. The examples in the dialog box update to reflect the changes.

  4. Select OK to apply the modified style.

Modify an AutoFormat Style After Applying It

After you apply a style to a table, modify the table style with the formatting options found on the Home tab of the ribbon. Then, save the modified AutoFormat style as a custom style that can be used with other tables and worksheets.

To create custom AutoFormat styles for tables:

  1. Select any cell in the table.

  2. Go to the Home tab, select Format as Table, then choose New Table Style.

    Screenshot of selecting a new table style in Excel
  3. In the New Table Style dialog box, choose a table element and select Format to apply the font, border, or fill formatting you like. Do this for each table element you want to change.

    Screenshot of formatting table style elements in Excel
  4. Select the Set as default table style for this document check box if you want to automatically use this style when formatting tables, then select OK to save the AutoFormat style.

  5. To use the custom style, highlight a table, go to Home, select Format as Table, and choose the custom style.

Work Smarter, Not Harder

AutoFormat features in Excel save a lot of time while creating data tables. Quickly apply table formatting with a click of the mouse, rather than formatting spreadsheet elements from scratch every time.