I love simple things that allow me to streamline process and work faster.
But sometimes those simple things get overlooked in favor of things that are perhaps more functional but require disproportionately more time and effort to use or implement thus negating any real cost savings.
The Simple Excel series of posts covers functionality that is quick and easy to use but provides great return on investment making those deep dives into data easier and more enjoyable.
What it does
Freezing panes allows you to keep selected portions of your worksheet on screen and visible at all times while the non-frozen sections of your worksheet continue to scroll normally.
Why it matters
Scrolling is an unavoidable fact of the data analyst’s life.
It’s also seriously time-consuming and can lead to errors and inconsistencies in data analysis.
And this doesn’t just affect large data sets. Even with a very large monitor it doesn’t take much data in a spreadsheet to force scrolling.
And when you have to scroll up and down is bad, left to right is worse, and both at the same time is a data nightmare. Once you begin scrolling it doesn’t take long before you forget the number you told yourself not to forget and then need to scroll all the way back to remind yourself what that number was.
And then scroll back to where you were.
And then back again.
Talk about a time-wasting process...
The different options
There are three options for freezing panes in Excel:
- Freeze top row
- This option keeps the top row locked in place at the top of the screen as you scroll up and down
- Freeze first column
- This option keeps the first column locked in place on the side of the screen as you scroll left and right.
- Freeze panes
- This is a custom option that allows you to freeze both rows above and columns to the left as you scroll.
How to do it: step by step
- Go to the view tab on the ribbon. This is where the magic happens!
- The display will look a little different on the Mac version vs the Windows version.
Here it is on Excel 2016 for Mac.
Here it is on Excel 2016 for Windows.
- Choose one of the three options below.
- Click freeze top row.
- No matter how far down you scroll the top row will always remain visible allowing you to see the connection between your data and your column headers.
- Click freeze first column.
- No matter how far right you scroll you will always be able to see the data in the first column.
- Click freeze panes.
- Any columns above the and to the left of the currently selected cell will now be frozen in place. So if you have your cursor in cell C3 then rows 1 and 2 will always be visible and columns A and B will always be visible.
- Once you click freeze panes a faint grey line will indicate where the split between the frozen row/column and scrollable area is occurring.
Remember that the best way to improve process is to make small and simple adjustments to your workflow. Adopt an iterative process of constantly refining your approach to data. Learn something each time you open your data app.
It doesn’t take long before a series of small, simple adjustments result in a huge time-savings and more accurate work.