Auto Format In Excel

What Is Auto Format In Excel?

The Auto Format in Excel is a feature that enables one to format tabular data by applying preset formats. And Excel allows formatting data elements such as numbers, borders, font styles, patterns, text alignments, and column or row sizes.

Users can use the AutoFormat option to quickly make multiple formatting changes to their lists, tables, or accounting data in Excel in one go.

For example, the below table appears with minimal formatting.

Then, as per the Auto Format in Excel definition, we can use the AutoFormat option from the Quick Access Toolbar and apply the necessary formatting.

Click on a cell in the table and then, select the highlighted option in the Quick Access Toolbar. It will open the Auto Format dialog box in Excel.

And then, we can pick the best-suitable predesigned formatting style and the elements we aim to format from the AutoFormat window.

Thus, now the tabular dataset appears more presentable and understandable.

Explanation and Uses

Sometimes we might require to format datasets in a specific style. But choosing multiple options one at a time from the Excel Ribbon can be tedious, especially when we require the data to look professional.

Instead, we can use the AutoFormat feature in Excel and pick the appropriate formatting style from over ten predesigned formats that best fits your requirement. So, while we make our data more presentable, readable, and professional, we can save significant time.

The uses of the AutoFormat feature in Excel are as follows:

• We can format accounting data, lists, tables, and reports to achieve a spreadsheet with a professional appeal.
• Similarly, we can include 3D effect styles by formatting spreadsheet data.
• We can also format the given dataset’s elements, such as numbers, fonts, and borders, in one go.

How To Unhide The Auto Format In Excel?

The below image shows the AutoFormat icon not available in the Quick Access Toolbar.

So, the steps to unhide the AutoFormat feature in Excel are as follows:

• Step 1: Click File → Options to open the Excel Options window.

[Alternatively, we can right-click on the Ribbon and pick Customize Quick Access Toolbar.

It will open the Quick Access Toolbar tab in the Excel Options window.]

• Step 2: Click on Quick Access Toolbar from the menu and set the left field as Commands Not in The Ribbon.

• Step 3: Pick AutoFormat from the list and click Add.

• Step 4: Click OK to view the AutoFormat button in the Quick Access Toolbar.

We can now click the AutoFormat icon in the Quick Access Toolbar to apply the required format to our data.

How To Use Auto Format In Excel?

We can use the AutoFormat option in Excel from the Quick Access Toolbar.

Click on a cell in the given table we require to format → click the AutoFormat icon in the Quick Access Toolbar.

The Auto Format dialog box in Excel will open, where we must pick the required format.

Further, clicking on Options will allow us to pick the elements to customize the predefined formats according to our requirements.

Finally, click OK to view the formatted data as per the chosen format design.

The following example will help us understand the Auto Format in Excel and the above steps to use it effectively.

The table below shows a list of fruits and their month-wise quantities.

Examples

The below illustrations show the practical ways of using the AutoFormat feature in Excel.

Example #1

The table below shows the per-day stock prices of four firms.

Here is how to format the above table using the AutoFormat option in Excel.

• Step 1: Choose a cell in the table and click the AutoFormat icon in the Quick Access Toolbar to open the AutoFormat window.

• Step 2: We shall pick the List 2 option from the predesigned formats in the AutoFormat window.

Clicking OK will result in the following formatted table.

In the above example, we do not see row 6 cells with the top cell border, as shown in the selected format preview in the AutoFormat window. The reason is that cell range B6:D6 do not contain formulas. Instead, they contain values that lead to the corresponding cells displayed without the top border.

But, suppose we wish to change a few formatting aspects in the above table, such as making the cell color in all the rows from 2 to 5 White. And then, change the row 1 cell color to Teal Green and set the cell borders on all sides. And finally, ensure cell A6 has the same cell format as row 1 for uniformity.

Then, the steps are as follows:

• Step 3: Click row 4 and select Home → Format Painter.

And then, drag the mouse over rows 2:3 to apply the row 4 format.

Next, select cell range A1:D1 and click Home → Fill Color → Pick the required color.

Next, keeping cells in the range A1:D1 selected, click Home → Borders → All Borders.

And then, select cell A1 and click Home → Format Painter.

And click cell A6 to apply cell A1 format and achieve the formatted table, as shown below.

So, the final format highlights the column headings and the average values, thus making the table more meaningful and easier to interpret.

Example #2

The following illustration explains how to change Auto Format in Excel.

The table below shows the yearly revenue figures for three branch offices of a firm.

Suppose the requirement is to update the table format. Then, here is how to use the AutoFormat option to apply an appropriate predefined format to the above table.

• Step 1: Select a cell in the above table and click the AutoFormat icon in the Quick Access Toolbar to open the AutoFormat window.

• Step 2: Assume the Classic 3 format fits the requirement. Then, select the option.

But suppose we wish to modify or change Auto Format in Excel option we picked. Then, click on Options while keeping the chosen format style selected.

Clicking Options will show a list of format elements already selected, from which we can choose the ones we wish to format in our data table.

For example, if we wish to format numbers, border, alignment, and column or row sizes. Then, check the boxes of the corresponding format options.

We will observe the predefined format previews changing accordingly. And it will help us decide whether the final design of the selected format meets our requirements.

Finally, clicking OK will result in the formatted table shown below.

So, the column headings retain the existing format, and we see the table and row 5 with thick outside borders, as shown in the chosen modified format preview.

Example #3

We shall see an example of removing individual format elements and the entire auto format applied to a given data.

The table below shows the salary hikes of a list of employees in a company.

Suppose the requirement is to format the above table. But we do not want to format the numbers and do not require the borders. Then, we can use the AutoFormat functionality and apply the customization options to achieve the required formatting.

• Step 1: Choose a cell in the above table and click the AutoFormat icon in the Quick Access Toolbar to open the AutoFormat window.

• Step 2: Assume we pick the Colorful 1 option in the AutoFormat window.

But we do not have to format the numbers and do not require the borders. Thus, we shall click on Options to remove the elements we do not wish to format.

• Step 3: Clicking Options will show the elements to format. Uncheck the Number and Border format options.

Clicking OK will result in the below table without formatted numbers and borders.

Next, if we require to remove Auto Format in Excel entirely, the steps are as follows:

• Step 4: Select a cell in the formatted table and click the AutoFormat button in the Quick Access Toolbar to open the AutoFormat window.

And pick the last option, None, in the AutoFormat window to remove Auto Format in Excel entirely.

And clicking OK in the AutoFormat window will result in a table without the applied format.

Important Things To Note

• Ensure the source data contains at least two cells and click on a cell in the given dataset for the option Auto Format in Excel to work.
• The row displaying values such as total and average must contain the corresponding formulas to ensure its cells show the top border when using the AutoFormat option.
• The AutoFormat option, with all the format elements selected, overrides the existing format to show the data in the chosen style.
• Applying the None AutoFormat option from the AutoFormat window will not result in the data in the original format. It will show the dataset without any format.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Auto Format In Excel, with its features and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Auto Format In Excel. Here we learn how to use, remove, unhide & hide auto format option, with examples & downloadable excel template. You can learn more from the following articles – 

• Conditional Formatting in Excel
• Date Format In Excel
• Quick Analysis Tools In Excel