Using the AutoFormat function in Excel

Contents
  1. Improve readability and save time with Excel’s built-in table formats
  2. Understanding Basic Formatting Areas
  3. Add AutoFormat to Quick Access Toolbar
  4. Apply AutoFormat style
  5. Change AutoFormat style before applying
  6. Change AutoFormat style after you apply it
  7. Work smart, don’t force it

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

The layout makes the worksheet look good. Selecting a background color, font style, font size, and other formatting options simplifies data analysis. It also makes it easier to see important information and gives the spreadsheet a professional look. One way to make formatting a worksheet in Excel easier is to use the AutoFormat option.

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

Understanding Basic Formatting Areas

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

  • number format

  • borders

  • fonts

  • Patterns and background color

  • center

  • Column and row size

Add AutoFormat to Quick Access Toolbar

To use AutoFormat, add an AutoFormat icon to the Quick Access Toolbar so that you can access it when needed. This is a one-time operation. After adding AutoFormat, it will remain on the Quick Access Toolbar.

  1. Select drop-down arrow Quick Access Toolbar

  2. Select Additional assignments to open the dialog Customize the Quick Access Toolbar

  3. Select arrow Select commands from

  4. Select All teams to list all available commands in Excel.

  5. Scroll through the alphabetical list and select Auto format

  6. Select To add

  7. Click Okay to add an auto-format icon to the Quick Access Toolbar.

Apply AutoFormat style

To quickly apply an AutoFormat style to a table:

  1. Highlight data on the sheet you want to format.

  2. Go to quick access bar and choose Auto format

  3. In the dialog box Auto format choose a style.

  4. Select Okay to close the dialog box.

  5. The new style is applied to the table.

  6. To apply a different style, select a cell in the table and select Auto format

Change AutoFormat style before applying

If you don’t like any of the available styles, change the style before applying it to your worksheet.

  1. In the dialog box Auto format select parameters:

  2. In section Application formats uncheck the boxes for the formats you don’t want to use in the spreadsheet.

  3. The previews in the dialog box are updated to reflect the changes.

  4. Select Okay to apply the changed style.

Change AutoFormat style after you apply it

After you apply a style to a table, change the style of the table using the formatting options on the Home tab of the Ribbon. Then save the modified AutoFormat style as a custom style that you can use with other spreadsheets and worksheets.

To create custom table auto-formats:

  1. Select a cell in the table.

  2. Go to the tab At home select Format as table and then select New table style

  3. In the dialog box New table style select the table element and select Format to apply the desired font, border, or formatting. Do this for each table element you want to change.

  4. Check box Set as default table style for this document if you want to use this style automatically when formatting tables, select Okay to keep the autoformat style.

  5. To use a custom style, select the table, go to At home select Format as table and select a custom style.

Work smart, don’t force it

Excel’s automatic formatting features save a lot of time when creating data tables. Quickly apply table formatting with one click instead of formatting spreadsheet elements every time.

Leave a Reply

Your email address will not be published.