E-TIPS

Finance, Valuation, Risk & Modelling

your one-partner solution for building skills and knowledge

Change Your Life with Excel Gotta Have Style

elcome to Change Your Life with Excel, the

article which burrows into the deepest seams of

Excel gold to bring you obscure but valuable nuggets

guaranteed to make your life easier.

We’re going to look at an under-utilised feature which

allows you to apply styles automatically to your

worksheets to help make them look professional and

more readable. This is especially useful if you repeatedly

use multiple formats in cells.

We also look at an old favourite of mine, the Analysis

ToolPak Add-In. In this instance we will use it to help us

to quickly create a frequency distribution and histogram

(column chart). If you’re not really sure what a frequency

distribution is then read on – you may find it useful.

As always, the features covered here are based on Excel

2003 however most can still be found in other versions.

Tip Automating Cell Styles

Creating a Style within Excel allows you to select a range

of cells and automatically apply a set of formats without

having to add all the different formats separately (e.g.

colours, borders, font...).

To demonstrate how easy it is to set up and apply a Style

to cells we’ll look at a simple list of data as shown below;

a list of students and their test scores.

The easiest way to set up a Style is to manually apply the

required formats to a cell or cells using the Format | Cells

option from the Menu Bar. In the example below:

• the column labels have a blue fill and bold, white

text

• the data have light blue fill with narrow white

borders

To save these format sets as Styles:

• Select a formatted cell

• From the Menu Bar select Format | Style

• In the Style dialog box which appears type over

the Style name (called Normal by default) with a

meaningful name e.g. “Blue Column Label”

• Click OK

• Repeat the process for other format sets

W

By Leigh Drake Director Arc Business Processes www.arcbusiness.com.au

E-TIPS

Finance, Valuation, Risk & Modelling

your one-partner solution for building skills and knowledge

To apply these Styles to other worksheets in the same

workbook:

• Highlight the cells to format

• From the Menu Bar select Format | Style

• In the Style dialog box which appears select the

Style name from the Style name drop down list

• Click OK

It’s as simple as that!

But hang on a minute. You’ll notice that when you want to

apply these styles in another workbook they’re not

available. Unfortunately Styles are only saved in the

workbook in which they were created. However we can

quickly copy Styles in from other workbooks.

• Open the source workbook which has the Styles

you want to copy

• From the workbook you want to copy the Styles

into select Format | Style from the Menu Bar

• Click Merge

• Select the source workbook name from the list

which appears

• Click OK twice

All Styles are now copied from the source workbook.

Secret Quickly Create a Frequency Distribution

For those of you not overly familiar with a frequency

distribution it’s a simple data analysis tool which

summarises the number of times values appear within a

list of data.

To achieve this quickly in Excel we can use a feature

called Histogram. To access this feature you will need to

load an Excel Add-In called Analysis ToolPak (an Add-In

is a feature not installed by default in Excel). To do this:

• From the Menu Bar select Tools | Add-Ins

• In the Add-Ins dialog box which appears tick

Analysis ToolPak (if it is already ticked ignore

these steps)

• Click OK

Now let’s look at an example of data that we want to

analyse as a frequency distribution. The table below lists

the test results of students. We want to determine how

many scored in the range 40% or less, 40%-50%, 50%-

60% etc. This is a simple frequency distribution.

• In an empty range of cells, set up a list of the

ranges (known as “bins”). The value should be the

upper end of the range i.e. 40% represents all

values up to and including 40%, 50% represents all

values over 40% up to and including 50% etc

• From the Menu Bar select Tools | Data Analysis

• Select Histogram from the list

• Click OK

E-TIPS

Finance, Valuation, Risk & Modelling

your one-partner solution for building skills and knowledge

• In the Histogram dialog box which appears click

into the Input Range field and then on the

worksheet highlight all “Test Score” cells including

the column label i.e. cells B1:B52

• Click into the Bin Range field and then on the

worksheet highlight all the “Range” cells including

the column label i.e. cells D1:D8

• Tick the Labels box

• In the Output Range field enter a cell reference

which represents the upper left corner of the report

you are about to create, G1 in this example

• Tick Chart Output to also display a histogram

• Click OK

A simple frequency distribution report is created as

below, showing how many students achieved marks

within each range of percentages.

This is accompanied by a histogram (column chart) which

will usually require some simple reformatting. A

reformatted histogram for these results is illustrated

below.

So there we have it for another edition of Change Your

Life with Excel. To recognise the increasing number of

you now using Excel 2007, future editions will include a

tip for this latest version (just in time for the release in

June of an even new version, Excel 2010!)

Until next time, happy Excelling.