Forum Discussion

nicholasjcstubbs's avatar
nicholasjcstubbs
Copper Contributor
Jul 10, 2023

Trace precedents of datatable

I have used the {=table(cell ref,)} datatable function to calculate a range of outputs from a range of inputs. Now I wish to check the calculations but trace precedents does not work with this function.  The function itself only shows the input cell and not the output cells - and it is those cells I need to trace to check that my calculation is sound.  I have drawn a blank with searching for this answer.

 

Any ideas?

BI & Data Analysis
excel
Excel on Mac
Formulas and Functions
  • no_good_with_computers's avatar
    no_good_with_computers
    Copper Contributor
    Mar 05, 2025

    Hi,


    I realise this is an old thread but it's so close to answering my question that I'll start here.


    I've been given responsibility of managing and maintaining an Excel spreadsheet that make a lot of use of {=table(cell ref,)} but I don't know how this was set up; I don't know what table is being referenced, what formulae feed into the table or anything other than the address of cell ref and the value of the output.

    Surely there must be a way of viewing what feeds into this function? Can anyone point me in the right direction?

    Any help will be appreciated.

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jul 22, 2023

    nicholasjcstubbs 

    TABLE() function from What-If Analysis is actually the macro which fills the range with calculated values. Table Precedents doesn't work with it.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jul 22, 2023

    nicholasjcstubbs 

    In Excel, the "Trace Precedents" functionality is not directly compatible with structured table references like the {=table(cell ref,)} function. However, you can still trace the precedents of the calculated output cells in the table using an alternative approach.

    One way to achieve this is by converting the structured table reference formula into regular array formulas using the INDEX function.

     Here's how you can do it:

    1. Select the output cells in the table that you want to trace precedents for.
    2. Go to the formula bar and edit the formula by adding the INDEX function around the structured table reference formula.

    For example, if your original formula was:

    {=table(A2,)}

    Change it to:

    =INDEX(table[A], A2)

    Replace "table" with the actual name of your table, and "A" with the column header of the output you want to trace.

    1. Press Ctrl + Shift + Enter (or Cmd + Shift + Enter on Mac) to enter the formula as an array formula. You should see curly braces {} around the formula, indicating that it's now an array formula.

    After converting the formula to an array formula using INDEX, you should be able to use the "Trace Precedents" functionality to trace the input cells that affect the output.

    Here is how to use "Trace Precedents" after converting the formula:

    1. Select the cell with the array formula (the output cell).
    2. Go to the "Formulas" tab in the Excel ribbon.
    3. Click on "Trace Precedents" in the "Formula Auditing" group.
    4. Excel will draw arrows to indicate the input cells (the structured table references) that affect the output of the array formula.

    By using the INDEX function, you can transform the structured table reference into a traditional array formula, which allows Excel's "Trace Precedents" functionality to work as expected. My knowledge of this topic is limited, but since no one has answered it for at least one day or more, I entered your question in various AI’s. The text and the steps are the result of various AI's put together.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

Resources