VLOOKUP – from Basic to Advanced.

• Learn all aspects of using VLOOKUP, INDEX/MATCH, and LOOKUP.
• Learn how to make your lookups operate faster
• How to troubleshoot VLOOKUP.
• Find the last match.
• Find the second match.
• Total all lookups in a single formula.

Introduction to PowerPivot.

PowerPivot is revolutionizing the way that we look at data inside Microsoft Excel. Allowing us to link multiple tables together without a single VLOOKUP statement, it enables us to pull data together from different tables and databases where we never could before. But linking data from multiple sources, while powerful, only scratches the surface of the impact that it is making in the business intelligence landscape.
In this class, Ken will walk through creating a basic PowerPivot model, demonstrating the incredible capabilities of the product. Topics to be demonstrated include:

• Connecting to a database with PowerPivot
• Linking tables
• Creating a PivotTable which uses fields from multiple data sources
• Writing custom calculated fields which return different outputs in the same column of the PivotTable
• Aggregating critical business info into a document that your system doesn’t have

If you haven’t seen PowerPivot before, YOU NEED TO ATTEND THIS CLASS! Plain and simple, PowerPivot is the future of Business Intelligence in Excel.

How to make Excel Calculate Your Workbooks Faster

Topics:

• Excel’s smart calculation engine
• The impact of Volatility and Multi-threading
• User-defined functions and Array formulas
• Lookups and SUMPRODUCT
• Finding Calculation bottlenecks
• Golden Rules for Faster Calculation

Date Intelligent Measures – Building a PowerPivot Time Machine.

To anyone who works in retail or finance, one of the most important things we want to be able to do is step back in time to look at results. Our dashboards are full of stats like Month to Date, Year to Date and so on. But what happens when we have huge amounts of data at our fingertips and we get asked to compare Month to Date versus the same period last year? Do you have to create another Pivot Table and/or some complicated formulas to pull it off? Depending on the approach this can be dangerous and maintenance can become a nightmare.
With PowerPivot this game changes significantly and with a single date pattern you can step through and answer 95% of your date period needs. From Month to Date, to Month to Date x Months ago, to same weekday last year, it can easily be done.
In this session Ken will discuss:

• The importance of a Calendar table in your PowerPivot models
• The “Golden Date Pattern” for calendar years
• Forcing measures to return data based on the earliest or latest date selected in a slicer or timeline
• Understanding and avoiding the “‘DATEADD’ only works with contiguous date selections” error
• Returning time intelligence over an inactive relationship

Please Note: This course will be covering DAX measures in PowerPivot. Attendees should either have some working experience with PowerPivot, or attend the Introduction to PowerPivot session earlier in the day.

Array formulas

• Building Dynamic Ranges
• Extracting data, based on criteria
• Counting all Fridays between two dates
• Unique counting formulas
• Extracting unique lists
• Sorting with a formula

PowerView and PowerQuery

Power Query is an amazing new tool for importing your data in Excel. Many users mainly use csv files to import data in Excel and unfortunately, this technique involves a lot of work (concatenate, delete, group data). With Power Query, you don’t need to do all of this because the tool saves all your data manipulation before importing the data into your Spreadsheet.
During the session we will see:

PowerQuery:

• Basic Power Query manipulation
• Import from different sources
• Import from many sources
• Formulas

Powerview:

• Presentation (Table, Chart, Map)
• Dynamic Filter
• Drill up / Drill down