AutoFormat in Excel

Last Updated :

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

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.

AutoFormat Option in Excel

7 Easy Steps to Unhide the AutoFormat Option

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

Click on the "File" tab.
Next, click on "Options."
Now, click on "Quick Access Toolbar."
Select the "Command Not in the Ribbon" option from the drop-down list.
Now, search for the "AutoFormat" option.
Then, click on "Add" and "Ok".
Now, it appears in the "Quick Access Toolbar."

Now, we have to unhide the "AutoFormat" option.

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

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).

Step 3: This will open the dialog box, as shown below.

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."

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.

Step 2: We will click on "AutoFormat" and "Options."

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.

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.