fbpx
Search
Close this search box.

CP054: Top 10 Pivot Table Tricks for YOU

Share

Facebook
Twitter
LinkedIn

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

session-054

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

 

Listen to this session

Click here to download the MP3 file.

Resources for this podcast

Comprehensive guides on,

Pivot Table Tips & Tricks

What are your favorite Pivot Table tricks?

Now its your turn. Go ahead and share your favorite pivot table tricks in the comments box.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

5 Responses to “CP054: Top 10 Pivot Table Tricks for YOU”

  1. Excel Whizz says:

    Hi Chandoo, great podcast as always!

    I have a client here in the UK who is on Excel 2010 (without any power bi add-ins enabled and an IT department unwilling to allow rights to download them) and I'm trying to get 'one slicer to rule them all'.

    He has numerous pivot tables with the same report filter fields names but they are from different datasets and it doesn't seem to allow the slicer to connect to more than one dataset. Does anyone have any ideas for a fix or workaround?

    The data is perfectly clean by the way, the report filters are all named the same thing in each query etc, it's just that they come from 5 or 6 different sources.

    Cheers,

    Chris

    • Chandoo says:

      Hi Chris,

      Thanks for your love. You can use VBA to automate report filter sync. See here to get started.
      http://chandoo.org/wp/2011/04/27/update-report-filter-macro/

    • Mike says:

      You can also create ONE dataset from all of the datasets, with a new column to label the dataset.

      I mention this because I have gone down the VBA path for aligning slicers and it's neat but sometimes problematic. Eventually I gave up and just created VBA to aggregate the datasets into one table and then have all pivots off the same data. Then you only need one slicer for the pivots and no vba to align slicers.

      Cheers

  2. Excel Whizz says:

    Great, thanks for your help Chandoo we managed to get it working using VBA today!

  3. Steven says:

    I am a big fan of the tabular view (tip 2) - how can I set this up as my default view of pivot tables ?

Leave a Reply