When working with a large, data-dense Excel sheet, it’s easy to lose your place without highlighting. If you can highlight the cell, row, and column under the mouse in real time, reviewing data becomes much more effective. How do you set that up?
Highlight the cell, row, and column under the mouse in real time
- Select A1:M20
- Home → Conditional Formatting → New Rule
- Use a formula to determine which cells to format
- Enter the formula:
=OR(CELL("row")=ROW(), CELL("col")=COLUMN()) - Format → Fill → select a color
- Select A1:M20
- Home → Conditional Formatting → New Rule
- Use a formula to determine which cells to format
- Enter the formula:
=AND(CELL("row")=ROW(),CELL("col")=COLUMN()) - Format → Fill → select a color
- CELL("row") — gets the row number of the cell under the mouse
- ROW() — returns the row number of the cell containing the formula
- CELL("row")=ROW() — the row number of the cell under the mouse equals the row number of the cell with the formula
- CELL("col") — gets the column number of the cell under the mouse
- COLUMN() — returns the column number of the cell containing the formula
- CELL("col")=COLUMN() — the column number of the cell under the mouse equals the column number of the cell with the formula
- OR(condition1, condition2) — returns TRUE if either condition1 or condition2 is met
- The formula means that when a cell's row or column number equals the row or column number of the cell clicked with the mouse, the condition is true and the entire row or column of the clicked cell will use the specified fill format.
- AND(condition1, condition2) — returns TRUE only if both condition1 and condition2 are met
- This formula means the condition is met when a cell's row and column numbers both equal those of the mouse-clicked cell; the clicked cell will then use the specified fill format.
Click any cell with the mouse, then press F9 — the cell and its row and column will be highlighted.
[Hide]
Join our 'Office Suite Real-Time Support' free Facebook group to learn more about Office.
View details
