8. Freeze Panes, Split Boxes and Zoom

 
Subtitles Enabled

Next lesson: Formatting Numbers

Preview next lesson

Overview

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!

Summary

  1. Freeze Panes (00:03)

    Freeze panes can be useful when you want to keep the first few rows or columns visible on screen at all times. To freeze panes, you select a cell, then use the shortcut Alt, W, F, F. Any rows above the selected cell and any columns to the left of the selected cell will be frozen. They will be visible on screen at all times as you scroll through the sheet.

    To unfreeze panes, you use the same shortcut command: Alt, W, F, F. You can freeze multiple rows or columns, but usually it makes sense to freeze just the top row or the leftmost column.

  2. Split Boxes (02:11)

    Split boxes split the screen into two separate parts. This is useful when you want to compare parts of the data set that don’t appear on the same screen. To create a split box, you select the cell where you want to create the split box, and use the shortcut Alt, W, S. This splits the screen into multiple boxes, which you can scroll through independently.

    If you create a split from column A, you will create a horizontal split. You can perform vertical splits by splitting from a different column, but these are less useful. To undo a split, use the same shortcut Alt, W, S.

  3. Zoom (03:28)

    The shortcut Alt, V, Z opens the Zoom dialog box. This allows you to select whatever level of zoom you want.

    A common use of the zoom function is to view the entire data set. To do this, select the whole data set, open the Zoom dialog box, and press F to fit selection. This lets you see the whole data set on a single screen.

Transcript

In the previous lesson, we learned how to adjust the size of rows and columns in sheets. In this lesson, we'll learn how to use the freeze panes, split boxes, and zoom tools. In most data sets, we typically have more row entries that 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 all 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 this selected cell and any column to the left of this 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 this 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 dataset, the subject heading stays 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 would 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 enter Alt + W + F + F.

But as you can see as we 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 the 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.

Split boxes work exactly the same way as freezing panes.

Select the cell where you want to place the split box, I'll go to cell A12, and then press the required shortcut.

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 A12, the split will happen above this row. I now have to 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 the column to the right of column A. However, I don't find these splits nearly as useful. I tend to stick with a horizontal split most of the time. To remove a split, just repeat the shortcut, Alt + W + S.

Unfortunately, screen splits don't work with frozen panes. So if you're going to use these commands, 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 data sheet. To zoom, we'll use the shortcut Alt + V + Z, and this brings up the zoom dialog box. To pick one of these options, we'll simply press the first number, for example, seven 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 Control + Shift + Right Arrow, and Control + Shift + Up Arrow. I'll then 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've covered today, freezing panes, split boxes, and zooming, will quickly make you much more efficient at Microsoft Excel.

Excel Excel for Business Analytics Learning Plan
Data Manipulation and Formatting