AutoFormat in Excel

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

AutoFormat Option in Excel

The AutoFormat option in Excel is a unique way of formatting data quickly. The first step is to select the entire data we need to format. Then the second step, we need to click on “AutoFormat” from the “Quick Access Toolbar.” Lastly, we need to choose the “Format” from the different options in the third step.

For example, suppose you have a dataset and do not like the available styles. In that case, you may modify them before applying them to your worksheet and create a professional and clean worksheet while altering your Microsoft Excel spreadsheet’s readability and saving time.

7 Easy Steps to Unhide the AutoFormat Option

Follow the below steps to unhide the cool option to start using it.

  1. Click on the “File” tab.

    Auto Format Option step 1

  2. Next, click on “Options.”

    Auto Format Option step 2

  3. Now, click on “Quick Access Toolbar.”Quick Access Toolbar (QAT) is a toolbar in Excel that may be customized and is located on the upper left-hand side of the window. It enables users to save important shortcuts and easily access them when moreQuick Access Toolbar (QAT) is a toolbar in Excel that may be customized and is located on the upper left-hand side of the window. It enables users to save important shortcuts and easily access them when more

    Auto Format Option step 3

  4. Select the “Command Not in the Ribbon” option from the drop-down list.

    Auto Format Option step 4

  5. Now, search for the “AutoFormat” option.

    Auto Format Option step 5

  6. Then, click on “Add” and “Ok”.

    Auto Format Option step 6

  7. Now, it appears in the “Quick Access Toolbar.”

    Auto Format Option step 7

    Now, we have to unhide the “AutoFormat” option.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to use the AutoFormat Option in Excel? (with Examples)

You can download this Auto Format Excel – Template here – Auto Format Excel – Template

Example #1

Applying format to our data is faster than the normally tedious, time-consuming formatting. For example, suppose we have data, as shown below.


We have headings in the first row and a total of each column in the 6th row.

This format looks unprofessional, ugly, plain data, etc. Whatever we call them but do not look good to watch now.

Here are the steps required to apply the “AutoFormat option” and make the data look treatable to watch.

  • Step 1: First, we will place a cursor in any data cell.
  • Step 2: Then, we will click on the “AutoFormat” option in the “Quick Access Toolbar.” (We will unhide this option).
Quick Access Toolbar - Auto Format
  • Step 3: This will open the dialog box, as shown below.
auto format dialogue box
  • Step 4: Here, we have 17 different kinds of pre-designed format options (one is for removing formatting). We will select the suitable format from them according to our taste and click “OK.”
Select the suitable format

Wow! Now our format looks a lot better than the earlier plain data.

Note: We can change the formatting at any time by selecting the different format styles in the “AutoFormat” option.

Example #2

All the formats are a set of 6 different format options. We have limited control over these formatting options.

We can make minimal modifications to this formatting. If needed, we can also customize this formatting.

The six formatting options are “Number Formatting,” “Border,” “Font,” “Patterns,” “Alignments,” and “Width/Weight.”

  • Step 1: Initially, we will need to select the formatted data.
Select the formatted data
  • Step 2: We will click on “AutoFormat” and “Options.”
Click on AutoFormat
  • Step 3: This will show us all 6 six formatting options. Here, we can select and deselect formatting options. Then, the live preview will happen according to our changes.
AutoFormat Design

We have unchecked the “Border” format option in the above table. Look at all the format options. We can see that the border format has disappeared for all the formats. Similarly, we can check and uncheck boxes according to our wishes.

Example #3

Like applying “AutoFormat” in Excel, we can remove those formatting by clicking a button.

  • Step 1 – We select the data, click on “AutoFormat,” and choose the last option.
Select the data then Click on AutoFormat

Things to Remember

  • Applying “AutoFormat” in Excel can remove all the existing formatting because it cannot recognize them.
  • We need a bare minimum of two cells to use “AutoFormat.”
  • We have 16 formatting options under “AutoFormat,” ranging from accounting to lists, tables, and reports.
  • If there are blanks in the data, “AutoFormat” restricts the formatting until the break is found.
  • We can customize all six formatting options using the “Options” method in “AutoFormat.”
  • It is probably the most underrated or underutilized technique in Excel.

Recommended Articles

This article is a guide to AutoFormat in Excel. Here, we discuss how to use AutoFormat in Excel, along with Excel examples and downloadable Excel templates. You may also look at these useful Excel tools: –