How to Use Excel's AutoFormat Feature GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office
How to Use Excel's AutoFormat Feature
Improve readability and save time with built-in table formats
By Ted French Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. lifewire's editorial guidelines Updated on October 26, 2021 Tweet Share Email Tweet Share Email MS Office Excel Word Powerpoint Outlook What to Know
Add AutoFormat to Quick Access toolbar: Select Quick Access Toolbar > More Commands > Choose commands from > All Commands.Scroll through the list and select AutoFormat > Add > OK. To apply an AutoFormat style to a table, highlight the data.Next, select AutoFormat from the Quick Access Toolbar, choose a style, and click OK. Select an AutoFormat style's options to modify it. This article explains how to use Excel's AutoFormat option to create a professional, clean worksheet while improving your Microsoft Excel spreadsheet's readability and saving time. Instructions in this article apply to Excel 2019, 2016, 2013, and 2010, as well as Excel for Microsoft 365.
Add AutoFormat to the Quick Access Toolbar
To use AutoFormat, add the AutoFormat icon to the Quick Access Toolbar so it can be accessed when you need it. After you add AutoFormat, it remains on the Quick Access Toolbar. There are 17 AutoFormat styles available in Excel. These styles affect number formatting, borders, fonts, patterns and background colors, alignment, and column and row size. Select the Quick Access Toolbar drop-down arrow. Choose More Commands to open the Customize the Quick Access Toolbar dialog box. Select the Choose commands from the drop-down arrow. Choose All Commands to display all the commands available in Excel. Scroll through the alphabetical list and choose AutoFormat. Select Add. Select OK to add the AutoFormat icon to the Quick Access Toolbar. Apply an AutoFormat Style
To quickly apply an AutoFormat style to a table: Highlight the data in the worksheet that you want to format. Go to the Quick Access Toolbar and select AutoFormat. In the AutoFormat dialog box, choose a style. Select OK to close the dialog box. The new style is applied to the table. To apply a different style, select any cell in the table and choose AutoFormat. Modify an AutoFormat Style Before Applying It
If you don't like any of the available styles, modify a style before you apply it to a worksheet. In the AutoFormat dialog box, select Options. In the Formats to apply section, clear the check boxes for the formats you don't want to use in the table. The examples in the dialog box update to reflect the changes. Select OK to apply the modified style. Modify an AutoFormat Style After Applying It
After you apply a style to a table, modify the table style with the formatting options found on the Home tab of the ribbon. Then, save the modified AutoFormat style as a custom style that can be used with other tables and worksheets. To create custom AutoFormat styles for tables: Select any cell in the table. Go to the Home tab, select Format as Table, then choose New Table Style. In the New Table Style dialog box, choose a table element and select Format to apply the font, border, or fill formatting you like. Do this for each table element you want to change. Select the Set as default table style for this document check box if you want to automatically use this style when formatting tables, then select OK to save the AutoFormat style. To use the custom style, highlight a table, go to Home, select Format as Table, and choose the custom style. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire Use Excel's EOMONTH Function to Add or Subtract Months How to Password Protect an Excel File How to Find and Use Excel's Free Flowchart Templates How to Insert a Table in Microsoft Word How to Create a Report in Excel How to Use the Address Book in Microsoft Word How to Use the ISBLANK Function in Excel How to Use Excel's MROUND Function How to Share an Excel File How to Embed Excel Files in Word Documents How to Use Undo, Redo, and Repeat in Excel Use Excel Shortcut Keys to Quickly Save Your Work How to Apply Background Colors to Tables in Word How to Print Labels from Excel How to Create a Histogram in Excel for Windows or Mac Shade Alternate Rows With Excel Conditional Formatting Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies