J
JHI
How can I delete data without deleting the formula?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Dave Peterson
You can't delete the "data" (results of the formula) without deleting the
formula itself--but you can change the formula to make it look like the cell is
empty. (A formula returns something to the cell that contains that formula.)
If you want to make it so that it looks empty if certain conditions are met,
then you can use a formula like:
=if(somecondition="something","",youroriginalformulahere)
So if I want to add 7 to A1 but only if A1 is greater than 10, I could use this:
=if(a1>10,a1+7,"")
or
=if(a1<=10,"",a1+7)
formula itself--but you can change the formula to make it look like the cell is
empty. (A formula returns something to the cell that contains that formula.)
If you want to make it so that it looks empty if certain conditions are met,
then you can use a formula like:
=if(somecondition="something","",youroriginalformulahere)
So if I want to add 7 to A1 but only if A1 is greater than 10, I could use this:
=if(a1>10,a1+7,"")
or
=if(a1<=10,"",a1+7)
C
Chip Pearson
A cell can contain either a static, literal value or a formula, not both.
There is no way to delete a value and leave the formula. If you think about
it, its a nonsensical question. You should write your formula something like
=IF(A1=0,"",Your_Formula)
Here, it will display an empty string if A1 = 0, making the cell appear
empty, but the formula remains.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
There is no way to delete a value and leave the formula. If you think about
it, its a nonsensical question. You should write your formula something like
=IF(A1=0,"",Your_Formula)
Here, it will display an empty string if A1 = 0, making the cell appear
empty, but the formula remains.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
M
Max
Just a slightly different take on your post ..
Perhaps you're asking how to clear only the data input cells within a
range at one go, say within cols A to E. The range may contain both
data cells and formula cells interspersed here and there, all over the
place <g>.
This play** might work sufficiently well for you ..
Select cols A to E
Press F5 > Special > Check "Constants" > OK
The above will select all of the non-formula, non-blank cells within
the range
Click inside the namebox (that's the one with the dropdown arrow just
above col header "A", to the left of the formula bar). Type a name, eg:
MyData, then press ENTER. Now, whenever you want to clear / reset all
the data cells, just select MyData from the namebox's dropdown and
press Delete key.
**Using F5 would unfortunately also grab any cells with text labels
within the range as well. You could try selecting only the "clear"
portions of the range (ie w/o text labels) before doing the press F5
.... , albeit the practicality of this is largely dependent on how
you've set it up.
Another way to avoid grabbing the text labels is to do a one-time
manual selection of only the data cells within the range. Hold CTRL
down while clicking to select the data cells, then click inside the
namebox and type in the name.
Perhaps you're asking how to clear only the data input cells within a
range at one go, say within cols A to E. The range may contain both
data cells and formula cells interspersed here and there, all over the
place <g>.
This play** might work sufficiently well for you ..
Select cols A to E
Press F5 > Special > Check "Constants" > OK
The above will select all of the non-formula, non-blank cells within
the range
Click inside the namebox (that's the one with the dropdown arrow just
above col header "A", to the left of the formula bar). Type a name, eg:
MyData, then press ENTER. Now, whenever you want to clear / reset all
the data cells, just select MyData from the namebox's dropdown and
press Delete key.
**Using F5 would unfortunately also grab any cells with text labels
within the range as well. You could try selecting only the "clear"
portions of the range (ie w/o text labels) before doing the press F5
.... , albeit the practicality of this is largely dependent on how
you've set it up.
Another way to avoid grabbing the text labels is to do a one-time
manual selection of only the data cells within the range. Hold CTRL
down while clicking to select the data cells, then click inside the
namebox and type in the name.