Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
Inserting Excel PivotTable Calculated Items is a great way to analyse your data and automatically incorporate that analysis in your PivotTables. Another way to think of them is the ability to add a new item to your report based on a formula which uses other items in the column. You can then include this new …
There’s no built-in way to create Excel Slicers for fiscal years, however the solution is easily achieved by adding an extra column to your source data to classify each date into its relevant fiscal year. Download the Workbook Enter your email address below to download the sample workbook. Get Workbook By submitting your email address …
Excel Slicers for rolling periods, for example; the last 12 months, requires a column in your source data that looks up the date and compares it to today’s date to see if it falls into the current period. Download the Workbook Enter your email address below to download the sample workbook. Get Workbook By submitting …
In this tutorial we’re going to look at how we can create a single Excel Slicer for Year and Month, as opposed to the default of having the Year and Month in separate Slicers. Let’s rewind a tad and look at how we got here in the first place. Below is an extract of my …
After having thousands of people recently attend our free PivotTable webinar we found there were 3 questions that were asked again and again, which are: How do I format my data in an Excel Table so I can refresh my PivotTable and it automatically picks up any new data added? How do I change the …
Excel PivotTables are a treasure trove of features. One of my favourites is the ability to expand/collapse and drill down into the data. Let’s look at an example; the first column of the PivotTable below lists Categories, which group and summarise Products. We can click on the + symbols to the left of the Category …
Excel PivotTable Calculated Fields are easy to add but there are a couple of ‘gotchas’ you should be aware of. Calculated Field Definition Microsoft Definition: A Calculated Field is a user-defined field in a PivotTable that can perform calculations using the contents of other fields in the PivotTable. Calculated field formulas can refer to one …
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 …
Not cake, sorry. I’m talking about Excel Slicers and while they are 'sweeeet', as my 8 year old says, and a piece of cake to use, they aren't quite as tasty. Slicers were introduced in Excel 2010 and they’re an interactive control that enables you to filter data in PivotTables, PivotCharts, Excel Tables and CUBE …
