In the third session of Chandoo.org podcast, we are going to get BI curious. 😉
Not that kind you silly, We are talking about Business Intelligence, Big Data, Power Pivot & other Power BI family members.
First some good news: We hit #1 on iTunes in our category
That is right. Around 14th of March, we hit #1 in our category of podcasts. Yay!!!
Please take a minute to subscribe to our podcast:
- Chandoo.org Podcast is on iTunes: Click here to subscribe.
- Our show is on Stitcher and Windows Phone Podcasts too: Click here to subscribe on Stitcher. For Windows phone, search your podcasts app for our show.
- As always, you can get latest episodes, show notes & resources from our Podcast page.
What is in this session?
In this session, I am happy to feature Mike Alexander – Microsoft MVP, Author, Blogger & a good friend. Mike talks about how Excel is shaping the BI (Business Intelligence) revolution with advent of Power BI functionality.
- 10% discount on Mike & Bill’s upcoming Power BI Bootcamp in USA
- Free Excel Dashboard Tools add-in
Listen to the podcast to know how to get these.
You will learn,
- Introduction, what Mike is up to these days?
- What is BI, what does it mean to an average Excel analyst?
- What BI capabilities Excel has – brief intro to each of them
- Power Pivot & what it does
- Power Query & why it is important
- Power View & how it works (and where it sucks)
- Power Maps
- How to learn about these new technologies
- Recommended Books
- Websites
- Courses
- Live classes
- Special gift for our listeners
Go ahead and listen to the show.
Podcast: Play in new window | Download
Subscribe: RSS
Links & Resources mentioned in this session:
Mike’s website – Data Pig Technologies
Books mentioned
- Microsoft BI Tools for Excel Analysts by Mike, Jared & Bernard
- 101 Ready to use Excel Macros by Mike Alexander
- Excel Dashboards & Reports by Mike Alexander
- Power Pivot for the Data Analyst by Bill Jelen
- DAX Formulas for Power Pivot by Rob Collie
- Power Pivot Alchemy by Rob Collie & Bill Jelen
- Author profiles of Chris Webb & Mike Alexander
Power Pivot related articles & resources
- Why Excel shines & where it falls compared to other BI tools [PowerPivotPro.com]
- Introduction to Power Pivot & how to use it?
Add-ins mentioned:
- SQL Server Data Mining add-in
- I could not get a link to the segmentation, clustering add-in I used way back in 2006.
Dashboards mentioned:
Courses mentioned:
Mike & Bill’s upcoming Live Class
Transcript of this session:
Click here to download the transcript.
Thanks to Mike
Thanks Mike for joining me and sharing your knowledge, humor and passion with our podcast community. As always, it was fun talking to you. I look forward to featuring you again in our show sometime.
- 10% discount on Mike & Bill’s upcoming Power BI Bootcamp in USA
- Free Excel Dashboard Tools add-in
Listen to the podcast to know how to get these.
If you enjoy the podcast…,
Please do these 2 things. It means a lot me & our community.
- Subscribe to our podcast on iTunes
- Please leave an honest review on iTunes by clicking here.. It will help the show & its rankings immensely. Thank you so much.
Do you use Excel’s BI capabilities?
Do you use Power Pivot et al? How do you like them? Do you feel these tools empower you & your business? Please share your experiences & thoughts in comments.
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)