Chandoo.org - Learn Excel, Power BI, Charting, Dashboards and VBA online

Latest Tips & Tutorials

Chandoo.org Podcast Sessions

CP054: Top 10 Pivot Table Tricks for YOU

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

Read This »

March 31, 2016

A slicer that doesn’t slice [Pivot Table Tricks]

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.

Read This »

March 30, 2016

There are seven pandas hidden in this workbook [Easter Eggs]

It is Easter time again. This year, we drove to my brother’s house in Hyderabad (700 km away from my home) to spend a weekend doing absolutely nothing (we will eat copious amount of food, share family memories, laugh and laze). It is Chandoo.org tradition to share few puzzles during Easter time, a la an Excel themed virtual Easter egg hunt. This year, I have prepared an amazing challenge for you.

Read This »

March 25, 2016

These icons are so pretty, can I get them in green? [conditional formatting trick]

One of our readers emailed this question recently,

I like the conditional formatting icons. I am trying to present some business data where going down is good. How do I get a green colored down arrow icon?

Essentially, Ms. CanIGetItInGreen wants this:

Unfortunately, Excel’s conditional formatting icons are not customizable. So we can’t get the green down arrows without some sneak. And sneak we shall.

Read This »

March 22, 2016

How to create animated charts in Power Point [VBA]

This is a guest post by Chirayu, a member of Chandoo.org forum.

Animating Charts in PowerPoint cannot be done without the help of 3rd party software’s that create a flash file of the chart & embed it into the presentation.

However there is a workaround for this. Save your chart as multiple images & insert them (overlapping on top of each other). Use VBA on Developer tab Controls such as Combo Box, Option Button, Check Box etc. to “Bring To Front”” the corresponding image. Thus giving the illusion of an Animated Chart in PowerPoint.

This guide will teach you how to animate the charts, using the three Developer tab Controls that were mentioned before. The code & functionality only works in Slide Show Mode. File must be saved as PowerPoint Macro-Enabled Presentation (*.pptm)

Read This »

March 17, 2016

Chandoo.org Podcast Sessions

CP053: Excel Data Validation for Dummies

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 53rd session of Chandoo.org podcast, let’s talk about data validation.

What is in this session?

In this podcast,

What is data validation
How Excel DV compares with database & software DV?
Types of data validation rules
List & custom rules explained
Input & error messages
Alternatives to data validation
Enhancing data validation
Removing data validation rules
Homework problem for you
Resources & show notes

Read This »

March 10, 2016

Unpivot and then pivot for clarity (case study)

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.

Read This »

March 8, 2016

“How Trump happened” in Excel [visualizations]

During last week, an alert reader of our blog, Jørgen emailed me a link to “How Trump happened“. It is an interactive visualization by Wall Street Journal. Jørgen asked me if we could replicate the visualization in Excel. My response: “Making a new chart in Excel? Hell yeah!”

Read on for awesome visualizations and full explanation.

Read This »

March 2, 2016