how to make a row ignored by all formulas and graphs

Question

l’m currently working on an Excel file that make lots a graphs from imported data. I need to be able to make a row of data ignored by all the formulas and graphs without needing to edit all the formulas of the excel file.

I need to be able to get the values back later, so just erase them is not an option.

the best solution would to have something like this:

ignored	data number	value
no	1	aaaa
no	2	bbbb
yes
no	3	cccc

Or the same but still with the data number displayed, or the same but just the row not be taken into account for calculations.

Do you know if such solution exists or if I need to stash the row out and making it comeback later when I need it using VBA?

Answer 1

Can't you just filter your data, e.g. in the example below

rows 8 and 9 are filtered, such that June & July do not appear on the chart (even though the chart data range can be seen to be a contiguous range from A2:B14)

(simply hiding rows 8 & 9, without any filtering, has the desired effect also)

If you need formula-driven results in the row ignored then you can follow the steps below to convert them to text:

Select all your formula-containing cells (B7:E7) in the example below

press Ctrl + H to activate the Find & Replace dialogue, click on Options, and ensure that Formulas is the option in the Look in dropdown

In the Find what field enter =, and in the Replace with field enter =""& and then click on Replace all; your formulae are converted to text.

When you need to re-instate your formulae, you repeat this process, but swap the contents of the Find what and Replace with fields.