What's New in Excel
From dynamic arrays to data visualizations to intelligent features, Excel has changed significantly since the release of Office 2016. In this session, Micah will provide you with a high level overview of what has changed in the most recent versions of Excel (spoiler alert: it’s so much more than the three areas listed above) and will also go in depth with demos and explanations of how to use some of the new features Excel offers, allowing you to return to work more efficient, more productive and more confident than ever before.
How to build interactive Excel Dashboards that update with the click of a single button using regular Excel; no Add-Ins, no macros / VBA, just good ol’ Excel and some data. Along the way we’ll share pro tips on chart choice, formatting and clever hacks as we build a complete dashboard from scratch in under one hour. Plus, we’ll show you how to set it up so it’s fast to build and has virtually zero maintenance.
- Starting with a blank workbook
- Practical considerations
- Pro tips
- Which chart, when and why
- Formatting ideas.
Excel 365 vs. Excel 2016
Since September 2015 when Excel 2016 (for Windows) was officially released (for Office 365 users) Excel 365 (not the official name but what it is known as) has developed significantly interesting features that set it aside from its counterparts, Excel 2016 and Excel 2019 (known as the “perpetual versions”). Today we can talk of Perpetual Excel and Excel 365 as two quite different versions of Excel, not only by methods of obtaining them but mainly by functionalities.
This session will show you features that will make you want to run out and get your very own Office 365 subscription, considering Excel Online and for apps too.
- Insiders Program
- New functions
- New charts
- New Data Types
- Exciting new tools
- Dynamic Arrays
- Noteworthy application changes.
Creating More Effective Charts
Creating effective charts can be challenging. But you don’t have to be a trained graphic designer to make good decisions when charting. In this session, we’ll review simple foundational techniques to help your data shine.
- Gestalt principles
- Pre-attentive attributes
- Glanceable vs referenceable
- Chart junk
Real & Unusual Needs / Uses for Excel
Oz du Soleil
Having spent almost 20 years working with Excel, Oz has spent much of that time with small businesses and rogue departments who need very unusual needs that are outside of Accounting and Finance. Oz will share some of these uses to expand how we think of Excel and what it’s capable of.
- Thinking outside of the box
- Unusual functions for unusual situations.
Painting to Present Your Data
The session will present how to create dashboard, using a lot of Excel future like Conditional formatting, Sparklines, Custom formatting, Charts and tables.
Build your border
Conditional or Custom formatting?
Create your Masterpiece
Excel Tips and Tricks
Ever thought, “there must be an easier way to do this?” If so, neurosurgery may not be for you, but this session might be. Intended as an interactive event, we’ll explore some of the lesser-known functions and features that might just save you time and solve problems you thought could not be handled by the humble spreadsheet. Bring a laptop, your problems and a healthy sense of humour.
- Useful time-saving tips
- Data manipulation tricks
- Useful functions, features and formulae
- Q&A welcomed.
Rolling Budgets and Charts
Learn how to automate regulated reports so that charts and reports will update automatically (or at the flick of a switch) without changing structure or causing #REF! errors.
- Explaining why you need two start dates in a model
- The importance of Tables
- Creating rolling budgets and charts that automatically update for actual data
- Explanation of key functions and formulae involved.
Oz du Soleil
Building functioning models is a fairly sophisticated skill. However, one thing that’s often missing from models is testing and protection. In this session we’ll cover ways that models should be tested so that they aren’t easily broken or unknowingly generate bizarre results.
- Concept of testing
- Discussion of protection
- Practical examples.
Advanced Chart Tricks for the Non-Advanced User
Ever thought, “There must be a way to do this”? When it comes to Excel, there probably is! In this session, we’ll review some hidden hacks that will make your life easier when creating charts.
- Chart templates
- Paste formats
- Number formats
- Specifying axis labels
- Direct labelling data
- Adding totals to stacked columns
- Specifying ranges on a chart
- Linking chart text to cells.
There hasn’t been a change this significant to Excel formulas ever. Dynamic arrays fundamentally change the way the Excel calc engine works. They simplify array formulas making easy work of distinct lists, sorting, filtering and more. Excel no longer requires you to enter array formulas with Ctrl + Shift + Enter and formulae that return multiple results will now automatically ‘spill’ into the cells below and or to the right. And that’s not all, as we also take a peek at the new data types and how collaboration has been improved.
- What is a dynamic array?
- New data types
- New errors
- Collaboration example.
Q&A session (Microsoft focused)
Micah Myerscough and Mark Traverso
Free-for-all Q&A session on all things Microsoft Excel.
Speaker to be confirmed
Details of keynote to follow.
Time Intelligence in Power Pivot
Every Data Model needs a good Calendar Table or said differently, every Data Model in Excel needs a time dimension. The main reason for this is not the mere fact that this allows you to slice and dice your data by weeks, months, years… it’s the fact that having a Calendar table in your Data Model allows you to use Time Intelligent functions like DATESYTD, TOTALYTD, DATEADD, PARALLELPERIOD,…
All these functions will be the focus of this session backed up by examples of their use.
- Calendar Table
- DATESYTD, DATESMTD, DATESQTD
- DATEADD, PARALLELPERIOD
- Custom Calendars and fiscal years
- Dynamic rolling periods and dynamic current periods.
Simulations analysis is an approach where different outcomes for the same project / model are assessed using probability distributions. It is an alternative technique to sensitivity and / or scenario analysis.
This session shows how you can build simulations analysis into a model without using VBA, macros or a PhD in Mathematics.
- Explaining simulations
- Monte Carlo approach
- Using Data Tables
- Building the model
- Examples throughout.
Understanding VLOOKUP and Why I’ve Not Used it for 15 years!
VLOOKUP is seen as a rights-of-passage function in taking your Excel skills to another level. It is one of the most commonly used formulas in business spreadsheets, but there is a better alternative.
INDEX MATCH is a more robust more flexible alternative to VLOOKUP and in this session you will learn a trick on how to make it easier to write than a VLOOKUP.
Then double down with an INDEX MATCH MATCH! Your spreadsheets will never be the same again…
- INDEX MATCH
- Tables and Power Query.
VBA 101: Back to Basics
Every year, we have MVPs presenting on how to do all these whiz-bang things in VBA, without ever addressing a core problem – most people simply aren’t across the basics of recording, adapting and extending macros. This session will go back to basics and address the “where do I start?” questions when you are looking to solve problems and turn to VBA.
- Recording and adapting macros
- Understand the high-level do’s and don’ts of working in VBA
- Learning how to use simple loops and conditions
- What to consider before you copy and paste code from forums and websites.
Data Model in Power BI: Different Approaches
The focus of this session will be on two different views of Data Modeling. We will look at the “Frankenstein’s Models” you can create with Power BI and on the other hand the totally different clean approach to the same model. We will compare the two by the number of Measures and the number of pages in a report. They will also be compared by complexity and speed. All along with this comparison, you will be able to pick up countless ideas for your next Power BI project.
- Power BI Modelling
- Setting goals
- The building of a typical data model
- Getting the typical data model to work
- Reimagining the model
Going from Excel to PowerPoint
It’s frustrating to spend a lot of time developing charts or spreadsheets in Excel only to have them blow up when they’re transferred into PowerPoint. In this session, you’ll learn techniques to make this process easier.
- Keeping colours and fonts intact
- Should you link or embed your data?
- Transferring charts
- Transferring spreadsheets
- How to activate a spreadsheet during a presentation.
Real-World Case Studies Using Power BI and Power Query
In this session, we’ll put away the AdventureWorks dataset and turn our attention to the wealth of data that is publicly available. We will apply some of the newer tools within Power BI to analyse non-standard datasets, showing you how easy it is to apply cutting-edge analytical tools to “difficult” websites. This is pretty new and pretty cool.
- Use Table from Examples in Power BI
- Learn how to extract data from ‘unfriendly’ websites
- Extract headings, values, hyperlinks and ‘hidden’ website variables
- Add news reports to your financial data.
Out of the Box Use of Well-Known Excel Functions
This session will present tips and tricks on how to use well know functions like IF, VLOOKUP, SUMPRODUCT in tricky situations. Some best practice tips will be provided to decide which function is better to use when.
- VLOOKUP vs Nested IF’s
- SUMPRODUCT: well know or not?
- CONSOLIDATE vs VLOOKUP.
Deep Dive into All Six Joins in Power Query
Oz du Soleil
Power Query is an incredible tool for cleansing, merging and segmenting data. One thing it brings us is a set of six joins that are named things like Inner, Left Anti and Right Outer. This session is a thorough examination of all six joins and their real world uses.
- What is a join?
- Why are they needed
- Six types of join
- Examples throughout.
Finance professionals need to learn efficient and effective data forecasting methods in order to make effective decisions. Almost all managerial decisions are based on forecasts of future conditions. Yet it is never finished.
This session looks at what you should consider when building forecasts, and how to build them in a timely, effective and accurate manner.
- Which forecasting technique when?
- The problem with Keeping It Simple
- The problem with not Keeping It Simple
- Different techniques
- Forecasting with one click (Excel 2016 onwards).
Three Real Business Solutions using Power BI for Excel
This session will present three real-life business cases from practice – projects using Power BI for Excel as a best solution. The first one is for pharmaceutical industry, the second one is for HR department and the final one is for information and technology services company. However, the learning points are relevant to all.
- Before and after (pharmaceutical industrial project)
- Don’t miss training or how to organise your people (HR project)
- Automate reports to customers (Call centre).
Conditional formatting is one of the most useful ways to help people wade through dense data and instantly spot outliers and KPIs. This session will show you ways to apply conditional formatting to highlight the good and the bad while avoiding the ugly.
- Data bars
- Color scales and heat maps
- Icon sets
- Using formulas
- Hiding numbers
- Less is more.
Q&A session (Excel focused)
Free-for-all Q&A session on all things Microsoft Excel.