5 Ways to Highlight Current Row and Column in Microsoft Excel

Do you ever find yourself getting lost in a worksheet? Keep reading to learn various ways to highlight current rows and columns in Excel.

Losing track of the current row and column in Excel is a frequent issue. Especially, it’s common in Excel worksheets containing thousands of columns and rows filled with data.

You’re constantly jumping from one cell to another, switching between applications, and your eyes are constantly scanning different areas on the PC screen.

When working with sensitive and critical data in Excel, it’s crucial to ensure you’re concentrating on the right record. However, the farther you stray away from the row and column headers, the more challenging it becomes to track the active cell position.

You often wish there was a more efficient method than painstakingly scrolling the mouse wheel. Regrettably, Excel lacks a native solution to tackle this problem.

Nothing to worry about as I’m going to explain below a few common methods to select the current row and column in Excel.

Reasons to Highlight Current Row and Column in Excel

Find below when you might need to see the current row and column position in Excel:

  • You’d like to enhance visibility and focus on the current record in a large dataset.
  • You’re looking to facilitate quick navigation and identification of data within the worksheet.
  • When you know which cell, row, and column you’re in, it minimizes the risk of making incorrect calculations referring to wrong cell addresses or cell ranges.
  • You can save time by eliminating the need for manual tracking or scrolling.
  • By providing visual cues to the data entry operators and data analysts in your Excel worksheet, you increase their productivity.
  • The audience can easily see the location of your cursor or highlighted cell when you add the feature to highlight the current row and column in Excel.
  • You can also help others reduce eye strain by making it easier to locate and follow the active row and column.

So far, you learned the benefits of highlighting the current cells, rows, and columns in Excel. Now, explore below various intuitive methods to accomplish this task:

Highlight Current Row and Column Using Excel UI Elements

You create your database or data tables in Excel within rows and columns. These rows and columns come with a grid coordinate-like identification system. It’s similar to X-axis and Y-axis graphs.

How to locate your cursor on Excel without any tools or functions
How to locate your cursor in Excel without any tools or functions

For example, you can consider the row numbers panel on the left side as the Y-axis and the column headers at the top as X-axis. Any cell you highlight within the Excel spreadsheet is an intersection of these X and Y axes.

Using this concept, you can easily identify the active cell in your worksheet. Since any cell is the cross-section of the Y-axis and X-axis, if you’re in the marked cell below, the position of your cursor is C5 (X=C, Y=5).

An address of a cell in Excel
An address of a cell in Excel

No matter how large the worksheet is, the column headers and row numbers panel will always be visible to you. So, it won’t be an issue if you go to the far right or scroll down the worksheet much below. You can always refer to the column headers and row numbers to know your exact position on the worksheet.

Also read: Ways to Freeze Rows and Columns in Excel

Use These Shortcuts to Highlight the Current Row and Column in Excel

Above, you learned how to manually locate the cell address, row number, and column header on any Excel worksheet. However, there’s a smarter way to do this. You can use cool shortcuts instead of using the grid coordinate system mentioned above.

Find below the shortcut to highlight the current cell and row:

Shift + Space

See below how the above hotkey works:

Hotkey to highlight the active row and cell
Hotkey to highlight the active row and cell

Here’s the shortcut key combination to mark the active column and cell in Excel:

Ctrl + Space

The image below shows the way this hotkey highlights the column and cell:

Hotkey to highlight the active column and cell
Hotkey to highlight the active column and cell

These hotkeys also work on Excel for Mac desktops and Excel online app.

Also read: Keyboard Shortcuts for Working with Rows and Columns in Excel

Utilize These Conditional Formatting Rules

The hotkey method above only highlights the current row and column in the default grey shade. It doesn’t give you the option to change the highlighting color.

When you wish to mark the active row and column using a color of your choice, you can use Conditional Formatting. Find below three different rules that you can use right away:

Highlight the Current Row

  1. Select the cell range where you want to apply this rule. Alternatively, select the entire worksheet.
  2. Click the Conditional Formatting button inside the Styles commands block.
  3. On the context menu that pops up, select New Rule.
New rule
New rule
  1. Select Use a formula to… inside the New Formatting Rule dialog box.
  2. Copy and paste the following rule inside the Format values where… field.
=CELL("row")=ROW()
New formatting rule
New formatting rule
  1. Click the Format button and select the Fill tab at the top of the Format Cells dialog box.
  2. Choose a color from the Background Color list.
  3. Click OK on the Format Cells dialog box.
Format Cells
Format Cells
  1. On the New Formula Rule dialog box, click OK to apply the rule.

When you’re back in the worksheet, you should see Excel already highlighted the row of the active cell. If you select another cell, just press F9 to refresh the Excel worksheet. This will also highlight the row of the new active cell.

Highlighting active row using conditional formatting
Highlighting active row using conditional formatting

Highlight the Current Column

Follow the steps mentioned above to create a new Conditional Formatting rule in the worksheet where you want to highlight the column. Enter the following rule into the Use a formula to… field:

=CELL("col")=COLUMN()

Don’t forget to click Format and choose a color from the Fill tab of the Format Cells window to highlight the active column. Click OK on Format Cells and New Formula Rule windows.

Highlighting active column using conditional formatting
Highlighting active column using conditional formatting

You should now see that Excel highlighted the column of the active within the selected range or the whole worksheet. When you change the cell selection, press F9 to highlight the new column.

Whenever you’re creating this Conditional Formatting rule, you must select the cell range or whole the worksheet.

Highlight Both the Column and Row

If you want to highlight both the active column and row in your worksheet, use the following formula inside the Use a formula to… field:

=OR(CELL("col")=COLUMN(),CELL("row")=ROW())
Highlighting active column and row using conditional formatting
Highlighting active column and row using conditional formatting

Follow the steps mentioned earlier to use this rule. Don’t forget to select all cells or a range of cells to see the effect.

Highlight Current Row and Column Using Excel VBA

Setting up conditional formatting for different worksheets is a time-consuming task. If you need to frequently highlight current row and column along with the active cell on multiple large worksheets, you can use automation. This automation is based on an Excel VBA script.

Follow these steps to insert the following VBA code into your active worksheet to automatically highlight row and column along with the active cell in Excel:

  1. Go to the worksheet where you want the above feature.
  2. Right-click on the worksheet name tab at the bottom of the software.
  3. Click View Code on the context menu that shows up.
View code on excel
View code on excel
  1. A blank module will open automatically. There, copy and paste this VBA script:
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static xRow
Static xColumn
If xColumn <> "" Then
    With Columns(xColumn).Interior
        .ColorIndex = xlNone
    End With
    With Rows(xRow).Interior
        .ColorIndex = xlNone
    End With
End If
pRow = Selection.row
pColumn = Selection.Column
xRow = pRow
xColumn = pColumn
With Columns(pColumn).Interior
    .ColorIndex = 6
    .Pattern = xlSolid
End With
With Rows(pRow).Interior
    .ColorIndex = 6
    .Pattern = xlSolid
End With
End Sub
  1. Click the Save button.
  2. Close the Excel VBA Editor.
Add code and close VBA
Add code and close VBA

Now, as soon as you click or highlight any cell on the worksheet, Excel will highlight the row and column in yellow. If you want the feature in other worksheets of the same or different workbooks, you must repeat the above steps on those worksheets.

Example showing how this code will work
Example showing how this code will work

Highlight Current Row and Column Using Find My Cursor Add-In

This is the easiest way to quickly find the cursor position, cell address, row, and column in Excel. You just need to download and install the Find My Cursor add-in from the JKP Application Development Service portal. Find below the instructions to download and run the Excel add-in:

  1. Go to JKP-Ads.com.
  2. Scroll down to Find My Cursor.
  3. Click the FindMyCursor.zip link just below the GIF animation.
Download add-in
Download add-in
  1. The findmycursor.zip file will download in the Downloads folder on your PC.
  2. Unzip the content to a directory of your choosing.
  3. Open your Excel worksheet.
  4. Click Developer and select Excel Add-ins inside the Add-ins commands block.
Developer tab Add-ins
Developer tab Add-ins
  1. On the Add-ins dialog box, click Browse.
Click browse on add-ins
Click browse on add-ins
  1. Using the Browse dialog box. go to the directory where you unzipped the add-in file previously.
Open add-in file
Open add-in file
  1. Select the file and click Open.

The Add-ins dialog box should now show the Find my Cursor in the list with a checkmark on it. Click OK to complete the add-in installation process.

Install add-in
Install add-in

That’s all about installing the Excel add-in. You can use the same method when installing other add-ins that are not in the Office Add-ins store.

Now, when working on a large worksheet on your Excel desktop app, press the following hotkeys to locate your cursor. The Find My Cursor add-in will create an animated view to show the cursor and currently selected cell.

Ctrl + Shift + H

The add-in will be available for all the worksheets that you open on the current PC. If you switch devices, you need to install the add-in again.

Conclusions

If you made it here, congratulations! You’ve enhanced your Excel skills to a better level than your friends or colleagues. Now you know how to highlight current rows and columns using shortcuts, Conditional Formulas, VBA scripts, and smart Excel add-ins.

Give the above methods a try and find out which one is the best for your worksheet. Don’t forget to mention the method below in the comment to help other readers with hands-on insights.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Related Posts

Comments

3 Comments

  1. Andy Yu

    I’ve enhanced the selection to multiple columns and rows. Thank you for your template.

    Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Static OldRows As Range
    Static OldColumns As Range

    ' Clear previous highlighting
    If Not OldRows Is Nothing Then
    OldRows.Interior.ColorIndex = xlNone
    End If
    If Not OldColumns Is Nothing Then
    OldColumns.Interior.ColorIndex = xlNone
    End If

    ' Set new rows and columns to be highlighted based on the target range
    Set OldRows = Rows(Target.Row & ":" & Target.Row + Target.Rows.Count - 1)
    Set OldColumns = Target.Worksheet.Range(Target.Worksheet.Cells(1, Target.Column), Target.Worksheet.Cells(Target.Worksheet.Rows.Count, Target.Column + Target.Columns.Count - 1))

    ' Highlight the entire rows and columns of the current selection
    Application.EnableEvents = False
    OldRows.Interior.ColorIndex = 6
    OldColumns.Interior.ColorIndex = 6
    OldRows.Interior.Pattern = xlSolid
    OldColumns.Interior.Pattern = xlSolid
    Application.EnableEvents = True
    End Sub

    • John MacDougall

      Thanks for the upgrade!

  2. Ser

    That VBA code removes my colors from header row.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos πŸ˜ƒ