Excel Is Our Friend – Part I

Blog Post created by rkoonce on Nov 6, 2013

Excel Is Our Friend – Part I

Navigating Excel

So, what can I tell you about navigation in Excel? That there are tabs for sheets and scrollbars right and bottom? No, I want to highlight the less known, navigational “tricks”. Shortcuts for quickly moving around or selecting a large range, without the clumsiness of scrolling.

Environment > Take in the following familiar looking screenshot:


· How to: Moving Around and Selection Range

With a list this small, we can, obviously, just take our mouse and click where we want to go. But what if the list was 1000 rows x 1000 columns?

The following assumes that all of cells have values (except for A22, A23), no blank cells:
With B2 active, using Ctrl+DownArrow, you would move to B1000, in a single keystroke.
With B2 active, using Ctrl+RightArrow, you would move to ALL2, in a single keystroke.

So, with blank cells present, like A22, A23
With A2 active, using Ctrl+DownArrow, you would move to A21. It stops there because that is the end of the contiguous range of non-blank cells.
Pressing Ctrl+DownArrow again would take you to A24 (skip blanks, stops on next value)
Pressing Ctrl+DownArrow again would take you to A1000
Pressing Ctrl+DownArrow again would take you to A1048576 (end of sheet)
Pressing Ctrl+Home takes you back to the top…

So, this is much faster navigation than the scroll bar.

So, while Ctrl+ is navigating, Ctrl+Shift+ is selecting.
With A2 active, using Ctrl+Shift+RightArrow, your active cell remains A2, but you have extended the “Selection” from A2:ALL2 = 1000 columns, faster than a speeding mouse.
With A2 active, hold down Ctrl+Shift, then RightArrow, then DownArrow (3 times), you have extended the “Selection” from A2:ALL1000 = 1000 columns x 999 rows (we started on row 2).
With A1 active, hold down Ctrl+Shift+End, you have extended the “Selection” to include all cells with data, even the non-contiguous data, including previously populated cells that are now clear or “UsedRange”.

  • Definition > UsedRange - Range object that represents all the cells that have contained a value at any time.

· How to: Using Shortcuts in PI DataLink

You have the taglist in column B, and you want to use DataLink > CurrentValues, in Column J:

Click in Cell J2, Click the DataLink Ribbon > Current Values
Your cursor in the datalink dialog will be in the field for the tags or “Data Items”, depending on your version.
Now, click in cellB2, Ctrl+Shift+DownArrow (you just selected 999 tags - selects all of the tags in the column, until it hits a blank cell), hit or modify the settings for timestamp in the dialog.

OK, so hopefully, you can see the value here, both in “zipping around” and for selecting a large or small range of cells within Excel sheets.

More favorites:

Ctrl+End would move the active cell to ALL1000 (Ctrl+Home would take you to A1) These two are also quite handy in notepad, try it out in a pipc.log?
Ctrl+PageUP moves you to previous tab (sheet), Ctrl+PageDown moves you to the next tab.
Ctrl+G is GoTo
Ctrl+G, followed by Alt+S (GoTo Special - I use this for selecting/ copying only the visible cells (used later in the article).

While I have only mentioned a few, a complete listing of shortcuts can be found here:

There are other features that can also help with navigation, and some serve both in navigation and also in simplifying the complexities, like the use of defined names. There are also shortcut equivalents for VBA in Excel’s Object Model.

This blog series will continue in a few days, so keep your spyglass pointed towards Treasure Island. I welcome your comments and feedback.