While working on a large excel worksheet, we often need to freeze rows and columns at the same time to easily navigate the worksheet. Freezing rows and columns at the same time will let us view the entire worksheet while keeping the necessary rows and columns always visible. In this article, I will explain to you how to freeze rows and columns at the same time in excel using 4 easy methods.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Methods to Freeze Rows and Columns at the Same Time in Excel
Imagine a case where we have an Excel worksheet with information about a company. As we have different information about a lot of employees, we have to slide horizontally to view the different information about an employee or scroll down to see the information about a specific employee whose information is very far down the worksheet. But as we are doing so, we need to keep the column header visible to see which type of information an employee we are looking at. We might sometimes need to keep the information of some employees (Rows) or specific information (Columns) in our view to compare them with others. We can do so by using Freeze Panes, Magic Freeze, and Splitting Panes.
1. Use Freeze Panes to Freeze Rows and Columns Based on Columns
We might want to freeze the Serial and Employee Name columns of our worksheet So that we can keep seeing these two vertical columns as we are sliding horizontally. These can be done following these steps-
- First, we have to choose a column, cell, or all columns on the right side of the Serial and Employee Name In this worksheet Department and Joining, Date columns are on the right of these two columns we want to freeze. So, we will select these two columns or we can also select any cell on column D.
- Now we will select Freeze Panes from the View After selecting the Freeze Panes, a Drop-down list will appear. From the dropdown list, we will select Freeze Panes.
- After selecting Freezing Panes, Serial and Employee Name columns will be frozen and two thin lines will appear above and on the right side of Serial and Employee Names We can now slide horizontally with these two columns always visible to us.
2. Use Freeze Panes to Freeze Rows and Columns Based on Rows
Sometimes we need to freeze some rows of our worksheet. In this worksheet, for instance, we might want to freeze the rows of the first 4 employees along with their information column so that we can compare them with others while scrolling down the worksheet. It is very similar to the first method where we freeze based on columns. This time instead of columns we will do a similar procedure on the rows of the first 4 employees. To do so-
- First, we have to choose a row, cell, or all the rows below the last row we want to freeze. In this worksheet, we want to freeze the rows containing the information of the first 4 employees (John, Devid, Tom, Augustin). So, the next row below these rows is the row containing the information of an employee named Ted (row 9). So, we will select this row or all the rows below it or any cell on row 9.
- Now we will select Freeze Panes from the View After selecting the Freeze Panes, a Drop-down list will appear. From the dropdown list to we will select Freeze Panes.
- After selecting Freezing Panes, the first 4 rows will be frozen and two thin lines will appear below and on the left of 4 frozen rows. We can now scroll down vertically with these 4 rows always visible.
- How to Transpose Column to Multiple Rows in Excel (6 Methods)
- Excel VBA: Get Row and Column Number from Cell Address (4 Methods)
- How to Convert Multiple Rows to Columns in Excel (9 Ways)
- Excel VBA: Set Range by Row and Column Number (3 Examples)
- How to Hide Rows and Columns in Excel (10 Ways)
3. Magic Freeze Button to Freeze Columns and Rows at the Same Time
We can also add the Magic Freeze button to the Quick Access Toolbar to freeze the rows, columns, or cells with a single click. To add the Magic Freeze, we have to follow the steps mentioned below-
- Click the down arrow from the top of the Excel file.
- Click More Commands from the drop-down list.
- Under Choose commands from, select Commands Not in the Ribbon.
- Select Freeze Panes and click Add.
- Click OK to add the command to Quick Access Toolbar.
- Now to freeze rows and columns, select the other rows and columns like the ways mentioned in the previous methods and click Freeze Panes.
- After clicking the Freeze Panes button, the columns and rows will be frozen at the same time.
4. Splitting Panes to Freeze Rows and Columns Simultaneously
Splitting panes is one of the types of freezing panes, when you use splitting panes, Excel will create either two or four separate worksheet areas that you can scroll, while the rows or the columns outside the freezing area will remain visible. To freeze the columns and rows using Splitting Panes we have to-
- Select below the last row which, or the column to the right of the last column you want to be frozen. Suppose we want to freeze the first two rows (information of the first two employees) of the worksheet. So, we will select row 7.
- On the View tab, in the Window group, click Split. The cells will be split and an intersection of two grey thin lines will indicate, where the split occurs.
- Following this step, we will select Freeze Panes from the View After selecting the Freeze Panes, a dropdown list will appear. From the dropdown list, we will select Freeze Panes.
- After clicking on Freezing Panes, our desired rows and columns will be frozen concurrently.
Things to Remember
- At the same time, you can not use Freeze panes and split panes. You can enable only one of the two.
- You have to select the row(s) below the one you want to be frozen or the column right of the column(s) you need to be frozen.
- If you want to freeze rows and columns at the same time based on a reference cell, then the reference cell must be below the row and right of the column you want to freeze.
In this article, we have learned 4 easy ways on how to freeze rows and columns at the same time in excel. To freeze the rows and columns we have used Freeze Panes, Magic Freeze Panes, and Splitting Panes. I hope you now have a clear idea of how to freeze rows and columns simultaneously. If you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!
- Excel Macro: Convert Multiple Rows to Columns (3 Examples)
- How to Transpose Multiple Columns to Rows in Excel
- Excel VBA to Set Range Using Row and Column Numbers (4 Examples)
- How to Add Rows and Columns in Excel (3 Easy Methods)
- Hide Rows and Columns in Excel: Shortcut & Other Techniques
- How to Switch Rows and Columns in Excel (5 Methods)
- Move Row/Column in Excel Without Replacing Existing Data (3 Best Ways)