Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
- Power Bi (35+)
Auto Format is by default not shown in the excel window, it needs to be enabled by customizing the ribbon of the quick access toolbar and we will find it in all commands list, once added it is available to the quick access toolbar, this is a unique way of formatting data quickly, first we need to select the entire data we need to format and then we can click on auto-format from the QAT and choose the format from the different options.
Excel AutoFormat (Table of Contents)
Auto Format in Excel
Hello folks, we spend a considerable amount of time designing our table or formatting our table to make it beautiful. Just take 3 headers and one footer as an example (as shown in the above image). To make the headers Bold, fill the background color, make the headers center alignment, insert lines to the footer and make them bold.
Huff, the lot of formatting for such a small table right?
Probably it would take easily more than a minute (just the minute but we can reduce this time by 90%) to format this simple table.
However, Excel has an inbuilt tool called AutoFormat option. Auto Format in Excel is nothing but a predetermined format for your table or data.
Ok, in this article I will take a tour to explore this option probably save 90% of your formatting time.
Let me tell you this first. In Excel 2003 and earlier versions, AutoFormat in Excel is available in the menu itself. However, in 2007 and later versions, this option is not readily available in the ribbon.
Do not be panic, it still exists man but you need to find a way to unhide this cool option to save your 90% of your time. Anyway, it is a onetime process to unhide thereafter it is just a click of the option.
How to use AutoFormat Option in Excel?
Ok, follow the below steps to unhide the cool option to start using it.
Step 1: Click on the File tab.
Step 2: Now click on Options.
Step 3: Now click on Quick Access Toolbar
Step 4: Now select Command Not in the Ribbon option from the drop-down list.
Step 5: Now search for the AutoFormat option.
Step 6: Now click on Add and Ok.
Step 7: Now it is appearing in the Quick Access Toolbar.
Now we have unhid the AutoFormat option.
In the below examples how will you show to use them practically?
Example #1 – Rely on Auto Format Option in Excel to Instantly Format the Data
Applying to format to your data is faster than the normal tedious time-consuming formatting.
Suppose you have data as shown in the below image.
We have headings in the first row and a total of each column in the 6th row.
This looks unprofessional, ugly, plain data, etc… Whatever you call but does not look treat to watch at the moment.
Here are the steps required to apply AutoFormat option and make the data look treat to watch.
Step 1: Place a cursor in any cell of the data.
Step 2: Click on the AutoFormat option in the Quick Access Toolbar. (We just unhide this option)
Step 3: Now this will open up below dialogue box.
Step 4: Here we have a totally of 17 different kinds of pre-designed format options (one is for removing formatting). Select the suitable format option according to your taste and click Ok.
Wow! Looking the lot better now than the earlier plain data.
Note: We can change the formatting at any point in time by just selecting the different format styles in the AutoFormat option.
Example #2 – Customize the AutoFormat Design in Excel
All the formats are a set of 6 different format options. We have limited control over these formatting options.
We can do very limited modifications to these formatting. If need be we can customize this formatting.
The six types of formatting options are Number Formatting, Border, Font, Patters, Alignments, and Width/Weight.
Step 1: Select the formatted data first.
Step 2: Click on AutoFormat and click on Options…
Step 3: This will open up all the 6 six types of formatting options. Here we can select and deselect formatting options. The live preview will be going on according to your changes.
In the above table, I have unchecked the Border format option. Look at all the format options border format is gone for all the formats. Similarly, we can check and uncheck boxes according to our wishes.
Example #3 – Remove AutoFormat Design
Like how we can apply AutoFormat in excel similarly, we can remove those formatting just by the click of a button.
Select the data then Click on AutoFormat and Select the last option
Things to Remember in about Auto Format in Excel.
- By applying Auto Format in excel, we are removing all the existing formatting. Because it cannot recognize the existing formatting.
- We need a bare minimum of two cells to apply AutoFormat.
- We have a total of 16 types of formatting options under AutoFormat ranging from accounting to list, tables to reports.
- If there are blanks in the data then AutoFormat restricts the formatting until the break is found.
- We can customize all 6 types of formatting options using the options method in AutoFormat.
- This is probably the most underrated or not utilized often technique in excel.
This has been a guide to Auto Format in Excel. Here we discuss how to use Auto Format in Excel along with excel example and downloadable excel templates. You may also look at these useful excel tools –