EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Auto Format in Excel
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Auto Format in Excel

By Madhuri ThakurMadhuri Thakur

Auto Format in Excel

Auto Format in Excel (Table of Contents)

  • AutoFormat Feature in Excel
  • Adding Auto Format in Excel
  • How to Use Auto Format in Excel?

AutoFormat Feature in Excel

AutoFormat in excel is used to change the format of excel content in one go, saving good time in doing this activity multiple times. To create an autoformat setting, go to Customize Ribbon either from the File menu Option or click right on the menu bar and select Customize Ribbon option from All Commands category select AutoFormat. Once the command is add into to the list, click on AutoFormat icon from the title bar. This will show the list of all the already built format available in it upon selecting any dataset.

Formatting is not only done to make your work look good, but it also affects six major components of formatting from the 17 pre-defined AutoFormat styles available in excel. These six major formatting components are as follows:

  1. Number Formatting
  2. Borders
  3. Fonts Style
  4. Patterns and background-color
  5. Text Alignment
  6. Column and Row size

Adding AutoFormat in Excel

AutoFormat option was available in the previous versions of Excel. In the versions of 2007 excel and onwards, this option needs to be added manually, as it is not present in any of the tabs option or on the Quick Access Toolbar. The steps to be followed to add AutoFormat function in excel are as follows:

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,813 ratings)
  • Right-click on any of the tab available or shown on the Quick Access Toolbar.

Auto Format Step 1

  • From the options shown, click on the ‘Customize Quick Access Toolbar.After clicking on the ‘Customize Quick Access Toolbar’ option, an excel options dialog box will appear. Click on the drop-down of the ‘Choose commands from’ option.

Auto Format Step 2

  • From the options dropped down, click on ‘All Commands’.

Auto Format Step 3

  • After selecting all commands, another list will drop down. From this list, scroll down and select ‘AutoFormat’ and click on Add button.

Auto Format Step 4

  • After adding the AutoFormat option, click on OK.

Auto Format Step 5

  • An Auto format option is seen at the top of Excel.

Auto Format Step 6

How to Use Auto Format in Excel?

This AutoFormat is very simple easy to use. Let us now see how to use the AutoFormat in Excel with the help of some examples.

You can download this Auto Format Excel Template here – Auto Format Excel Template

Example #1

Consider the data table shown below, which shows the quarterly revenue in USD Million of few companies.

Example 1

In order to use the AutoFormat function, follow the below steps:

  • Select the entire data as shown.

Example 1-1

  • After selecting the entire data, click on the AutoFormat option.

Auto Format Example 1-2

  • An autoformat dialog box will appear, which will display 16 different formatting styles.

Auto Format Example 1-3

  • Select any formatting style; click OK.

Auto Format Example 1-4

The output is given below:-

Example 1-5

Example #2

How to Modify the Formatting Design in AutoFormat?

In order to modify the formatted design in AutoFormat, there are only 6 limited formatting components, as mentioned earlier, which can be added or removed with a combination of 6 different components or single as well.

Follow the below steps to modify the formatting design while using AutoFormat function.

  • Select your data and click on the ‘AutoFormat’ option.

Auto Format Example 2

  • An autoformat dialog box will appear. From that dialog box, click on ‘Options’.

Options Example 2-1

  • Add or delete the formatting design options as per your preference which you want to apply for your selected data, and Click OK.

Auto Format Example 2-2

In the above figure, alignment and width/height formatting components are not ticked, and number, font, border, and patterns are selected. And the selected formatting style is highlighted as well.

Auto Format Example 2-3

Auto Format in Excel

How to remove the formatting done to your data set?

The formatting done to your dataset can be removed by using the AutoFormat function. There are two ways to do this, but it completely depends on your choice where you need to select which formatting needs to be removed.

The two ways or method to this is as follows:

  • Removing individual formatting components.

For example, consider the below figure, which shows where the pattern formatting option was chosen to be removed.

Remove Formatting

The above figure shows that the highlighted formatting style is selected from the list of 16 different formatting styles, and the ‘Patterns’ formatting option is removed.

Patterns formatting option Example 3

The figure above shows the effect of removing the pattern formatting option.

  • Removing all the formatting components.

You can also remove all the formatting components by selecting the entire data set and later clicking on AutoFormat option, and out of the 16 different formatting styles, select the ‘None’ formatting style option. The figure will illustrate this step.

Auto Format Example 3-2

The figure above shows the highlighted formatted style is the selected formatted style, which removes all the formatting components.

Auto Format Example 3-3

In this figure, all the formatting is removed, and we get the original data set back.

Limitations of AutoFormat in Excel

There are many another formatting/design options available in the ribbon of the excel toolbar; hence the utilization of AutoFormat function is reduced.

Things to Remember

  • It will ignore the formatting when you try to perform formatting on the data set which is already formatted; it will apply the new formatting which you selected.
  • There should be at least two cells with data to perform the AutoFormat function.
  • The data table set should have one header row and a header column.

Recommended Articles

This has been a guide on Auto Format in excel. Here we discuss how to Auto Format in Excel along with excel examples and downloadable excel templates. You may also look at these useful functions in excel –

  1. VBA Format
  2. Excel AutoCorrect
  3. Excel Auto Numbering
  4. Excel AutoFilter
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Auto Format Excel Template

EDUCBA

Download Auto Format Excel Template

EDUCBA

डाउनलोड Auto Format Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more