Chapter 42: Freeze Panes, Hiding, and Custom Views in Excel

In this chapter, we will explore how to use Freeze Panes to keep specific rows or columns visible while scrolling through large datasets. We’ll also cover how to hide and unhide columns and rows, as well as how to use Custom Views to save and switch between different display settings.

1. Freeze Panes

Freeze Panes is a feature that allows you to keep specific rows or columns visible as you scroll through your worksheet. This is particularly useful when working with large datasets where you want to keep headers or labels visible.

Freezing a Single Row:

1. Select the Row Below:
   • Click on the row number directly below the row you want to freeze.
2. Freeze Panes:
   • Go to the View tab on the Ribbon.
   • Click on Freeze Panes.
   • Select Freeze Panes from the drop-down menu. The row above will remain visible while you scroll down.

Freezing Multiple Rows:

1. Select the Row Below the Last Row to Freeze:
   • Click on the row number below the last row you want to freeze.
2. Freeze Panes:
   • Go to the View tab.
   • Click Freeze Panes.
   • Select Freeze Panes from the drop-down menu. All rows above the selected row will stay visible while scrolling.

Freezing a Single Column:

1. Select the Column to the Right:
   • Click on the column letter directly to the right of the column you want to freeze.
2. Freeze Panes:
   • Go to the View tab.
   • Click on Freeze Panes.
   • Select Freeze Panes from the drop-down menu. The column to the left will remain visible while scrolling horizontally.

Freezing Multiple Columns:

1. Select the Column to the Right of the Last Column to Freeze:
   • Click on the column letter to the right of the last column you want to freeze.
2. Freeze Panes:
   • Go to the View tab.
   • Click on Freeze Panes.
   • Select Freeze Panes from the drop-down menu. All columns to the left of the selected column will stay visible.

2. Hiding and Unhiding Columns and Rows

Hiding columns and rows can help you manage your worksheet by removing unnecessary data from view without deleting it.

Hiding Columns:

1. Select the Columns to Hide:
   • Click on the column letter(s) you want to hide. For multiple columns, click and drag across the column letters.
2. Hide Columns:
   • Right-click on the selected columns.
   • Choose Hide from the context menu.

Unhiding Columns:

1. Select the Adjacent Columns:
   • Click on the column letters on either side of the hidden columns.
2. Unhide Columns:
   • Right-click on the selected columns.
   • Choose Unhide from the context menu.

Hiding Rows:

1. Select the Rows to Hide:
   • Click on the row number(s) you want to hide. For multiple rows, click and drag across the row numbers.
2. Hide Rows:
   • Right-click on the selected rows.
   • Choose Hide from the context menu.

Unhiding Rows:

1. Select the Adjacent Rows:
   • Click on the row numbers on either side of the hidden rows.
2. Unhide Rows:
   • Right-click on the selected rows.
   • Choose Unhide from the context menu.

3. Custom Views

Custom Views allow you to save and switch between different display settings in your worksheet. This is useful when you need to view the same data in different ways.

Creating a Custom View:

1. Set Up Your View:
   • Adjust the worksheet layout, column widths, row heights, filters, and other settings to your desired view.
2. Create the View:
   • Go to the View tab on the Ribbon.
   • Click on Custom Views.
   • In the Custom Views dialog box, click Add.
   • Enter a name for the view and click OK. Your current view settings are saved.

Switching Between Custom Views:

1. Open Custom Views:
   • Go to the View tab.
   • Click on Custom Views.
2. Select the View:
   • Choose the view you want from the list.
   • Click Show to apply the selected view.

Deleting a Custom View:

1. Open Custom Views:
   • Go to the View tab.
   • Click on Custom Views.
2. Delete the View:
   • Select the view you want to delete.
   • Click Delete.

Practice Assignment

1. Freeze Panes:
   • Create a worksheet with a large dataset. Freeze the top row and the first column to keep headers visible.
2. Hide and Unhide Columns and Rows:
   • Hide columns B and C, then unhide them. Similarly, hide rows 5 through 10, and then unhide them.
3. Custom Views:
   • Set up a worksheet with different filters applied. Save this as a custom view named “Filtered View”. Create another custom view with different column widths and save it as “Wide Columns”. Switch between these views to see how they affect your data display.

Pro Tip

• Quick Freeze Panes Shortcut: You can quickly access the Freeze Panes options by pressing Alt + W, F, F. This shortcut opens the Freeze Panes menu, where you can select the desired option.

This chapter covers essential techniques for managing your worksheet’s display, including freezing panes, hiding and unhiding rows and columns, and using custom views. Practice with the provided assignments to become proficient in these features!