Summit Program

Banner
April 18th and 19th, 2017, 18 en 19 april 2017

Program for The Amsterdam Excel Summit, April 19, 2017
Het programma van The Amsterdam Excel Summit, 19 april 2017

(The program for day 1 is located here)

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

Registration
Registratie

Register soon as the number of available seats is limited!
Registreer snel, het aantal beschikbare plaatsen is beperkt!

Location
Locatie

Zilveren Toren Amsterdam
Stationsplein 51-53
1012 AB Amsterdam
The Netherlands
Map and Directions

Please note this program may be subject to change!

Start

End

BI track

Deep Into Excel track

08:30 08:50

Registration

08:50 09:05

Start and introduction of speakers

Tony De Jonker & Jan Karel Pieterse

09:05 09:50

Key note

Guy Hunkin (Microsoft Excel Senior Program Manager)

09:55 11:10 Side sessions 1

Automate your data preparation using Power Query

Are you tired of copying and pasting data into Excel worksheets?  Power Query is the ultimate tool that automates the process of getting data into Excel and will save you hours of repetitive and error-prone work.

Power Query makes it easy to extract data from many different data sources, filter that data, aggregate/clean it, and perform calculations on it and finally loading that data into your worksheet.

Frédéric will demonstrate the following topics:

  • Importing data from the web
  • basic manipulation (filtering / removing columns)
  • Pivoting/unpivoting tables
  • Creating a time table
  • Creating a personal function based on the minimum date
  • Getting back on track in case you make a mistake when defining the transformation steps

Frédéric Le Guen

Make Your Life Easier with Excel Tables

Excel Tables (not “tables”) have been helping users since their introduction as “Lists” in Excel 2003. Four major versions later, Excel’s Tables have matured into a powerful and dynamic data structures. Their filtering and sorting capabilities as well as their dynamic nature make them an indispensable sidekick to Pivot Tables and Charts. We’ll look at just a few ways that Tables can make Excel more fun.

Jon will discuss:

  •  Keep your data organized
  • Autofill formulas and formats effortlessly
  • Enable quick sorting and filtering
  • Expand pivot table source data automatically
  • Create truly dynamic charts without defining Names or using VBA

Jon Peltier

11:10 11:30 Break and networking opportunities
11:30 12:45 Side sessions 2

Power Query versus Native Excel: the Battle

There’a a lot to love about Power Query, but some things are difficult and not intuitive:

  • The problem with hardcoded column headers
  • Shifting rows
  • Cell formatting
  • Some counting starts at 0, others start at 1
  • Duplicates that PQ doesn’t see as duplicates

Oz Du Soleil

A comparison of Excel Extensibility Technologies

VBA, VB6, VSTO,
XLL, Excel DNA, Javascript Office Addins, …

The objective for this session is to answer questions like:

  • What are the strong points and weak points of XXX?
  • I hear XXX technology is the way forward for Excel, lets do the next project using that
  • Lets convert this project to XXX
  • VBA is dying – what technology should I retrain in?
  • I want to use XXX with Excel, where do I start?
  • How long does it take to learn XXX?
  • I have an idea for an Excel-based product I want to sell …

Charles Williams

12:45 13:45 Lunch
13:45 15:00 Side sessions 3

Creating dynamic financial statements using Power Pivot

In this session we’ll deep-dive into some unknown DAX functions and Power pivot functionality. We will build a balance sheet and a Profit & Loss Statement. To accomplish this we use the DAX PATH functions and the new possibilities of VAR definitions within Excel 2016. Next we will build a control table to combine slicers with (predefined) measures. The prime DAX function we use in this case is the is DAX SWITCH. Basically what we’ll do is create different measures and use a slicer to present the selected measure in the desired dashboard. By combining the hierarchy with  the control table the output dashboard stays very compact.

Henk Vlootman

Bing maps & Excel

What’s the distance between two cities (addresses) if you go by car, walk or take public transportation? Where in Excel can we get that information? Short answer is nowhere.

But wait, we live in the time of internet and web API-s. And guess what. Bing Maps does allow you to create an API and do requests from Excel. In this session, we’re going to do that three different ways. The traditional Excel Way, VBA and if we really feel “Olympic”, we’re going to use Javascript.

Gašper Kamenšek

15:00 15:20 Break and networking opportunities
15:20 16:35 Side sessions 4

Moving beyond Excel with Power BI

If you haven’t come across Power BI yet, you’re missing a huge change in the business intelligence landscape. A free tool that lets you rapidly collect, clean and transform your data, it also allows you to build great insights quickly using the Excel skills you already know.  Even better, however, it also removes several sharing barriers that existed with Excel… have you even wanted to deploy an interactive report to your phone?  Well now you can!

Join Ken as he shows how you can take your Excel knowledge into the new and exciting world of Power BI.

Ken will cover the following topics:

  • Collect and clean data using Power BI Desktop
  • Quickly build interactive and engaging visuals
  • Publish your report to the web
  • Deploy your reports to your phone
  • Generate additional insights with ease

Ken Puls

Checks & Balances

People build models all the time.  People build errors all the time. People seldom build checks.  Correlation??  Would you like us to keep you posted about why checks will pay you back with interest? You can cash in with this session.

Experienced financial modeller and auditor Liam Bastick will highlight
some of the most common errors in modelling and how you can overcome
them with three types of checks and an over-arching system.  Examples
will include:

  • Why checks should be paid in dollars when referencing formulae
  • Copying errors even experts make
  • Common errors when calculating interest
  • Common errors when calculating growth
  • Common errors in valuations
  • Why VLOOKUP and HLOOKUP may kill your business
  • Why data validation might not work
  • Introducing checks

Liam Bastick

16:40 17:25

Office JavaScript

Microsoft Principal Designer Jakob Nielsen and Microsoft Program Manager Sudhi Ramamurthi will be presenting this session. More details will be disclosed soon.

17:25

Wrap up

Top Excel classes, training and events