All articles in 'Pivot Tables & Charts' Category
Ever wanted to count distinct values in your pivot tables? Something like above:
Let’s say you have store sales data. Several products are sold on each day. When you make a pivot table from this data and add product count, Excel counts all products. But we want to see just the distinct count (ie if there is a duplicate product in a day, we want to count it just once).
Here is a simple trick to add distinct count to Excel pivot tables easily.Continue »
Analyst’s life is busy. We have to gather data, clean it up, analyze it, dig the stories buried in it, present them, convince our bosses about the truth, gather more evidence, run tests, simulations or scenarios, share more insights, grab a cup of coffee and start all over again with a different problem.
So today let me share with you 35 shortcuts, productivity hacks and tricks to help you be even more awesome.Continue »
We all know that Pivot Tables are best thing since avocado on toast. But they can’t slice text values and spread them in a table with Pivots. So how to take a large blob of text and turn it in to something meaningful like above?
Simple, we use Power Query.Continue »
Ever wanted to make a cool, snazzy interactive chart in Excel? Something like this:
In this tutorial, learn all about making your very first interactive chart. We use both formulas and pivot tables to build two versions of an awesome interactive chart in Excel.Continue »
Pivot tables are fun, easy and super useful. Except, they can be ugly when it comes to presentation. Here is a quick way to make a pivot look more like a report.
- Just type over the headers / total fields to make them user friendly.
See this quick demo to understand what I mean:
So simple and effective.Continue »
Of course, not everyone can whip up a sumproduct formula like that. On a scale of One to Hui of Excel awesomeness, you would need to be at least an H to write sumproduct or countifs formulas shown in that post. So does it mean, you can’t conditional rank if you don’t know your X from L?
Don’t worry. We got you covered. You can still get your conditional ranks, without inception level array formulas. Simple, use pivot tables instead.Continue »
Recently I was creating a pivot report with multiple items in row labels area. I had to show sub-totals, but only for one of the fields. Something like above.
How to show selective sub-totals in Pivot TablesContinue »
Late. Jayaram, my uncle is also a teacher. When I was a kid, I used to spend a lot of time with him, learning all sorts of things. He taught me chess, maths and so many life lessons. I remember one such lesson very vividly. One day, he asked me to do something. I did it in a very long way. After seeing me struggle for several minutes, he chipped in and showed me how to do it easily. He then said, “when someone asks you where your nose is, you don’t twist arm around your head. You just point to your nose directly.”
The idea is that when you have a direct, simple way to do something, you should use it.
Nose and pivot tables… how are they connected?
We are coming to the point. Read on for full case study and solution.Continue »
Pivot tables are lovely. But sometimes they are hard to work with. Let’s say you are analyzing some HR data and want to see number of weeks worked in each hour classification.
And you want this.
Except, there is a teeny tiny problem.
The sort order on the classification is all messed up.
Here is a quick fix to get custom sort order on your pivot table row labels.Continue »
First a quick personal update: There has been a magnitude 7.8 earth quake in NZ on 14th November 2016 early morning. It is centered in Kaikoura, which is about 250 km away from Wellington. We did feel several shakes and after shocks. It has been an interesting and often scary experience. But my family is safe. I feel very sad for the all the damage and the loss for families in NZ. If you suffered from this quake, My prayers and thoughts are with you.
Yesterday, a friend asked me an interesting question. He has school distance data, like above. He wants to know which is the closest school for each school.
There are a few ways to answer this question. Let’s examine two approaches – formulas & pivot tables and see the merits of both.Continue »
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.Continue »
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.Continue »
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.Continue »
Time for a quick but very useful tip. Ever wanted to create all combinations from two (or more) lists? a la Cartesian product of both lists.
Here is a ridiculously simple way to do it.Continue »
Wellington(NZ) sure is beautiful on a clear day. There are so many cool bike tracks, walking trails and beaches to keep you busy. Today, I went cycling on my usual route towards Makara beach. At the turn off point, I thought, “This is selfish. I can’t keep all this beauty to myself. I must share it with you.” So here we go.
A quick video about data analysis while on bike
Technically, I was not on bike when recording this video. Watch it below or on our YouTube Channel.Continue »