Outlining Cells Referenced in a Formula

Written by Allen Wyatt (last updated February 27, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021


4

When Fraser selects a cell that contains a formula and then clicks in the Formula bar, any cells referenced in the formula are outlined in different colors. This makes Fred wonder if it is possible to outline cells in this manner without the need to click in the Formula bar. He would find it helpful to have the outlining just when a cell is selected.

Your first thought might be that you could create a macro to do what Fraser wants. The problem is, the macro would be quite complex, as it would need to evaluate the contents of the cell and then provide the desired outlining. (Figuring out which colors to use would be a headache, plus you couldn't highlight the cell references in the formulas in color.) Also, there needs to be a way to remove the outlining when moving to a different cell. Finally, when you are in Edit mode (as when you click in the Formula bar), then all bets are off because you can't run macros.

No, it is simpler to remember what is happening when you click in the Formula bar—as already mentioned, you are putting Excel into Edit mode so that you can edit the contents of the cell. There are two very common ways to switch to Edit mode: click in the Formula bar (as Fraser did) or simply press F2. Thus, a quick way to see the outlining that Fraser enjoys would be to press F2. When done, press Esc and Edit mode is exited.

It is possible, of course, to use a macro to kick you into Edit mode whenever you select a cell. Here's an example of one that will do it:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
  ByVal Target As Range)
    If Target.HasFormula Then
        Application.SendKeys ("{F2}")
    End If
End Sub

The macro, which should be stored in the ThisWorkbook module, implements an event handler that is executed every time the selection changes. The code then checks to see if the cell contains a formula, and if it does, then the F2 key is stuffed into the keyboard buffer and Excel goes into Edit mode. This macro effectively automates the manual pressing of the F2 key. (Personally, for me, I would find it easier to just press F2 in those instances when I want to look at the outlining.)

There is another thing you might find helpful. Excel has this obscure little feature that allows you to select the cells referenced in a formula. To see how this works, select the cell containing the formula and then press Ctrl+[ (that's the left bracket). You should see that Excel creates a selection set that consists of the cells referenced in the formula. Since this is actually a selection set, the original cell you were in (the one containing the formula) is no longer selected.

On our Office 365 systems, the same effect is attained by simply double-clicking on the cell containing the formula. On older versions of Excel, double-clicking a cell may result in actually going into Edit mode instead of creating the selection set from the formula in the cell.

Finally, you can also use the auditing tools in Excel to see which cells are related to the current cells. Describing how to use all of the auditing tools is beyond the scope of this tip, but you can explore them by displaying the Formulas tab of the ribbon and working with the tools in the Formula Auditing group.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7336) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Quickly Deleting Rows and Columns

Deleting rows or columns is easy when you use the shortcut described in this tip. Just select the rows or columns and ...

Discover More

Comments Only Visible When Hovering Over a Word or Phrase

The comment feature of Word allows you to easily attach comments to words or phrases in your document. How those comments ...

Discover More

Adding Multiple Rows to a Table

Need to add more than a single row to an existing table? Word provides an assortment of ways that you can accomplish the ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Determining a Name for a Week Number

You could use Excel to collect data that is useful in your business. For instance, you might use it to collect ...

Discover More

Pulling Formulas from a Worksheet

The formulas in your worksheet can be displayed (instead of formula results) by a simple configuration change. You can ...

Discover More

Calculating a Geometric Standard Deviation

One of the areas in which Excel provides worksheet functions is in the arena of statistical analysis. You may want to ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 - 7?

2020-02-24 14:17:33

1695814

This might not be exactly what you're looking for, but ctrl+tilde (ctrl+`) will display the cells' formulas instead of results. When a cell is selected it will highlight in various colors, the other cells used.


2020-02-23 03:03:21

Thomas Papavasileiou

A small addition to Ron S, plus a comment to John Mann's posts

Typing Ctrl+{ is equivalent to trace precedents command, typing Ctrl+Shift+{ is equivalent to second level trace precedents.

Using } instead of { has the same type of behavior as the trace dependents

All of the above select the dependents or precedents and can be formatted with a single command

I think that John Mann's impression is the correct one.

Hope that Fraser's question is answered


2020-02-22 15:32:45

John Mann

I get the impression that Fraser is looking for a simple way of achieving the marking of reference cells. I f he is initially selecting the cell with the formula using the mouse(likley since he then clicks the formula bar). then there is a very easy way to semi-automaiically colour mark the referenced cell. Instead of clicking the formula cell to select it, double-click the cell. Tbis will both select the cell in question and cause the referenced cells to be marked. If you have you mouse set up so that an extra button is programmed as double-click, then there won't even be any extra finger moveents ( I have the centre button/scroll wheel set that way). I tested this using Excel 2010


2020-02-22 13:33:53

Ron S MVP

Just found this other article that does something similar to this request

Highlight all cells referenced by a formula in Excel?
https://www.extendoffice.com/documents/excel/4292-excel-highlight-cells-referenced-in-formula.html
This article is talking about highlighting all cells which was referenced by a formula in Excel. Please follow the below two methods to get it done.
. * Highlight all cells referenced by a formula with Shortcut key
. * Highlight all cells referenced by a formula with VBA code
.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.