You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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):
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.
...netbook or iPad anybody?
So Microsoft is working on an Excel version for the iPad?
Wouldn't split work? Table in the lower right and chart in the upper left or something similar!
XCLent idea 4 sure! Rather presents a Vista Type beauty! However, the code needs to be inserted separately 4 EACH and EVERY sheet due to Worksheet_SelectionChange!
Can't be applied as an addin?
I was wondering if someone can help me with a chart I am making. I am stuck and I know I am close but I cant seem to get it. The spreadsheet is a sheet that will automatically populate the correct dates in excel for Both Interim Evaluations and Yearly Evaluations.
Nice. My "solution" has generally been to make row 1 very tall and to put the chart there, above the column headers. The downside of that approach is that I tend to have a lot of empty space to the right of my workbook (and I can only see half as much data).
Can you not use the New Window option and arrange vertically.
Why not just start the data lower down, say on row 20 (headers). Put the graph at the top over rows 1 to 19. Then freeze panes between row 20 and 21. So you get the graph and data header frozen, but the rest of the data scrollable.
Or am I missing some subtle point here?
Ditto Andy Pope: create a new window for each chart, arrange and save the workspace. An event macro seems like overkill for this.
Lot's of comments - neat! Here's goes my attempt at answers...
@*** - Nothing currently in the plans as far as I know - guess we'll both have to wait and see. That was suppossed to be a bit of a joke (but of course, that doesn't come across as well when it's not in person.
@Vaibhav Garg - I don't think split would work in my case, given the constraints I'm running under - that is, I have very limited screen size on a netbook, and so I can't afford to lock more than a row or two. Locking anything (regardless of positioning of the chart, would result in me only seeing a couple rows of data at a time.
@Faraz Ahmed Qureshi - yep, that is a downside. I'll see if I can come up with a workaround for that one, though it probably won't be real pretty - something along the lines of a way to automatically add the code to each sheet, and to new sheets when they are added. Then it could be easily done as an addin.
@John DC - A couple good places to ask: you can use the contact link on the blog here, or you can go to the public Excel newsgroups (microsoft.public.excel) and ask there - and hopefully someone can help you out.
@Mike Woodhouse - Yeah, I've done that before too - same problem as I mentioned for Vaibhav in my case - just not a big enough screen...
@Andy Pope - Yep, I could do that, and it would get me something similar to the screenshot I showed in the post (with the scrollbar in the middle.) I'm not a huge fan of multiple windows though, so I generally try to stay away from those - again, it's a screen-real estate thing for me.
@SteveA - Screen size issue again - I think I tried to cover the situation in the second paragraph - I'd end up with 2 rows scrolling if I froze the rows containing the chart - yuk!
Does the chart automatically grow as you add data or do you have to update it every time? If it automatically grows how do you create such a chart?
@GregB: Dan stored his data in an Excel table (introduced in Excel 2007), so the chart updates whenever you add, remove, or update the table. Basically, the chart draws its two data series from the table columns named Bullet and Silver as opposed to the cell ranges D3:D25 or E3:E25.
I like the New Window approach and you can recover a little screen realestate by
1. Turning off Heading while you are on the chart side.
2. Turning off Sheet Tabs, if you want more vertical space on the chart side.
3. And since scrollbars only show in the active window you automatically gain that realestate.
If it is OK to do it for one workbook, then you can use the Workbook_SheetSelectionChange event in the Thisworkbook module:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'Modified version of Dan's code goes here
If you want this to work for ANY worksheet in ANY workbook, then you'd have to put the code inside a class module which handles application events and uses the application's SheetSelectionChange event:
Class module code (needs some code in a normal module to hook up this class to the Excel application):
Public WithEvents App As Excel.Application
Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
In a normal module (Class is named Class1):
Dim mAppClass as Class1
' Create instance of class in memory
Set mAppClass = New Class1
'Tie Excel application to class so it starts listening to events:
Set mAppClass.App = Application
do you need the vba code ? - i thought you can have split window and switch the sync off
@JKP - Thanks, right along the lines of what I was thinking for adding to the book level, but the add-in modification is a great addition!
@Shane, @Alex99 - yep, those are options (and Shane, you have some good points about reclaiming space. Just a personal preference I guess that I don't like the multiple windows.