E-TIPS

Finance, Valuation, Risk & Modelling

your one-partner solution for building skills and knowledge

Change Your Life with Excel Sort Yourself Out

elcome to Change Your Life with Excel.

This month we look at some under utilised

features of the Sort command and how to make our data

tables look pretty (and more readable) using a cool

formatting trick guaranteed to have colleagues

proclaiming you an Excel guru.

As always, the features covered here are based on Excel

2003 however most can still be found in other versions.

Time Saving Tip Sorting Data into a Custom Order

Most of you probably know how to sort the rows in a table

of data into alphabetical or numerical order (for those of

you that don’t, select a single cell within your data and

choose Data | Sort from the Menu Bar).

Well if that was all this article was about I hope you would

feel a little bit short changed because although this is all

most of us ever ask of Excel’s Sort feature it can do so

much more – as we will now see.

Suppose we have a table of data which includes a

column of state names as in the example below.

Extract of a data table including states

We need to present this table with states sorted in the

following order:

1. NSW 5. WA

2. VIC 6. TAS

3. QLD 7. ACT

4. SA 8. NT

Clearly this is not alphabetical so firstly we need to tell

Excel the order we require, which we do by creating a

Custom List.

• Select Tools | Options from the Menu Bar and

select the Custom Lists tab

The Custom Lists dialog box

• In the List entries window on the right, type the list

items “NSW”, “VIC”, “QLD” etc pressing ENTER

after each item

• Click the Add button once complete; or

• If the list already exists in a worksheet, click in the

Import list from cells field, select the cells containing

the list and click the Import button

• Click OK

Note that this writes the list to the computer’s registry

which means that it is always available to any Excel

workbook opened on that computer, until it is deleted.

Now we need to tell Excel to sort the data in this order.

• Select a single cell anywhere within the data

• Select Data | Sort from the Menu Bar

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

• Choose “State” in the Sort by field as the column to

sort on (if the column titles aren’t listed, select

Header row)

• Select Ascending order

• Click on the Options button

The Sort Options button

• From the drop down list select the Custom List

required

Select a Custom List from the Sort Options dialog box

• Click OK twice

The data is now sorted in the required order.

The data sorted in custom order

But wait, there’s even more to the Sort feature.

Suppose the same table of data has the states listed

across a row as illustrated below.

Extract of data table with states in columns

We now want to sort the columns in the same order as

the custom list.

• Select the complete range to be sorted, being

careful to exclude the row titles (“Equipment”,

“Parts” etc) i.e. B1 to the last cell in the table

• Select Data | Sort from the Menu Bar

• Click on the Options button

• From the drop down list select the Custom List

required

• Select Sort left to right

Select the Sort left to right option to sort by columns

• Click OK

• Select Ascending order

• Click OK

The columns in the data table are now sorted in custom

order.

The data sorted left to right in custom order

E-TIPS

Finance, Valuation, Risk & Modelling

your one-partner solution for building skills and knowledge

Trick Formatting Banded Rows

This trick creates coloured banded rows to assist with

reading a large data table which might otherwise use cell

borders which can make a table look “dense”.

Extract of a large data table made “denser” with cell borders

The same “less dense” extract with light blue banded rows

You may be thinking “so what, it’s easy to apply a cell fill

colour to every other row”. However, what if you need to

add or delete rows. This could disrupt the pattern by

clumping together several rows of the same colour. The

following technique allows you to add or delete rows and

still maintain the pattern.

Firstly remove all the cell borders except for the outer

edges and colour the header row manually if required

(these are suggestions only).

• Highlight the range to apply the banded rows to

• From the Menu Bar select Format | Conditional

Formatting

• In the Conditional Formatting dialog box select the

Formula is option from the drop down box

The Conditional Formatting dialog box

• In the field to the right type in the formula

=MOD(ROW(),2)=1

ROW() returns the row number of a particular cell. We

could put a cell reference between the brackets e.g.

ROW(B10) returns the value 10, but we can use the

shorthand version without the cell reference which

returns the row number for whichever row a cell is in.

MOD(number,divisor) returns the remainder when

dividing one number by another (the divisor). The number

is provided by the ROW() function in this case.

As examples, MOD(5,2) returns 1 since 5 ÷ 2 is 2

remainder 1 and MOD(6,2) returns 0 since 6 ÷ 2 is 3

remainder 0.

The formula “=MOD(ROW(),2)” always returns a value of

0 for even rows and 1 for odd rows. Therefore the full

formula above “=MOD(ROW(),2)=1” is TRUE for odd

rows (since 1=1) and FALSE for even rows (since 0≠1).

• Click the format button and choose the row colour

in the Patterns tab (use a light colour)

• Click OK twice

This format now applies whenever the formula is TRUE

i.e. for each odd numbered row.

Now try inserting and deleting rows in the data table and

Excel will maintain the pattern of alternate coloured rows

(because the formatting is determined by the row

number).

I must tip my hat to www.mrexcel.com for this trick. If you

haven’t come across this site before it’s a fantastic Excel

resource, in particular the downloadable video podcasts

(come on, I know you can’t get enough of Excel).

Until next time, happy Excelling.