Program for The Amsterdam Excel Summit, June 7, 2018
Het programma van The Amsterdam Excel Summit, 7 juni 2018

(The program for Masterclass 1 is located here)
(The program for Masterclass 2 is located here)

All presentations during this event will be in English.
Merk op dat de voertaal van de dag Engels is.

Even though this day consists mainly of presentations rather than trainings, if you would like to follow along with the presenters make sure you bring your laptop. Our presenters will typically be using the most recent version of Office 2016. Using an older version? Make sure you have the PowerQuery and PowerPivot add-ins installed.
Hoewel deze dag voornamelijk bestaat uit presentaties en geen training is, als u mee wilt doen met onze presentatoren, zorg dan dat u een laptop meebrengt. Onze presentatoren zullen vooral de meest recente Office 2016 versie gebruiken. Gebruikt u een oudere versie van Excel, installeer dan in ieder geval de PowerQuery en PowerPivot invoegtoepassingen.

Registration
Registratie

Register here

Location
Locatie

Tentative:

Zilveren toren Amsterdam
Stationsplein 51
1012 AB Amsterdam

Start

End

Track 1

Track 2

Registration

Start and introduction of speakers

Tony De Jonker & Jan Karel Pieterse

Key note

Tamar Tzruya Bar Zakai – Microsoft

Adding Pizazz & Power to your Charts

Using charts right out of the box seldom produce the desired high-quality result for your Excel dashboard or report. For instance, charts often need extra formatting, especially when dealing with legacy workbooks. Do you know there’s a world of further improvements that you can easily apply right away? This session will uncover many top-notch tricks that will certainly wow your boss. You will learn how to:

• Display various measures with one interactive chart template
• Let your user switch between different chart types
• Automatically sort a bar chart ascending, descending or alphabetically
• Add an option to show/hide data labels in any chart
• Substitute a legend with contextual labels for more communicational impact
• Include high-quality helper information in your dashboards
• Zoom in or out on a chart
• Add Chart animation
• David Hoppe en Tony de Jonker

Unleash the Power of Excel with Power Query

“People keep telling me that Power Query is the thing to learn instead of VBA, is that true?”

Someone asked me that recently, and I’d never suggest that someone deliberately not learn VBA. However, he was really expressing skepticism about the buzz around Power Query.

Let’s dig into that! How do Power Query and VBA compare? Why have a lot of VBA coders spend more time in Power Query and much less time in the Visual Basic Editor?

This session will explore some real-life projects that were all VBA but could have been accomplished purely within the Power Query interface.

• Looping
• Parsing Strings
• Segmenting Data
• Magical Transformations
• Where VBA is still useful

Oz du Soleil

What if I Care?

You have sweated blood and tears getting the best estimates for your model that you possibly can. You know it’s certain your forecast will be wrong though – because things change. This session looks at simple ways to undertake “what if?” analysis in your Excel spreadsheets, without the need for “black box” calculations, Excel features (e.g. Solver) and VBA.

• Four types of analysis (with examples): breakeven, scenario, sensitivity and simulation
• Why Solver and VBA are not the answer
• Key functions to consider: NORM.INV, OFFSET, RAND and RANK
• Key features to consider: simplicity, Tables and charts

Liam Bastick

Mastering Your Data in Power Pivot

While Excel is a calculation app, Power Pivot is a filtering app. That means that you need to focus on filtering context for instance by using DAX. One of the problems of using DAX functions for filtering is that you can use the same DAX function on row level and on column level. The results can be different! Mastering context means that you are in control of your Data model.

•  The three levels of filtering
• Some easy examples of how context works
• Row level context versus column level context
• Virtual tables and how they work

Henk Vlootman

The best Excel secrets revealed

Would you like to save time when working with Excel and learn about the hidden features? Join us on this amazing session jam packed with tips and tricks

• Summarizing detailed Actuals and condensed Budget data using the Data Model
• Create dynamic dependent drop down lists
• Protecting data in Excel Tables
• Connecting slicers with a macro
• Extended Scenario Analysis with a bit of VBA
• Comparing monthly trial balances
• And many more…

Tony de Jonker

The Time of Your Life

99,9% of Excel Data Models rely on a good Calendar. This session will cover a few techniques on how to create a dynamic calendar in your Power Pivot Data Model and explain why a good Time Dimension (Calendar) is probably the most important thing in any Excel or Power BI Data Model. This session will also feature some time intelligence calculations in Power Pivot that are only available if you have a good Calendar.

• Creating a Calendar in Excel
• Creating a Calendar with Power Query
• Creating a Calendar with Power Pivot
• The dos and don’ts of Calendar building

Gašper Kamenšek

Advanced Data Visualization in Excel – Power BI and Apps

Showing your results in charts and/or dashboards is an effective way to analyze and communicate the results of your spreadsheet model. Because charting has evolved a great deal in Excel, this session lays out the different options available and considers the pros and the cons of each. At the end of the session, you can make more informed decisions in your next data visualization project. This session covers the following topics:

• Learn about the differences between the new chart types from Excel’s native charts
• Overview of the various visualizations in the Excel App-store
• Consider the case of Pivot charts vs. Power View
• Discover what Power BI’s visualizations can do for your Excel model
• Tips & tricks to spice up your native Excel charts

David Hoppe

Building a Budget Comparison Model in Power Pivot

In this session we will look at solving the un-vlookupable issue of comparing actuals and budgets by using Power Pivot. During the session you’ll be exposed to proper modelling techniques, and how to solve common many-many problems that occur when building Power Pivot.

Things you’ll learn:

• The difference between Facts and Dimensions
• How to link multiple tables together in Power Pivot
• How to use Power Query to do light reshaping of data
• How to solve “Many to Many” joins using “Composite Keys” and “Bridge Tables”

Ken Puls

Tips & Tricks

By “Excel out of the box” Bob Umlas

Meet the Experts

And we’ll wrap up during this session too