Building PivotTable Reports Using Excel’s Data Model in Power Pivot with DAX formulas

Data Modeling Steps in Excel Power Pivot

1.Import data into Power Pivot Data Model

2.Create relationships between the Dimension Tables and Fact Tables

3.Create DAX formulas (Data Analysis Expressions)

  • DAX Measures are used in the Values area of a PivotTable
  • Calculated Columns are used in the criteria for Rows, Columns, and Slicers

4.Hide Tables and Fields not to be used in the PivotTables reports

5.Create PivotTables & PivotCharts

6.Refresh when source data changes

7.Update the Data Model if necessary

 

 

 

 

 

Posted in Excel | Leave a comment

How to Use the IF Function and the VLOOKUP Function Together In Excel

The Vlookup function is an awesome function. It looks up information in a table where you have defined the lookup value in the left-most column of the table. In this video I share how to use it with an IF function after determining whether an individual has made their sales goal to determine their commission check.

Practice with my training file here. Also you might be interested in using the Match and Index functions as an alternative to the Vlookup if you have a large list of table columns.

Download my Excel Keyboard Shortcuts guide here.

Posted in Excel, Videos | Tagged | Leave a comment

How to Use the People Graph in Excel

The People Graph is an Excel 2013/2016 Add-In which makes graphic style charts. Find this command in the Add-Ins group of the Insert tab. Below is a video demonstration.

people graph excel

 

Posted in Excel, Videos | Tagged | Leave a comment

Automatically Update the Date and Time Stamp for Access Modified Records

Posted in Access, Videos | Tagged | Leave a comment

Link an Excel Chart to a PowerPoint Presentation

In this post I will share with you how to link an Excel chart to a PowerPoint presentation. This is helpful when you expect changes to be made to the Excel chart and you want to have your audience view those changes from in one of your PowerPoint slides.

  1. Right click the edge of the Excel chart and select copy.excel chart copy

2. Navigate to PowerPoint slide and select the Paste Special menu from the clipboard group.

paste special powerpoint

 

3. Select Paste Link as Microsoft Excel Chart Object

paste special link as picture chart object

4. Click File >Info and then click “Edit Links to Files” which is in the bottom right corner of PowerPoint 2010, 2013, and 2016 versions. Check box for “Automatic Update” in the Links box.

automatic update powerpoint slide deck

Note: It is important that the Excel file is saved to a location in which the PowerPoint can read the link otherwise the update cannot work. Typically you would save the Excel file with the chart to a network drive that is accessible to the file from which the PowerPoint is saved.

 

Click here to download my PowerPoint shortcuts.

 

Posted in Excel, PowerPoint | Tagged | 2 Comments

Understanding Visio’s Glue to Shape vs Glue to Connection Point

In this post, I am going to explain how to connect Visio shapes. You can connect to the shape or to its connection point.

 

Visio glue to point or shape

Posted in Videos, Visio | Tagged | Leave a comment

Format Red Cells if the Date is Older than One Year in Excel [Video]

In this video, I will demo how to format rows with red formatting if the date has been more than a year old. I will use the DATEDIF function to work within the conditional formatting rules.

 

 

 

Posted in Excel, Videos | Leave a comment

Create a PivotTable in Excel [Video]

In this video, I will demo how to create an Excel PivotTable. This PivotTable will have Slicers and Timeline filters as well as a PivotTable chart.

 

Posted in Excel, Videos | Leave a comment

A Special Trick to Select All Cells within the Find Box [Video]

This video will share with you how to search for cells within Excel’s find command and then select all the contents simultaneously.

In the video, I show you how to delete all rows that contain the same value. I use Excel’s find (control + F), type what I want to select. Then I click “find all”). The secret is to do control + A to select all.

Posted in Excel, Videos | Leave a comment

Using the DATEDIF Function in Excel for Conditional Formatting Expired Training Dates [Video]

Posted in Excel, Videos | Tagged | 4 Comments