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
Ok, follow the below steps to unhide the cool option to start using it.
- Step 1: Click on the File tab.
- Step 2: Now click on Options.
- Step 3: Now click on Quick Access Toolbar
- Step 4: Now select Command Not in the Ribbon option from the drop-down list.
- Step 5: Now search for the AutoFormat option.
- Step 6: Now click on Add and Ok.
- Step 7: Now it is appearing 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 just unhide this option)
- Step 3: Now this will open up below 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 the lot better now than the earlier plain data.
Note: We can change the formatting at any point in time by just 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 do very limited 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 Options…
- 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 are removing all the existing formatting. Because it cannot recognize the existing formatting.
- We need a bare minimum of two cells to apply AutoFormat.
- We have a total of 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 –