Computing desk
< July 25	<< Jun \| July \| Aug >>	Current desk >

Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

July 26

Immutable values

A question from a spreadsheet ignoramus. Suppose I have a set of numbers in a single sheet, from row 3 to row 23, and from column C to column P. But randomly dotted among these are a number of blanks (perhaps C6, C19, D3, D4, D22...). I want a sum for each column, simply ignoring the blanks, so (for LibreOffice Calc) in C25 I'd enter =SUM(C3:C23), and I'd copy this to each of D25, E25 ... P25. Later, I'll get values for some or all of the blanks, and LibreOffice will automatically and "helpfully" recalculate the sums. However, I won't want it to do this. On the contrary, I want the existing sums to be unaffected by later appearances of or changes to the "addends". A crude and slow way to achieve this would be for me, a human, to read the number displayed in C25 and to type this number into C24; ditto to create D24, E24 ... P24; whereupon I'd be free to delete row 25. But what's the not-stupid way to arrive at the same end? -- Hoary (talk) 01:33, 26 July 2022 (UTC)[reply]

Right-click on C25 and select "Copy". Then right-click on C24 and select "Paste Special". Make sure that "Paste all" and "Formulas" are unchecked, then click OK. This will copy the numbers without copying the formulas. You can of course copy and paste-special multiple cells as well. CodeTalker (talk) 03:06, 26 July 2022 (UTC)[reply]

Excellent! Thank you, CodeTalker. -- Hoary (talk) 07:14, 26 July 2022 (UTC)[reply]

If you need to do this frequently as a primarily keyboard-oriented user, Excel and some other applications accept Ctrl+Shift+V to paste unformatted text. Ctrl+Alt+V is a dedicated shortcut in Excel to open the 'paste special' dialog box. 2603:8001:CD3F:B872:4153:C780:AF97:28A0 (talk) — Preceding undated comment added 21:39, 30 July 2022 (UTC)[reply]