Excel AutoFormat – A One Click Data Formatting Trick

Do you spend a lot of time formatting data in Excel?

If yes, then you may find the AutoFormat option useful in speeding up your formatting work. It allows you to quickly apply a preset formatting on a data set that has one header row and one header column.

It allows you to quickly apply a preset formatting on a data set that has one header row and one header column.

So you quickly get a result as shown below:

Never heard of it? Don’t worry – I will tell you all about it in this tutorial.

Where is the AutoFormat in Excel?

If you can’t find the AutoFormat option in Excel, that is because it’s not already available in the ribbon or the Quick Access Toolbar (QAT).

You will have to manually add it to the QAT.

Here are the steps to add the AutoFormat option to the QAT:

• Right-click on any of the existing icons on the QAT.
• Click on ‘Customize the Quick Access Toolbar’ option.
• In the Excel Options dialog box that opens, in the ‘Choose commands from’ drop down, select All Commands.
• In the list of commands, scroll down and select AutoFormat.
• Click on the Add button. This will add the AutoFormat option to the list of icons already available in QAT.
• Click OK.

The above steps would add the AutoFormat icon to the Quick Access Toolbar (as shown below).

Note that this is a one time exercise only. Once it is added to the QAT, you can use it anytime with a single click.

Using AutoFormat Option to Quickly Format Data

AutoFormat option is suitable for data that has the first row and the first column as headers.

Something as shown below:

Here are the steps to format this data using the AutoFormat option:

• Select the entire data set.
• Click on the AutoFormat icon in the Quick Access Toolbar.
• In the dialog box, you will find 16 different kinds of formatting design. Select the one you want to apply.
• Click OK.

This will instantly apply the selected formatting to the data set.

Note that you can change the formatting after you have used the AutoFormat design. For example, if you don’t like the color of the headers, you can change it with any other color.

Also, if you already have some formatting applied to the dataset, it will be overridden. For example, if you have the header cells in red color and you select a format with a blue header, it will override the red headers and apply the blue color to the cells.

Modifying the Formatting Design in AutoFormat

There are some limited modifications that you can do in the formatting design when using the AutoFormat options.

Below are the six types of formats that you can enable/disable when using AutoFormat:

• Number Formatting
• Border
• Font
• Patterns
• Alignment
• Width/Height

Here are the steps to modify the formats when using AutoFormat:

• Click on the AutoFormat option in the QAT.
• In the dialog box, click on the ‘Options’ button.
• Select/deselect the formatting design options that you want to be applied to the data set. Note that as soon as you select/deselect an option, a live preview will be available for all the designs in the dialog box.
• Click OK when done.

Removing the Formatting from the Dataset

You can easily remove the formatting after you have applied it using the AutoFormat option.

There are two ways to do this:

• Remove individual elements using the inbuilt options. For example, if you only want to remove the borders, use the borders options to remove it.
• Remove the entire formatting. You can do this by selecting the last design in the Autoformat dialog box (as shown below).

Limitations of AutoFormat Option in Excel

This option has been available for a long time and its utility has declined significantly when table options more refined formatting/design options became available in the ribbon in Excel.

I recommend using it only when your data has a certain structure to it (has headers rows and columns). If your data is not structured this way, I recommend you use individual formatting options.

Also, you can not modify a particular formatting option with AutoFormat. For example, if you want the border to be thick or dashed, you can’t do that.

You May Also Like the Following Excel Tutorials:

• Use Format Painter in Excel to Quickly Copy Formatting.
• How to Remove Cell Formatting in Excel
• How to Calculate and Format Percentages in Excel.
• Using Conditional Formatting in Excel.
• How to Create a Heat Map in Excel.
• How to Apply Conditional Formatting in a Pivot Table in Excel.