All articles in 'Pivot Tables & Charts' Category

Apply Conditional Formatting using Slicers

Apply Conditional Formatting using Slicers

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.

Continue »

Advanced Interactive Charts using Excel [Master Class]

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 […]

Continue »

CP054: Top 10 Pivot Table Tricks for YOU

CP054: Top 10 Pivot Table Tricks for YOU

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
Continue »

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

Published on Mar 30, 2016 in Pivot Tables & Charts, Power Pivot
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.

Continue »

Unpivot and then pivot for clarity (case study)

Published on Mar 8, 2016 in Pivot Tables & Charts, Power Query
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.

Continue »

Use slicers to create a cool selection mechanism [quick tip]

Published on Feb 8, 2016 in Learn Excel, Pivot Tables & Charts

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

using-slicers-as-selection-mechanism-demo

Looks slick, eh? Read on.

Continue »

CP047: Best Excel tools for Entrepreneurs

CP047: Best Excel tools for Entrepreneurs

In the 47th session of Chandoo.org podcast, let’s see how Excel can make you an awesome entrepreneur.

What is in this session?

In this podcast,

  • Why Excel for entrepreneurs
  • Key areas of a business owner’s work
    • Projects & to dos
    • Finances
    • Customers & marketing
    • Planning & strategy
    • Processes & workflows
  • 5 features of Excel that help
  • Conclusions
Continue »

Unpivot data quickly with Power Query [tutorial]

Published on Sep 29, 2015 in Pivot Tables & Charts, Power Query
Unpivot data quickly with Power Query [tutorial]

Power Query (Get & Transform data in Excel 2016) is a must have tool, if you wrangle with data every day. Here is a quick introduction, in case you are new.

Let’s learn how to use Power Query to unpivot data.

Essentially, we are trying to go from left to right in the above picture.

Doing something like this thru either formulas or VBA can be very complex. But Power Query can get you unpivoted data in just a few clicks. Sounds interesting? Read on.

Continue »

Use GETPIVOTDATA to integrate pivot tables with dashboards

Published on Aug 26, 2015 in Pivot Tables & Charts
Use GETPIVOTDATA to integrate pivot tables with dashboards

Pivot tables are very powerful analysis tools. They can summarize vast amounts of data with just few clicks. But they are lousy when it comes to output. Imagine the horror of putting a pivot table right inside your beautiful dashboard. One refresh could ruin the layout and create half-an-hour extra work for you.

How to combine the power of pivot tables with elegance of your dashboards?

The answer is: GETPIVOTDATA()

Continue »

How to create dynamic sparklines for latest 30 days [video]

Published on Aug 2, 2015 in Charts and Graphs, Pivot Tables & Charts
How to create dynamic sparklines for latest 30 days [video]

Sparklines are fun and very insightful. They are easy to create, easy to maintain and fit into any dashboard.

But there is one tiny problem with them. Usually we have a lot of data, but we don’t to visualize all of it. We just want to visualize latest 30 days trend or last 12 months trend or QTD or something similar. What then?

In this video, learn a powerful and very simple way to create dynamic sparklines using Excel.

Continue »

Introduction to Slicers – What are they, how to use them, tips, advanced techniques & interactive reports using Excel Slicers

Published on Jun 24, 2015 in Learn Excel, Pivot Tables & Charts
Introduction to Slicers – What are they, how to use them, tips, advanced techniques & interactive reports using Excel Slicers

Slicers are one of my favorite feature in Excel. And here is a quick demo to show why they are my favorite.

Slicers – what are they?

Slicers are visual filters. Using a slicer, you can filter your data (or pivot table, pivot chart) by clicking on the type of data you want.

For example, let’s say you are looking at sales by customer profession in a pivot report. And you want to see how the sales are for a particular region. There are 2 options for you do drill down to an individual region level.

  1. Add region as report filter and filter for the region you want.
  2. Add a slicer on region and click on the region you want.

With a report filter (or any other filter), you will have to click several times to pick one store. With slicers, it is a matter of simple click.

Read more to learn all about slicers

Continue »

Who is the most consistent seller? [BYOD]

Published on Feb 18, 2015 in Analytics, Excel Howtos, Pivot Tables & Charts
Who is the most consistent seller? [BYOD]

Who is the most consistent of all?

Imagine you are a category manager at a large e-commerce company. Your site offers various products, but you don’t really make these products. You list products made by other vendors on your site. Every day, these vendors would send you invoices for the amount of product they have sold. Above is a snapshot of such invoices.

Looking at this list, you have a few questions.

  1. Who is the best seller?
  2. Who is the most active seller?
  3. Who is the most consistent seller?
  4. Which seller has fewest invoices?

Let’s go ahead and answer these using Excel. Shall we?

Continue »

How to consolidate data that is different shapes [BYOD]

Published on Feb 16, 2015 in Excel Howtos, Pivot Tables & Charts, VBA Macros
How to consolidate data that is different shapes [BYOD]

Last week, I asked my email newsletter readers to submit “one data analysis problem you are struggling with”. We called it BYOD – Bring your own data. More than 100 people have emailed various interesting (and often very difficult) problems. This week (between 16th of February to 20th of February), let’s take a look at some of these problems and solve them.

Consolidating data in different shapes

We can use either VBA or Excel’s consolidation features to combine data that has same shape (ie same number & type of columns). Here is one way to do it.

But what if we need to consolidate data that is in different shapes?

Something like above.

In such cases, we can use 3 powerful tools.

  1. Multiple Consolidation Ranges – Pivot Tables
  2. VBA
  3. Power Query

So let’s examine how to use these approaches to consolidate data in different shapes.

Continue »

Introducing…Structured References for PivotTables

Published on Oct 18, 2014 in hacks, Pivot Tables & Charts, Posts by Jeff, Random, VBA Macros

Howdy folks. Jeff here, bringing you a Public Service Announcement: Thanks to the magic of VBA , Structured PivotTable References are coming to a PivotTable near you! Structured References for PivotTables? So what? Well, because PivotTables are the best bit of ‘old’ Excel, and Tables are the best thing about ‘new’ Excel, and it’s about […]

Continue »

CP018: Dont be a Pivot Table Virgin!

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

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.

In this podcast, you will learn,

  • 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
Continue »