Auto Format in Excel

AutoFormat Option in Excel

AutoFormat option in excel is a unique way of formatting data quickly, the first step is we need to select the entire data we need to format and then the second step we need to click on auto-format from the QAT and third step we need to choose the format from the different options.

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. Now click on Options.

    Auto Format Option step 2

  3. Now click on Quick Access ToolbarQuick 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. Now select Command Not in the Ribbon option from the drop-down list.

    Auto Format Option step 4

  5. Now search for the AutoFormat option.

    commands not in ribbon

  6. Now click on Add and Ok.

    Excel Options

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

    Quick Access Toolbar

    Now we have unhide the AutoFormat option.

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

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

Example #1

Applying to format to your data is faster than the normal tedious, time-consuming formatting.

Suppose you have data, as shown in the below image.


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

This looks unprofessional, ugly, plain data, etc. Whatever you call but does not look treat to watch at the moment.

Here are the steps required to apply the AutoFormat option and make the data look treat to watch.

  • Step 1: Place a cursor in any cell of the data.
  • Step 2: Click on the AutoFormat option in the Quick Access Toolbar. (We unhide this option)
Quick Access Toolbar - Auto Format
  • Step 3: Now, this will open up below the dialogue box.
auto format dialogue box
  • Step 4: Here, we have a total of 17 different kinds of pre-designed format options (one is for removing formatting). Select the suitable format option according to your taste and click Ok.
Select the suitable format

Wow! Looking a lot better now than the earlier plain data.

Note: We can change the formatting at any point in 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 these formatting. If need be, we can customize this formatting.

The six types of formatting options are Number Formatting, Border, Font, Patters, Alignments, and Width/Weight.

  • Step 1: Select the formatted data first.
Select the formatted data
  • Step 2: Click on AutoFormat and click on Option.
Click on AutoFormat
  • Step 3: This will open up all the 6 six types of formatting options. Here we can select and deselect formatting options. The live preview will be going on according to your changes.
AutoFormat Design

In the above table, I have unchecked the Border format option. Look at all the format options border format is gone for all the formats. Similarly, we can check and uncheck boxes according to our wishes.

Example #3

Like how we can apply AutoFormat in excel similarly, we can remove those formatting just by the click of a button.

  • Step 1 – Select the data, then Click on AutoFormat and Select the last option.
Select the data then Click on AutoFormat

Things to Remember

  • By applying AutoFormat in excel, we remove all the existing formatting because it cannot recognize the existing formatting.
  • We need a bare minimum of two cells to apply AutoFormat.
  • We have 16 types of formatting options under AutoFormat ranging from accounting to list, tables to reports.
  • If there are blanks in the data, then AutoFormat restricts the formatting until the break is found.
  • We can customize all 6 types of formatting options using the options method in AutoFormat.
  • This is probably the most underrated or not utilized often technique in excel.

Recommended Articles

This has been a guide to AutoFormat in Excel. Here we discuss how to use AutoFormat in Excel along with excel example and downloadable excel templates. You may also look at these useful excel tools –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion