MrExcel Excel Videos

How to prevent or embrace GetPivotData. Most people hate it and want to prevent it. But there is an actual good use for it. Episode Recap: GetPivotData happens when a formula points inside of a pivot table While the initial formula is correct, you can not copy the formula Most people hate getpivotdata and want to prevent it Method 1: Build a formula without the mouse or arrow keys Method 2: Turn off GetPivotData permanently using the dropdown next to options But there is a use for GetPivotData You manager wants a report with Actuals for past months and budget for future The normal workflow would have you create a pivot table, convert to values, delete columns Removing Subtotals to prevent January Actual+Plan using Field Settings...

Publish your Excel workbook as a web-app dashboard. Episode recap: Before going to the web, make your dashboard look less like Excel Select all cells and apply a light fill color to get rid of the gridlines. On the View tab, uncheck Formula Bar, Headings, and Gridlines. At the right edge of the ribbon, use the ^ to collapse the Ribbon. Use the arrow keys to move the active cell so it is hidden behind a chart or slicer. Hide all sheets except for the dashboard sheet. I use Custom Views so I can later Unhide All Worksheets In Excel Options, Advanced, you can hide the scrollbars and sheet tabs. Create a range name around your dashboard File, Info, Browser View Options (used to be File, Export) Save to the One Drive Get a sharing link

The awesome advantage of slicers is that you can control multiple pivot tables from one set of slicers. Recap: One set of slicers can run multiple pivot tables! This is great for dashboards. You can go to each pivot table and choose Slicer Connections Or you can get to each slicer and chance the connections To hide zeroes and other numbers from cells, use Custom Number Format of ;;;@

Sparklines are a great way to fit a trend into a small space. Episode recap: Sparklines are word-sized charts See the book Beautiful Evidence by Edward Tufte Use Sparklines to fit data in a small space Build a second pivot table from the original data Put the data for the sparklines in the pivot table Remove the grand totals from the pivot table Select the numbers for the sparklines Choose Insert, Sparklines, Column Select where you want the sparklines to go Make the column wider to make the sparklines larger You can also make the row height taller You can even (gasp) merge cells to make the sparklines wider Change the color of the high and low point If you want the sparklines to be scaled together, change the axis minimum and maximum

Slicers are visual filters for a pivot table. Pivot Filters have been around They have to live near the pivot table They are awful when you want to select multiple items Visual filters were introduced in Excel 2010 as Slicers When you insert slicers, they are tiled in the middle of the screen You can change number of columns Arrange the size Change the colors Great for multi-select

Building a pivot chart to summarize a data set I start with a pivot table. If you want years to go across the chart, make them go down the Rows area Change Field Settings to Show Values as a Percentage of Row Format the pivot table as % with no decimal places Create the pivot chart Immediately Cut, Ctrl+Home, Paste Formatting the chart: Adding a Title at the top Triple click the title to edit in place Select second series. Ctrl+1. Narrow the Gap Width. Add Data Labels to only the second series

Icon Sets debuted in Excel 2007 In Excel 2010, they added a set with Up, Flat, Down But an icon set can only look at one cell! How can you use it to compare two cells? Copy two helper columns off to the side Use a calculation to show the change Type that formula without using the mouse or arrow keys to prevent =GETPIVOTDATA Use the SIGN function to convert that to +1, 0, -1 Add the Three Triangles icon set to the helper cells Why do they call it Three Triangles, when it is really 2 triangles and a dash? Manage Rules, Edit the Rule. Show Icon Only. Manage Rules, Edit the Rule. Change from percent to numbers Use greater than 0 for Green up arrow Use greater than or =0 for yellow dash all of the negative numbers will get the red down...

Rather than use VLOOKUP to compare lists, you can solve it with a pivot table. Episode Recap: You have three lists to compare. Time for lots of VLOOKUP?! There is a far easier way Add a "Source" column to the first list and say that list came from List 1 Copy List 2 beneath List 1 Copy List 3 to the bottom of both lists If you have more lists, keep going Create a pivot table from the list Move the Source to the columns area Remove the grand total You now have a superset of items appearing in any list and their answer on each list After the credits, a super-fast-motion view of MATCH, VLOOKUP, IFERROR, MATCH, VLOOKUP, IFERROR old way of solving the problem

A great trick from Szilivia Juhasz. If you want to create a pivot table for each sales rep or each product line or each whatever, you can use the Show Pages feature. Episode Recap: There is an easy way to replicate a pivot table for each rep, product, whatever Create the pivot table To replace the Row Labels heading with a real heading, use Show in Tabular Form To replace Sum of Revenue, type a space and then Revenue Provided you select all value cells and the grand total, you can change the number format without going to Field Settings Greenbar effect using Design, Banded Rows, and then choosing from the Format gallery Add the "for each" field to the Filter area Go to the first Pivot table tab in the ribbon (Options in 2007 or 2010...