E-TIPS

Finance, Valuation, Risk & Modelling

your one-partner solution for building skills and knowledge

Change your life with Excel. Completing Data Using the Go To Box

elcome to Change Your Life with Excel, the

column that delves to the bottom of Microsoft®

Excel’s jamboree bag and pulls out some surprises

guaranteed to delight.

This month we’re going to stick our finger right in the very

bottom corner of the bag to pick out one of Excel’s most

obscure features, but before we do let’s have a look at a

useful time saver.

The features covered here are based on Excel 2003

however most can still be found in other versions.

Time Saving Tip Completing Data Using the Go To Box

Have you ever had a spreadsheet that looks a bit like the

example below?

The date column contains a lot of blank cells because the

date is only entered once even though it applies to rows

below. With this arrangement you wouldn’t be able to e.g.

sort or filter by dates. We therefore need a way of filling

in the blank cells.

Of course if there isn’t much data you could fill in the

blank cells manually. However, you may not be surprised

to learn that there is a very quick method to achieve this.

• Highlight the range of cells containing the blank

cells. It doesn’t matter if you select a slightly

larger range as long as it doesn’t contain any

other blank cells which you want to leave alone

• Select Edit | Go To... from the Menu Bar or

CTRL-G on the keyboard

• Select the Special... button

• From the dialogue box choose Blanks

• Select OK

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

You will now see that all the blank cells within the range

you selected have been highlighted.

• Without clicking anywhere in the worksheet type

“=A2” or just type “=” and select cell A2

This references the value in cell A2 (“5/01/2009”) into the

first blank cell highlighted, in this case A3

• Hit CTRL-ENTER

This last keyboard command enters the formula into all

the highlighted cells which fills all the blank cells with the

date immediately above.

Now you just need to freeze the dates.

• Highlight the range of dates

• Select Edit |Copy from the Menu Bar

• Select Edit | Paste Special... from the Menu Bar

• From the dialogue box choose Values

• Select OK

This now replaces the formula with its value and allows

you to manipulate the data at will (leaving the formula in

place would give incorrect results after e.g. sorting).

Want a slightly quicker method to freeze values? Of

course you do. You just don’t have this sort of time on

your hands.

• Again highlight the range of dates

• Right click on the right hand black border

• Keeping the right mouse button clicked, drag to

the right and then back to the left in one

movement

• Choose Copy Here as Values Only from the

menu which appears

So there you have it. In a matter of seconds you can fill in

large ranges of blank cells with the required data.

E-TIPS

Finance, Valuation, Risk & Modelling

your one-partner solution for building skills and knowledge

Go To is a very useful feature. You’ll have noticed that

there are many other options available on the Go To

Special dialogue box. Have a play and see what other

cool things you can do with it.

Secret – The Excel Camera

Now for 2 of Excel’s best kept secrets. These are

particularly useful for creating professional looking

reports.

We’ll get onto the Camera feature shortly but first we’ll

look at its slightly inferior sibling Copy Picture.

Have you ever wanted to display, say, a table of data on

a worksheet but the other contents on that worksheet

dictate the column widths or row heights such as the

example below?

The column widths required for the 2nd table are not

appropriate for the 1st table.

The trick here is to create one or both tables elsewhere,

preferably on another worksheet, where you can set the

appropriate column widths. Leave sufficient space on the

main display worksheet.

In this instance we will create the 1st table elsewhere.

Once you are happy with its layout:

• Highlight the table

• Hold down the SHIFT key (this is the secret!)

• Select Edit | Copy Picture...

Copy Picture does not appear as an option unless you

hold the SHIFT key.

• From the dialogue box choose As shown on

screen

• Select OK

• Select the cell to copy the table to. In this

example cell B2 (you only need select the top

left cell for the range you are copying to)

• Select Edit | Paste

The 1st table now appears as a picture above the 2nd

table with the appropriate column widths. It can now be

moved around the screen completely independently of

the gridlines.

This is great but it’s not all over yet. The disadvantage

with this is that we have created a static image. If the

original table needs changing we have to go through the

whole copying process again. Well no we don’t, because

it’s time to get the camera out.

The Camera feature does pretty much the same thing as

Copy Picture but it takes a real time image i.e. whenever

E-TIPS

Finance, Valuation, Risk & Modelling

your one-partner solution for building skills and knowledge

you change the source of the image, the image changes

too. Marvellous!

Now, where did we put the camera? It’s not in any Menu

option, nor is it on any of the Toolbars. The secret here is

that it’s a custom command which you need to add to

your Toolbar (see last month’s newsletter for how to

customise the Toolbar).

You will find the command under the Tools category.

• Highlight the source table

• Select the Camera icon from wherever you

placed it on your Toolbar

• Click the location where you require the image

(once done you can freely slide it around to

wherever you want. Hold the ALT key down if

you want to snap it to the gridlines)

Now we have a dynamic image. Change anything in the

source table and it instantly changes the image.

One final note. You may find that you have an unwanted

border around the image. To remove this:

• Right click inside the image

• From the menu choose Format Picture...

• Select the Colors and Lines tab in the dialogue

box

• From the Line Color drop down box choose No

Line

• Select OK

So there you have it. The Camera feature allows you to

create great looking reports and is especially useful for

dashboards which often have a mixture of charts and

tables on the one page.

If you think it’s all over, it is now!

Until next time, happy Excelling.