8. Freeze Panes, Split Boxes and Zoom
Large datasets can be hard to navigate but Excel provides a few tools to help us out. Freeze panes keeps the top row visible at all times. Split boxes allow you to view any two parts of a dataset, and zoom, well zooms!
- Freezing panes ensures that certain rows and columns remain visible at all times
- Where the pane freezes depends on the selected cell
ALT + W, F, F: Freeze / unfreeze panes to the left and above selected cell
- If you want to view two parts of the dataset at the same time, split boxes work well
- To create a split box, drag the grey rectangle above the scroll arrow onto the page
- To remove a split box, drag the dividing line down to the bottom of the worksheet
ALT + V, Z: Open the zoom dialogue box
Using 'Fit selection' for your dataset:
1. Select all your data
2. Press ALT + V, Z
3. Press "F" and click Enter
4. This adjusts the zoom setting so that the full dataset will be shown on one screen
In most data sets, we typically have more row entries than can fit on one screen. As you can see, if I navigate to the bottom of my data set, we can no longer see the column headings at the top of the page.
It would be nice if we could keep the column headings on the page at all times. And to do this, we'll need to use a command called freeze panes.
Where the pane freezes, is determined by the currently selected cell. Any row above the selected cell and any column to the left of the selected cell will be frozen. I would like to freeze the top row and the first column. So to do this, I'll select cell B2, and then I'll use the shortcut Alt + W F F. After freezing panes, you can see black lines appear on the spreadsheet that show you where the panes are now in place. Now let's see what happens when we scroll down the page. As we move down the data set, the subject headings stay in place. Exactly what we want and making our data set much easier to read.
If I scroll across the page, the order number column also stays in place. On worksheets with a lot of columns, for example financial models, freezing panes can save you a lot of time scrolling back and forth across the page. To unfreeze panes, we simply use the same shortcut command we used earlier: Alt + W F F, and that removes our panes from the data set. If we only want to freeze the top row and not the column, we will freeze panes on cell A2. If we wanted to freeze the first column and not the row, we would freeze cell B1. It's also possible to freeze multiple columns and multiple rows. We simply navigate to this cell for example, and Alt + W F F.
But as you can see, if you scroll down the page, this doesn't make a huge amount of sense when we're freezing data entries.
In actual fact, it can get quite confusing. Let's remove these panes again with Alt + W F F.
Sometimes we'd like to compare two parts of a data set at the same time, but unfortunately, they don't appear on the same screen. Say the first five rows and the last five rows of our data set. One nice way to complete this task is using a split box. In Excel 2013 split boxes work exactly the same way as freezing panes. Select the cell where you want to place this split box, I'll go cell A 12 and the press the required short cut. If I go to the View Tab with Alt+ W I can now find the split command here and so I'll press S. Because I'm in cell A 12 the split will happen above this row. I now have two scroll bars on the right so I can see the bottom five rows of the data set. Just what I wanted.
You can also perform vertical splits by selecting a cell in a row to the right of column A. However, I don't find These splits nearly as useful and tends to stick with the horizontal splits most of the time. To remove the split, just repeat the shortcut Alt + W S. Unfortunately, screen splits don't work with frozen panes. so if you're going to use this command, be sure to unfreeze all panes before doing so. We'll finish up this lesson by introducing the Excel zoom function. Because data sets can extend to thousands of rows and columns, the zoom function is a helpful way of getting a bird's eye view of your full datasheet. To zoom, we'll use a shortcut: Alt + V Z, and this brings up the zoom dialog box. To pick one of these options, we can simply press the first number, for example 7 for 75, or we can use the arrow keys. To select a new zoom, press OK. I tend to use the zoom function to view the whole data set, and as a result, I use the fit selection command.
To do this, let's select the whole data set with Ctrl + Shift + Right Arrow, and Ctrl + Shift + Up Arrow.
Then I'll use my shortcut: Alt + V Z and then click F and press OK. Now we can view the whole data set at the largest possible zoom setting. Fit selection is the last command we're going to cover in this lesson.
Hopefully, the three techniques we covered today, freezing panes, split boxes and zooming, will quickly make you much more efficient at Microsoft Excel.