AUTO-FORMATS IN EXCEL

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.