How to select and highlight diagonal cells in Excel?
When working in Excel, selecting multiple cells typically involves choosing blocks of adjacent cells, rows, or columns. However, there are situations such as marking the main diagonal of a matrix, highlighting key values in certain reports, or preparing visual data presentations where you may want to select and highlight only the diagonal cells within a range (for example, cells where the row and column numbers are equal, like A1, B2, C3, etc.). Understanding how to quickly select and format these diagonal cells can improve efficiency, especially when dealing with large datasets or applying custom visual indicators. This article details easy methods to select and highlight diagonal cells in Excel, offering both VBA and formula solutions to fit different user preferences and scenario demands.
Select and highlight diagonal cells (VBA)
Conditional Formatting - Highlight diagonal cells
Select and highlight diagonal cells
In situations where you need to select diagonal cells efficiently, especially over large ranges or for repeated analysis, using an automated approach is highly practical. Below, a VBA macro is provided to help you accomplish diagonal cell selection—particularly useful for matrix-style data, time schedules, or reports where diagonal highlighting is meaningful. One advantage of this method is flexibility; you can specify the number of diagonal cells to select, making it adaptable to ranges of any size. It is recommended to back up your data before running macros and save your workbook as a macro-enabled file (.xlsm) to ensure proper functionality. If you have cells formatted with merged areas or filters, consider unmerging or clearing filters to avoid selection errors.
1. Click on any cell where you wish to begin the diagonal selection (commonly the top-left cell of your target range), then launch the Microsoft Visual Basic for Applications editor by pressing Alt + F11. This opens a new window for VBA scripting. Ensure that you have enabled macros in Excel's Trust Center settings if you encounter execution errors.
2. In the VBA editor, go to Insert > Module to add a new module, and then paste the following code into the module window. This macro will automate the process of selecting diagonal cells from your chosen start point.
VBA: Select diagonal cells
Sub selectDiag()
'UpdatebyExtendoffice20171128
Dim I As Long
Dim xCount As Long
Dim xRg As Range
On Error Resume Next
Set xRg = ActiveCell
If xRg Is Nothing Then Exit Sub
xCount = Val(InputBox("How many cells do you want to select diagonally?", "KuTools For Excel"))
If xCount = 0 Then Exit Sub
For I = 1 To (xCount - 1)
Set xRg = Union(xRg, ActiveCell.Offset(I, I))
Next I
xRg.Select
End Sub3. Press the F5 key to run the macro. A dialog box will prompt you to enter the number of diagonal cells you want to select. Input a number that fits your selected range (for example, if your matrix is 8x8, entering "8" will select all diagonal cells from top left to bottom right). Avoid entering numbers larger than your range's length, as this may cause cells outside the intended area to be selected.
4. Click OK. The macro will automatically select the specified number of diagonal cells within your selection, visually demonstrated as shown below.
Once the diagonal cells are selected, you can easily highlight them by applying a background color or conditional formatting. To do this, simply go to the Home tab, click the Fill Color button, and choose your preferred color in order to emphasize these key cells for further analysis or reporting.
Tip: If your workbook contains multiple sheets, make sure you are working on the correct sheet before running the macro. If you face issues with macro execution, double-check that macros are enabled in Trust Center settings via File > Options > Trust Center > Trust Center Settings and enable the appropriate macro options. For troubleshooting, ensure you have only one region selected without merged cells or hidden rows/columns, which might interfere with selection logic.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Conditional Formatting - Highlight diagonal cells
Excel’s Conditional Formatting enables automatic visual highlighting of diagonal cells in any range based on logical rules, allowing you to visually emphasize important data with zero manual selection. This method is well-suited for dynamic tables—such as schedules, scoring matrices, or comparative lists—where the diagonal represents unique or particularly relevant values. Conditional formatting updates highlights automatically if you restructure or expand your table, minimizing maintenance.
1. Select your target range (for example, cells A1:D4 for a4x4 table).
2. On the Excel Ribbon, go to Home > Conditional Formatting > New Rule. In the rule dialog, choose Use a formula to determine which cells to format.
3. In the formula box, enter the following formula (change A1 to the top-left cell reference of your selected range):
=ROW(A1)=COLUMN(A1)4. Click Format, select a highlight color, and confirm with OK. Click OK again to apply the rule. Diagonal cells within your selected range will be automatically highlighted, adapting as your data changes.
Tip: If your selection starts from a different cell (not A1), adjust the formula accordingly—for example, if you select from B2, use =ROW(B2)=COLUMN(B2). If you resize or move your range, double-check the formula reference in the conditional formatting rule. For troubleshooting, make sure the formula is written relative to the top-left cell of your chosen range, and test by changing values to see the highlight update dynamically.
Suggestions: When working with diagonal cells in Excel, choosing between VBA, formulas, or conditional formatting depends on whether you need repeated automation, custom calculations, or automatic visual feedback. VBA is most flexible for complex scenarios or batch operations. The conditional formatting solution offers quick visual results and is easy to update as your data changes. Always check cell references and ensure you apply rules/solutions to the correct range for reliable results. If errors or unexpected results appear, review your formula logic or macro settings, and consult Excel’s Help documentation or support resources for additional troubleshooting.
Relative Articles
- How to use conditional formatting values not between two numbers in Excel?
- How to highlight the duplicates and overlapping dates in 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