Formatting is a tedious task.
Itβs really hard to format your data whenever you present it to someone. But, this is the only thing that makes your data more meaning full, and easy to consume.
It would be great if you have an option that you can use to format your data without spending much time. So, when it comes to Excel, you have an amazing option that can help you to format your data in no time.
Its name is AUTO FORMAT. In auto-format, you have several pre-designed formats which you can apply to 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
In this post, you will learn about this amazing option that can help you to save a ton of time in the coming days.
Quick Note: Itβs one of those Excel Tricks that can help to get better at Basic Excel Skills. So, letβs get startedβ¦
Where to Find AUTO FORMAT Option?
If you check Excel 2003 version, the 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 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 the 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 endpoint of the toolbar.
- And, when you click on it, you will get a drop-down menu.
- From this menu, click on βMore Commandsβ.
- Once you click on it, it will open βCustomize the Quick Access Toolbarβ in the excel options.
- From here, click on the βChoose commands fromβ drop-down and select βCommands Not in the Ribbonβ.
- After that, come to the list of commands you have right below this drop-down.
- And, select the βAuto Formatβ option and add it to the quick access toolbar.
- Click OK.
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.
Download this file from here to follow along and follow the below steps to format the table.
- Select any of the cells from your data.
- Go to the quick access toolbar and click on the auto-format button.
- Now, you have a window, where you have different data formats.
- Select one of them and click OK.
- Once you click OK, it will instantly apply your chosen format to the data.
How To Modify a Format in Auto Format
As I mentioned 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 them.
Letβs say, you want to add formatting on the below data table but without changing its font style and column width. You need to apply formatting using the below steps.
- Select your data and click on auto-format button.
- Select a format to apply and click on the βOptionsβ button.
- From options, un-tick βFontβ and βWidth/Heightβ.
- And, click OK.
Now, both of the components are 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.
- Select it and click OK.
Important Points
- The auto format is not able to recognize if you already have some formatting on your data. It will ignore 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
An auto format is a time- saver tool when you donβt want to put your time and effort into 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 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 time?
Please share your views in the comment box. I would love to hear from you.
Must Read Next
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ο¬nd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.
Very useful and very good narration.
yes ofcourse auto-formating is a very good thing . it gives instant designs and also saves the time.
Time Saver Hacks π
Very useful for accountants
Im a little confused. Why is Auto format is used for, and why is it important?
quick formatting.
Thank you Puneet for the answer
π
Brilliant !!
Hey Zahid, Thnaks for your words.