This week brings a tapas of excel goodies, do feel free to try out one or more of these offerings to suit your appetite.  You’ll need to download the thing8 sample worksheet [ thing8excel ] – but by all means do try this on your own data too.

Setting print areas

Be kind to the person you send your lovely data to.  Define the print areas and repeat the row titles if it spans more than one page.

Try this out on the Biscuits Consumed sheet

• Set the print area to the first 4 columns
• Use print titles to repeat the first row
• Use View/Page break preview to check it is good.

Autofilter

Autofilter can help you explore your data. The “Biscuits consumed” sheet holds data from a follow on study catalysed from the biscuit world cup – this time participants chose one biscuit per day and gave it a score.

• Select the 4 columns making up the table and click “sort and filter” and select “filter”

• Use the drop down menu on the column headings to try a few things out eg:

• sort the biscuits A-Z
• select just the Fruit Club results
• On the comments heading use “Text Filter” to look for all rows containing “choc”
• Clear all filters before you go on…

Pivot Tables

We often get information that we need to summarise. Pivot tables can make this really easy.

On our biscuit example we might like to ask “how may times was each biscuit chosen?” and “what was the average score for each biscuit?”.

Here’s what to do:

• select the first three columns
• Click on the Insert Menu and select Pivot Table
• accept the defaults to create a new worksheet

• Drag “biscuit” from the field list to the rows area and drag score to the values area
• If you like drag, score into the values area a second time, click on it and change the “value field settings” to give average of score.
• See if it matches the Answer to Biscuits Consumed sheet.

Absolute and Relative Referencing

If I have not yet exhausted your appetite for Excel tapas, here’s one that has its own shortcut key (F4).

By default Excel always uses relative referencing.  If you copy a cell four rows down, any formulas in that cell are updated to point to the corresponding cells four rows down.  You might not always want this, and the good news is that you can override it by using $ in the formula.

To try this out have a look at the sheet labelled MyShares.  (This relates to an alternative reality where you have enough spare cash to have some shares and time to ponder how they will grow in a variety of scenarios).

We need to copy the formula in cell E5 to fill the other areas and work out how our shares may grow.  At the moment the formula is

=D5*E4

A fruit club goes to the first 3 people who can tell me by email where to add two $ signs!

(See this Microsoft article if you need a hint!)