Thanks to Dan Battagin for writing this blog post.
OK, so I’m sure everyone knows about the Freeze Panes feature in Excel – it’s been around forever, and it’s pretty useful in certain circumstances where you want to keep a row (or more) of data at the top of the sheet, or column on the side of the sheet – especially for filtering. Of course, we’ve made it a little less necessary in the 2007 and 2010 releases, since table headers (2007) and now table filters (2010) automatically camp out in the row headers so that you can easily see and filter any information in a table.
Convenient column header – without freeze panes – nice.
I like this new functionality, but I often find myself wanting to create a chart to summarize some data in the table – usually to see the chart continue to grow as I continue adding data to the table, showing me a trend over time. Once I do this, I’m constantly scrolling up and down (up to see the chart, and down to enter new data). What I really want is a feature that only scrolls the table rows, and not the whole sheet…which of course, Excel doesn’t have. (It’s almost like freeze panes, except with an extra set of scrollbars, so that you can scroll each of 4 frozen panes independently…though that leads to other problems.) Oh, and since I can read your mind – I can’t use freeze panes with the chart ‘in the freeze’ because I’ve got a super-small notebook, with very limited vertical screen space. To freeze panes, I’d end up with a whopping two rows that scrolled vertically!
Here’s a sample spreadsheet that illustrates the problem, where I track the weight of my two cats, Silver and Bullet (brothers), who like to eat more like dogs, than cats.
What I want (note the vertical scrollbar in the middle), even though the design ‘falls apart’ a bit when you actually think about it.
OK, so what’s my solution? While it may be a bit anti-climactic, it’s to simply move the chart(s) down the page as the visible range changes, making sure that it always stays in generally the same spot visually. I’m sure there’s a better way to write the code – feel free to propose options!
To add this code to your own project, just take the following steps (or open the attached project):
- press Alt+F11 to open up the VBA editor
- Open the Sheet class for the sheet that contains your table and chart (Sheet1 in my case)
- Add the following code inside the editor.:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim vOffset As Double
Dim currentRow As Long
currentRow = Me.Application.ActiveWindow.scrollRow
If previousRow <> currentRow Then
If previousRow = 0 Then
previousRow = Me.Application.ActiveWindow.scrollRow
‘ reposition charts
For Each c In ActiveSheet.ChartObjects
vOffset = c.Top – Me.Rows(previousRow).Top
c.Top = Me.Rows(currentRow).Top + vOffset
previousRow = currentRow
- When you’re done, just close the VBA editor, and scroll away (vertically). Any charts you have on the sheet should stay in their relative positions, within the visible range of the sheet as you scroll vertically.
Anytime you have a really long table of data, and especially if you have a small screen resolution (netbook or iPad anybody?) this can be a lifesaver, or at least a convenience. Of course, you can take this example even further to handle horizontal scrolling as well as vertical, but I’ll leave that as an exercise for the reader. Now, if you’ll excuse me I’ve got to go weigh my cats.