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.
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.
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
CTRL + ENTER: Start Using it Yesterday!
There is no action more basic in Excel than entering data. Most people are aware that possibilities include Enter and Tab. Some have even heard of Shift + Enter and Shift + Tab and are using them. Very limited few have become used to Ctrl + Shift + Enter since they work with array functions and that is basically the only way to confirm those entries.
But there is another way you can enter data, and it is by far the most versatile and useful, and it has by far the smallest following, and this needs to stop today! This session will take you through examples of using Ctrl + Enter and basically make you faster and more efficient in Excel.
- Entering data in Excel
- What about the formats?
- Mixing it with Go To-Special
- Putting it all together.
Excel Tips and Tricks
Have you ever been stuck on how to do something in Excel? Looking for inspiration or new ideas on how to solve old problems? Found yourself nesting monolithic formulae that Tolstoy would be proud of? Come to this session to find new ways to work smarter, not harder. Bring your questions, and we’ll use both old-school and Modern Excel features to solve them.
- Bring your questions, we’ll bring the ideas
- Use old-school Excel functions and formulae to solve common problems
- Learn how Modern Excel features can make your life easier.
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.
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).
Assessing Client Needs
Oz du Soleil
A client can be, in the traditional sense, someone who’s paying you to build a solution. A client can also be a co-worker who needs a solution because you’re the resident guru.
In this session, Oz will share what he’s learned from assessing client needs. This is critical because a lot of solutions aren’t adopted because the client’s needs weren’t fully assessed, and the delivered solution is missing a key piece. Sometimes the client can’t fully articulate their needs. We have to help them.
Also learn why Oz delivered a beautiful dashboard to a client who commented, “I can tell this is smart, but I kind of hate it.”
- Who is the client?
- How to assess client needs
- Case study examples throughout.
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.
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.
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.
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.
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.
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).
Pivot Tables and Slicers
Start where we mean to end! This session provides a refresher on all things PivotTable and Slicer, and how to avoid classic gotchas. By the end, you will add slicers and timelines like a pro and even be introduced to how to create a PivotTable without refreshing. Now that makes a refreshing change.
- Creating a PivotTables
- PivotCharts vs. Charts
- Using slicers and timelines
- Layout tips.
Q&A session (Excel focused)
Free-for-all Q&A session on all things Microsoft Excel.