Chandoo.org - Learn Excel, Power BI, Charting, Dashboards and VBA online

Latest Tips & Tutorials

Chandoo.org Podcast Sessions

CP056: So which formulas you should care to learn?

Subscribe: Apple Podcasts | Spotify | RSS

In the 56th episode of Chandoo.org podcast, let me answer the chicken and egg question of Excel users. How many formulas should you care to learn?

What is in this session?
In this podcast,

Two personal updates
3 legs of formula writing
- Function knowledge
- Operators
- Referencing
6 categories of must-know functions
- Basic math
- Conditions
- Lookups
- Text
- Date & time
- Work specific
Closing remarks & resources for you

Read This »

October 27, 2016

Find first & last date of a sale using Pivot tables [quick tip]

Here is a quick Pivot table tip. Let’s say your work at ACME inc. requires some fancy pants analysis of product sales. Imagine looking at below data & trying to find out the earliest & latest date for each product sale.

Of course, we can concoct a version of MINIFS & MAXIFS to answer the question. But why bother, when you can answer the question with just a few clicks.

Read This »

October 26, 2016

How many formulas should you learn? [Weekend Poll]

Over at twitter, @for_the_moves asks,

@For_the_moves Same as growing your vocabulary. Remember, words (or functions) = ideas. the more you know, the better you can think.

— Chandoo.org (@r1c1) October 12, 2016

That got me thinking. How many functions should you care to learn?

Read This »

October 20, 2016

Finding if a cell has 7 in it… [Pattern matching in Excel]

Imagine you work at MI5 as a HR officer. You want to find all agents who have license to kill (licence 7). Your data looks like above.

How would you go about it?

If you filter the list or use FIND() or SEARCH() formulas, you will end up with agents who also have licenses 77, 17 or not7. So how would you solve this problem?

Of course, you do what any smart person does. You summon Excel and ask it nicely by using some wicked pattern matching logic.

Read This »

October 18, 2016

Chandoo.org Podcast Sessions

CP055: “Yes, I am back” edition (and a bonus Excel tip)

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

Ladies & gentlemen, its time we revived the much loved Chandoo.org podcast. In the 55th episode, I do a lousy imitation of Arnold Schwarzenegger’s famous “I will be back” and tell you why there was such a long gap between episodes, my plans for reviving our podcast and more.

What is in this session?

In this podcast,

Why there was such a long gap between last and this episode
What next?
How to extract every 6th item from a list?

Read This »

October 13, 2016

Interactive Decision Tree Visualization in Excel [Trump vs. Hillary in Swing States]

It is election time in USA, and that means there is a whole lot of drama, discussions and of course data analysis. There are tons of cool visualizations published on all the data. Previously, we talked about “How Trump happened” chart.

Today let’s take a look at the beautiful decision tree chart by NY Times explaining what would happen if each of the 10 swing states vote for Democrats or Republicans. Go ahead and look at that chart. And when you are done playing with it, come back.

My first thought after looking at the chart is: Wow, that is cool. I wonder how we can recreate that experience in Excel?

But as you can guess, making a dynamic tree visualization in Excel is pretty hard. You can create a bubble chart mixed with XY chart to show all the nodes of the decision tree, but as this tree has 2^10 nodes at the bottom level (and 2^11-1 total nodes) our chart would look very clumsy and busy.

So, instead of replicating NY Times chart, why not make our own version that explains the data? You can reuse this idea when visualizing outcomes of several what-if scenarios.

Read This »

October 11, 2016

Currency format Pivot fields with one click [Friday VBA]

Anyone who has made a pivot table and their grandma knows that formatting them is a pain. Let’s recap the steps to apply one of the most common formats – currency format.

Right click on any value field
Go to Value field settings
Click on “Number Format” button
Choose Currency format
Close the boxes, one after another

Unless you get paid per click, you wont be happy with all those clicks.

Wouldn’t it be cool to just click once and apply most common format to your pivot fields?

Of course you can. Just add oneClickCurrency macro to your personal macros workbook. And then add this to your Home ribbon as a custom button and you have a one click format option for any pivot.

Read This »

October 7, 2016