How to Lock Rows in Excel (6 Easy Methods)

The dataset we are using to lock rows contains some products and their prices and percentage of value-added tax (VAT).

6 Easy and Simple Ways to Lock Rows in Excel

Method 1 – Lock Rows Using Freeze Panes Feature

Case 1.1. Lock Top Rows


  • Go to the View tab on the ribbon.
  • Select the Freeze Panes and choose Freeze Top Row from the drop-down menu.

Lock Rows Using Freeze Panes Feature

  • This will lock the first row of your worksheet, ensuring that it remains visible when you browse through the remainder of it.

Lock Rows Using Freeze Panes Feature

  • If we scroll down, we can see that the top row is frozen.

Case 1.2. Lock Several Rows


  • Select the first cell in the row below the rows we want to freeze.
  • Click the View tab on the ribbon.
  • In the Freeze Panes drop-down menu, choose the Freeze Panes option.

Lock Several Rows

  • The rows will lock in place, as demonstrated by the gray line. We can look down the worksheet while scrolling to see the frozen rows at the top.

Read More: How to Create Rows within a Cell in Excel

Method 2 – Excel Magic Freeze Button to Freeze Rows


  • Go to the drop-down arrow from the top of the Excel file.
  • Click on More Commands from the drop-down.

Excel Magic Freeze Button to Freeze Rows

  • From the Quick Access Toolbar, choose Commands Not in the Ribbon.

Excel Magic Freeze Button to Freeze Rows

  • Scroll down to the Freeze Panes option then select it.
  • Click on Add and then OK.

  • Freeze Panes are shown above on the Name Box. We can now access the freeze panes option quickly.
  • After clicking on the Freeze Panes button, the columns and rows will be frozen at the same time.

Method 3 – Lock Rows Using the Split Option in Excel


  • Select the row below that we want to split.
  • Click the Split button on the View tab.

  • We can see two separate scroll bars. To reverse a split, click the Split button once again.

Lock Rows Using Split Option in Excel

Method 4 – Use VBA Code to Freeze Rows


  • Select any cell below where we want to lock the rows and columns at the same time.
  • Right-click on the spreadsheet and select View Code.

Use VBA Code to Freeze Rows 

  • A VBA Module window appears where you can paste this code:

VBA Code:

Sub UseFreezePanes()
ActiveWindow.FreezePanes = True
End Sub
  • Click on Run or use the keyboard shortcut (F5) to execute the macro code.

Use VBA Code to Freeze Rows 

  • All the rows and columns are locked in the worksheet.

Method 5 – Insert Excel Table to Lock Top Row


  • Select the whole table.
  • Go to the Home tab and select Format as Table.

Insert Excel Table to Lock Top Row

  • A pop-up window appears. Ensure that the source box covers the entire dataset.
  • Check My Table has headers.
  • Click on the OK button.

Insert Excel Table to Lock Top Row

  • This will make your table fully functional.

Insert Excel Table to Lock Top Row

  • When scrolling down, we can see our headers are shown on top.

Method 6 – Lock Both Rows and Columns in Excel


  • Choose a cell that is just below the rows and close to the column we wish to freeze. For example, if we want to freeze rows 1 to 4 and columns A, B, C, we will select cell D5.
  • Go to the View tab.
  • Click on the Freeze Panes drop-down.
  • Select the Freeze Panes option from the drop-down.

Lock Both Rows and Columns in Excel

  • The columns to the left of the selected cell and the rows above the selected cell will be frozen. Two gray lines appear, one just next to the frozen columns and the other directly below the frozen rows.

Freeze Panes Aren’t Operating Properly to Lock Rows in Excel

If the Freeze Panes button in the worksheet is disabled, it’s most likely for one of the following reasons:

  • You are in cell editing mode. Press the Enter or Esc keys to leave cell editing mode.
  • The spreadsheet is protected. Please unprotect the worksheet first, then freeze the rows or columns.


You can freeze rows from the top and columns from the left. You can’t freeze the third column and nothing else around it.

Download Practice Workbook

You can download the workbook and practice with them.

Related Articles

<< Go Back to Rows in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sabrina Ayon
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF