AutoFormat Option in Excel

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

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.

  • Click on the “File” tab. Next, click on “Options.” 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 needed.read 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 needed.read more 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.

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

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.

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.

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: –

  • How to use Conditional Formatting in Excel?Excel SubscriptAutofit in ExcelVBA AutofillHow to Create a Formula in Excel?