Auto Format in Excel (Table of Contents)
AutoFormat Feature in Excel
In order to make formatting decisions quicker for the data typed by a user in excel, AutoFormat feature is provided in excel. AutoFormat helps the user to format their data easily and present it in a meaningful way by giving the selected data on the spreadsheet a professional look. Formatting is not only done to make your work look good, but it also affects six major components of formatting from the 17 pre-defined AutoFormat styles available in excel. These six major formatting components are as follows:
- Number Formatting
- Borders
- Fonts Style
- Patterns and background color
- Text Alignment
- Column and Row size
Adding AutoFormat in Excel
AutoFormat option was available in the previous versions of Excel. In the versions of 2007 excel and onwards, this option needs to be added manually, as it is not present in any of the tabs option or on the Quick Access Toolbar. The steps to be followed to add AutoFormat function in excel are as follows:
- Right click on any of the tab available or shown on the Quick Access Toolbar.
- From the options shown, click on the ‘Customize Quick Access Toolbar’.After clicking on the ‘Customize Quick Access Toolbar’ option, an excel option dialog box will appear. Click on the drop down of ‘Choose commands from’ option.
- From the options dropped down, click on ‘All Commands’.
- After selecting all commands, another list will drop down. From this list scroll down and select ‘AutoFormat’ and click on Add button.
- After adding the AutoFormat option. Click on OK.
- An Auto format option is seen at the top of Excel.
How to Use Auto Format in Excel?
This AutoFormat is very simple easy to use. Let us now see how to use the AutoFormat in Excel with the help of some examples.
Example #1 – Auto Format in Excel
Consider the data table shown below which shows the quarterly revenue in USD Million of few companies.
In order to use the AutoFormat function follow the below steps:
- Select the entire data as shown.
- After selecting the entire data click on the AutoFormat option.
- An autoformat dialog box will appear which will display 16 different formatting styles.
- Select any formatting style, Click OK.
The output is given below:-
Example #2 – Auto Format in Excel
How to Modify the Formatting Design in AutoFormat?
In order to modify the formatted design in AutoFormat, there are only 6 limited formatting components as mentioned earlier, which can be added or removed with a combination of 6 different components or single as well.
Follow the below steps to modify the formatting design while using AutoFormat function.
- Select your data and click on ‘AutoFormat’ option.
- An autoformat dialog box will appear. From that dialog box click on ‘Options’.
- Add or delete the formatting design options as per your preference which you want to apply for your selected data and Click OK.
In the above figure, alignment and width/height formatting components are not ticked and number, font, border, and patterns are selected. And the selected formatting style is highlighted as well.
Auto Format in Excel
How to remove the formatting done to your data set?
The formatting done to your dataset can be removed by using the AutoFormat function. There are two ways to do this, but it completely depends on your choice where you need to select which formatting needs to be removed.
The two ways or method to this is as follows:
- Removing individual formatting components.
For example consider the below figure, which shows where the pattern formatting option was chosen to be removed.
In the above figure, it can be seen that the highlighted formatting style is selected from the list of 16 different formatting styles and ‘Patterns’ formatting option is removed.
The figure above shows the effect of removing pattern formatting option.
- Removing all the formatting components.
You can also remove all the formatting components by selecting the entire data set and later clicking on AutoFormat option and out of the 16 different formatting styles select ‘None’ formatting style option. The figure will illustrate this step.
The figure above shows the highlighted formatted style is the selected formatted style, which removes all the formatting components.
In this figure, all the formatting is removed and we get the original data set back.
Limitations of AutoFormat in Excel
There are many other formatting/design options available in the ribbon of excel toolbar, hence the utilization of AutoFormat function is reduced.
Things to be Remember
- It will ignore the formatting when you try to perform formatting on the data set which is already formatted, it will apply the new formatting which was selected by you.
- There should be at least two cells with data to perform the AutoFormat function.
- The data table set should have one header row and header column.
Recommended Articles
This has been a guide on Auto Format in excel. Here we discuss how to Auto Format in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
