Freezing panes in Excel is used to help display large datasets in a user-friendly format; it should be a tool in your Excel skill set so read this quick guide on how to freeze panes in Excel 2010…
As an Excel user you will often come across large datasets or tables that need to be displayed on a spreadsheet. Excel is designed to handle a large amount of information so placing a table of say 50 columns by 1000 rows would be no problem, however if you have ever opened an Excel workbook with a table like this you will realise that it is not the most user friendly format to display the data.
The problem is you will usually have column headings representing fields in the data and you also might have row headings taking the format of names, business departments etc.
These headings are valuable as they tell the user exactly what piece of data they are looking at, and for whom but if you scroll too far right or too far down these headings will disappear and they can make interpreting the data a lot of effort because the user has to keep scrolling up or left to remember what they are looking at.
We can change the experience for the user quite simply by employing a concept known as freezing pane. Freezing panes is a way of telling Excel to always display certain rows or columns on the worksheet; it effectively locks them from scrolling and comes in very handy.
Take the following spreadsheet example:
This spreadsheet contains employee records and as you can the data goes off the visible screen both to the right and down.
If the user wanted to see the “Actual Bonus” field for employee number “00032” then they would need to navigate to cell K33 so their new view would be:
As the above shows the data can be found, but if you are unfamiliar with Excel or the report in question then you wouldn’t really know what column J is showing anymore as you can no longer see the header row, and we can easily lose track of Employee number “00032” because Column A is no longer visible.
The first step is to navigate back to cell A1 then along the top icon bar select “VIEW”, the click “SPLIT” on the icon list, a cross like shape will appear across the worksheet:
What we actually have now are 2 lines, a vertical (top to bottom) and a horizontal (left to right) which has partitioned our data (only visually). If you hover the mouse pointer over the vertical line, press, and hold a left-click of the mouse, you can now move the selected line around the worksheet using the mouse.
We need to keep column A on show always so the user can always see which employee number they are looking at so the first step is to move the vertical line to after the A column, note you always move the line after the column or row which needs to stay visible.
Don’t worry for now about the fact the A column looks like is has duplicated, it hasn’t, again these are just visual changes we are making.
Next step is to freeze the header row, row 1, so move the vertical bar up to after row 1:
Once that has done the next step is to freeze the panes so they remain set this way. To do that left-click the “Freeze Panes” icon and select “Freeze Panes”:
What will now happen is the thick vertical and horizontal lines will disappear from your view on the worksheet but you will now find that if you scroll across again to cell K33 our view now looks like:
We can still see the employee numbers in column A, and we can also identify the headers so if we now want to check what column J held we can easily see it is “Potential Bonus %”, rather than having to scroll all the way to the top to check, then back down to the value.
Any time you have a dataset or table that goes off the visible screen you should adopt this concept to make sure your Excel workbooks are as user-friendly as possible.