E-TIPS

Finance, Valuation, Risk & Modelling

your one-partner solution for building skills and knowledge

Change your life with Excel. Customise that Toolbar

elcome to the inaugural column of the modestly

titled Change Your Life with Excel, a series of

time saving tips, tricks and secrets which will take you

into the exciting world of Microsoft’s spreadsheet

application.

Most of us will have come across Excel in our working

lives, some to a much greater extent than others.

However the fact remains that the vast majority of users

will only scratch the surface of Excel’s capabilities (for

example, did you know that Excel is a great conversation

starter at parties and continued use can make you more

attractive)?

As a self confessed Excel addict who has witnessed the

horrors of seeing users take 10 or 15 minutes to

complete a 5 second task, I offer this series of columns

as a soupçon (thank you Excel translator) of

enlightenment to the wondrous world that is Microsoft

Excel.

Note that this column will focus on Excel 2003 since

around 75% of us still use this version. Only very basic

knowledge is presumed.

So without further flourish, let’s jump straight in with a

time saving tip and a useful secret i.e. a feature that

Excel keeps in an obscure place.

The Toolbar

Firstly, did you know that you can add or remove whole

Toolbars?

Customise that Toolbar The Toolbar is the name given to the row(s) of small

icons running across the top of your Excel screen.

• Select View from the Menubar (at the very top of the

screen)

• Select Toolbars from the menu which appears

View Toolbars

You will see that there are many more toolbars available,

categorised into common themes. For example if you find

that Excel’s Drawing tools are useful, then select the

Drawing option from the menu, which is then added to

the Toolbars already displayed.

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

Once added, Toolbars can be moved around the screen

and “docked” to the top, bottom, left or right edges of

your screen.

• Grab the left end of the Toolbar as illustrated below (a

black crosshair appears)

• Slide it around the screen until you are happy with its

location

Moving a Toolbar

Secondly, rather than selecting a whole Toolbar to add,

you can just add further icons to your existing Toolbars

and place them where you want within that Toolbar.

• Right click anywhere within the Toolbar

• Select Customize… at the foot of the menu

• Choose the Commands tab on the dialogue box which

appears

•

Excel groups all Toolbar icons into common functions

listed in the left hand window of the dialogue box.

Selecting from one of these groups reveals a new set of

icons in the right hand window.

Suppose you want to add an icon to your Toolbar which

will insert rows into your spreadsheet when you click on

it.

• Select the Insert group in the left hand window

• If necessary, scroll down the right hand window until

the icon for the feature you require is displayed. In this

case the Rows icon (if you’re not sure which icon

represents the feature required, select a likely icon and

click on the Description button at the bottom left of the

dialogue box. This provides brief detail on what that

icon does)

Selecting an feature icon

• Click on the Insert Rows icon (just called Rows as in

the above illustration) and drag it up to the Toolbar you

want it displayed in and position it next to whichever

existing icons you require (a vertical black bar appears

to confirm where the icon will be placed)

Placing an icon in the Toolbar

The Customize dialogue box

E-TIPS

Finance, Valuation, Risk & Modelling

your one-partner solution for building skills and knowledge

The Insert Rows icon is now inserted into your Toolbar.

The newly placed icon

Now you can just select any cell in your spreadsheet,

click the Insert Rows icon and a row will be inserted

above the selected cell.

You can add or remove as many icons as you wish using

this method (to remove an icon, simply activate the

Customize dialogue box again then click and drag the

icon away from the Toolbar).

The customised Toolbars will remain in place on your

screen and apply to all Excel files until you decide to re-

customise.

Secret – List Unique Items

Have you ever had a list of information which contains

duplicates which you want to remove? For example you

may have a list of people’s first names, last names and

telephone numbers. Where they have multiple telephone

numbers, their names appear multiple times. You want a

list which includes their first names and last names only

once. Well Excel can do this in less time than it took to

read this paragraph.

Look at the list below.

It contains duplicate first names and duplicate last names

(the musical theme of these names is purely coincidental

and in no way reflects the author’s tastes. Do not try

calling these numbers - they won’t answer).

• Select the entire list (a good shortcut is to select any

cell within the list and press both the ctrl key and * key)

• Select Data from the Menubar at the very top of the

screen

• Select Filter

• Select Advanced Filter…

The Advanced Filter Dialogue box appears.

The Advanced Filter dialogue box

• Select Copy to another location

• In the List range box select only the range in the

worksheet which covers the First Name and Last

Name. To do this, select the List range box and then

highlight the range on the worksheet (in this example

A1:B15)

• In the Copy to box select a cell in the same worksheet

where you want to copy the resulting list to. Again,

select the Copy to box and then select any cell in the

worksheet (note that the resulting list can only be

copied within the same worksheet as the original list)

• Tick the Unique records only box. Very important!

List containing duplicate names

E-TIPS

Finance, Valuation, Risk & Modelling

your one-partner solution for building skills and knowledge

The completed Advanced Filter dialogue box

• Click OK

The filtered list with unique names only

The final filtered list includes names only once, but it still

allows first names or last names to be duplicated where

necessary.

Well there you have it – only 2 small croutons from the

vast Excel soup and already we’re making our lives

easier. If this leaves any of you hungry to learn more,

until the next column I highly recommend a visit to

www.mrexcel.com where you can download a daily 2

minute videocast of Excel tips. Heaven!

Until next time, I wish you happy Excelling.