How to Highlight All Formulas in Your Excel Worksheet

Formulas in Excel can help us to calculate certain values. And we here introduce about an Excel tips to highlight formulas in your Excel worksheet.

In your Excel worksheet, there will be many different formulas. However, sometimes, you will use the result of those formulas for other needs. But if you don’t know which one is the result of formula, you will meet with errors. In addition, in our previous article, we have talk about this problem. You can refer to this article: 2 Effective Solutions to Solve Errors when Copying Formulas .

And today we have found another method.Example to Highlight Formulas

You can see that there are prices of different products. And some of the numbers are calculated by formulas. So here you can use this way to mark those values.

Change the Format of Formulas

Now there are steps about changing the format of cells.

  1. Press the shortcut keys “Ctrl +F3” on the keyboard.
  2. Then in the pop-up window, click the button of “New”.Click New
  3. Then in the text box of “Name” in the new window, input the name that you need. Here we input “ChangeFormat”.Set Your New Name

And if you want to mark the formula in a certain workbook, you can click the small arrow and choose your target.

  1. Now input the formula into the next textbox:

=GET.CELL(48,INDIRECT(“rc”,FALSE))

The GET.CELL is a kind of macro function in Excel. Therefore, you will need to save this workbook with macro. The “48” means if the cell contains a formula, it will return TRUE. The INDIRECT function will return the references by a text string. “rc” means the current row and current column. And the FALSE means that it uses the R1C1 style reference. Thus, if one cell contains a formula, the INDIRECT function will return the R1C1 reference of the cell, and then the “48” will return true to the formula.Input the Reference Formula

  1. Now click “OK”. And then you will come back to the “Name Manager” window. And in this window, you can see the name that you have created.
  2. Then close this window.
  3. Select the area where you want to change the cell format. You can also press “Ctrl +A” to select the whole worksheet.
  4. And then click the button of “Conditional Formatting”.
  5. In the new menu, click “New Rule”.New Rule
  6. In the new window, choose the last option “Use a formula to determine which cells to format”.
  7. And then in the textbox, input the name that you have set before:

=ChangeFormat.New Formatting Rule

  1. Now click the button “Format” in the window. And then the window of “Format Cells” will pop up.
  2. Then set the format that you need.Set Format
  3. Click “OK” to save your setting.
  4. And in the “New Formatting Rule”, also click “OK”.

Now, in your workbook, all the cells with formulas will change the format.Format Changes

And the next time if you will input a new formula, the format will also automatically change according to your settings.

Be Cautious about Excel Files

Excel file corruption can cause disastrous result to you. However, a third party tool can help repair Excel. It is adept in handling problems with Excel and you can get back almost all your data and information in this damaged Excel file.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com

One response to “How to Highlight All Formulas in Your Excel Worksheet”