All articles in 'Pivot Tables & Charts' Category

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.

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.

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.

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.

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.

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.

1. Right click on any value field
2. Go to Value field settings
3. Click on “Number Format” button
4. Choose Currency format
5. 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.

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.

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.

Over the weekend, I got an email from Mr. E, one of my students. Mr. E works at a police department in California and as part of his work, he was looking at calls received by police. Whenever police get a call for help, multiple teams can respond to the call and go to the location. All of these dispatches are recorded. So a single call can have several such dispatches. And Mr. E wanted to findout which team responded the first. The problem?

Finding the first responded team is tricky.

Today let’s take up this problem as a case study and understand various methods to solve it.  We are going to learn about writing better lookups, pivot tables, power pivot and optimization. Put on your helmets, cause this is going to be mind blowingly awesome.

Have you ever wondered about applying different Spreadsheet Formats or Styles to reports which you may be send to different people and so the styling may be different for each recipient?

I haven’t, but in this post I will show how you can add it to your worksheets.

As announced earlier, here is the first one of our Master Classes. In this one, you will learn how to create advanced interactive charts in Excel by blending pivot tables, slicers, timelines, VBA, conditional formatting and charts. The final outcome is BEAUTIFUL!!! Monthly Master Class – Episode 1 – Trailer Check out a quick trailer of the […]

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 54th session of Chandoo.org podcast, let’s make you awesome in Pivot Tables.

What is in this session?

In this podcast,

• Quick updates
• Top 10 pivot table tricks
  • Adding same value field twice
  • Tabular layouts
  • GETPIVOTDATA & 2 bonus tricks
  • Relationships & data model
  • One slicer to rule them all
  • Show only top x values
  • Relative performance
  • Show unique count
  • Spruce up with conditional formats
  • Not so ugly pivot charts
• Resources & Show notes for you

Mary Ellen, one of our readers, has an interesting conundrum,

I have some data that goes to Pivot table then to pivot chart. There is a slicer to filter the data. But when I slice, my pivot chart gets messed up. How to have the slicer, but still see the insights in the chart?!?

See above demo to understand:

This is because when you slice by a school, the pivot table gets filtered and hence % row total for that school becomes 100% (as there are no other schools).

How to fix the problem? The easy answer is to remove the slicers. But we want to have our slicers and eat a slice of them too. So we crank up the Excel awesomeness valve and get to work. There are two ways to achieve what we want.

• Old school method: Two pivot tables, some formulas & a line chart
• New Excel method: Power Pivot and a line chart

Read the rest of this article to know more.

Or more appropriately titled, the one where Power Query solves the problem in less time than it takes you to say Get & Transform Data.

Recently, one of my students Mr. K, sent me a pivot table problem.

Today my boss asked me “how much we paid to staff since the inception of our business with their respective date of joining?” He wanted to know, level wise summary of the last 16 years (on Quarterly / Year wise basis).

The records appended from the database month wise. Have a look to the file and give your ideas.

Mr. K’s data looked like above.

Most advanced Excel users know that slicers are cool. Today, let’s learn how to use slicers to create an awesome selection mechanism for your dashboards and forms.

First see a quick demo

Looks slick, eh? Read on.