skip to Main Content

Course Prep and User Interface for Excel 2010/2013/2016/Power BI Desktop

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)

Read the Rest

Thanks for attending the *beeping* webinar: Q&A From Webinar

By Avi Singh

Folks, thanks for attending our webinar. We did have a full house with 200 attendees. Here is how things started…

“Welcome to our webinar: Excel to power pivot: *BEEP* the gap. A lot of users *BEEP* Excel but don’t *BEEP* Power Pivot. We’re here to *BEEP* you up about the possibilities that Power *BEEP* offers…”

Okay, that wasn’t much fun. For some truly funny unnecessary censorship watch “This Week in Unnecessary Censorship” (Caution:  If you’re at workplace, you might want to use headphones).

I did sort out the matter eventually with the help of Citrix Support. Apparently there were issues with the last automatic update of GoToMeeting software. Uninstall/Reinstall restored my ability to turn off those annoying beeps.

Q&A from the Webinar

We published the Q&A from the last webinar and I’ll try to keep that up. So here’s the Q&A from the session on Mar 17th.

TIP: If you have other questions, use the search box. We have 750+ blog posts and most likely you will find something helpful
image

List of Questions (click to jump to answer):

Q: If the tables are not related, can you create a Pivot Table for each unrelated table separately?
Q: Why wouldn’t you use Microsoft Access instead?
Q: How can we automate the refresh of a Power Pivot report without using server components?
Q: How many rows/columns can Power Pivot handle? My data set is wide and flat
Q: How easy is it to share Power Pivot data with non-Power Pivot users?
Q: What are good practices for preparing the tables? The tables can have blanks, right?
Q: In my calculations, Can I exclude some of the rows where the information is not applicable and calculate for the remaining rows?
Q: Can we combine/leverage VBA with Power Pivot?
Q: How do you filter out certain fields to not bring in to PP?
Q: Why is PP not available on Excel 2013 Professional?
Q: Many questions around Date table and creating Date tables
Q: Other questions discussed
Q: Will webinar be recorded?   

Q: If the tables are not related, can you crate a Pivot Table for each unrelated table separately?
D. van Eyl

It is very common to have multiple data tables distinct from each other in the same model. Example Sales data, Budget data, Inventory data, Website traffic data etc. It is valuable to keep these data sets in one model so you can analyze them together.

Your data model should not look like mushrooms where each data set has its own lookup tables


Multiple Data Tables should NOT look like mushrooms

It should look more like a web with a set of data tables and a set of lookup tables. With the data tables connected to the relevant lookup tables.

Read the Rest