3+1 Ways to Learn Advanced Excel

Share

Facebook
Twitter
LinkedIn

Many of us want to learn advanced Excel and make progress in our career. But how to do it?

In this post, I show 3+1 ways in which you can learn advanced excel.

Last week I did an interview with Robert Mundigl of clearlyandsimply.com. Robert is an Excel wizard. You may know him thru the KPI Dashboard articles he has written on chandoo.org a while ago.

We spent about 90 minutes discussing some really cool & advanced Excel stuff. The interview will be available shortly on Excel School for our Dashboard students. But here is a snapshot of the dashboard we discussed in the interview. Robert taught me how to make such a dashboard using Excel.


[view large]

Anyhow, I digress, so lets comeback. The topic of this post is 3+1 ways to excel in Excel.

1. Join Excel School:

Excel School - Online Excel Training ProgramOf course, the best possible way to learn Excel is to go thru a class. This is a proven approach and the 900 students of Excel School are a glowing testimony that it works. I believe that, by investing as little as 2-3 hours every week, anyone can become really awesome in tools like Excel. Sometimes, the benefits of training program are far-reaching, like the case of Ceri Williams, Excel School student in batch 1, 2 and 4:

I want to share some good news with you ! In recognition for my outputs & assistance to others, BT and recently made me an Excel SME (subject matter expert) … there’s only 9 of us in BT (100k+ employees in BT, and I’m the only one in BT Retail ~25k employees). Whilst I always considered myself as having strong excel skills I can honestly say your blog & tuition has taken me to a different level. So a massive thank you for sharing your knowledge & experience !!!

Key areas I think I have developed the most are :
– Integration of advanced functions to meet the needs of everyday problems (and even using simple ones to better effect) !
– Simplifying my style for visualizing data … I confess I use to add a few bells & whistles for my own guilty pleasures to old charts as opposed to delivering what they were designed for .. deliver simple, clear messages.

— Ceri Williams

You too can become like Ceri or countless other students who become awesome at their work just by learning the ropes of Excel.

Join Excel School today.

2. Learn Financial Modeling & Project Finance

Excel is used very much in financial industry because of the powerful analysis, modeling and calculation features it has. That is why, learning Financial Modeling or Project Finance modeling using Excel can be great career move.

We have concluded our first batch of financial modeling school recently and re-opened the program for students this week. So far, we already have 31 students in the program and many more are joining each day.

You too can join the program and become a financial modeling ninja.

For details & sign-up instructions, visit Financial Modeling School page.

3. Learn Excel Dashboards

Excel dashboards & Excel based Business Intelligence is another emerging area. Due to its ease of use and ability to integrate with database systems, Excel is a favorite among people building dashboards.

But making a dashboard is an arduous, complex process. And this is where, you could use step-by-step instruction and example material.

If you wish to learn Excel Dashboards, I recommend joining Excel School with Dashboards Option. It is an excellent program that teaches all things in Excel School + video instruction on making 4 different type of dashboards – KPI Dashboards, Business Dashboards, Sales Dashboards and Website Dashboards. There is a wealth of bonus material, dashboard tips & interviews with more than 10 hours of video content.

Excel School Dashboards

Click here to learn more about the program and Join.

+1. Read something new & Play with examples

Even if you are not ready for a paid program to learn excel, you can still excel in Excel by just reading 1-2 articles on Chandoo.org or any other Excel blog once a week. For starters, I recommend reading any article on these pages,

That is all for now. Go on and become awesome in Excel.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

13 Responses to “Using pivot tables to find out non performing customers”

  1. David Onder says:

    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 

    • Chandoo says:

      Of course with alternative data structure, we can easily setup a slicer based solution so that everything works like clockwork with even less work.

  2. Martin says:

    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,

  3. JMarc says:

    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

  4. Elias says:

    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

  5. RichW says:

    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.

  6. Kevin says:

    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

  7. Kevin says:

    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

  8. Kevin says:

     
    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.

  9. Bermir says:

    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.

    • Bermir says:

      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)

Leave a Reply