Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

Excel’s AUTO FORMAT Option

Formatting is a tedious task. It’s really hard to format your data every time when you present it to someone.

But, this is the only thing which makes your data more meaning full and easy to consume.

It would be great if you have an option which you can use to format your data without spending much time.

So, when it comes to Excel, you have an amazing option which can help you to format your data in no time.

Its name is AUTO FORMAT.

In auto format, you have numbers of pre-designed formats which you can apply on your data instantly.

All you have to do just select a format and click OK to apply. It’s simple and easy.

In all the pre-designed formats you have all the important components of formatting, like:

  • Number formatting
  • Borders
  • Fonts Style
  • Patterns and Background color
  • Text Alignment
  • Column and Row size

So today, in this post, you will learn about this amazing option which can help you to save your ton of time in coming days.

Where to Find Auto Format Option?

If you check Excel 2003 version, auto format option is there on the menu. But, with the release of 2007 with ribbon this option is not available in any of the tabs.

That doesn’t mean you can’t use it in earlier versions. It’s still there, but hidden. So, to use it in the Excel versions like 2007, 2010, 2013, and 2016 you need to add it to your Excel’s quick access toolbar.

It’s a one-time setup so you don’t have to do it again and again.

Add Auto Format on Quick Access Toolbar

To add auto format to your quick access toolbar please follow these steps.

  • First of all, go to your quick access toolbar and click on the small down arrow from the end point of the toolbar.
click drop down to activate auto format in excel
  • And, when you click on it, you will get a drop down menu.
click more commands to activate auto format in excel
  • From this menu, click on “More Commands”.
click more commands to activate auto format in excel
  • Once you click on it, it will open “Customize the Quick Access Toolbar” in excel options.
  • From here, click on “Choose commands from” drop-down and select “Commands Not in the Ribbon”.
select commands not in the ribbon to add auto format in excel quick access toolbar
  • After that, come to the list of commands you have right below this drop-down.
  • And, select “Auto Format” option and add it to quick access toolbar.
select from auto format command in excel to add to quick access tool bar
  • Click OK.
auto format button in excel quick access toolbar

Now, you have the auto format icon in your quick access toolbar.

How to Use Auto Format?

Applying formats with auto format option is super simple. Let’s say you want to format below data table.

data table to use auto format in excel

Download this file from here to follow along and follow below steps to format the table.

  • Select any of the cells from your data.
select any cell from data to apply formatting with auto format in excel
  • Go to quick access toolbar and click on auto format button.
click on auto format buttton to add formatting on data table
  • Now, you have a window, where you have different data formats.
  • Select one of them and click OK.
select format and click ok to apply formatting with auto format in excel
  • Once you click OK, it will instantly apply your chosen format on the data.
formatted table with auto format in excel

How To Modify a Format in Auto Format

As I mention above, all the formats in the auto format are a combination of 6 different components. And, you can also add or remove these components from each format before applying it.

Let’s say, you want to add formatting on below data table but without changing its font style and column width.

You need to apply formatting using below steps.

  • Select your data and click on auto format button.
  • Select a format to apply and click on “Options” button.
  • From options, un-tick “Font” and “Width/Height”.
modify format in auto format in excel
  • And, click OK.
format without font style and width with auto format

Now, both of the components is not there in your formatting.

Removing Formatting

Well, to remove formatting from data the best way is to use a shortcut key Alt + H + E + F. But you can also use the auto format option to remove formatting from your data.

  • Select your entire data and open auto format.
  • Go to last in the format list where you have a “None” format.
remove format with auto format
  • Select it and click OK.

Important Points

  • Auto format is not able to recognize if you already have some formatting on your data. It will ignore it and apply new formatting as per the format you have selected.
  • You need at least 2 cells to apply formatting with auto format option.

Conclusion

Auto format is a time saver tool when you don’t want to put your time and efforts on formatting you can use it.

It’s quick and simple. It’s all 16 formats are suitable for different types of data, from accounting to list, tables to reports.

I hope it will help you save your a lot of time in formatting.

Now, I have a question for you. Have you ever used this tool before? What do you think about it? Do you think it will help you to save your time? Please share your views in the comment box. I would love to hear from you.

And, please don't forget to share with me in the comment section.

Must Read Next

  1. Custom Date Formats in Excel
  2. How to Use Undo & Redo Buttons in Excel
  3. How to Add Different Formatting in a Cell in Excel
  4. How to Apply Strikethrough in Excel
  5. Insert Checkmark in Excel