Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

fabrum

macrumors member
Original poster
Jan 15, 2021
48
3
Title says it all - How to Clear Cell Contents While Preserving Cell Formulas? I can't find the menu option in Numbers.
 
I'm a light user of numbers so hopefully a power user can chime in. But I'm having trouble imagining how this feature would work. A cell with formulas will present the result of the formula as the contents of the cell, right? What is a scenario where you would want to clear a cell AND have an active formula in that cell?
 
  • Like
Reactions: Juicy Box
I'm a light user of numbers so hopefully a power user can chime in. But I'm having trouble imagining how this feature would work. A cell with formulas will present the result of the formula as the contents of the cell, right? What is a scenario where you would want to clear a cell AND have an active formula in that cell?
I am counting calories. I don't need to save the data long term; only the sums.
 
Ok, I think I understand. The goal is to be able to select the entire table and clear all the contents at once, but preserve the formulas so you can enter fresh data in the other cells and it will be calculated properly in the formula cells. Is that right?
 
Ok, I think I understand. The goal is to be able to select the entire table and clear all the contents at once, but preserve the formulas so you can enter fresh data in the other cells and it will be calculated properly in the formula cells. Is that right?
Correct.
 
at least on the Mac you can use Applescript to achieve that. Open Apple’s Script Editor and paste the following code:

tell application "Numbers" to tell front document to tell active sheet tell (first table whose selection range's class is range) repeat with aCell in cells of (get selection range) tell aCell to if not (exists its formula) then set its value to missing value end repeat end tell end tell

to test, open a Numbers file and select the range of cells with data and formulas. With the cells still selected, click the "run" button in Script Editor. Save and add it to the Script menu.

EDIT: the indentation of the code seems to be lost when copying into a code block in a comment (!?!!) - at least some times on the latest beta…strange.
 
Last edited:
  • Like
Reactions: sahnert
When I have done tables like this in the past I have always just selected the cells I want to clear with drag select or shift+click and then clear the contents of the selection. Never knew about the ability to filter out formulas in Excel and I can see how that would be a useful feature.

Depending on how complex the document, another option would be to take advantage of the ability to create multiple tables in one sheet in Numbers.

All of the formulas could be in one table and all of the data entry fields could be in a separate table. You could even align and format them as if they were one continuous table if that is important.

Then when you select the data entry table and clear all contents, the formula cells remain intact in their own table.
 
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.