From the Not Just Numbers blog:

Just a short little tip this week, due to a hectic diary.

When you reference a cell in Excel, you will get its value, but what if you want to return the formula itself?

Excel, as usual, provides a way to do this.

NB: This function is only available in Excel 2013 onward. If you need this functionality in earlier versions of Excel, Bill Jelen has a simple video on how to create a User-defined function to do it.

Let us say that cell A1 contains:

=SUM(B1:B10)

If we enter the following into cell C1:

=A1

then cell C1 will show the same number that A1 shows, i.e. the sum of the numbers in cells B1 to B10.

What if we wanted to see the actual formula in cell A1?

We can use the FORMULATEXT function in C1:

=FORMULATEXT(A1)

This will return the text:

=SUM(B1:B10)

This may be useful for its own sake (for example, to show how numbers are calculated alongside the numbers themselves), or to return the text of the formula, so that you can manipulate it.

A couple of things to note:

  • FORMULATEXT will return #N/A if:
    • the cell does not contain a formula
    • the formula is more than 8,192 characters!
    • worksheet protection doesn’t allow the formula to be shown
    • it refers to an external workbook that is not open.
  • If the range argument is more than one cell, it will return the formula in the top left cell of the range.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.