Contents
The appearance of the Excel spreadsheet greatly contributes to the good perception of the spreadsheet. Also, formatting makes it possible to make it more functional. By using the background color, font features, font size and other table appearance options, data analysis becomes much easier and also makes the table look more professional.
At the same time, fine-tuning table formatting elements can take a lot of time and effort. To solve this problem, there is an auto-formatting feature, which we will talk about in more detail now.
Auto Format in Excel – what can be useful for
So, auto-format in Excel is the ability to automate the settings for the appearance of a table using predefined styles. All that remains for a person to do is to choose the appropriate appearance, and the program will do the rest for him. Also, the user can change those styles that have been preset or add their own in order to make certain customizations for the branding of the spreadsheet.
It is also possible to change the style after it has been applied, which gives additional possibilities.
Formatting areas
What specific settings can an Excel user automate? By default, Excel has 17 preset styles, each of which affects 6 areas:
- Number formatting.
- Border setting.
- Font options.
- Background color and various patterns.
- Centering.
- Size of rows and columns.
The user can also change these parameters arbitrarily. But you can use the preset ones and do nothing else. In 80% of cases this is enough. So what should be done for this?
How to add AutoFormat in Excel to the Quick Access Toolbar
To use autoformat, you first need to add its icon to the quick access toolbar. It is enough to do it once, and in the future you need to refer to it in order to apply this function. After completing these steps, the user will be able to find it on the Quick Access Toolbar. What actions need to be taken?
- Find the arrow on the Quick Access Toolbar and click on it.
- Then you need to go to the “More Commands” menu to open a dialog box with the settings for the Quick Access Toolbar.
- We are interested in the item “Select commands from”.
- Then, to show all possible commands of the program, you need to select the “All commands” item.
- Then we find the item “AutoFormat” in the alphabetical list.
- Next, add this item to the toolbar using the corresponding button. In the screenshot, it is highlighted with a green rectangle.
Finally, you need to confirm your actions by pressing the OK key.
How to apply an autoformat style to a table
After the auto-formatting icon has been added to the quick access toolbar, we need to select the data we want to change the appearance of. 
After that, we find the auto-format button on the quick access toolbar, and in the dialog box that appears, select the style that we need. After that we close the dialog box and our style is automatically applied to the table.
If you suddenly decide to change the style to another one that is already on the list, then the entire procedure described above must be repeated.
Change AutoFormat Style Before Applying It
But what to do if you do not like any of the styles that the program currently offers? In this case, you can edit it. To do this, open the “AutoFormat” dialog box and select the “Options” item there. 
In the window that opens, uncheck the boxes next to those formats that you do not want to edit in the table. 
This dialog box provides a preview that allows you to see how the range will look after making changes. To apply the style according to your wishes, you need to click on the “OK” button.
Changing an AutoFormat Style After Applying It
In some situations, the user is faced with the fact that he does not like the style that he applied to the table. If he has already used it, then you need to make changes manually, using standard formatting tools, and then save it in the list of styles in order to use it later in other documents and sheets.
In simple words, the steps for changing the auto-formatting style after applying it and creating a new style are exactly the same. What needs to be done?
- Use the Home tab on the ribbon. Before that, you need to select any cell whose formatting we will change. Or range. After that, go to the “Home” tab and select “Format as Table”. Then click on the “New Table Style” button.
- After that, a dialog box will open in which you need to select the table element with which we will work, and then click on the “Format” button to adjust the font, fill, borders, and so on. The same actions must be performed in relation to any other element of the table.
- Then check the box next to “Set as the default table style for this document” if you want this style to be automatically applied to tables, and then click OK to confirm the changes.
- To apply your own style, you need to select the table, go to the “Home” tab, click on “Format as Table” and select the appropriate custom style.
As you can see, you can save a huge amount of time if you use the auto-formatting feature in Excel. This will help to process the data even more quickly compared to previous times, since the program will highlight the key elements on its own. It remains only to enter data. There are several other formatting automation tools in Excel, such as Conditional Formatting. And all of them together are capable of giving you superpower. Don’t miss this opportunity.