Three (3) Easy Ways to Auto Format Formulas in ExcelDetailed Tutorials with Screenshots

  |
Written By:
Adiste Mae

It is possible that cells containing formulas may be deleted or have their contents cleared by mistake, which can be inconvenient when you are working with formulas.

To prevent this, it would be helpful if cells with formulas are formatted or highlighted in a way that makes them more noticeable.

In this tutorial, we will demonstrate how to automatically format cells with formulas in Excel to make them more visible.

Let us begin! 

Using Conditional Formatting to Highlight Cells with Formulas

One simple way to automatically format cells containing formulas is through the use of conditional formatting.

With conditional formatting, you can easily apply a different format to cells that contain formulas if you are able to identify them.

The ISFORMULA function, introduced in Excel 2013, can be used to identify cells with formulas. It checks if a cell contains a formula and returns TRUE if it does and FALSE if it does not.

Here are the steps to use the ISFORMULA function in Conditional Formatting to automatically format cells with formulas in Excel:

  1. Choose the dataset you wish to apply the formatting to cells containing formulas.
  2. Select the Home Tab
  3. Select Conditional Formatting under the Style group. 
  4. Select New Rule.
  5. In the New Formatting Rule dialog box, select the option to “Use a formula to determine which cells to format.”
  6. Enter the formula =ISFORMULA(A1), in the formula field.
  7. Select the Format button.
  8. In the Format Cells dialog box, specify the desired formatting for cells with formulas. For example, you could choose to apply an orange cell fill color as shown in the example.
  9. Lastly, click the OK button. 

By following the above steps, you can quickly apply automatic formatting to all cells that contain a formula.

Not only will the above steps apply formatting to existing cells with formulas, but they will also automatically apply the chosen formatting to any new cells that contain formulas as they are added.

This is because conditional formatting is dynamic and continually checks for changes in the worksheet.

This means that the above steps ensure that cells with formulas are automatically highlighted as needed.

In addition to the method described above, there is another way to automatically format cells with formulas in Excel.

Using “Go To Special” to Highlight Cells with Formulas

The method described in this paragraph is not dynamic, unlike the method using Conditional formatting.

This means that cells with formulas will only be highlighted when the method is applied, and will not update to reflect any changes made to the worksheet later.

If you make any changes to the worksheet and add a formula to a cell after using this method, that cell will not be highlighted.

However, this method does allow you to quickly select and format all cells with formulas at once.

Here are the steps to use the “Go To Special” feature to select and format all cells with formulas:

  1. Choose the dataset you wish to apply the formatting to cells containing formulas.
  2. Press the F5 key to open the Go To dialog box.
  3. Click the Special button.
  4. In the Go To Special dialog box, select the option for Formulas.
  5. Lastly, Click the OK button.

By following the above steps, you can select all cells that contain formulas.

Once these cells are selected, you can apply any desired formatting such as a cell color or bold font.

Please note that if you add a new formula to the range after using this method, it will not be automatically formatted.

In order to apply formatting to the new formula, you will need to repeat the process described above.

Using VBA in Format Formula

A third method to quickly format cells with formulas is to use VBA (Visual Basic for Applications).

This method achieves the same result as the previous method using “Go To Special,” but with a single line of code.

Here is the VBA code that will immediately apply a yellow highlight to all cells containing formulas:

  • Selection.SpecialCells(xlCellTypeFormulas, 23).Interior.Color = vbYellow

This code first selects all cells with formulas and then applies the specified yellow color (using the vbYellow constant).

Here are the steps to use this VBA macro code to apply formatting to cells with formulas:

  1. To use this VBA macro code, right-click on the tab for the active sheet containing the data with formulas that you want to format.
  2. Select View Code.
  3. If the “Immediate window” is not already visible in the VB Editor, you can access it by clicking “View” in the menu and then selecting “Immediate Window.”
  4. To use the VBA code, copy and paste it into the “Immediate Window.”
  5. To execute the VBA code, place the cursor at the end of the line.
  6. Press the Enter key

The VBA code provided first identifies all cells with formulas and then applies the specified yellow color.

If you wish to apply a different type of formatting, you can modify the code accordingly.

In summary, these are three methods for quickly applying formatting to cells with formulas in Excel.

I hope this tutorial was helpful to you!