Wednesday 20 November 2013

Sharing workbooks

By , Director, Naturally Sales Ltd.

Tired of having to exit an Excel workbook every time a colleague wants to make a change?

You can use the “Share workbook” tool, which you’ll find on the “Review” tab. If you click this button, you’ll find a check-box to “allow changes by more than one user at the same time”. This will re-save the workbook, and you’ll see the word “Shared” at the top by the workbook name. Now anyone can be in the workbook and modify it together. If there are any conflicts (i.e. 2 people trying to change the same cell), then the second person to make the change has the option to accept the previous change or insert their own.

For added security and tracking, this can be combined with various passwords.

Wednesday 13 November 2013

Future-proofing your VLOOKUPs

By , Director, Naturally Sales Ltd.


Someone called me the other day with a VLOOKUP problem. We’ll look at that problem today, as it’s common when you first start working with this powerful function.

The lady in question, let’s call her Sarah, had created a VLOOKUP formula to search for a value within her data list which stretched from C1:D1000. She was looking up corresponding values for a list of alpha-numeric codes. Her formula was constructed something like this:

    (in cell B2) =VLOOKUP(A2,C1:D1000,2,FALSE)

She found that the formula worked at the top of her data list but in other places returned an error, even though she knew the data was present. Also, when she sorted her list, other values worked instead.

The problem was that she’d neglected to put in her absolute references, and as she dragged the formula down the worksheet, the data list reference increased as well, causing it to look at empty cells. The correct formula should have been:

    =VLOOKUP(A2,C$1:D$1000,2,FALSE)

In our Excel training courses, we talk a lot about “future-proofing” our spreadsheets, and this is a classic example. If Sarah’s data list had grown past D1000, she’d have needed to re-work the formula. A best-practice way of writing the same formula would be:

    =VLOOKUP(A2,C:D,2,FALSE)


In this case, if more data were added to the list, it would be picked up as the formula is now selecting the full columns.

If you’d like to take a look at this problem, you can download an example here: http://www.nsales.co.uk/excel-examples/Future-proofing-VLOOKUPs.xlsx

Monday 4 November 2013

Another keyboard shortcut

By , Director, Naturally Sales Ltd.

Regular readers of our e-tips will know that we regularly share keyboard shortcuts, so here's another one for this week's tip.

You may know that pressing the RETURN key moves the selection down and worksheet, and pressing the TAB key moves it to the right. Did you know, however, that using SHIFT+ENTER moves the selection up, and SHIFT+TAB moves it to the left

Try this on one of your worksheets to speed up your navigation skills.