Got a tip for us?
Let us know
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.
Numbers: How to Clear Cell Contents While Preserving Cell Formulas
- Thread starter fabrum
- Start date
- Sort by reaction score
I don't think Numbers can do that like Excel does.
Copy, move, or delete cell content in Numbers on Mac
In Numbers on Mac, copy, move, or delete data and formatting from a table cell, or delete the data but keep the formatting.
support.apple.com
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?
yeah; I saw that Excel can do that! Why doesn't Apple with its' huge balance sheet invest in Numbers?!
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?
at least on the Mac you can use Applescript to achieve that. Open Apple’s Script Editor and paste the following code:
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.
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 tellto 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:
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.
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.