In the past I have written a number of posts on the use of Custom Number formats including
Selective Chart Axis Formats
Custom Chart Axis Formats (Part 2)
A technique to quickly develop custom number formats
Chandoo has written about Custom Number Formats in:
Custom cell formatting in Excel a few tips tricks
Color Modifier
As part of these techniques you have the option to set the colors using the [Color] modifier
You can use a Custom format of: $#,##0;[Red]-$#,##0
10 Dollars will be displayed as $10
-10 Dollars will be displayed as -$10
Colors available include Red, Blue, Green, Yellow
However there is a much larger color palette available
Anybody who has or is still using Excel 2003 or prior will have a color picker which looks like this:
Well these 56 colors are all available and not just in Excel 95-2003 but in All Excel versions up to and including Excel 2013.
We have two methods to access these colors:
1. Using the Colors Name or
2. Using a Color Number.
Color Name
In Excel 95-2003 you can Right Click on a cell and change the Font or the Fill color
Simply select a color like below:
Note that a Green Color has been selected, the Dialog shows the name of the Color as Sea Green
To save you opening an early version of Excel here are all the colors listed above:
Top Row
Black, Brown, Olive Green, Dark Green, Dark Teal, Dark Blue, Indigo, Grey-80%
2nd Row
Dark Red, Orange, dark yellow, Green, Teal, Blue, Blue-Grey, Grey-50%
3rd Row
Red, Light Orange, Lime, Sea Green, Aqua, Light Blue, Violet, Grey-40%
4th Row
Pink, Gold, Yellow, Bright Green, Turquoise, Sky Blue, Plum, Grey-25%
5th Row
Rose, Tan, Light Yellow, Light Green, Light Turquoise, Pale Blue, Lavender, White
6th Row
Periwinkle, Plum, Ivory, Light Turquoise, Dark Purple, Coral, Ocean Blue, Ice Blue
Bottom Row
Dark Blue, Pink, Yellow, Turquoise, Violet, dark Red, Teal, Blue
To use these use the format $#,##0;[Color Name]-$#,##0
eg: [Blue Grey]$#,##0;[Sea Green]-$#,##0
This will display Ten Dollars as $10 and Negative Ten Dollars as -$10
Color Number
The Alternative method is to use a Custom Number Format and using the Color Number modifier like [Color Number]$#,##0;[Color Number]-$#,##0
[Color4]$#,##0;[Color3]-$#,##0
This will display Ten Dollars as $10 and Negative Ten Dollars as -$10
Once again to save you trialing each color you can see the effects of each color on a white and Black background below:
Warnings:
I haven’t tested it but I am sure the Color Names will be different in different language versions of Excel.
I haven’t tested these techniques on a Mac version of Excel but I am pretty sure these techniques should work.
Forward compatibility should be ok, but can’t be guaranteed.
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)