1

I need a specific column in my spreadsheet to show the formula and not the resulting value. It still needs to be seen by Excel as a formula but it just needs to show the formula in the cell and not the value. That means I can't convert the cell format to Text as Excel then doesn't see it as a formula. I also need this only for 1 column so toggling between showing formulas and showing results / values isn't an option either. So, as an example:

Cell B3 = 11/11/13 (a short date) In cell F5 I put in =B3 and thus cell F5 shows 11/11/13.

I want cell F5 to show =B3 but I have other cells that use the result in F5 so it can't be a Text format.

How do i do this (preferably not with VB code but I'll take it if that's the only way to do it)

1

If you have a lot of data you could use this simple VBA method which I believe goes after what you want, or at least points you in the right direction. First of all you can't display the formula and have it calculate without duplicating the cell

If you can get past that part then the next step is to either manually copy each cell and display it as text or use a VBA method like below to do it for you.

I created this sub to show you how to get the formula property of a cell:

Public Sub test()
Range("C1").NumberFormat = "@"
Range("C1").Value = Range("B1").Formula
End Sub

I simply put that formula into a side column as text:

enter image description here

You could of course apply a loop to this to go through an entire sheet.

The other way to achieve what you are looking for is to understand that my argument Range("B1").formula can be set or retrieved. This means that hypothetically you could do it backwards:

dim value as string
value = Range("B1").formula

..then you could use the variable value in additional formulas allowing your formula to be displayed and calculated at the same time.

Hopefully this helps you along your way.

  • OK, so if Eric F is correct and I "can't display the formula and have it calculate without duplicating the cell" then we need to go no further with my query. I have a work around that the user enters the Cell reference and then I use the indirect function to create the cell reference. The problem with this method is that if I insert or delete rows, the value the user enters doesn't dynamically adjust because Excel sees it as text or a label and not a formula. – Craig Nov 11 '15 at 20:58
  • well you can still go the macro route if you want it to automatically update... You could add some procedures to fire when a cell is changed.. All up to you! – Eric F Nov 11 '15 at 21:14
1

Here is a simple way to get the cells in column F that contain formulas to display the formula rather than the value. Run this short macro:

Sub ShowFormula()
   Dim DQ As String, mesage As String
   Dim rng As Range, r As Range
   DQ = Chr(34)
   Set rng = Range("F:F").Cells.SpecialCells(xlCellTypeFormulas)

   For Each r In rng
      mesage = DQ & r.Formula & DQ
      r.NumberFormat = mesage & ";" & mesage & ";" & mesage & ";"
   Next r
End Sub

For example:

enter image description here

Explanation:

Without using VBA, it is possible to completely override the display of a value in a cell by using a Custom Format like:

enter image description here

The macro does a similar function, but uses the cell's formula as the forced display.

  • (1) You might want to explain that; I didn't understand it the first two times I read it.  (2) If you change it to say mesage = DQ & r.Formula & DQ & ";", you can simplify the next statement. – Scott Nov 12 '15 at 1:16
  • @Scott - See my EDIT.............. – Gary's Student Nov 12 '15 at 1:33
0

There's an Excel addin called KuTools whose 'content converter' can show the formula for a range of cells. I'd probably go with the VB solution already outlined though.

  • 1
    welcome at superuser, please expand your answer with a bit more details on how that tool can help to solve the problem. – Máté Juhász Nov 12 '15 at 7:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.