In Excel, how do you make it clear that a pane is frozen?

Question

I'm making a reporting application in Excel. On some sheets, I have multiple rows of header that I want to keep visible at all times. So, naturally, I'm using Excel's Freeze Panes feature to freeze those rows at the top.

The problem is that if the user scrolls down or finds a report that's already been scrolled down, it's not immediately visually obvious that there's more data hiding above, unless the user looks at the scroll bar or the row numbers.

Do you know of any good ways to visually hint to the user that there's more data hiding behind the header?

I'm open to ideas that may require scripting to implement. I'm more interested in collecting design ideas; I can figure out how to implement them afterward.

Answer 1

Your main issue here isn't really "how to visualize that the frozen pane is frozen".

That part is easy. Add some gray background and some bold border, and you're halfway there :-)
Make it look different and title'ish.

---

Your real question is: "How do you visualize that there is more data above the top row?"
And question doesn't only apply to Excel's frozen top row. That question is relevant in other situations as well.

(Have you ever followed some link and after a while you realize that you're in the same web-page as before, just in another #Name section. Try this: http://en.wikipedia.org/wiki/Microsoft_Excel#File_formats. How do you know that you're in the middle of the page?)

The best way to visualize that there is something above, is to show a little bit of that content. So that the user doesn't believe that the topmost thing is the beginning of the content.

vs

Unfortunately, Excel only scrolls by whole rows, so it's impossible to show half the content of the row above (or am I wrong?). The only (really lame) workaround I can think of is to merge two cells vertically. That way you might show half the content of the row above - leaving no doubt to the user.

---

The other way you can visualize that there is more above, is to use the Gestalt law on closure and give the user the feeling of an un-closed area above the visible area.

-->
(Sorry about the bad visual appeal. Please put a little bit more work into the look.) :-)

---

I would also add that this depends a lot on the content you are showing, and the task the user is performing. If the user is looking at a list of numerically ordered items or alphabetically sorted names, the he will know that he will find more names above - especially if he is looking for it. If he is not looking for it, does he need to know that it's more content above?

Where is customer No#3 and where is Arthur???
and

Answer 2

There are two parts to the solution:

First, dynamic manipulation in excel is done using VBA and enables you to manipulate objects on the spreadsheets easily. By placing an image element on the spreadsheet and attaching code to the its click event, you can visually represent a change in the sheet state and an implied action. The image can be an up arrow...

Code example:

Private Sub ImgArrowUp_Click()
With ActiveWindow
    .ScrollRow = 1
    .ScrollColumn = 1
End With
ActiveSheet.Shapes("ImgArrowUp").Visible = False
End Sub

This code will scroll up and hide the arrow.

---

The second part is a bit tricky - you need code to display/hide the arrow on the spreadsheet based on the user location on it:

ActiveSheet.Shapes("ImgArrowUp").Visible = True/False

but there are no scroll events in excel so if you want to implement this you'll need access to windows API. You can use the implementation described here: Detecting Scrolling In A Worksheet Window

Answer 3

I'm not sure if you are familiar with the Google Docs spreadsheets but they default a gray bar about 5px or so high to indicate that the row above it is locked to the page. that seems pretty intuitive to me.

Answer 4

Indicate the number of hidden rows in the row header. The further up or down you scroll, the number changes to show the changing number of hidden rows. When you're scrolled right up, the indicator disappears (as it would be zero). It's the least visually invasive indicator I can think of to the actual main content area.

The good thing here is that if you also have a filter in place and some of the rows are actually hidden because of a filter rather than because you are scrolled down, then the indicator can show the true number of rows hidden from view.

Answer 5

Just to throw an idea onto the table (pun intended) you could try for something like the following which makes use of an ellipsis in each column and every other row (dependent on width of rows/columns) and which sits in a more emphasized separator between the panes.

If the pane is scroll all the way to the top or the left, the relevant ellipses are hidden - ie they only indicate hidden content.

If you click on the horizontal separator, the panes snap to the top (ie scrolls completely up and the horizontal ellipses hide themselves) and similarly if you click on the vertical separator, the pane snaps to left.

Answer 6

Here's what I ended up implementing, inspired by some of the excellent ideas proposed here by others:

Below the header rows, I have two half-high rows, with the pane freeze between them, so that the top one is always there, and the bottom one goes away when you scroll. In the top one, I have a line of "/\ /\ /\ " spanning the row, in blue, and sized to fit in the half-height. In the bottom one, I have a similar line of "\/ \/ \/ ". The result is that when not scrolled, there's a line of blue diamonds between the header and the data, and when scrolled down, there's a line of blue up-chevrons.

Answer 7

This is Excel's default behavior. I don't think it's possible to 'program' every possible situation. You could create links that take the user to a certain cell within a certain sheet. This way, even though the user doesn't scroll, you can point the user in the right direction. If you name the links 'up' or something, then people will probably get it.