{"id":1937,"date":"2026-10-01T10:50:29","date_gmt":"2026-10-01T10:50:29","guid":{"rendered":"https:\/\/www.exceldepartment.com\/?p=1937"},"modified":"2026-10-31T12:13:49","modified_gmt":"2026-10-31T12:13:49","slug":"excel-formula-auditing","status":"publish","type":"post","link":"https:\/\/www.exceldepartment.com\/en\/excel-formula-auditing\/","title":{"rendered":"Formula Auditing in Excel: Find and Fix Formula Errors Efficiently"},"content":{"rendered":"
\n

Your formula returns the wrong result and you can’t see why. Or it shows #REF! and you don’t know which reference is wrong. Excel has powerful formula auditing tools that few people know about \u2014 tools that make troubleshooting 10 times faster.<\/p>\n

This guide shows you how to use Excel’s built-in formula auditing features like a professional. You will learn to trace precedents, evaluate formulas step by step, and find errors that are impossible to spot any other way.<\/p>\n

For general troubleshooting, see the Excel troubleshooting guide<\/a>. For common problems, read common Excel problems<\/a>.<\/p>\n

The Formula Auditing Tools: Overview<\/h2>\n

All the tools are found on the Formulas<\/code> tab in the “Formula Auditing” group.<\/p>\n

The toolbox<\/h3>\n\n\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n\n\n
Tool<\/th>\nFunction<\/th>\nWhen to use it<\/th>\n<\/tr>\n<\/thead>\n
Trace Precedents<\/strong><\/td>\nShows which cells the formula uses<\/td>\nCheck that the right cells are referenced<\/td>\n<\/tr>\n
Trace Dependents<\/strong><\/td>\nShows which formulas use the cell<\/td>\nSee the impact of changes<\/td>\n<\/tr>\n
Remove Arrows<\/strong><\/td>\nClears all trace arrows<\/td>\nClean up the view<\/td>\n<\/tr>\n
Show Formulas<\/strong><\/td>\nShows formulas instead of results<\/td>\nOverview of all formulas<\/td>\n<\/tr>\n
Error Checking<\/strong><\/td>\nFinds potential errors<\/td>\nAutomatic review<\/td>\n<\/tr>\n
Evaluate Formula<\/strong><\/td>\nStep-by-step evaluation<\/td>\nUnderstand complex formulas<\/td>\n<\/tr>\n
Watch Window<\/strong><\/td>\nMonitor cells in real time<\/td>\nFollow values while you edit<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Keyboard shortcuts:<\/strong> – Ctrl+\u00f6 (Swedish Excel): Show formulas – Ctrl+` (English Excel): Show formulas – F2: Edit the cell and show colour-coded references<\/p>\n

Tool 1: Trace Precedents<\/h2>\n<\/p>\n

What it does:<\/strong> Draws blue arrows from the cell to all the cells it refers to.<\/p>\n

How to use it<\/h3>\n
    \n
  1. Select the cell with the formula<\/li>\n
  2. Formulas<\/code> \u2192 Trace Precedents<\/code><\/li>\n
  3. Blue arrows show which cells the formula uses<\/li>\n
  4. Click several times to trace several levels<\/li>\n<\/ol>\n

    Example:<\/strong><\/p>\n

    C1: =A1+B1<\/code><\/pre>\n
    Trace Precedents \u2192 arrows from C1 to A1 and B1.<\/p>\n
    Practical use<\/h3>\n
    Scenario: Verifying VLOOKUP<\/strong><\/p>\n
    E5: =LETARAD(D5;A1:B100;2;0)<\/code><\/pre>\n
    Trace Precedents shows: – Arrow from E5 to D5 (lookup value) – Rectangle around A1:B100 (lookup table)<\/p>\n
    Problem:<\/strong> Does the arrow point to D4 instead of D5? \u2192 The reference is wrong; the formula was copied without adjustment.<\/p>\n
    Interpreting trace arrows<\/h3>\n\n\n\n\n\n\n\n
    Arrow colour<\/th>\nMeaning<\/th>\n<\/tr>\n<\/thead>\n
    Blue<\/td>\nNormal precedent<\/td>\n<\/tr>\n
    Red<\/td>\nThe error is caused by this reference<\/td>\n<\/tr>\n
    Black dotted<\/td>\nReference to another sheet\/workbook<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Remove Arrows<\/h3>\n
    Formulas<\/code> \u2192 Remove Arrows<\/code> \u2192 Choose: – Remove Precedent Arrows<\/strong> \u2014 Precedents only – Remove Dependent Arrows<\/strong> \u2014 Dependents only – Remove All Arrows<\/strong> \u2014 Everything<\/p>\n
    Tool 2: Trace Dependents<\/h2>\n
    What it does:<\/strong> Draws blue arrows from the cell to all the formulas that use it.<\/p>\n
    How to use it<\/h3>\n
      \n
    1. Select the cell<\/li>\n
    2. Formulas<\/code> \u2192 Trace Dependents<\/code><\/li>\n
    3. Blue arrows show which formulas depend on the cell<\/li>\n<\/ol>\n
      Example:<\/strong><\/p>\n
      A1: 100\nB1: =A1*1,25\nC1: =A1+B1<\/code><\/pre>\n
      Select A1, trace dependents \u2192 arrows to B1 and C1.<\/p>\n
      Practical use<\/h3>\n
      Scenario: Understanding the impact of a change<\/strong><\/p>\n
      You are about to change a value in A1. Which formulas are affected?<\/p>\n
      Trace Dependents \u2192 you see all the dependent cells.<\/p>\n
      Scenario: Finding the cause of a circular reference<\/strong><\/p>\n
      If you have a circular reference: 1. Go to the cell Excel points to 2. Trace dependents 3. Follow the arrows 4. When an arrow leads back to the original cell, you have found the circle<\/p>\n
      Tool 3: Show Formulas (Ctrl+\u00f6)<\/h2>\n
      What it does:<\/strong> Shows formulas instead of results in all cells.<\/p>\n
      How to use it<\/h3>\n
        \n
      1. Press Ctrl+\u00f6 (or Ctrl+` in English Excel)<\/li>\n
      2. All formulas are displayed as text<\/li>\n
      3. Column widths adjust automatically to fit the formulas<\/li>\n
      4. Press Ctrl+\u00f6 again to return to the results view<\/li>\n<\/ol>\n
        Practical use<\/h3>\n
        Scenario: Overview of the whole model<\/strong><\/p>\n
        When you take over someone else’s file: 1. Press Ctrl+\u00f6 2. You see all the formulas at once 3. It is easy to identify patterns, errors or illogical references<\/p>\n
        Scenario: Documentation<\/strong><\/p>\n
        To create documentation of formulas: 1. Show formulas 2. Take a screenshot or print<\/p>\n
        Scenario: Finding hardcoded values<\/strong><\/p>\n
        In formula view it is easy to see what is a formula and what is a hardcoded value.<\/p>\n
        Tool 4: Evaluate Formula<\/h2>\n<\/p>\n
        What it does:<\/strong> Step-by-step evaluation of a complex formula.<\/p>\n
        How to use it<\/h3>\n
          \n
        1. Select the cell with the complex formula<\/li>\n
        2. Formulas<\/code> \u2192 Evaluate Formula<\/code><\/li>\n
        3. A dialog opens with the formula<\/li>\n
        4. Click “Evaluate” repeatedly<\/li>\n
        5. Excel shows the result of each sub-calculation<\/li>\n<\/ol>\n
          Example:<\/strong><\/p>\n
          =OM(OCH(A1>10;B1<5);SUMMA(C1:C10);0)<\/code><\/pre>\n
          Step-by-step evaluation:<\/strong><\/p>\n
          Steg 1: =OM(OCH(100>10;3<5);SUMMA(C1:C10);0)\n        (A1 och B1 ers\u00e4tts med v\u00e4rden)\n\nSteg 2: =OM(OCH(SANT;SANT);SUMMA(C1:C10);0)\n        (J\u00e4mf\u00f6relserna utv\u00e4rderas)\n\nSteg 3: =OM(SANT;SUMMA(C1:C10);0)\n        (OCH utv\u00e4rderas)\n\nSteg 4: =OM(SANT;250;0)\n        (SUMMA utv\u00e4rderas)\n\nSteg 5: =250\n        (OM utv\u00e4rderas)<\/code><\/pre>\n
          Practical use<\/h3>\n
          Scenario: Formula gives an unexpected result<\/strong><\/p>\n
          You can’t see why. Evaluate Formula shows exactly at which step it goes wrong.<\/p>\n
          Scenario: Learning complex formulas<\/strong><\/p>\n
          When you need to understand someone else’s complex formula: evaluate it step by step to see the logic.<\/p>\n
          Tool 5: Error Checking<\/h2>\n
          What it does:<\/strong> Automatically finds potential errors in the workbook.<\/p>\n
          How to use it<\/h3>\n
            \n
          1. Formulas<\/code> \u2192 Error Checking<\/code><\/li>\n
          2. Excel goes through the workbook<\/li>\n
          3. Stops at each potential error<\/li>\n
          4. You can:\n