MrExcel Excel Videos

You need to convert formulas to values Mouse: Right-drag the border of the range to the right, back to the left, let go Choose Copy Here as Values Only from the Alternate Drag-and-Drop menu Keyboard: If you have the right-click key, Ctrl+C, Right-Click Key, V If you don't have Program key, you can use Shift+F10 instead Several other methods shown in the outtake

The mighty F4 key is popular when building formula references But it is also great for repeating the last action If you have to do a similar command 10 times, do it the first time select the next cell and press F4 Alt+EDC Enter deletes an entire column Alt+EDR Enter deletes an entire row Right-drag the fill handle to get Fill without Formatting F4 is likely more famous for creating absolute references By default, a formula reference changes as you copy the formula If you need one portion of a formula reference to stay fixed, press F4 after pointing at it For rectangular ranges, F4 works after mouse or arrow keys, but not when typing After the fact, select whole range or just the colon It seems difficult to use F4 to create an expanding...

With the Power Pivot tab, you can build relationships faster using the diagram view You can also insert Calculated Fields or Measures to do new calculations They use the DAX formula language They are very powerful Think of them as Helper Cells - they provide intermediate formulas

You have a small top-down budget data set You want to compare to a bottoms-up actuals data set The actuals might come from an invoice register The data model will let you compare these differing-size data sets Make both data sets into a Ctrl+T table For each text field that you want to report by, create a joiner table Copy the values and remove duplicates For dates, you can include dates from both tables and convert to month end Make the joiners be Ctrl+T tables Optional but helpful to name all five tables Create a pivot table from Budget and choose the Data Model Build a pivot table using Budget and Actual from the original tables All other fields must come from the joiner tables Add slicers by Product Create three relationships from...

Creating a distinct count or unique count in a pivot table used to be hard. This episde shows the new easy way and the old way. Recap: Introduced the Data Model in Podcast 2014 for Joining Tables Another Benefit is the ability to do Distinct Count Regular pivot table can not count customers per sector Add the data to the Data Model and you have Distinct Count available Before Excel 2013, you would have to add 1 / COUNTIF to the original data

You need to create a pivot table from two tables. Rather than doing a VLOOKUP, you can use the Data Model. Episode Recap: Starting in Excel 2013, the Pivot Table dialog offers the Data Model This is the code word for Power Pivot Engine To use the data model, make a Ctrl+T table from each table in the workbook Build a pivot table from the first table In the Pivot Table Field List, change from Active to All Choose a field from the lookup table Either create the relationship or Auto-Detect Auto-Detect was not there in 2013 Thanks to Colin Michael and Alejandro Quiceno for suggesting Power Pivot in general.

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 ;;;@