9

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.

1
  • I'm new here and no UI expert by any means, and would therefore appreciate any help with tagging. Aug 30, 2011 at 21:39

7 Answers 7

8
+50

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.

enter image description here
vs
enter image description here

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.

enter image description here


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.

enter image description here --> enter image description here
(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???
enter image description here and enter image description here

1
  • 2
    Thanks very much for your thoughtful analysis. I think you're right on the money, and I like that closure idea. I don't have a great deal of space to spare, but I'll see if I can implement something along those lines. Sep 14, 2011 at 21:28
7

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

1
  • 1
    As a comment, I'd never use this code. ActiveSheet will do as the name says and you could end up with unintended behavior. You should use ThisWorkbook.Sheet1 or Sheet1 (or whatever name you've given your sheet).
    – GUI Junkie
    Sep 13, 2011 at 18:54
5

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.

A spreadsheet with column headers in Google Docs Spreadsheet

2
  • 1
    I'm not sure about the excel implementation but you could probably freeze a second row below the first that is thin and gray and get a similar effect. If you could make it blue and stripey it would probably get the point across even more. Sep 15, 2011 at 13:33
  • Ooh. I like the blue and stripey idea. Sep 15, 2011 at 14:00
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.

enter image description here

4
  • Is that a standard feature in Excel? I've never seen that before... How do you activate it? Sep 15, 2011 at 11:09
  • 4
    Err - standard - no. Isaac said in another comment: I'm looking for design ideas...I'm not asking how to implement them. so I took him at his word :-) Would be nice if it was standard though! Sep 15, 2011 at 13:08
  • OK. That's what I thought. Nice suggestion, though. Sep 15, 2011 at 13:14
  • 2
    @Jørn, Roger's exactly right. Brainstorming design ideas is what I was hoping for, and what I appreciate getting. This one would probably be quite difficult to implement in Excel, but maybe I or someone could come up with a related idea, use this idea in a different context, or use in in a future version of Excel that makes it easier to implement! Sep 15, 2011 at 14:04
2

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.

enter image description here

2

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.

1
  • 1
    Brilliant and simple. - And to be honest, I like that you did it without having to use any script-functions - just plain UI.
    – awe
    Apr 4, 2012 at 8:29
0

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.

7
  • I'm not sure what you mean by "default behavior." I'm the one who chose to freeze the header rows at the top. I also don't get your second sentence; I have a specific issue that I'm trying to solve. Sep 13, 2011 at 15:21
  • @Isaac, The pane freezing is done by the user, as you state. Thereafter, Excel will not move the columns / rows you have chosen to freeze, as you know. There is no way to indicate that there are 'hidden' rows above when a user sees a sheet at row 400 (for instance). The only indicator is the row count that's always visible. This is what I mean with Excel default behavior.
    – GUI Junkie
    Sep 13, 2011 at 15:34
  • @Isaac, If your user can scroll down to row 400 (for instance) and another user can open that workbook to find the selected cell at row 400, then you cannot 'program' anything to visually indicate that there is information upwards.
    – GUI Junkie
    Sep 13, 2011 at 15:36
  • 2
    I'm looking for design ideas, including those that can be achieved via programming. Is that really not in-scope here? I'm not asking how to implement them. Sep 13, 2011 at 15:39
  • 1
    If you want to program, you could show an arrow icon with a link to the 'first' cell whenever the 'selectedcell.row' is above a certain number. My VBA is a little bit rusty, but this could be done in about half an hour (or less). An arrow could be a visual indicator.
    – GUI Junkie
    Sep 13, 2011 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.