Here is a little experiment to freak out excel.
Go to cell C3 and write =C3 and press Enter. Excel would throw up nasty message saying, “Microsoft did not know what to do. We have a sent a support engineer to your home, but he is stuck at the round-about near your house.”
Well, not really. But what you did when you wrote the formula =C3 in cell C3 was, you created a circular reference.
What is a Circular Reference & why use them?
A circular reference is created when you refer to same cell either directly or indirectly.
We use circular references when we need circular references.
Excel Circular Reference Example:
For eg. (borrowed from John Walkenbach’s Excel 2010 Bible), lets say you run a fictitious company named Sky is the Ltd.
And you have a strange policy of donation 10% of your profits after tax to charity.
But, in your country, charity donations are tax exempt (they are expenses).
So charity = 10% * after tax profits
after tax profits = (revenues - expenses - charity)*(1-tax rate)
By definition, charity refers to after tax profits, which refers to charity, thus creating a circular reference.
Now, how would you find out how much to donate to charity?
Simple, we write formulas with circular references, like this:
But wait, just when you press enter after writing the formulas, Excel would scream bloody and curse your entire family for having a circular reference in your worksheet.
Enabling Iterative Calculation Mode
You must enable what they call iterative calculation mode before the formulas work. For this we must go to Excel Options.
In latest versions of Excel,
- Click on Office button
- Go to Excel Options, this is analogous to opening the bonnet of your car, but just a bit more confusing.
- Locate the “Formulas” on the left, click on it
- Now, check the “Enable iterative calculation”. This way you are telling Excel to evaluate references iteratively, up to 100 times (default).
- Click ok, close the bonnet. That is all.
In per-historic versions of Excel,
- Go to Menu > Tools > Options > Calculation Tab
- Check Iterative Calculation box. (see image)
Once you do this, your formulas will work nicely and you will find that the required charity donation to be made.
How to avoid Circular References?
As you can understand circular references are a pain in cell. You may want to get rid of them altogether. Thankfully, with careful inspection and a mug of coffee, you can reduce most circular references to simple formulas. For eg, in the above case, we can calculate charity amount directly by using the following equations.
But, keep in mind that, in few cases, circular references may be required. For eg. if you want to add timestamps to your workbook.
How to locate Circular References?
Do you know that you can find all the circular references in a workbook?
Whenever you see circular reference warning message, just go to formula ribbon and click on error checking options. You can see all the circular references there.
Note: In Excel 2003, you can see the same from circular reference toolbar (Menu > View > Tool-bars > Circular Reference)
Examples & More Resources on Circular References:
- Add timestamps to your workbook
- Team Todo List template in Excel
- Circular References & Other Repetitive Calculation Features in Excel
- Solve Circular References instead of using them
Do you do circular references?
I try to avoid circular references whenever possible. But in some rare cases, I think a circular reference gives elegant, shorter solution than a non-circular variation of it.
What about you? Do you use circular references often? What are the reasons / uses of them according to you? Please share your experience, tips thru comments.
PS: Here is a very useful link on circular references.
PPS: Monalisa pic source is here.
21 Responses to “How to Filter Odd or Even Rows only? [Quick Tips]”
Infact, instead of using =ISEVEN(B3), how about to use =ISEVEN(ROW())
So it takes away any chance of wrong referencing.
I like Daily Dose of Excel
I like it.
Just a heads up, you do need to have the Analysis ToolPak add-in activated to use the ISEVEN / ISODD functions. An alternative to ISEVEN would be:
=MOD(ROW(),2)=0
rather than use a formula, couldn't you enter "true" in first cell and "false" in the second and drag it down and than filter on true or false.
Just for clarification, is Ashish looking to filter by even or odd Characters or rows?
so many functions to learn!
Nice support by chandoo and team as a helpdesk. Give us more to learn and make us awesome. Always be helpful.......
In case you want to delete instead of filter,
IF your data is in Sheet1 column A
Put this in Sheet2 column A and drag down
=OFFSET(Sheet1!A$1,(ROWS($1:1)-1)*2,,)
(This is to delete even rows)
To delete odd rows :
=OFFSET(Sheet1!A$2,(ROWS($1:1)-1)*2,,)
If your numbered cells did not correspond to rows, the answer would be even simpler:
=MOD([cell address],2), then filter by 0 to see evens or 1 to see odds.
I sometimes do this using an even simpler method. I add a new column called "Sign" and put the value of 1 in the first row, say cell C2 if C1 contains the header. Then in C3 I put the formula =-1 * C2, which I copy and paste into the rest of the rows (so C4 has =-1 * C3 and so forth). Now I can just apply a filter and pick either +1 or -1 to see half the rows.
Another way, which works if I want three possibilities: in C2 I put the value 1, in C3 I put the value 2, in C4 I put the value 3, then in C5 I put the formula =C2 then I copy C5 and paste into all the remaining rows (so C6 gets =C3, C7 gets =C4, etc.). Now I can apply a filter and pick the value 1, 2, or 3 to see a third of the rows.
Extending this approach to more than 3 cases is left as an exercise for the reader.
Another way =MOD(ROW();2). In this case, must to choose betwen 1 and 0.
[...] How to Filter Even or Odd rows only [...]
very different style Odd or Even Rows very easy way to visit this site
http://www.handycss.com/tips/odd-or-even-rows/
Thanks for the tip, it worked like magic, saved having to delete row by row in my database.
Thanks!
Thankssssssssssssssss
Hi Chandoo- First of all thanks for the trick. It helped me a lot. Here I have one more challenge. Having filtered the data based on odd. I want to paste data in another sheet adjacent to it. How can I do that?
For Example-
A 1 odd
B 3 odd
C 4 even
D 6 even
I have fileted the above data for odd and want to copy the "This is odd number" text in adjacent/next sheet here. How can I do that. After doing this my data should look like this
A 1 odd This is odd number
B 3 odd This is odd number
C 4 even
D 6 even
Hi! Could you please help me find a formula to filter by language?
Thank you!
Chandoo SIR,
I HAVE A DATA IN EXCEL ROWS LIKE BELOW IS THERE ANY FORMULA OR A WAY WHERE I CAN INSTRUCT I CAN MAKE CHANGES , MEANS I WANT TO WRITE ONLY , THE FIG IS FRESH, BUT IN BELOW ROW IT WILL AUTOMATICALLY TAKE THE SOME WORDS FROM FIGS AND MAKE IN PLURAL FORM , WHILE USING '' ARE'' LIKE BELOW
The fig is fresh - row 1
Figs are fresh - row 2
The Pomegranate is red - row 3
Pomegranates are red - row 4
=IF(EVEN(A1)=A1,"EVEN - do something","ODD - do something else") with iferron (for blank Cell)