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.
- Click on the File tab.
- Now click on Options.
- 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 needed.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.
- Now select Command Not in the Ribbon option from the drop-down list.
- Now search for the AutoFormat option.
- Now click on Add and Ok.
- Now, it appears in the Quick Access Toolbar.
Now we have unhide the AutoFormat option.
How to use AutoFormat Option in Excel? (with Examples)
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)
- Step 3: Now, this will open up below the 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.
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.
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.
- Step 2: Click on AutoFormat and click on Option.
- 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.
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.
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.
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.
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 –