Is it possible in Excel to render a cell that shows, instead of cell references, the values within those cells? Like instead of the formula being =Q6+R6+S6+T6+U6, I could get a cell containing the contents, but within a formula (=5+4+0+2+10)?



A coworker suddenly wants every sheet to show a TYPED IN formula instead of the values, which I had calculated with an off-print-area helper column that summed the current and past to-date quantities. Copy/paste values, simple. She wants to see all the individual numbers, and it will take forever to update multiple sheets that way. I was just trying to figure out a workaround.

Lame as this is, it sort of works.

In a column to the right, concatenate:
=CONCATENATE("="&P15&"+"&Q15&"+"&R15)

Take that column, and paste As Values to the right of it. It will display as =1+2+3 reading as text, rather than returning the total. Then highlight that column and run a macro replacing one of the formula's components with itself. I chose replace = with =, since there's an equals sign in every formula. It does change to displaying 6, and when you click in, it shows =1+2+3 in the formula bar. Yes, it's a nuts thing to need to do.

On fly!

Leave the column with sum as it is. Add an empty column next to it, with formula like
NB! The added column displays all components of sum as string! You can take it as comment of formula.

And in case the components of original sum are formulas instead cell references, like:
in added column you use formula:

Take the formula such as =Q6+R6+S6+T6+U6
In the formula bar, double-click on say Q6 to select that single reference:
2022-07-20_231308.png
then press the key F9 on the keyboard. Excel will replace the reference with its value. If you select more than one reference in the formula it will try and calculate the bit of formula that you've selected.
Here I've done it with Q6:
2022-07-20_231348.png
and here I've double clicked on all but one reference, one at a time and pressed F9 each time and got:
2022-07-20_231441.png


Any use?

I like the idea of it, and will give it a try. But to do a whole string of these would almost take as much time as doing it her way. I think what I did on mine works, if it comes to that.

It's definitely a cool tool to know, if I manage to remember it!

To follow up on this, as it may come in handy for someone else...

I was using =CONCATENATE("="&F7&"+"&D7) to get started, and that works when the running total in F7 is just a number (the quantity used to date.) But when that cell is eventually replaced with the running "adding machine tape" that she wanted (=2+0+3+2), I ended up using

=CONCATENATE(FORMULATEXT(F7)&"+"&D7

Seems simple, but it took me a while to sort out what would work. I do that in a helper column, paste values over the F row, then use a little helper macro to refresh all those cells so it goes back to a working formula again.