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).

Method 1 – Lock Rows Using Freeze Panes Feature

Case 1.1. Lock Top Rows

Steps:

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

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

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

Case 1.2. Lock Several Rows

Steps:

• 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.

• 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

Steps:

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

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

• 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

Steps:

• 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.

Method 4 – Use VBA Code to Freeze Rows

Steps:

• 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.

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

VBA Code:

Sub UseFreezePanes()
ActiveSheet.Range("D5").Select
ActiveWindow.FreezePanes = True
End Sub

• Click on Run or use the keyboard shortcut (F5) to execute the macro code.

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

Method 5 – Insert Excel Table to Lock Top Row

Steps:

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

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

• This will make your table fully functional.

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

Method 6 – Lock Both Rows and Columns in Excel

Steps:

• 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.

• 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.

Notes

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

• How to Resize All Rows in Excel
• How to Create Collapsible Rows in Excel
• How to Expand and Collapse Rows in Excel
• How to Expand or Collapse Rows with Plus Sign in Excel
• How to Copy Every Nth Row in Excel
• How to Color Alternate Rows in Excel

<< Go Back to Rows in Excel | Learn Excel