Excel Auto Formatting (Add, Customize, Use & Remove) -

In this article, we are going to explore how to use auto formatting in Excel and how we can modify and remove the formatting in Excel. We have used Excel autoformatting feature, Excel autoformat options and used Format as Table Option. Moreover, we will show how to undo formatting to get previous format.

Note: To make this article we have used Microsoft 365 but concepts are okay with all Excel version.

Excel’s auto-formatting feature is one of the most commonly used tools that helps us make our data look better without much effort. It lets us quickly apply different styles and designs to our tables, cells, or entire worksheets. With just a few clicks, we can change things like fonts, colors, borders, and shading to create a visually appealing and organized look. The best part is that if we add or change data later, the formatting automatically adjusts to match, saving time and keeping everything consistent.

Download Practice Workbook

Excel autoformat.xlsx

What Is Auto Formatting in Excel?

Auto formatting in Excel is a feature that lets you quickly make your data look nice. It applies ready-made styles to tables, cells, or whole worksheets. You can choose from different fonts, colors, borders, shading, and number formats to make your data easier to understand. The formatting automatically adjusts when you add or change data, so everything stays consistent without you having to do it manually. There are different types of auto formatting available in Excel. They are

AutoFormat
AutoFitRow
AutoFitColumn
AutoCorrect
Format as Table

Here, we’ll discuss the AutoFormat feature.

How to Add AutoFormat Option from Quick Access Toolbar in Excel

Here we have to add the AutoFormat option to Quick Acces Toolbar so that we can use Autoforamt option easily.

First, we have to select AutoFormat. So we have pressed Alt + F + T to get Excel Options.
Next, choose Quick Access Toolbar >> All Commands >> AutoFormat >> Add and
Then press OK.

Now, you will get the AutoFormat option in Excel Ribon.

How to Use AutoFormat Feature in Excel: Practical Example

Here we have given an example of how we can use autoformat feature.

Here, we have given an example dataset of “Sales of Bytes Mobile”.

Then, you have to select the dataset and pick the Autoformat

Then, after selecting the AutoFormat option from Excel, select any type of formatting you like for your data.

After selecting formatting, you’ll get the following output. You can use any formatting you like.

How to Customize the Formatting After Adding AutoFormat Options

Here we have used Autoformat options to customize our formatting.

First, select Options… from AutoFormat.

Then, we can add any options we want to create our formatting of our datatable.

Read More: Stop Auto-Formatting Numbers

Undo Auto Formatting in Excel

1. How to Remove AutoFormat Feature

In this section, we will show how we can remove the autoformatting in Excel. Here, we have used both keyboard shorcut and Autoformat option.

The keyboard shortcut for removing auto formatting is Alt + H + E + F. You can easily remove auto formatting.

But the best option is to select None from AutoFormat window to remove auto formatting from your dataset.

Now we have got the dataset without any formatting.

2. Remove Other’s Automatic Formatting

We have to use the keyboard shortcut Alt + F + T and then select Proofing >> AutoCorrect Options…

Format as Table Option: Alternative to AutoFormat Option

By default, you won’t get the AutoFormat option in the Excel ribbon. You have to add it manually. But you’ll get another alternative of the feature i.e. Format as Table in the Home tab.

Here, we formatted the dataset as a table. But in the previous example, the dataset did not turn into a table. Previously, it just changed the format of the dataset. But now this autoformatting turns the dataset into a table.

Read More: Stop AutoCorrect for Dates

Things to Remember

Selection of dataset: You should select dataset before going to AutoFormatting window.
Updates and changes: If your data changes or you add new data, remember to update the formatting rules accordingly. Auto-formatting is not dynamic by default, so you may need to adjust the rules or apply them again to include the changes.

Frequently Asked Questions

1. Can I copy auto-formatting from one range to another in Excel?

Yes, you can copy auto-formatting from one range to another in Excel. Select the range that has the desired formatting, go to the “Home” tab, click on the “Format Painter” button in the “Clipboard” group, and then click and drag over the range where you want to apply the formatting.

2. Is there a way to preview auto-formatting before applying it?

Yes, you can preview auto-formatting before applying it in Excel. When you are in the “Format as Table” or “Conditional Formatting” dialog box, there is usually a preview section that shows how the formatting will look based on the selected options. You can make adjustments and see the preview in real time.

3. Can I create my own custom auto-formatting styles in Excel?

While Excel does not provide a direct way to create custom auto-formatting styles, you can achieve similar results by combining conditional formatting rules. By defining specific conditions and formatting options in conditional formatting, you can create your own custom formatting styles that automatically apply to your data.

4. Why does Excel automatically format cells?

Excel automatically formats cells to make the data look better and easier to understand. It saves you time by applying styles automatically instead of doing it manually. The formatting stays consistent even if the data changes. It makes your spreadsheet look professional and organized.

Conclusion

In conclusion, Excel’s auto-formatting feature is a valuable tool that empowers users to enhance the appearance and organization of their data effortlessly. By applying predefined formatting styles or creating custom formats, users can transform raw data into visually appealing tables with just a few clicks. The dynamic nature of auto-formatting ensures that the formatting adapts automatically as data changes, maintaining consistency and saving time.

Excel Auto Formatting: Knowledge Hub

<< Go Back to Excel Cell Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Excel Auto Formatting (Add, Customize, Use & Remove)