What if we can make an excel workbook that can teach us excel? Wouldn’t that be cool..?
It was a question that motivated Sean Duffy, a former Google employee and his friends to design Excel Everest – an excel spreadsheet built to teach you, … wait for it … , Excel.
Sean wrote to me a few weeks back and invited me to test his beautiful product. I have played with Excel Everest and I found it to be quite an interesting tool to learn Excel, so much that I wanted to share with you about this.
What is Excel Everest?
Excel Everest is an excel workbook designed to teach you excel. It has 41 topics and has 155 exercises, 339 buttons, and 87 embedded videos. It is a text book, problem set, video library all rolled nicely in to one excel file. It teaches you various excel topics, one at a time and gives you some problems to work on. Once you finish the problems, Excel Everest even grades you automatically. Pretty cool, eh?
See this short demo to understand how Excel Everest’s automatic grading works:
and here is a superb youtube video explaining Excel Everest:
Who is this for and What can you learn from Excel Everest?
Excel Everest is designed to help beginners and not-so-regular users of excel learn various features without getting lost. It teaches the following topics very well:
- Excel basics: What is excel, how to format data, using paste special, sorting and filtering data, adding / removing / hiding rows or columns, working with shapes
- Formulas: Formula basics, text formulas, IF formula, VLOOKUP formula, basic math formulas, statistical formulas (average etc.)
- Charts & Pivot Tables: Understanding numbers and answering questions.
- Tips to make your life easy with Excel: Removing duplicates, using keyboard shortcuts, introductory macros, printing excel sheets
Each topic is explained in a separate worksheet with text, images, videos (youtube videos embedded in excel) and various examples.
See an example page – Learning Basic Mathematical Formulas using Excel Everest
How much is it?
Excel Everest is priced at $34.95.
But here is the good news. When I told Sean that I would love to write a review of his product, he was kind enough to give readers of Chandoo.org a discount of 20%. So, you will actually pay just $28 for this when you use the discount code “chandoo“.
What is my opinion about Excel Everest?
Excel Everest is a fantastic way to learn excel if you are starting out. It is beautifully designed with lots of clear, simple explanations for various everyday excel features. I especially liked,
- How the file is structured and how each topic is flagged as easy / medium or hard (see below).
- Exercises and automated grading. There are questions / short quizzes after each topic and as soon as you enter you will graded.
- You can keep track of your progress and see how well you have scored across various topics / difficulty areas
That said, this is not the product if you are already familiar with various excel features and use them decently. For the rest of you, this can be an extremely fun way to learn excel all the while using it.
I recommend getting a copy of Excel Everest if you are new to Excel or need a thorough introduction to various features in Excel. Make sure you use the discount code chandoo to get 20% off the final price.
Do you have any questions about Excel Everest?
I have been using Excel Everest for last few weeks, so I kind of know what it does best and how it works. If you have any questions about it, ask them thru comments. I can answer them.
Disclosure: I receive small commission whenever you buy a copy of Excel Everest with discount code “chandoo“. But I am sure you will derive more benefit out of this than Sean or I will make out of the sale.
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 […]