AUTO-FORMATS IN EXCEL

NEW! More Books..

Add Excel Answers & Search To Your Google Toolbar Details

Lesson 33 - Excel AutoFormats. Auto Formatting in Excel. Level 1 Free Training

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

EXCEL AUTO-FORMATTING

Another very handy feature of Excel is it’s ability to use its built-in AutoFormats on your data, and the flexibility that you have in changing these to suit your personal taste. There are many AutoFormats available to you and they are found under Format>AutoFormat. Lets have a look at how this would work with our previous Workbook Data Sorting.

Open the Workbook Data Sorting.
Click on the Sorting with Text sheet tab.
Click on cell D5 or any other single cell within your list.
Select Format>AutoFormat and the AutoFormat dialog box will pop up in front of you. Note here that as when doing a sort operation, Excel will automatically highlight your whole table for you (it will only do this if there are NO blank columns or rows).
Scroll through the list of formats available to you using the vertical scroll bar, or you can use the arrow keys on your keyboard if you prefer.
Select List 2 by clicking on it, then select OK.

You should note that if you select more than one cell before going to Format>AutoFormat Excel will assume you only want to format the selected cells and not any surrounding data.

By using the Options button on the AutoFormat dialog box, you can make many more formats available to you. The options available to you through this button will be Number, Border, Font, Patterns, Alignment, Width/Height. Note however, that by default all options are selected, which means that all current formatting in your selected table or range will be overridden by the AutoFormats. By deselecting certain options, you will have the ability to go back into your range or table and manually apply the format attributes you have unchecked in the Formats to Apply box. However, we suggest only doing this if really necessary.

Make sure you are still selected somewhere in your data.
Go to Format>AutoFormats.
Select Options.
You will notice that the AutoFormats dialog box has now expanded to include Formats to Apply.
Uncheck the Number option and see how the numbers now look. You will notice the most changes in the Accounting formats (Accounting 1, Accounting 2, Accounting 3, Accounting 4).
By unchecking the Border checkbox, you are removing the outline of the cells as shown in the AutoFormats preview window.
By unchecking the Font checkbox, you are unchecking the attributes to the font that are applied to the AutoFormat which basically includes font size, font type, color and bolding, italics etc.
By unchecking the Patterns checkbox you will remove the background color and/or any patterns that may be applied.
By unchecking the Alignment checkbox you will change the alignment of your text or number within the cells.
By unchecking the Height/Width checkbox you are changing the height and width of the columns and rows. Basically, having this option checked means that your columns and rows are set to AutoFit, which is probably the best option to have.

Once you are happy with your selection, simply click OK to see exactly how your data will look.

If after applying an AutoFormat and adjusting accordingly, you decide you no longer want it, simply select any single cell within your data list, go to Formats>AutoFormats and use the scroll bar to scroll to the very bottom of the list and click None then OK.

Note however, that while applying an AutoFormat to your range will override any formatting you have previously applied, removing it does not return it to its original state.

Extend AutoFormats

By default Excel will automatically extend down any AutoFormats and manual formats. You can change this via Tools>Options - Edit and uncheck the Extend list formats and formulas

Extend list formats and formulas. Automatically formats new data added to the end of a list, or table to match the format of the rest of the list/table. Formulas that are repeated in every row are also copied. To be extended, formats and formulas must appear in at least three of the five last rows preceding the new row.

Go To Free Excel Training Lesson 34 . Back to Previous Lesson

Go to Excel Basic/Level 1 Training Index

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft