416-621-9348 cgreaves@chrisgreaves.com
Home Site About Services Products

Excel Styles (Home), Excel Styles are Additive , Hand-Crafted Styles, Implementing Protection , Style History

Excel Styles

Styled formatting in Excel is a powerful means of producing consistently-formatted worksheets at less cost than the traditional method of formatting.

Save money, improve delivery schedules, look better.

We embark on a trip of discovery.

These notes were developed using Microsoft Excel 2000; what you see here will be applicable to Excel versions 2000, 2002, 2003, 2007 and 2010. You should find that the general principles are applicable to other main-stream Windows-based spreadsheet processors such as Lotus, QuattroPro, and so on.

As well we encourage you to use Styles formatting in your Word processing software.

Local Formatting Bad, Style Formatting Good.

Toolbar Bad, Automation Good.

By this we mean to encourage you never again to use the Format, cells sub-menu, but instead to use the Format, Styles sub-menu.

As well we discourage the practice of clicking the mouse on the various local-formatting buttons for Bold, Italic, Currency, Percent and the like.

Meet the home team

I will be starting off with a very simple data sheet; you may elect to follow along using a COPY OF one of your own workbooks. Open a workbook, then immediately re-save it (using the F12 function key) with a new name, preferably in a “playpen” folder.

In the sample data shown above, I have generated some random data using the RAND function then fixed it using Edit, Copy; Edit paste Special.

The first cell has a value that displays one decimal place but boasts 10 decimal places in the formula bar!

Creating a style

We will create our first style – one that can be used to adorn cells designed for user-input.

This is about as complex as ever it’s going to get!

Select the home cell, then from the menu system choose Format, Style.

Type in a new style name “Unlocked”, for this style will be used in our cell protection scheme.

Check off all the major attributes except Protection.

Choose Modify

On the Protection tab check OFF the locked box.

Confirm out of there using the OK command buttons.

When you return to your spreadsheet nothing appears to be changed, but if you set protection on your worksheet (Tools, Protect), you will find that you can modify the home cell but no other.

Select all your data cells and apply the style Unlocked.

Creating Two Different Number Styles

Create two more styles, one called Number0 (that displays no decimal places) and one called Number2 (that displays 2 decimal places)

Apply the Number0 style to the 1st and 2nd columns of data; apply the Number2 style to the 3rd and 4th columns of data.

Select the first two columns of data, 8 cells in all, then choose Format, Style.

Type in a new style name “Number0”.

Check off all the major attributes except Number.

Choose Modify

On the Number tab choose the category Number, set decimal places to zero, and check ON the comma separator for thousands.

Confirm out of there using the OK command buttons.

When you return to your spreadsheet the first 8 data cells will be displayed as whole numbers, with no decimal places, and a comma separating the thousands.

Note that the address bar shows the number is stored with an accuracy of ten decimal places, but the display shows NO decimal places!

Repeat the exercise now, selecting the last 8 data cells, and create a style Number2, rather like Number0, but this time with 2 decimal places:

Note again the distinction between the stored value (38757.0401707806)and the displayed value (38,757.04).

Loading

Toronto and Mississauga, Thursday, April 15, 2010 1:44 AM

Copyright © 1996-2010 Chris Greaves. All Rights Reserved.