CP018: Don’t be a Pivot Table Virgin!

Backlinkseller

CP018: Don’t be a Pivot Table Virgin!
Zum Original Blog Chandoo.org

In the 18th session of Chandoo.org podcast, lets loose your Pivot table virginity.

Note: This is a short format episode. Less time to listen, but just as much awesome.

CP018: Don't be a Pivot Table Virgin! - Introduction to Excel Pivot Tables - Chandoo.org Podcast

First some good news!

Our podcast crossed 100k downloads. Yay!!!

That is right. It took us 6 months & 17 episodes to reach this milestone.

Thanks for loving Chandoo.org podcast. You are awesome.

PS: If you have not already subscribed, get it here from iTunes (other ways to subscribe).

What is in this session?

Pivot tables are a very powerful & quick way to analyze data and get reports from Excel. But surprisingly, not many use them. Today, lets bust your pivot table virginity and understand the concepts like pivoting, values, labels, filters, groups and more.

Convert fractional Excel time to hours & minutes [Quick tip]


Convert fractional Excel time to hours & minutes [Quick tip]
Zum Original Blog Chandoo.org

Time for another quick Excel tip.

Convert fraction to hours & minutes in Excel - Tip

Lets say the park near your house rents tennis courts by hour. And they charge $10 per hour. At the end of an intense tennis playing week, Linda, the tennis court manager called you up and said you need to pay $78 as rent for that week.

How many hours did you play?

Of course 78/10 = 7.8 hours.

But we all know that 7.8 hours makes no sense.

We also know that 7.8 hours is really 7 hours 48 minutes.

So how to convert 7.8 hrs to 7:48 ?

That is our quick tip for the day.

Custom cell formatting to show hours & minutes in Excel from fractional timeSimple, assuming the fractional time is in cell A1,

Use below steps to convert it to hours & minutes:

What is the average speed of this road trip? [Solution & Discussion]


What is the average speed of this road trip? [Solution & Discussion]
Zum Original Blog Chandoo.org

Last week, we had an interesting homework problem – What is the average speed of this road trip?

We received more than 150 answers. But to my surprise, 57 of them are wrong. So today, lets learn how to calculate the average speed correct way.

Please click here to download solution workbook.

What is Speed?

Back in school days, we learned what speed is.

Speed = Distance / Time

Now lets look at the problem

Here is the data:

what-is-jacks-average-speed

Jack kept track for every 50 mile interval. And he did that for 12 intervals. So the total distance is 12×50 = 600 miles.

We just need to know how much time Jack took to cover the 600 miles to calculate the speed.

CP017: Top 10 non-Excel MS Office tips for you – Interview with Paul Woods – Office MVP & Blogger

CP017: Top 10 non-Excel MS Office tips for you – Interview with Paul Woods – Office MVP & Blogger
Zum Original Blog Chandoo.org

In the 17th session of Chandoo.org podcast, lets leave Excel aside and talk about other MS Office apps.

Thats right. We will be learning 10 tips on how to use Word, Power Point, Outlook etc. Ready?

CP017: Top 10 non-Excel MS Office tips for you - an interview with Paul Woods - Office 365 MVP

What is in this session?

A while ago, I got an email from Paul Woods. Paul is an Office 365 MVP, fellow blogger, father of twins and intrepreneur.  Since we have several coincidences in our lives (both of us are MVPs since 2009, dads to twins, bloggers & started businesses), I wanted to know more about him. I invited him to a podcast interview so that he can tell us his story and teach us how to use MS Office better.

In this podcast, you will learn,

Mapping relationships between people using interactive network chart

Mapping relationships between people using interactive network chart
Zum Original Blog Chandoo.org

Today, lets learn how to create an interesting chart. This, called as network chart helps us visualize relationships between various people.

Demo of interactive network chart in Excel

First take a look at what we are trying to build.

Network Relationships - Interactive Chart in Excel - Demo

Looks interesting? Then read on to learn how to create this.

Note: thanks to Hans whose email question inspired me to create this chart.

Tutorial to create interactive network chart in Excel

Note: This tutorial requires intermediate-to-advanced Excel knowledge. So if you are beginner, learn the basics & advanced concepts first and then comeback for this.

In order to create this chart in Excel, we need to first understand various ingredients of it.

As you can see, the chart contains these parts:

  1. A set of dots, each representing one stakeholder

What is the average speed of this road trip? [homework]

What is the average speed of this road trip? [homework]
Zum Original Blog Chandoo.org

Its homework time again.

This time, lets tackle an interesting & everyday problem.

Lets introduce our protagonist of the story – Jack.

Jack likes long road trips, smell of freshly brewed Colombian coffee, clicky-clack sound of his computer keyboard. He hates toll plazas (they slow him down) & Potassium permanganate.

And oh yes, Jack is obsessive about analyzing every little bit of data in his life.

That brings us to the recent road trip he took.

It was a total of 600 miles.

And Jack tracked the speed at which he was covering every 50 mile distance.

The data is neatly typed in to an Excel workbook (snapshot below), in the range A2:A13.

what-is-jacks-average-speed

CP016: 3 Must have books for aspiring analysts

CP016: 3 Must have books for aspiring analysts
Zum Original Blog Chandoo.org

In the 16th session of Chandoo.org podcast, lets review 3 very useful books for aspiring analysts.

CP016: 3 Must have books for aspiring analysts - Chandoo.org podcast - become awesome in data analysis, charting, dashboards & VBA using Excel

What is in this session?

Analytics is an increasingly popular area now. Every day, scores of fresh graduates are reporting to their first day of work as analysts. But to succeed as an analyst?

By learning & practicing of course.

And books play a vital role in opening new pathways for us. They can alter the way we think, shape our behavior and make us awesome, all in a few page turns.

So in this episode, let me share 3 must have books for (aspiring) analysts.

Note: these are not only 3 books you should read. But these 3 are the ones I am reading now and I think they will certainly help you.

A dashboard to visualize this FIFA worldcup [guest post]

A dashboard to visualize this FIFA worldcup [guest post]
Zum Original Blog Chandoo.org

This is a guest post by Krishna, a football lover & one of our readers. He is also a student of Chandoo.org online VBA classes.

The wait for lifting the most valued priced in football for Germans was finally over. For a football fan, world cup is best time that is scheduled every four years and that if your favorite team lifting the trophy is like your crush is going on a date with you. :)

A sneak-peek at the final dashboard

Here is the final dashboard (it has more functionality than depicted). Click on it to enlarge.

FIFA 2014 Worldcup Excel dashboard

Download the dashboard workbook

Click here to download the workbook. Refer to it while reading this article for most benefit.