- Announcements
- What is a Pivot Table?
- Example of business data & reporting needs
- Key pivot table terms to understand
- Creating your first pivot table
- Learning more about pivot tables

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

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).

**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.

In this podcast, you will learn,

- Announcements
- What is a Pivot Table?
- Example of business data & reporting needs
- Terms to understand
- Labels
- Values
- Groups
- Filters
- Report filters
- Slicers
- Label & Value filters

- Creating your first pivot table
- Learning more about pivot tables

**Excel Pivot Tables – Introduction, Examples, Tutorials & Tips**

**Advanced Pivot Table concepts
**

- Introduction to Excel 2013 data model & relationship features
- Grouping Data in Pivot Tables
- Report Filters – what are they and how to use them?
- Slicers – What are they and how to use [example dashboard]
- Value filters – showing top 10 values

**Pivot Table uses & case studies**

- Show monthly values & % change in one pivot table
- Matching transactions using pivot tables
- Calculating conversion ratio using pivot tables
- Selecting a random sample of data with pivot tables
- More on Pivot tables

**Books & Courses on Pivot Tables**

- Recommended book: Excel 2013 pivot table data crunching by Mike Alexander & Bill Jelen
- Recommended course: Excel School online training program

Download this podcast transcript [PDF].

I lost my pivot table virginity in 2005. But I quickly regained it as I did not use them much for next 3 years. Then I lost it for good and I am glad for that. Now a days, I use pivot tables almost every week. And they give me quick and easy solutions to many analytical problems I face.

**What about you?** When did you loose your pivot table virginity? How do you use them every day? Please share your tips, stories & experiences in the comments area.

]]>

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.

Simple, assuming the fractional time is in cell A1,

Use below steps to convert it to hours & minutes:

- In the target cell, write =A1/24
- Select the target cell and press CTRL+1 to format it (you can also right click and select format cells)
- Select Custom from “Number” tab and enter the code [h]:mm
- Done!

**52 Bonus tips:**

Thats all for now. Stay awesome until next *time*.

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**.

Back in school days, we learned what speed is.

Speed = Distance / Time

Here is the data:

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.

We know that Jack covered first 50 miles @ 43mph.

So the time taken for first 50 miles is 50/43 = 1.16 hrs (or 1 hr & 9 mins)

Like wise, if we calculate times taken for all the 50 miles, we get this:

Now you are talking.

While the detailed break-up of the calculation above helps us understand how Time, Speed & Distance are related, when answering a question like “What is Jack’s average speed?”, you may want to write a single formula to get the answer (instead of all the extra helper column cells).

The range A2:A13 contains speeds per 50 mile intervals.

The time taken for first 50 mile is =50/A2

The time taken for second 50 mile is = 50/A3

…

So, 50 / (A2:A13) should give us an *array of times. *

And the total time taken is a simple sum of this array.

So, SUM(50/(A2:A13)) should give the total time.

Now, if we divide 600 by this, we should get our average speed for the entire trip.

Our first formula for calculating average speed is,

=600 / SUM(50/(A2:A13))

Since this is an array formula, you should press CTRL+Shift+Enter to get it work.

While the above formula works beautifully, it is a bummer that we must press CTRL+Shift+Enter to get it work. Why not use a formula that can natively process arrays.

Enter SUMPRODUCT.

=600 / SUMPRODUCT(50/(A2:A13))

works just as beautifully and you don’t have to press CSE.

Lets expand the formula and see what is happening, *mathematically speaking.*

Our formula is,

=600 / SUM ( 50 / (A2:A13) )

in mathematical terms, this is,

= 600 / ? [ 50 / (A2:A13) ]

= 600 / [50/A2 + 50/A3 + 50/A4 + ... + 50/A13]

After isolating 50, we get:

= 600 / [50 * (1/A2 + 1/A3 + 1/A4 + ... + 1/A13)]

= 12 / (1/A2 + 1/A3 + 1/A4 + … + 1/A13)

Lets call this blue expression as **(1)**.

Time for introducing the concept of **Harmonic Mean.**

The harmonic mean is the *reciprocal of the arithmetic mean of reciprocals*.

Sounds confusing?!?

Take a sip of that coffee and read again.

*reciprocal of the arithmetic mean of reciprocals*

So harmonic mean of a range of numbers (say a,b,c,d…) is

=1/ [(1/a + 1/b + 1/c +...) / (count of numbers)]

or in other words,

= count of numbers / sum of reciprocals

Applying this concept to the range A2:A13, we get

= count of range / sum of reciprocals of A2:A13

= 12 / (1/A2 + 1/A3 + 1/A4 + … + 1/A13)

Now, isn’t the red expression of harmonic mean same as the blue expression **(1)** above?

Thus, to calculate the average speed, we just need harmonic mean of the the range A2:A13.

And there is a perfect formula for that.

**=HARMEAN(A2:A13)**

*So, we can use that and it gives average speed for the trip in one step!*

Lets say Jack measured his speed at 40,50,60,40,60,50,40,50,60,60,50,40 mile intervals instead of every 50 miles.

In such case, we can’t use HARMEAN() because the distances are not equal. Fortunately, we can still use SUMPRODUCT.

Assuming the distance covered per interval is in the range B2:B13 (speeds are already in A2:A13),

The formula,

=SUM(B2:B13)/SUMPRODUCT(B2:B13/A2:A13)

tells us the average speed of the trip.

Learn more: Calculating weighted average using SUMPRODUCT.

Click here to download the solution workbook. Examine the formulas to learn more.

As a bonus, It contains an additional problem to test your skills.

Lets say after all this formula struggle, Jack (our driver of the road trip) wised up and started tracking time instead of speed. So his new log looks like this:

**Now how do we calculate the average speed?**

The time stamp data is in range A2:A16 and distance is in B2:B16.

Please post your formulas in the comments section.

PS: The solution workbook contains answer to this problem as well. Just unhide to see.

Go ahead and post your answers. This time, lets hope we get fewer than 1/3rd answers as wrong.

Check out any article from our formula forensics or Excel homework pages to learn something interesting & cool. Also go thru SUMPRODCUT & Advanced SUMPRODUCT articles to sharpen your formula writing skills.

Listen to our podcasts on averages to raise above your average.

]]>- About Paul
- Ten tips for MS Office
- 1. Use Excel to communicate instead of just calculations
- 2. Paste Special
- 3. Double click trick!
- 4. Inserting screenshots
- 5. Turning off notifications
- & more...

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

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,

- About Paul
- Ten tips for MS Office
- 1. Use Excel to communicate instead of just calculations
- 2. Paste Special
- 3. Double click trick!
- 4. Inserting screenshots
- 5. Turning off notifications
- 6. Reply with a meeting
- Bonus tip: Type plain English notation of dates in meeting requests
- 7. Outline view in Word
- 8. Styles
- 9. Presenting slides online
- 10. Use One Note
- Conclusions

**Paul Woods websites**

Many thanks to Paul Woods for sharing beautiful tips with all of us in this podcast. Please visit his websites to know more about his work & life.

**Follow up tricks**

- Format Painter (and 7 more formatting tips)
- Format Painter for charts
- Double click tricks in Excel
- Using Paste Special in Excel

Download this podcast transcript [PDF].

I like the tips about outline view & presenting online from this podcast. Apart from these 10, I also like alignment tools, slide transitions in Power Point, mail merge in Word.

**What about you?** What are your favorite non-Excel Office tips? Please share using comments.

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

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

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

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:

- A set of dots, each representing one stakeholder
- A set of grayish thick & dotted lines representing all relationships between people.
- A set of green thick & blue dotted lines representing relationships for the selected person.
- A slicer for person selection (can be replaced with list box or clickable cells in Excel 2007 or below)
- Summary statistics of the selected person

To simplify our tutorial, lets assume we are talking about relationships between just 4 people, named Ash, Billy, Cynthia & Darren.

Our relationship matrix looks like this:

- 0 means no relationship
- 1 means weak relationship (for example: Ash & Billy just know each other)
- 2 means strong relationship (for example: Cynthia & Billy are friends)

*The downloadable workbook is created to take up to 20 stakeholders. *

If we draw the relationships between these 4 people (Ash, Billy, Cynthia & Darren) on a paper, it would look like this:

The 2 things we need to determine are,

- The location of dots (where person names are printed)
- The lines (starting & ending point of lines)

We need to plot our dots in such a way that gap between each dot is same. This will create a balanced chart.

What shape satisfies our need for such equal gaps? *A circle of course.*

**Hey wait, I don’t see a circle in the chart you have shown…?**

Thats right. We don’t need to draw a circle. We just need to plot dots around it.

- So we have 4 stakeholders, we need 4 dots
- If we have 12 stakeholders, we need 12 dots
- If we have 20, we need 20 dots.

Assuming the origin of our circle is (x,y), radius is r and *theta *is 360 divided by number of dots we need,

the first dot (x1,y1) on the circle will be at this position:

x1 = x + r*COS(theta)

y1 = y + r*SIN(theta)

[Related: How to create a spoke chart in Excel]

Once all the dots are calculated & plugged in to an XY chart (scatter plot), lets move on.

Lets say we have **n **people in the network. So that means, each person can have a maximum of **n-1 relationships. **

So the total possible lines in our chart are n*(n-1)/2

*We need to divide it by 2 as if A knows B, then B knows A too. But we need to draw only 1 line.*

My network chart template is set up to work with up to 20 people. So that means, the maximum number of lines we can have will be **190**

Each line requires a separate series to be added to the chart. That means, we need to add 190 series of data just for 20 people. And that satisfies only one type of line (either dotted or thick). If we want different lines based on type of relationship, then we need to *add another 190 series*.

This is painful & ridiculous.

Fortunately there is a way out.

**We can use far fewer series and still plot the same chart.**

Lets say we have 4 people – A B C & D. For the sake of simplicity, lets assume the co-ordinates of these 4 are

- A – (0,0)
- B – (0,1)
- C – (1,1)
- D – (1,0)

And lets say, A has relationships with B, C & D.

That means we need to draw 3 lines, from A to B, A to C & A to D.

Now, instead of supplying 3 series for the chart, what if we supply one *long series *that looks like this:

(0,0), (0,1), (0,0), (1,1), (0,0), (1,0)

That means we are just drawing one long line from A to B to A to C to A to D. Agreed that it is not a straight line, but Excel scatter plots can draw any line as long as you provide a set of co-ordinates.

PS: This is a trick I learned from Roberto of E90E50. He used this trick in the winning entry of our recent dashboard contest.

See this illustration to understand the technique.

So instead of 190 series of data for the chart, we just need 20 series.

In the final chart, we actually have 40 + 2 + 1 series of data. This is because,

- 20 lines for weak relationships (dotted lines)
- 20 lines for strong relationships (thick lines)
- 1 line for highlighted person’s weak relationships
- 1 line for highlighted person’s strong relationships
- 1 set of no line & just dots for the people

**How to generate all the 20 series of data:**

This requires following logic:

- Assuming we need lines for the relationship of person n.
- That person’s dot location will be (Xn, Yn) and already calculated earlier (in the plotting dots around circle)
- We need total of 40 rows of data
- Every odd row will have (Xn, Yn)
- For every even row
- Divide the row number by 2 to get person number (say m)
- (Xn,Yn) if there is no relationship between n and m
- (Xm,Ym) if there is a relationship

We need MOD & INDEX formulas to express this logic in Excel.

Examine the download workbook to understand how its done.

Once all the line co-ordinates are calculated, add them to our scatter plot and format.

*I used a macro to automate the formatting. It can be done manually too, just takes a little patience.*

*This works only in Excel 2010 or above.*

Select the first 2 columns of relationship matrix & create a pivot table.

Now, insert a slicer on Person name column.

Using simple IF formula, extract the selected person name from pivot table (examine download file for the logic).

And using the name, extract the subset of line data to separate range (2 sets of data – one for weak & one for strong relationships)

Add this new data to our scatter plot and format.

Format the slicer (using slicer styles) so that it looks slick.

Related: formatting slicers using styles.

**NOTE About Slicers: **If you change or add any data, you must refresh (from Data ribbon) to update the slicer. This can be automated with a macro, but I want to keep this file macro free.

You can use either a list box or a range of clickable cells. See the 2003 compatible download file for an example of this.

Using simple formulas extract statistics for the selected person and show them near the chart.

In our chart, we are showing person names instead of regular label like X or Y value. This is done with **value from cells **label feature in Excel 2013.

For earlier versions of Excel, I recommend using Rob Bovey’s excellent XY Chart Labels add-in.

Once everything is ready, clean up the chart, slicer and other elements, put them together. And we are ready to go.

**Click here to download the chart template workbook**. The download is a ZIP file and it contains 3 workbooks – compatible with Excel 2013, 2010 & 2003+. Use the version that you need.

Please examine the formulas & chart settings to understand how it is constructed.

Note: Hit Refresh from Data ribbon to change slicer once you have added or modified data.

A network graph is a good place to explore relationships between people in a project or team. It is especially useful when selecting a sub-set of people from large group to closely work on a project.

There is a popular Excel Add-in named **NodeXL** that can help you visualize and analyze relationships between people in a more in-depth fashion.

Check out Chord diagram & Cosmograph from E90E50 site for other ways to present this data.

I have used network charts earlier to depict relationships between various people or things. But I have never created such charts in Excel, I always used either Power Point or some other drawing program to create them. That is why I am excited about this chart. Figuring out the formula & graphing logic was fun.

**What about you? **Have you used such charts before? How do you like the network chart presented here? Please share your thoughts using comments.

]]>

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.

At the end of the trip, Jack wants to know what his average speed is.

But his Excel skills are mediocre. So he approached you, an **awesome analyst in the making**.

Your mission, if you choose to accept, is this:

Figure out

which formula calculates average speedand post it in the comments section

So what are you waiting for. Open up Excel, solve the problem and share your answers here. Make Jack happy.

**Click here to post your answer.**

There is no harme.An unusual formula works too.

If you are hungry for helping more poor Jacks, check out our Excel Challenges & Home work pages. Test your Excel skills, solve something tricky and feel awesome.

]]>*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.

**Participate in our Analyst Book Giveaway & win:**

- You could win a copy of any of these 3 books.
- Last date: 15-AUG-2014 – Friday.

Listen to the podcast to know how to participate.

This podcast is essentially a review of the 3 books – Data Smart by John Foreman, Ctrl+Shift+Enter by Mike Girvin & Think Like a Freak by Steven Levitt & Stephen Dubner.

- Why this book?
- About John Foreman
- Writing style
- Example chapter

**Ctrl+Shift+Enter – Mastering Array Formulas by Mike Girvin**

- Why this book?
- About Mike Girvin
- Writing style
- Example Array Formulas

**Think Like a Freak by Steven Levitt & Stephen Dubner**

- Why a non-Excel book?
- About authors
- Writing style
- Example Chapter on Takeru Kobayashi

Note about the book links: These are affiliate links. It means, when you click on them and purchase a copy of the book, Chandoo.org receives a small commission. I recommend these books because I really enjoy them and I genuinely think they will benefit you. I would have recommended them even when there is no commission involved.

**About the bicycle ride**

**Excelapalooza Excel conference:**

**Advanced Excel, Dashboards & Power Pivot Masterclass:**

**More on Array Formulas:**

Download this podcast transcript [PDF].

I read quite a few books every year. Apart from these three, I also enjoy and recommend these books.

What about you? What is your favorite book for analysts? **Please share your thoughts in comments**.

**Participate in our Analyst Book Giveaway & win:**

- You could win a copy of any of these 3 books.
- Last date: 15-AUG-2014 – Friday.

Listen to the podcast to know how to participate.