I would like to view the formulas on my spreadsheet using Ctrl + ~. Is there a way then to copy paste those "exploded" formulas onto another excel sheet in the "exploded" view?
Asked
Active
Viewed 6,377 times
1
-
I don't think there is a non-VBA way of doing this unfortunately http://stackoverflow.com/questions/9122046/excel-getting-formula-of-another-cell-in-a-cell-without-vba. However, there are ways of creating a user defined function to get the formula which you could then use i. http://dmcritchie.mvps.org/excel/formula.htm – Steph Locke Jun 12 '13 at 16:08
-
If you only need to do it a few times, then just double-click on the cell, select the entire formula, and copy & paste as normal. – PowerUser Jun 12 '13 at 16:08
3 Answers
3
I know of one way, but it requires some outside program. I'm assuming that you have access to notepad or another text editor (I will refer to notepad below, but replace that with other text editors if applicable). The below also applies to a whole sheet.
- Copy the formula and paste it in notepad. You'll notice the formulae get pasted as you want there.
- Copy everything again and paste it in the sheet you want it to be, by using Paste Special.
- Wait! That's not over, because you'll notice excel still evaluate the formula. Click on the little paste icon that appears and select the option "Use text import wizard"
- Use 'Delimited', Uncheck everything in the next step and in step 3, pick "Text".
Tadaa! :)
Jerry
- 70,495
- 13
- 100
- 144
3
Another way to accomplish what you're looking for would be to:
Do a find/replace - Replace all = with, say, # (or whatever character is specifically NOT in your formulas at all) - so it stops being considered a formula in Excel.
Then copy & paste to the other sheet, then do the opposite find/replace (# for =).
Hope that makes sense...
John Bustos
- 19,036
- 17
- 89
- 151
-
for me this does not work whenever i paste the values get pasted. Any idea why that might be? – HenriDev May 31 '23 at 13:10
1
Unfortunately, there's no elegant way to do this. The best way I've found is to press Ctrl + ~, copy the desired range of formulas, then paste them into notepad or another text editing program. Reselect and copy the formulas, which are now just text, and paste them into your new range on the other sheet.
