Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
In its simplest form the Excel GETPIVOTDATA function enables you to extract values from a PivotTable report, but if you’re like me when you first tried to figure out how to use GETPIVOTDATA, you were less than pleased with the results. Understandably so, because in its default form it’s quite inflexible. However, the benefit in …
PivotTables are a treasure trove of features and one that has been brought out of the dungeons in more recent Excel versions is ‘Show Values As’. In Excel 2007 it was hidden 3 clicks away in the Value Field Settings dialog box (see below): Then in Excel 2010 it was given prime position in the …
Excel Slicers are a professional way to enable users to easily and intuitively interact with your reports, filtering data in PivotTables, Pivot Charts, Excel Tables and CUBE functions. They're available in Excel 2010 onwards for PivotTables, and for Excel Tables from Excel 2013. In this comprehensive tutorial I cover EVERYTHING you need to know about …
My second Excel love is PivotTables (VLOOKUP is still my number 1). PivotTables make short work of summarising data and if you change your mind like the wind you can easily satisfy those whims with little more than a drag and a drop, unlike formulas which often require a lot more cajoling. Once you have …
Conditional Formatting in PivotTables has its ups and downs. Unfortunately I find them mostly ‘downs’ but let’s not dwell on the negative as the few ‘ups’ might still make it worthwhile depending on your needs. The Upside of Conditional Formatting PivotTables When you apply Conditional Formatting to the Values area of your PivotTable the formatting …
Q: How do you get the PivotTable to show the missing dates in your data? A: Not as easily as it should be, but here are a couple of workarounds you can use. Option 1: If you don’t care how Excel formats your dates The limitation of this option, as you will see, is that …
This is a guest tutorial written by Bryon Smedley of Bristol, Tennessee. If you’ve ever used Pivot Tables in Excel, you no doubt have discovered the wonders of filtering. The ability to filter row or column items can be extremely helpful when you don’t wish to analyze all of the items in the driving data …
I regularly get asked: “What formula can I use to extract a list or subset of data from a master sheet into separate sheets for each division/sales person/product etc., and when I add to the master sheet I want it to automatically feed through to the other sheets?” Before we had the FILTER function, my …
In this last tutorial, in my 3 part series on PivotTable tips and tricks, I’m going to show you how to include the month on month change. In the example below we can see that beverage sales in Feb were $10,391 less than the previous month, and in March they were $4,622 less than February: …
Battling to consolidate monthly figures into quarters, or segment data into numeric bands? Perhaps you're aiming for a bespoke grouping in your PivotTables. PivotTable grouping lets you intuitively classify dates, times, and number intervals, while also allowing for tailored group creations to reveal the hidden narratives in your data. Table of Contents What is …
Excel PivotTables make light work of analysing data but there are a few tricks once you get past the basics. In this first tutorial, in my 3 part series on PivotTable tips and tricks, I’m going to show you how to add a percentage of total sales to your PivotTable like this one in column …
PivotTables are the Origamists of Excel. Folding and summarising data into almost any shape. I say 'almost' because until Excel 2013 they couldn’t calculate a unique (sometimes called distinct) count. For example, you might want to count the number of 'things' you had, whether it be products or customers, or any other unique combination of …
This Excel Factor tutorial was sent in by Bryon Smedley of Bristol, Tennessee. Excel PivotTables are one of the greatest tools in the spreadsheet user’s toolkit. However, there is one tiny bit of functionality that appears to be missing: the ability of pivot tables to automatically update when information in the source data changes. Most …
How to Create a Reverse PivotTable - this is genius. This tutorial was sent in by Bryon Smedley of Bristol, Tennessee. There comes a time when you are presented with data in a cross-tabular format but your analysis requires that the data be formatted into a traditional table (or normalized) structure. Your report is in …
An Excel Pivot Table is one of the most powerful productivity tools available, but some common data layout mistakes can make Pivot Tables difficult, frustrating and downright scary for some users. Thankfully these issues are easily fixed. I've put together a short video that'll get you started with Pivot Tables, and when your data is …
Excel Pivot Tables are one of the most powerful tools at our disposal, and once you understand how they work, they’re actually quite easy to insert and modify. In this example we’re going to work with a small amount of data (see image below) for illustration purposes, but Pivot Tables are in their element with …
![Excel PivotTable Month Comparison [Video]](https://www.myonlinetraininghub.com/wp-content/uploads/2013/04/pivot_tip_mth_change_thumb.png)
![Excel PivotTable Add Percentage of Total Column [Video]](https://www.myonlinetraininghub.com/wp-content/uploads/2013/04/pivot_tip_percent_total_thumb.png)
