1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Excel AutoFormat

Using Excel's AutoFormat Feature

By

Excel's AutoFormat Option

Excel's AutoFormat Option

© Ted French

To simplify the job of formatting a worksheet in Excel use the Autoformat option.

Formatting a worksheet is not done just to make it look good. The choice of background color, font style and font size, plus other formatting options can make data easier to read and allow readers to easily pick out the most important information in the spreadsheet.


There are 17 Autoformat styles available in Excel. These styles affect six main formatting areas: number formatting, borders, fonts, patterns and background color, alignment, and column and row size.

Finding the Autoformat Button

Although readily available in earlier versions of Excel, Microsoft chose not to add Autoformat to one of the tabs of the ribbon in Excel.

The first step to using Autoformat, therefore, is to add the Autoformat button to the Quick Access Toolbar so that we can use it.

If you are unfamiliar with how to do this, the article Add the Autoformat Button to the Quick Access Toolbar will give you step by step instructions on how to complete this one-time task.

Applying an Autoformat Style

  1. Highlight the data in the worksheet that you want formatted

  2. After adding the Autoformat button to the Quick Access Toolbar, click on the Autoformat button to bring up the feature's dialog box

  3. Scroll through the list of available styles

  4. Click on the one you want to use

  5. Click OK

  6. The chosen Autoformat style should be applied to the selected data.

If none of the styles are quite to your liking, you can modify them either before or after they have been applied to your worksheet.

Modify an Autoformat Style before Applying

To modify a style before applying it:

  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 before applying the style in order to deactivate them.

  3. The example in the dialog box will show you what the style will look like with the different options deselected.

  4. Click OK to apply the modified style.

Modify an Autoformat Style after Applying

Once you have applied a style you can modify it further using Excel’s regular formatting options located mostly on the Home tab of the ribbon.

Many people use the Autoformat options as a starting point and then use the regular formatting options to fine-tune their selection to meets their needs.

©2014 About.com. All rights reserved.