skip to Main Content

By Avi Singh [Twitter]

Information on this page is meant to support our class attendees (Live In-Person, Live Online, see Choosing your training option) and has two sections:

1) Course Prep YouTube Video Playlist: Covers some basic material so we can get to the fun stuff quicker in the class

2) User Interface Cheat Sheet for Excel 2010/2013/2016/Power BI Desktop: The user interface may vary but the concepts remain the same. This helps you navigate the UI across various versions.

Course Prep Video Playlist (more videos to be added soon)

Key Differences between Excel 2010/2013/2016/Power BI Desktop User Interface

(click to jump to that section)

Excel 2010 User Interface

Excel 2013 User Interface

Excel 2016 User Interface

Power BI Desktop User Interface

We would roughly cover the following UI element for each:
Launch Power Pivot
Create New Measure (Calculated Field)
Edit Measure (Calculated Field)
Slicers
Insert a Pivot Table connected to Power Pivot Model
Add Excel Table to Power Pivot
Power Pivot Menu Tabs (Existing Connections)

Excel 2010 User Interface

Launch Power Pivot: PowerPivot > PowerPivot Window

Create New Measure:
(Must be inside a Pivot Table)
PowerPivot > New Measure
PowerPivot Field List > Right Click > Add New Measure

Edit Measure: PowerPivot Field List > Right Click > Edit Formula…

Slicers: PowerPivot Field List has a dedicated are for slicers, where you can just drag and drop the fields. The slicers also auto-arrange.

Slicers auto-arrange in Vertical and Horizontal areas

Insert a Pivot Table connected to Power Pivot Model: (From Excel) PowerPivot > PivotTable

Add Excel Table to Power Pivot: PowerPivot > Create Linked Table

Power Pivot Menu Tabs: This varies slightly across versions, especially where “Existing Connections” is found.

Design > Existing Connections

—————————————————————————————-

Excel 2013 User Interface

Launch Power Pivot: PowerPivot > Manage

Create New Measure (Calculated Field): PowerPivot > Calculated Fields > New Calculated Field

Edit Measure (Calculated Field):
PowerPivot > Calculated Fields > Manage Calculated Fields
Select Measure > Click Edit…

Slicers:
Option1 Insert Single Slicer) Right Click in Field List > Add as Slicer

Option2 Insert Multiple Slicers)
PivotTable Tools > Analyze > Insert Slicer
Select Fields > Click OK

Insert a Pivot Table connected to Power Pivot Model:
1. (From Excel) PowerPivot > Manage (to launch Power Pivot)
2. (Within PowerPivot Window) Home tab > PivotTable

Add Excel Table to Power Pivot: PowerPivot > Add to Data Model

Power Pivot Menu Tabs: This varies slightly across versions, especially where “Existing Connections” is found.Home Tab:

Home > Existing Connections

—————————————————————————————-

Excel 2016 User Interface

Launch Power Pivot: PowerPivot > Manage
or Data > Manage Data Model

Create New Measure: PowerPivot > Measures > New Measure…
or Pivot Table Field List > Right Click a Table > Add Measure…

Edit Measure:
Pivot Table Field List > Right Click a Measure > Edit Measure…
or Power Pivot > Measures > Manage Measures…
Select Measure > Click Edit…

Slicers:
Option1 Insert Single Slicer) Right Click in Field List > Add as Slicer

Option2 Insert Multiple Slicer)
PivotTable Tools > Analyze > Insert Slicer
Select Fields > Click OK

Insert a Pivot Table connected to Power Pivot Model:
1. (From Excel) PowerPivot > Manage (to launch Power Pivot)
2. (Within PowerPivot Window) Home tab > PivotTable

or
1. (From Excel) Insert > PivotTable
2. Click OK (if Power Pivot Data Model exists, that is selected by default)

Add Excel Table to Power Pivot: Power Pivot > Add to Data Model

Power Pivot Menu Tabs: This varies slightly across versions, especially where “Existing Connections” is found.Home Tab:

Home > Existing Connections

—————————————————————————————-

Power BI Desktop User Interface

Launch Power Pivot: You would not really see the name “PowerPivot” anywhere, but the PowerPivot data model absolutely exists. You can see it in “Data” view or “Relationship” view.

Create New Measure: Many ways to create a new measure
a) Home Tab > New Measure
b) Modeling Tab (when active) > New Measure
c) Right Click any Table in Field List > New Measure

This would land you in the “Formula Bar” where you can write out the measure.

Edit Measure: Click on Measure in Field List > Edit in Formula Bar

Slicers:
1. Click on “Report” View
2. Click to add any field to canvas
3. Select “Slicer” as Visualization

Insert a Pivot Table connected to Power Pivot Model: Well, can’t create an Excel pivot table, but can create a simple table element
1. Click on “Report” View
2. Click to fields to canvas
3. Select “Table” as Visualization (this should be the default when adding new elements)

Add Excel Table to Power Pivot: You just bring in any data from Excel the same way…
Get Data > Excel

Edit Queries: There is no “Existing Connections” concept. You just click “Edit Queries” to edit your existing data queries (which is really Power Query).

—————————————————————————————-

Power On!
-Avi Singh

Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI.

This Post Has 2 Comments
  1. Hello Avi,

    Thanks for making these course introductory images and video.

    New students to live and online training courses will definitely have an advantage to visit and learn from this page.

Leave a Comment or Question