Difficulty: Easy
Issue: Keeping a certain row or column of cells "Frozen" so that when you scroll down / sideways, these frozen columns/rows will still be in view.
Process: Select rows / cells >> View >> Freeze Panes
Example: Freezing a single row or column
In this example, I have a large table dataset in Excel. I would like to be able to scroll down and view all the data whilst also still seeing the table headings. Select the row (our table headings) that you want to freeze. In our example, we want to freeze row 3.
Click on the View tab on the Ribbon. Select the Freeze Panes command, then choose Freeze panes
The rows will be frozen in place, as indicated by the gray line. You can scroll down the worksheet while continuing to view the frozen rows at the top
You can also follow the same methodology to freeze larger areas of data or even columns. In the below example, I have selected column A and followed the same process to select "Freeze Panes"
Example: Unfreezing cells
You can only have one set of data frozen at a time (e.g. either header or column) (however you can have both with a workaround, discussed below).
In this example, I had previously frozen the table headings, but now I wish to freeze column A (Salespersons) so I can scroll across to the last month To unfreeze rows or columns, click the Freeze Panes command, then select Unfreeze Panes from the drop-down menu.
Advanced: Workaround to freeze both the headings row and first column simultaneously aka splitting a worksheet
Sometimes you may want to compare different sections of the same workbook without creating a new window. The Split command allows you to divide the worksheet into multiple panes that scroll separately. Select the cell where you want to split the worksheet. In our example, we'll select cell C7.
Click on the View tab on the Ribbon and then select SPLIT
The workbook will be split into different panes. You can scroll through each pane separately using the scroll bars, allowing you to compare different sections of the workbook.
3After creating a split, you can click and drag the vertical and horizontal dividers to change the size of each section. To remove the split, click the split command again.
Full Excel Masterclass Course
If you want to learn more about Excel, please check out our full excel course here (you can try the free course first here)
If you want to learn more about Excel, regardless of your skill level, we offer free and premium Excel training courses on our website, www.meritexcel.com. Our Excel masterclass covers:
- all the key formulas you need (sumif, index, match, offset, eomonth, concatenate, lookup, if, or, sumproduct, date and time formulas, trim and much more)
- How navigate and use Excel effectively (viewing in 2 windows, paste special, locking cells, removing duplicates, autofill, freezing rows, fill blanks)
- Best practice techniques in Excel (formatting, checks, grouping, formula auditing, structure)
- How to present and manipulate data (Pivot tables, charts, editing charts, filtering and sorting)
- How to create engaging and dynamic spreadsheets (drop-downs, protect data, goal seek and data tables)
- And much, much more!
We offer a free, limited course which you can view here OR you can purchase the full, Excel masterclass here, which has over 20+ hours of amazing Excel content.
Comments