Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm familiar with basic freeze pane functionality. If I freeze the first column by Freeze Pane > Freeze First Column, it works against a horizontal scroll, but not a vertical scroll.

I'd like for the column to be frozen no matter how I scroll. IE, what's in the first column should always remain visible, no matter how I scroll the rest of the window. Is this possible?

Edit

To be clear, regular Freeze Panes won't work because I don't want any rows to be locked. So when I scroll vertically, I want the first column to not scroll, but all rows (outside of any cells in the first column) to scroll as normal.

share|improve this question
    
So you mean if you scroll down, you still see say A1:A10 but not the rest of rows 1-10?!? –  Peter Albert Feb 19 '13 at 22:32
    
Yes, that's exactly what I'm looking for. –  Jeremy Wiggins Feb 19 '13 at 22:34
1  
The only thing close to what you need is to use Split (select B2 and click View-Window-Split). However, this has so many downsides that i cannot imagine you want this... :-( –  Peter Albert Feb 19 '13 at 22:36
    
I'm sure you want to solve your problem this way, but could you explain a little more what you are attempting to accomplish? I get the feeling that you want to treat the left column as a kind of nav-bar. Or does it show results that are modified by changes you're making deep in the table? –  Dane Feb 19 '13 at 22:43
    
@Dane - the second thing you said. It basically shows some cells with calculations based on summations & averages of other columns. A summary of sorts. I'd like to be able to see the summary, even if I'm 200 rows down the 'data' part of the sheet. –  Jeremy Wiggins Feb 20 '13 at 0:32
add comment

3 Answers

up vote 2 down vote accepted

Here's what you can do.

  • First unmaximize the worksheets in your workbook by left-clicking on the small interlocking windows (squares) icon under the red close icon in the top right corner of the Excel window.
  • Create a second window for your workbook by right-clicking on the top border of your worksheet (above the row identifiers A, B, etc.). -
  • Choose View / Arrange All with the "Vertical" radio button selected and "Windows of active workbook" checked.
  • Adjust the vertical boundaries of the two windows so that the left one just shows the "summary" columns you are interested in seeing, and the right one fills the rest of the space.
  • In the right window, insert a new column to the right of the columns that are showing in the left window and then scroll the right-side sheet so that this column is the leftmost one showing in the right window. (The new column won't be visible in the left window.)
  • Reduce the width of the column to make a narrow margin and then freeze it in place (I'm assuming you will freeze the top row as well).

In the end it will look something like this:

enter image description here

Since the scrolling of the two windows is not synchronized, you will see the summary numbers you are interested in the window on the left, and you will be able to scroll through the rest of the sheet in the right window without problem.

share|improve this answer
    
Where exactly do you click in the first step? –  Peter Albert Feb 20 '13 at 8:12
    
See amended answer above. –  chuff Feb 20 '13 at 13:46
    
Brilliant! +1 I didn't know you can create a new window of an existing, open workbook - great! I took the liberty to add a screenshot to your answer as I was following your instructions anyway. –  Peter Albert Feb 20 '13 at 18:54
    
I took another liberty - and used your approach for the below macro. Hope you don't mind! ;-) –  Peter Albert Feb 20 '13 at 19:47
add comment

Based on chuff's answer, I wrote the following macro. It does what chuff describes - and in addition names the two windows "Dashboard" and "Data" and hides all elements (scrollbars, worksheet tabs, row/column header) from the dashboard.

To get it to run, do the following:

  • Copy the code below
  • In Excel, go to the Visual Basic Editor (Alt-F11)
  • Insert a new module by right-clicking on the project in the left tree and selecting Insert->Module (in case you want to apply this technique to other workbooks, too, place it in your personal macro workbook instead)
  • Paste the code in the new module
  • If you wish, change the number of columns or the titles of the two windows by changing the first lines of the code starting with Const
  • Execute the code - either be clicking in the first procedure and pressing F5 - or going back to Excel (Alt-F11) and pressing Alt-F8. Select the macro here and click Run. (Note that you can also assign a shortcut to it in this dialog).

Here is the macro:

Sub SplitWindows()
    Const cIntPaneColumns As Integer = 2
    Const cStrPaneName As String = "Dashboard"
    Const cStrMainName As String = "Data"

    Dim i As Integer
    Dim wndMain As Window, wndPane As Window
    Dim dblOldWidth As Double, dblPaneWidth As Double

    Set wndMain = ActiveWindow

    If ThisWorkbook.Windows.Count > 1 Then
        If MsgBox("Multiple windows for current workbook are already displayed. Do you want to close/rearrange them?", vbYesNo) = vbYes Then
            For i = 2 To ThisWorkbook.Windows.Count
                ThisWorkbook.Windows(1).Close
            Next
        Else
            Exit Sub
        End If
    End If

    Set wndMain = ActiveWindow
    wndMain.WindowState = xlNormal
    Set wndPane = wndMain.NewWindow

    ActiveWorkbook.Windows.Arrange ArrangeStyle:=xlVertical

    dblOldWidth = wndPane.Width
    dblPaneWidth = Range("A1").Resize(, cIntPaneColumns).Width

    ConfigureWindow wnd:=wndPane, blnShowElements:=False, _
        strCaption:=cStrPaneName, dblWidth:=dblPaneWidth, _
        dblLeft:=1

    ConfigureWindow wnd:=wndMain, blnShowElements:=True, _
        strCaption:=cStrMainName, _
        dblWidth:=wndMain.Width + (dblOldWidth - dblPaneWidth), _
        dblLeft:=dblPaneWidth

    With wndMain
        .ScrollColumn = cIntPaneColumns + 1
        .Activate
        .ActiveSheet.Range("A1").Offset(, cIntPaneColumns + 1).Select
        If .FreezePanes Then .FreezePanes = False
        .FreezePanes = True
    End With

End Sub

Private Sub ConfigureWindow(wnd As Window, _
    blnShowElements As Boolean, _
    strCaption As String, _
    dblWidth As Double, _
    dblLeft As Double)

    With wnd
        .Width = dblWidth
        .Left = dblLeft
        .DisplayHeadings = blnShowElements
        .DisplayHorizontalScrollBar = blnShowElements
        .DisplayVerticalScrollBar = blnShowElements
        .DisplayWorkbookTabs = blnShowElements
        .Caption = strCaption
    End With
End Sub
share|improve this answer
    
Wow man, that's f'ng awesome. Thank you. –  Jeremy Wiggins Feb 20 '13 at 19:41
    
I'm accepting @chuff's answer since he provided the actual how-to, but many thanks for this macro! –  Jeremy Wiggins Feb 20 '13 at 19:42
    
@JeremyWiggins: You're welcome! –  Peter Albert Feb 20 '13 at 19:46
    
Good for you, @Peter! –  chuff Feb 20 '13 at 22:53
add comment

This is possible:

Just select cell B2 and the click Freeze Panes:

enter image description here

This will keep everything left and top of the cell visible, i.e. row 1 and column A!

If you only want the left column, simply select cell A2 instead - or click on Freeze First Column.

share|improve this answer
    
I don't want any rows to be frozen though. I'll edit OP to be more clear. –  Jeremy Wiggins Feb 19 '13 at 22:28
    
I gave the generic answer, as with this approach you can achieve whichever freeze you wish. I updated the answer to also show how to freeze only one column. HTH! –  Peter Albert Feb 19 '13 at 22:30
    
Freeze First Column only freezes for horizontal scroll though. I specified in OP that I want it to be frozen for vertical scroll as well. –  Jeremy Wiggins Feb 19 '13 at 22:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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