All articles in 'Pivot Tables & Charts' Category
Excel pivot tables are very useful and powerful feature of MS Excel. They can be used to summarize, analyze, explore and present your data. In plain English, it means, you can take the sales data with columns like salesman, region and product-wise revenues and use pivot tables to quickly find out how products are performing in each region.
In this tutorial, we will learn what is a pivot table and how to make a pivot table using excel.Continue »
Let’s say you are starting at a large dataset with multiple columns. You need to make a pivot report from it for a client or manager. How would you go about it?Continue »
Ever wondered how to get percentage of another value in Excel pivot tables, like this: In this tip, learn how to create such calculations using Excel pivot tables. Note: this tip is not compatible with older versions of Excel. If you are using Excel 2007 / 2010 / 2013, then please install free Power Pivot […]Continue »
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 »