How to highlight a selected cell's row, column, or both in Excel?
When working with large or complex Excel spreadsheets, it's all too easy to lose track of the currently selected cell—especially while scrolling or analyzing data. Highlighting the entire row, column, or both corresponding to the selected cell can greatly improve readability and help you maintain focus on the data context. This tutorial explains several practical solutions, using Conditional Formatting, the helpful Kutools for Excel add-in, and a VBA event macro for automatic dynamic highlighting—allowing you to choose the best fit for your workflow and preferences.
- Highlight the row / column / column and row of selected cell with Conditional Formatting
- Highlight the row / column / column and row of selected cell with Kutools for Excel
- Automatically highlight the active cell's row and/or column with VBA SelectionChange event
Highlight the row / column / column and row of selected cell with Conditional Formatting
Conditional Formatting is a flexible feature in Excel that allows you to format cells dynamically based on specific rules. By using formulas with Conditional Formatting, you can highlight the entire row, column, or both for the cell you select. The highlight region updates as you change the active cell. However, there are some limitations—such as having to refresh (using F9) to update the highlight and possible slowdowns with very large datasets.
1. Navigate to the worksheet you want to work with and click cell A1. Select the whole worksheet by clicking the triangle at the intersection of the row and column headers. This selection ensures the formatting applies to the entire sheet. See the screenshot:
2. On the Home tab, click Conditional Formatting > New Rule. In the New Formatting Rule dialog box, choose Use a formula to determine which cells to format, then input the following formula in the Format values where this formula is true field:
=OR(CELL("col")=CELL("col",A1),CELL("row")=CELL("row",A1))
This formula highlights both the row and the column of the currently activated cell. See the screenshot:
Tips:
- To highlight only the entire row of the selected cell, use: =ROW()=CELL("row")
- To highlight only the entire column of the selected cell, use: =COLUMN()=CELL("col")
Be aware that these formulas use the active cell for the highlight, and the update will only appear after recalculating the worksheet.
3. Click the Format button in the dialog. Select your desired highlight color on the Fill tab, and you can further customize the font, border, or other formatting styles if you wish. See screenshot:
4. Click OK on each dialog to apply your settings. Now, when you select a cell (such as A1), its entire row and column become highlighted. If you select a different cell, you may need to press the F9 key to refresh the highlight, because Conditional Formatting formulas that use CELL("row") or CELL("col") do not always update immediately with every selection.
Notes & Practical Tips:
- This method relies on workbook calculation. If Automatic Calculation is off, the highlight will not update until you manually recalculate (pressing F9).
- For large worksheets, excessive Conditional Formatting can sometimes slow down performance.
- To remove the highlight, clear the Conditional Formatting rule from the worksheet.
If you need the highlight to move instantly with every cell selection—without pressing F9—consider the following VBA-based method or try a dedicated add-in such as Kutools for Excel.
Highlight the row / column / column and row of selected cell with Kutools for Excel
For those who seek a faster, hands-free solution, Kutools for Excel offers an intuitive approach. Its Reading Layout feature will highlight the currently selected cell's row, column, or both, and the highlight moves automatically as you click any cell or select any range. This is ideal for anyone frequently navigating large tables, creating presentation-ready worksheets, or just wanting a stress-free experience without dealing with formulas or macros.
1. Select any cell (or a group of cells) that you want to focus on. Then, in the Excel ribbon, go to Kutools > Reading Layout. You will instantly see the row and column of the selection highlighted. As you click to other cells or select different areas, the highlight dynamically follows your selection. No further setup or recalculation is necessary. See the screenshot:
Additional suggestions and notes:
- You can easily personalize the highlight appearance. Click on Reading Layout Settings to choose highlight color, rectangular area, border styles, and even transparency—ideal for presentations or visual tracking without obscuring cell content.
- This feature won't interfere with your existing formatting and works well regardless of worksheet size.
- To disable or turn off the highlighting, simply click Reading Layout again. This provides a quick toggle without affecting any underlying data or styles.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
Automatically highlight the active cell's row and/or column with VBA SelectionChange event
If you'd like to achieve live, automatic highlighting of the active cell's row and/or column—and want to avoid pressing F9 or using add-ins—using Excel's VBA (Visual Basic for Applications) event macros is a robust and customizable solution. By utilizing the Worksheet_SelectionChange event, you can dynamically apply highlight effects anytime the selection changes, without manual refresh. This approach is especially useful in shared workbooks, interactive dashboards, or custom Excel forms.
Advantages:
- No need for periodic recalculation—highlight refreshes immediately upon cell selection.
- Customizable: You can choose to highlight just the row, just the column, or both (as shown below).
- This solution requires macros to be enabled. If your environment restricts macros for security, consider another approach.
- Highlight appearance is usually implemented using background color; existing cell formatting may be temporarily overwritten. Always consider this when using in formatted reports.
1. Open your Excel workbook, then press Alt + F11 to launch the Visual Basic for Applications (VBA) editor. In the left-hand Project Explorer window, locate and double-click the specific worksheet where you want the automatic highlighting to appear (for example, Sheet1).
2. In the worksheet code window that opens, copy and paste the following code for live highlight of both row and column:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Remove previous highlights
Me.Cells.Interior.ColorIndex = 0
' Highlight the active row and column
With Target
Me.Rows(.Row).Interior.Color = RGB(255, 255, 204) ' Light yellow highlight for row
Me.Columns(.Column).Interior.Color = RGB(204, 238, 255) ' Light blue highlight for column
End With
' Optional: Highlight the active cell itself with a different color
Target.Interior.Color = RGB(255, 200, 102) ' Orange for active cell, optional
End Sub3. Close the VBA editor and return to your worksheet. The highlight will update automatically as you move the selection. No need to press F9 or manually refresh.
Customizations:
- If you want to highlight only the row, remove or comment out the
Me.Columns(.Column).Interior.Colorline. - If you want to highlight only the column, remove or comment out the
Me.Rows(.Row).Interior.Colorline. - You can adjust the
RGB(...)color values for different highlight effects.
Troubleshooting and Notes:
- This macro affects all cells in the worksheet, so if you have pre-existing fill colors, they will be temporarily cleared and replaced by the highlight effect.
- To restore original formatting, you will need either to remove the macro and manually reset formatting, or enhance the code to preserve color history (more advanced).
- To disable the effect, simply open the VBA editor and delete or comment out the code.
- Macros must be enabled for this to function. If you see a macro warning, enable content as prompted.
This VBA event method is efficient, automatic, and seamless for dynamic worksheet navigation. However, always test in a backup worksheet first if you rely on complex original formatting, and remember that utilizing this code affects only the worksheet where you install it (not the entire workbook unless added to each sheet as required).
Demo: Highlight the row / column / column and row of selected cell with Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in