Remember Excel School? It was all I talked about between Jan last week and middle of Feb. Then as if someone has pressed mute button, there was not even a single mention about the school. So I thought, why not give you all an update on Excel School and tell you how the classes are going.
Some stats about the program:
- A total of 147 students signed up for the program.
- 3 Students dropped out for various reasons.
- Out of total 12 weeks, we have finished 8 weeks of lessons now.
- We have covered extensively on topics like excel formulas, charting, conditional formatting, formatting, tables and pivot tables until now. We also did a class project.
- In the next 4 weeks we will be talking about data validation, filters, importing data, advanced formulas, macros and do another class project.
- There were a total of 25 lessons, with roughly 800 minutes of video lessons and several downloadable excel workbooks.
- A total of 219 comments were posted by students discussing lesson topics, asking questions and doubts.
What is the student feedback?
While it is a little early, I am very happy to tell you that students are really enjoying Excel School and have been liking what is shared so far. Some encouraging comments I have received are,
I just wanted to give you some feedback on our lessons so far. While I wouldn’t consider Excel school to be a beginner’s 101 course, I wish I had found something with this level of instruction years ago.
I’m having a blast looking at things that I thought I understood well. You have given some new insight into several things that I’ve been able to apply to some workbooks at my job. Just with one powerful little formula tweaks I figured you saved me and a colleague 475 minutes per month on a monthly report that we have to prepare. Over a years time that equates to over $3500. That’s only one instance.
This is the best instruction that I’ve ever come across. You make Excel fun! THANK YOU! Thank you for being so generous with all of the great information that you share. You truly are awesome!
I am a member of your Excel School and continuously watching your postings. I just want to thank you personally for providing me such a good excel tutorials.
I really am learning a lot…I am stubborn and will keep trying till I get the ideas. And you have so many wonderful tricks I never knew about. I got started with you in charting and I have learned so much and done some nifty charts. I am looking forward to learning a lot more from you in the future. I am amazed how much you know and how well you are able to explain things. I thought I was pretty proficient in formatting till I watched week 2 and I have discovered that there is much more I do not know than I do. Can’t wait for the rest of the classes.
I just wanted to tell you that so far in the Excel School, I am blown away with the quality and amount of information I’m getting out of the course. I have always been the excel expert where ever I work, and it’s inspiring to see what you’ve been able to do with it. I didn’t realize there was so much more to learn.
Been taking an online class in Excel. About 6 weeks into it and wow, I have learned a lot. And I am the best Excel user in my department at work. Class is hosted by Chandoo and he is good, really good. Runs a great blog called Pointy Haired Dilbert. A lot of value in this thing: class was approximately $100 for 15 weeks of awesome-ness. … I am loving it. … The surprising thing I have learned is how to better present information for others. I have always taken pride in making good spreadsheets instinctively, but this has made me much better.
My thoughts on the program so far,
To be frank I was *very* nervous when I closed the signups for the excel school. Having 147 students in the class, each with different and probably very high expectations psyched me a bit. To top it, after running barely one week of the program, I had to move from Denmark to India and that posed different challenges (mainly on bandwidth, internet connectivity, free time fronts). Having 2 small babies at home didn’t help either.
But, the students are generous, eager and fun. They lapped up the lessons with enthusiasm and discussed topics with a sense of humor. They supported me when I told them I had to lie low on comment replies during my transit from DK to IN. They didnt mind when a video link was broken or download went 404. They just gave me time to correct it. Few enthusiastic fellas even emailed me and told how *awesome* the classes are and helped me gain confidence. Thank you.
The promised goodies…
I am hoping to start the next batch of Excel School at End of May 2010. I am actively looking for students now…
So if you are interested in an online excel training program please tell me your name and email address. I will update you once the program is ready for registration. Also, you will be receiving 3 free lessons,
- Excel conditional formatting lesson – as soon as you sign up below
- SUMPRODUCT Formula lesson – by End of April
- Excel Pivot Table Tricks (lesson by Debra Dalgleish and me) – by end of May.
Please use the below form (click here if are not able to see it) to express your interest in Excel School Program:
PS: For Excel School Curriculum & Details pls. visit – http://chandoo.org/wp/excel-school/
PPS: The price of Excel School will remain same at $97
Are you a student? What do you think of Excel School?
If you are an Excel School student, please take a minute and share your opinion of the program thru comments. I would love to know how it is helping you be more awesome in excel. Also, pls. share your suggestions for improvements for future batches.
13 Responses to “Using pivot tables to find out non performing customers”
To avoid the helper column and the macro, I would transpose the data into the format shown above (Name, Year, Sales). Now I can show more than one year, I can summarize - I can do many more things with it. ASAP Utilities (http://www.asap-utilities.com) has a new experimental feature that can easily transpose the table into the correct format. Much easier in my opinion.
David
Of course with alternative data structure, we can easily setup a slicer based solution so that everything works like clockwork with even less work.
David, I was just about to post the same!
In Contextures site, I remember there's a post on how to do that. Clearly, the way data is layed out on the very beginning is critical to get the best results, and even you may thinkg the original layout is the best way, it is clearly not. And that kind of mistakes are the ones I love ! because it teaches and trains you to avoid them, and how to think on the data structure the next time.
Eventually, you get to that place when you "see" the structure on the moment the client tells you the request, and then, you realized you had an ephiphany, that glorious moment when data is no longer a mistery to you!!!
Rgds,
Chandoo,
If the goal is to see the list of customers who have not business from yearX, I would change the helper column formula to :
=IF(selYear="all",sum(C4:M4),sum(offset(C4:M4,,selyear-2002,1,columns(C4:M4)-selyear+2002)))This formula will sum the sales from Selected Year to 2012.
JMarc
If you are already using a helper column and the combox box runs a macro after it changes, why not just adjust the macro and filter the source data?
Regards
I gotta say, it seems like you are giving 10 answers to 10 questions when your client REALLY wants to know is: "What is the last year "this" customer row had a non-zero Sales QTY?... You're missing the forest for the trees...
Change the helper column to:
=IFERROR(INDEX(tblSales[[#Headers],[Customer name]:[Sales 2012]],0,MATCH(9.99999999999999E+307,tblSales[[#This Row],[Customer name]:[Sales 2012]],1)),"NO SALES")
And yes, since I'm matching off of them for value, I would change the headers to straight "2002" instead of "Sales 2002" but you sort the table on the helper column and then and there you can answer all of your questions.
Hi thanks for this. Just can't figure out how you get the combo box to control the pivot table. Can you please advise?
Cheers
@Kevin.. You are welcome. To insert a combo box, go to Developer ribbon > Insert > form controls > combo box.
For more on various form controls and how to use them, please read this: http://chandoo.org/wp/2011/03/30/form-controls/
Thanks Chandoo. But I know how to insert a combobox, I was more referring to how does in control the year in the pivot table? Or is this obvious? I note that if I select the Selected Year from the PivotTable Field List it says "the field has no itens" whereas this would normally allow you to change the year??
Thanks again
worked it out thanks...
when =data!Q2 changes it changes the value in column N:N and then when you do a refreshall the pivottable vlaues get updated
Still not sure why PivotTable Field List says “the field has no itens"?? I created my own pivot table and could not repeat that.
Hi, I put the sales data in range(F5:P19) and added a column D with the title 'Last sales in year'. After that, in column D for each customer, the simple formula
=2000+MATCH(1000000,E5:P5)
will provide the last year in which that particular customer had any sales, which can than easily be managed by autofilter.
Somewhat longer but perhaps a bit more solid (with the column titles in row 4):
=RIGHT(INDEX($F$4:$P$19,1,MATCH(1000000,F5:P5)),4)
[…] Finding non-performing customers using Pivot Tables […]