Mind the (Excel) Gap

When I moved to London in 2007, I found the fastest way to get around was the underground train network affectionately called “the Tube”.

It’s the oldest underground train network in the world with the first section opening in 1863.

If you’ve ever travelled on the Tube you’ll be familiar with the phrase “Mind the Gap”. This is the gap between the train and the platform. At many stations the gap is only a few inches. At others, the gap is wide enough for you to fall through into the abyss between the platform and the train tracks.

Some Excel applications have a gap where your users can fall into an abyss.

One moment your users are happily navigating their way around your Excel solution, then suddenly, they scroll too far or press Page Down once too many times and they’ve fallen off your application.

They now face a blank section of worksheet.

They’ve fallen into the gap.

Like a website when you’ve ended up on a 404 page, your user may feel confused or disorientated by your Excel solution.

Here’s two ways you can reduce the odds of your Excel users navigating off your application by confining where they can navigate.

Like a website when you’ve ended up on a 404 page, your user may feel confused or disorientated by your Excel solution.

Your Excel Application

When you develop Excel applications for users to work with, you typically have an area or range they should work with on a given worksheet.

Here’s an example of a dashboard page which aggregates data on other worksheets.

Behold, the Gap

One moment your users are happily navigating their way around your Excel solution, then suddenly, they scroll too far or press Page Down once too many times and they’ve fallen off your application.

They now face a blank section of worksheet.

They’ve fallen into the gap.

Like a website when you’ve ended up on a 404 page, your user may feel confused or disorientated by your Excel solution.

Here’s two ways you can reduce the odds of your Excel users navigating off your application by confining where they can navigate.

Hiding Columns & Rows

This is the brute force approach in which you literally hide the columns to the right, and rows below your worksheet “page”.

You can either physically hide the columns and rows. That is, select all the columns to the right of the worksheet page, right-click the column headers and select Hide from the short-cut menu.

You’d then do the same for the rows.

Or you can write some VBA code to hide the columns and rows instead.
From an aesthetic perspective, this approach leaves a dull, battleship grey area around your worksheet.

Scroll Area

This approach uses VBA to restrict the area or range a user can scroll around a given worksheet.

ScrollArea is a property of the worksheet. You set it at a worksheet-level. That is you’ll need to set the ScrollArea for each worksheet you want to restrict movement in.

You can also have a different ScrollArea in each worksheet to accommodate differing worksheet layouts.

When a user attempts to scroll or page-down beyond the defined area, nothing happens.

To remove the restrictions, simply set ScrollArea to an empty string.
Aesthetically, the restricted columns and rows are still be visible using whatever colour theme your Excel application uses.

In conclusion

Ensuring the users of your Excel solutions don't fall in the gap is an important part of their "User Experience" (UX). Excel offer several approaches confine users to specific areas of a worksheet.

Have you created a spreadsheet where you needed to control where users could navigate on a worksheet? How did you approach it?