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

Get FREE Advanced Excel Exercises with Solutions!

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. Overview of excel auto formatting

Download Practice Workbook

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.

Adding excel auto formatting

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

Selecting auto format from ribbon

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”.

Dataset for adding auto format

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

Adding auto formatting

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

Selecting autoformatting

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

After adding auto format

How to Customize the Formatting After Adding AutoFormat Options

Here we have used Autoformat options to customize our formatting.

  • First, select Options… from AutoFormat.

Selecting option for customization

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

Add customization

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.

Removing auto formatting

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

Get non option for removing formatting

  • Now we have got the dataset without any formatting.

After removal formatting

Read More: [Fixed!] Excel Changing Dates to Random Numbers

2. Remove Other’s Automatic Formatting

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


Read More: Stop Excel From Changing Numbers to Dates

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.

Table formatting

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.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Joyanta Mitra
Joyanta Mitra

Joyanta Mitra, a BSc graduate in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, has dedicated over a year to the ExcelDemy project. Specializing in programming, he has authored and modified 60 articles, predominantly focusing on Power Query and VBA (Visual Basic for Applications). His expertise in VBA programming is evident through the substantial body of work he has contributed, showcasing a deep understanding of Excel automation, and enhancing the ExcelDemy project's resources with valuable... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF