MrExcel Excel Videos

How to discover which Excel calculation function to use. This episode shows how to use the PMT function to calculate a loan payment and how the Function Arguments helps with tricky arguments like dividing the interest rate by 12. Episode recap: There are over 400 calculation functions in Excel. If you don't know what function to use, click the fx button. This button is on the Formulas tab and also to the left of the formula bar. Type what you are trying to do and Search. The results will show you the functions related to your search term. When you click OK, you are taken to the Function Arguments dialog. This dialog provides help for each argument. Bold arguments are required. Once the bold arguments are completed, you will see the...

How to highlight all of the formula cells in a spreadsheet Select all cells using the icon northwest of A1 Home, Find & Select, Formulas or, Find & Select, Go To Special, Formulas Once you have the formulas selected, use Cell Styles or a Fill Color Look for things that should be formulas that are not! Once you have the formulas selected, you can use Trace Precedents Or, if you turn of Edit Directly in Cells, Double-Click Once you have multiple cells selected, press Enter to move to the next cell

F2 puts a formula in edit mode, Esc will exit edit mode Faster way to see all formulas: Ctrl+` Press again to toggle back

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.