Program for day 1 (May 26th, 2016)
Het programma van dag 1 (26 mei 2016)

(The program for the post-conference masterclass  is located here)

We have prepared an excellent and exciting program, spanning one day for the Summit and one post-conference masterclass.
We hebben een een uitstekend programma samengesteld, bestaande uit één dag Amsterdam Excel Summit en één post-conferentie dag masterclass.

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

Registration
Registratie

Location
Locatie

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

Customize Charts with VBA

By Jon Peltier

It can be very tedious to modify your charts, and it gets worse the more charts you have. But you can use VBA to streamline the process.

We will work through examples that should introduce you to:

• Recording and cleaning up simple macros
• Chart Object Model
• Time-saving syntax
• Immediate Window shortcuts
• Chart data
• Chart formatting

Evil and Hero Functions

By Charles Williams

A discussion of some of Excel’s most Evil and Greatest functions.

• Why are they evil?
• What alternatives are there?
• Can a function be both evil and one of the greatest?
• When to use and when NOT to use
• INDIRECT, OFFSET, GETPIVOTDATA, VLOOKUP, SUMPRODUCT, SUMIFS, INDEX/MATCH, CHOOSE …

The role of slicers and Dimension tables in a Power pivot dashboard.

By Henk Vlootman

Within Power pivot dashboards Dimension tables plays an important role as intermediary between the different fact tables, that can come from different sources. It functions as the oil between the tables, but they also play an important role as being the steering wheel for your `Power pivot dashboard. In this session we’ll exploring Dimension tables and how you can use them in your dashboards.

• What are Dimension tables and how do you recognize them?
• Proper naming within Dimensions tables.
• Making your own Dimension table in Excel.
• Making your own Dimension table in Power Query.
• Making slicers out of your Dimension table in your Power pivot Dashboard.

Tuning your slicers

Helper cells, Array Formulas, Tips & Tricks

By Bob Umlas
Many people will look for a solution to a problem with complicated formulas, not realizing that simpler formulas used as helpers make things easier. In this session I will demonstrate what looks to be the result of using VBA, when, in fact, 9 helper columns are used (out of sight, of course!)
The session will include

• Array formulas
• Using the OR-function to compare lists
• Finding the bottommost cell
• Searching ranges for errors
• Counting occurrences of substrings
• Extracting the last part of a string
• Determining if a range contains unique values
• Calculating the sum of digits
• Getting the number part of a string (like getting 123 from ABC123)
• Getting the closest value to the average
• Adding conditions (like adding up all the amounts for which the name is Bob OR the date is before 5/1/98)
• Creating a calendar with one formula
• Tips & tricks

Customize the Excel Ribbon for your Workbooks and Add-Ins

By Jon Peltier

You can readily modify the Excel ribbon to make your workbooks and add-ins easy for people to use.

• Simple Customizations in the Excel Interface
• CustomUI in the Workbook’s XML
• The Custom UI Editor
• Buttons, Menus, etc.
• Icons – Built-In and Custom
• Make the Ribbon Dynamic

Advanced Power Query and M Language

By Guy Hunkin (Microsoft)

Excel 2016 includes a powerful new set of features called Get & Transform, which provides fast, easy data gathering and shaping capabilities. During this session we will learn how to leverage the powerful Get & Transform capabilities to connect, combine, and refine data to meet your analysis needs. We will also take a quick glance at the Power Query Formula Language, known as M, which helps you to provide solutions to even more complex business problems.