Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
In this tutorial we’re going to look at the options we have available to auto refresh PivotTables in Excel without using VBA. The process differs depending on whether you use Power Query to get the data or not. I’ll also show you the VBA method required for regular PivotTables. Beware because not all PivotTables based …
Show Items with no Data in PivotTables is a handy setting that allows you to show items in the PivotTable even if there isn’t any data for them. For example, the PivotTable and chart below includes the East and West regions even though the Sum of Count is blank: Watch the Video Download Workbook Enter …
There’s no built-in way we can force Excel Slicers to single select, but we can use some crafty tricks to prompt users to only select one item, and even hide the data if they try to select multiple items! Watch the Video Download Workbook Enter your email address below to download the sample workbook. Get …
Creating an Excel PivotTable Profit and Loss statement is surprisingly easy. And because it’s a PivotTable you can team it with Slicers to make it interactive. While you’re at it you might as well add some conditional formatting to make reading, what is usually a drab report, quick and easy. Watch the Video Download Workbook …
The Excel PivotTable field list automatically appears in the task pane on the right-hand side of the Excel window when any cell or cells in the PivotTable are selected: Tip: If you can’t see the field list > right-click any cell in the PivotTable > Show Field List: Or select any cell in the PivotTable …
There are a couple of ways you can hide blanks in Excel PivotTables. To be clear, the ‘blanks’ I’m referring to are those shown below where the text, (blank), is inserted as a placeholder for empty cells in your source data: It’s important to point out that these (blank) placeholders only occur in row or …
Excel Slicers are great, but they’re a bit on the chunky side and that can be a pain when you’re building reports like Dashboards where space is limited. Unfortunately, the Excel Slicer Formatting available on the Slicer contextual tool tab is limited because you can’t adjust the font size (see below): I’m going to show …
With PivotTable Quick Explore we can filter and drill down in PivotTables and Pivot Charts without the field list exposed. This means our users can customise their reports without needing to know how to build a PivotTable: Quick Explore is also available in Pivot Charts: Note: Applies to Excel 2013 onward for Power Pivot/Data Model …
Inserting PivotTables in Excel Online is now possible. It’s still in its infancy with many features you may be used to in the Desktop version of Excel not yet available, but it’s a start. Inserting Excel Online PivotTables As with any PivotTable, you need to begin with some Tabular Data. I recommend storing your data …
Obviously, we don’t want our PivotTables littered with #DIV/0! and #NULL! errors if we’re presenting them in a report (like the one below), that would just create unnecessary questions and we’re busy enough. Thankfully Excel PivotTable error handling is easy to control via the PivotTable Options; right-click the PivotTable > PivotTable Options > On the …
