How to Auto Format Excel

Search
Generic filters

Table of Contents

How to use Auto Format Option in Excel?

Formatting is a very tedious task.

It’s really hard to format the 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 which you can use to format your data without spending much time. So, when it comes to Excel, you have an amazing option that 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.

How to 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.

home equity loan calculator

click-drop-down

  • And, when you click on it, you will get a drop-down menu.

click-more-commands

  • 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 the excel options.
  • From here, click on the “Choose commands from” drop-down and select “Commands Not in the Ribbon”.
select-commands
  • 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.
select from auto format command in excel to add to quick access tool bar
  • Click OK.
auto-format-button-in-excel

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.
formatted-table-with-auto-format-in-excel-min
select any cell from data to apply formatting with auto format in excel
  • Go to the quick access toolbar and click on the auto-format button.
auto format button
  • Now, you have a window, where you have different data formats.
  • Select one of them and click OK.
modify-format-in-auto-format-in-excel-min
  • Once you click OK, it will instantly apply your chosen format to the data.
formatted table with auto format in excel

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”.
modify format in auto format in excel
  • And, click OK.
format without font style and width with auto format

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.
remove format with auto format
  • Select it and click OK.

Important Points

  1. 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.
  2. 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.

We Create Your Life Easy With Our Free Excel Templates & Spreadsheet Dashboards

Use free Excel Templates, Tools and Dashboards to create and draft a professional looking dashboards and computation sheet for your personal and business use. You can Explore Calendars, Activity Planners, Invoice Templates, Sales Forecast Sheets, Budget templates and various business information delivering Templates in Excel and Spreadsheet at XLSX Templates. 

These templates, dashboards and tools are useful in various occasions. You can also customize these templates as per your requirement. Modification of fields and data source helps in making the template more relevant.

Learn Microsoft Excel : Blogs and Articles

Learn about various tips and tricks in Microsoft Excel and Spreadsheet. Create best templates and dashboards using free tricks and tutorials in excel and spreadsheet. These tutorial posts are useful for everyone who wants to master the skills in excel and spreadsheet.

Deselect Cells in Excel

How To Deselect Cells in Excel

Table of Contents How to Deselect Cells in Excel After adding the deselecting cells feature in Excel, this  becomes so  …

Select Non-Contiguous Cells in Excel

Learn how to Select Non-Contiguous Cells in Excel

Table of Contents Select cell without using Ctrl Key By following these simple steps we can do it. At first, …

Format Painter in Excel

How to use Format Painter in Excel

Table of Contents Where’s the Format Painter Button? It’s there on your Home Tab. If you look at the clipboard …

Apply Strikethrough in Excel [Shortcut + Command]

How to apply Strikethrough in Excel [Shortcut + Command]

Table of Contents Shortcut Key to Apply Strikethrough to a Cell Let’s say you’re in hurry and don’t want to …

Formula Bar in Excel

Formula Bar in Excel

Table of Contents Key Points To Consider Using Formula Bar You can hide or unhide the formula bar. You can …

Convert Date into Day

Convert Date into Day Sub date2day() Dim tempCell As Range Selection.Value = Selection.Value For Each tempCell In Selection If IsDate(tempCell) …

Basic VBA Macro Codes in Excel

Custom Header & Footer

Sub CustomHeader() Dim myText As String myText = InputBox(“Enter your text here”, “Enter Text”) With ActiveSheet.PageSetup .LeftHeader = “” .CenterHeader …

Get Macro Code

Add Header and Footer Date

Sub DateInHeader() With ActiveSheet.PageSetup .LeftHeader = “” .CenterHeader = “&D” .RightHeader = “” .LeftFooter = “” .CenterFooter = “” .RightFooter …

Get Macro Code

Open Calculator

Sub OpenCalculator() Application.ActivateMicrosoftApp Index:=0 End Sub In Windows, there is a specific calculator and by using this macro code you …

Get Macro Code

Unmerge Cells

Sub UnmergeCells() Selection.UnMerge End Sub Unmerge Cells code simply uses the unmerge options which you have on the HOME‌ tab. …

Get Macro Code

Remove Text Wrap

Sub RemoveTextWrap() Range(“A1”).WrapText = False End Sub This code will help you to remove text wrap from the entire worksheet …

Get Macro Code

Auto Fit Rows

Sub AutoFitRows() Cells.Select Cells.EntireRow.AutoFit End Sub You can use this code to auto-fit all the rows in a worksheet. When …

Get Macro Code

Auto Fit Columns

Sub AutoFitColumns() Cells.Select Cells.EntireColumn.AutoFit End Sub Use above Excel macro code to auto fit rows in all your sheet at …

Get Macro Code

Insert Multiple Columns

Sub InsertMultipleColumns() Dim i As Integer Dim j As Integer ActiveCell.EntireColumn.Select On Error GoTo Last i = InputBox(“Enter number of …

Get Macro Code

Insert Multiple Rows

Sub InsertMultipleRows() Dim i As Integer Dim j As Integer ActiveCell.EntireRow.Select On Error GoTo Last i = InputBox(“Enter number of …

Get Macro Code