The following agenda has been put together in good faith with the topics and presenters as cited. However, we do reserve the right to change the order, the timing, the topic or presenter for circumstances beyond our reasonable control (e.g. presenter sickness, transportation issues, change of functionality in Excel, etc.). If you have any concerns, please contact us before booking.
Welcome / Dynamic Charts
This session welcomes you all to the event and also considers common issues in charting, such as:
- Extending ranges
- Hiding data
- Dynamic labelling of titles
- Dynamic labelling of, er, labels.
Reading Real-Time Data into Excel with Data Streamer
In this session, we will discover a very little explored feature of Excel: the Data Streamer. Using an Arduino board connected to a computer and some sensors, we can make data flow into Excel in real-time reading them with Data Streamer. By creating some formulas and charts, we can produce a powerful data visualization tool to give us instant metrics and insights. As a bonus, you will see that with Data Streamer we can also control external devices using Excel.
Excel Tips and Tricks
A potpourri of miscellaneous Excel tips and tricks.
Cheating with Charts
I was emailing a colleague about “charting”, but AutoCorrect changed it to “cheating”. That made me realise that so much of what I do with Excel charts is somehow cheating, using smoke and mirrors to achieve the visual effects I need. This presentation will cover a number of ways in which I cheat with my charts, e.g.
- how do I draw lines where I want them
- how do I put a label where it makes most sense
- how do I highlight important individual points in a chart.
Easier Excel BI Modeling with Monkey Tools
HOW MONKEY TOOLS HELP EXCEL BI MODELLERS
This session will demonstrate how Ken’s Monkey Tools add-in can help you build better Excel BI models faster, and audit models that you receive from others.
Pleb vs Pros: How to Look Like You use Excel for a Living
This session is all about taking typical, “basic” approaches to solving problems and making a few small tweaks to make it look like an Excel pro has put it together. We are going to show you how the way you use functions and features in Excel can make you look like a pleb, and what you can do instead to look like a pro.
Mind-Blowing PowerPoint for Effective Visual Storytelling
Excel is an awesome tool for calculations, formulas and managing data. But what about when you need to share it with others? You’ll often do a presentation, and that’s where PowerPoint comes in. PowerPoint is such an essential tool for all types of communication, but it’s badly used.
RevolutioniSe your content with amazing visuals, engage your audience with compelling animations, and impress them with beautiful, professional design. All using only PowerPoint. You’ll see a host of visual storytelling techniques, all demonstrated live, that you can use immediately to create amazing presentations or interactive content. It will contain ways to improve upon Excel-based charts with formatting and animation for better storytelling, and then into more custom charting and more in-depth visual storytelling. All using only PowerPoint (well, and a bit of Excel).
Participants will be able to:
- Create dynamic visuals that support persuasive stories to engage your audience
- Develop professional looking designs for presentations and other content
- Become a PowerPoint legend with techniques to make effective presentations quickly and easily.
The Sensual Six: All of Power Query's Joins Explained
Oz du Soleil
If you work with datasets that need to be merged, compared or segmented, Power Query’s joins are important to know. They are designed to simplify and automate those needs.
These can be a little tough to understand, but this session is designed to go step-by step to give you a solid foundation for understanding and using these hot tools.
The session will cover:
- all six joins
- when to use a join instead of VLOOKUP or XLOOKUP
- ways to be efficient with joins
- real world complexities and warnings.
So relax. Get mellow while Oz guides you on this journey through all of the six joins in Excel’s Power Query.
- Overview of joins:
- Full Outer Join
- Inner Join
- Left Outer Join
- Left Anti-Join
- Right Outer Join
- Left Outer Join
- Complex Joins
- Multiple criteria
- Merge for Categories instead of Exact Matches
- Using a Reference Table for Inconsistent Entries
- Real World Examples.
本课程还邀请到Oracle的研发经理George Huang来给大家介绍Oracle使用Excel Add-in的真实解决方案案例。
- 什么是Office add-ins
- Office Add-in优势
- Learn about Office add-ins
- Learn about what’s new
- Demos / example customer solutions
See how easy it is to get started with great tools such as Script Lab and Visual Studio Code.
Excel妙招搞定数据处理与分析 (Tips for Data Processing and Analysis in Excel)
(To introduce some key methods of data processing and analysis in the latest version of Excel, including multi-table consolidation to generate PivotTables, quick beautification of data tables, generation of data maps with PowerMap, and more.)
Introduction to DAX
Power BI is a powerful tool to help you analyse your data. While it is possible to simply drag columns of numbers into a report, this approach only scratches the surface of what is possible. If you want to be good at using Power BI, you must learn to write DAX. In this session, Matt Allington will provide an overview of how to use Power BI with and without the DAX language, and explains the benefits of learning to do more than just the basic drag and drop of columns.
Create a Basic Quiz in Excel with Conditional Formatting
Data Validation and Conditional Formatting are great features in Microsoft Excel. In this session, you will learn to create an interactive quiz in Microsoft Excel using these features. You will also calculate the score based on the values selected.
Charting a Virus - Dashboard Challenges in Excel and Power BI
When COVID-19 case numbers were still low in New Zealand, there were no visuals to illustrate how the virus was spreading. I set out to build a dashboard using the official data from the government web site. I’ll walk you through the challenges I faced and the workarounds I used with Excel and Power BI. If you’ve ever looked at a COVID-19 chart, this session is for you.
Top 15 Tips and Tricks in Excel
In this session you will learn the below tips and many more
- How to insert multiple lines
- Move rows and columns easily
- Freeze Panes
- Flash and auto fill
- Text to columns
- Conditional formatting
Lists in Power Query - A Hidden Gem
To clarify, they are not as much hidden as nobody uses them to even half of their ability. This demo driven session will be all about using lists in different scenarios in Power Query.
Excel vs Google Sheets: Crazy Cool Features Available in One but Not the Other
Google sheets has some mind blowing functionality that will shock even the most advanced Excel users. Since unlocking the power of Google Sheets last year, many of the spreadsheet solutions I create relies on both mediums to do different things.
Despite simultaneous editing being available in Excel, Sheets is still shining above and it is especially important during the pandemic.
Collaboration benefits are just one thing which Sheets does better including data validation, protection and use of images. On the flip side, modern Excel has some great things that google doesn’t.
This talk is applicable to daily users of Excel & google sheets from beginner to advanced. This article I wrote might give you an idea of what’s to come. https://ion.icaew.com/itcounts/b/weblog/posts/excel-tip-of-the-week-240—6-things-google-sheets-does-better-than-excel
We will also discuss the revolutionary new “Dynamic Array” functionality (and how that differs between Google and Excel). Each app does it differently and each offers advantages the other cannot.
How to build a check list using Excel? - Excel లో చెక్ లిస్ట్ చెయ్యడం ఎలా?
Purna Chandra Rao Duggirala
The session will do exactly what it says on the tin, showing you how to build a checklist in Excel. In Telugu.
Integrating Python and Excel
How to write Excel add-ins in Python and seamlessly integrate Python into Excel. Learn why Python is the leading programming language for data science, machine learning and AI, and how you can leverage that power from Excel.
In this session, Tony will introduce you to Python and explain why it has taken the world by storm. We will also look at how Python and Excel can work together, including writing and using Excel add-ins written in Python.
- Introduction to Python
- Overview of Python tools for Excel
- Writing UDFs and Macros in Python
- Demos of Python-powered Machine Learning and AI in Excel.
Mapping Your Data Using 3D Maps or the Filler Map Chart
Using Geography Data Types to Retrieve Data
A) Filled Map Charts
- Filled Map Charts by Country
- Filled Map Charts by State
- Filled Map Charts by Postal Code
- Filled Map Charts using text instead of numbers
B) 3D Maps
- Creating a map from a single data set
- Assigning colours by category
- Navigating in the map (Zoom, Rotate, Perspective)
- Animating data over time
- Understanding the time options
- Layer options for individual house addresses
- Real estate mapping
- Building a Tour and a video
- Creating a map from multiple data sets
Excel Blueprint: How to use the Modern Power Tools to Become an Excel Hero
Learn what the new “Power Tools” (Power Query, Power Pivot, Power BI) are and how they work. I share my 5-Step Blueprint for Excel which explains how existing tools like formulae, PivotTables, macros and VBA might fit into your workflow. You will learn ways to automate Excel, save time with your job and become the Excel Hero of the office.
Testing Spreadsheets - Quality Assurance in Excel and VBA
This 45-minute session is based on current concerns about risks from the uncontrolled use of spreadsheet-based information systems. Real statistics on error rates are presented which will provide you with evidence for the case for better control. Good practices are presented for error detection, correction and prevention. These are appearing in new syllabuses for skills certification. I cover how to assess risk in the context of spreadsheet criticality, and a process of high level and detailed reviews. I conclude with a review of software tools on the market to facilitate the audit process.
- Current concerns – spreadsheet hell
- What’s the problem? – what happened
- Metrics for Error & Rework Rates
- How to manage risks & user training syllabus
- Good practice: Prevention, Data Control, Detection
- Risk assessment, scoping, questions, maturity model
- High level & detailed review and testing
- Management process, what not to do
- Software tools, VBA analysis, some product screenshots
ADDITIONAL RESOURCES TO BE PROVIDED (the take-aways):
- List of references to useful web sites
- Articles on spreadsheet risk management
- Access to evaluation version of XLTEST tool for spreadsheet audit
Building Excel Chart Worksheet Templates
Regularly updating charts with new data every month takes a lot of time. In the coronavirus pandemic, many financial professionals were forced to be updating charts every day and the normal monthly workload was multiplied many times over. This session shows you how to build a chart worksheet so that when the new data is available, the chart is automatically updated. It will cover structure of the worksheet, approaches to chart creation, and some of the helpful functions that are used.
Excel-DNA in Perspective
Govert van Drimmelen
The .NET development platform is embraced by millions of programmers across industries and throughout the world. Excel is the most popular computing power tool, and with its built-in VBA language brings an approachable programming environment tightly coupled to the functional spreadsheet modelling interface. Excel-DNA is an integration library that glues .NET into Excel, bringing together these tools and also their respective user communities.
I will share some of my perspectives on what happens when these worlds interact – both the joys and the tensions. VBA enthusiasts might like to approach the contemporary development ecosystem without leaving the end-user agility and power of Excel – even if they find Visual Studio overly complicated, or GitHub and the NuGet package manager are strangers. Likewise, .NET developers can reach a new power-user audience for their back-end services and software libraries by embracing Excel as a powerful presentation and interaction front-end.
Create Dashboards in Excel with Data from MS Project
Join us on this hands-on session to learn how you can use Microsoft Excel to obtain data from multiple MS Project schedules, creating a report that consolidates the portfolio of projects and their performance.
Building Faster Excel Solutions
- Learn how to build fast Excel solutions
- Understand the available Excel extensibility technologies
- Speed up slow VBA solutions 100 or 1000 times
- Object model extensions for Dynamic Arrays and new Data Types
Matrizes Dinâmicas, Novas Funções e seus Equivalentes nas Funções Nativas
(Dynamic arrays, new functions and their equivalents in native functions)
Microsoft has released many functions for Excel Insider, but we may replace most of them with the functions already in the application. In my talk, I will demonstrate how these resources may be used, even for those who do not have the functions of Insider.
Build Add-ins for Microsoft 365
- learn about Office add-ins
- learn about what’s new
- demos / example customer solutions
- see how easy it is to get started with great tools such as Script Lab and Visual Studio Code.
Calling OAuth2 API's in Excel or Power BI using Power Automate
Power Query can’t handle OAuth 2 API’s, but we’ll see how we can use Power Automate’s custom connectors to call any OAuth2 API and return data.
Advanced Data Validation (using Classic Functions or Dynamic Arrays)
Data Validation allows us to control what can be entered in sheet and what cannot. There are eight options to select from. Dropdown lists are one of them.
Dropdown lists can be a game changer to add interactivity to our worksheets. We can use them to switch Charts, to turn Conditional Formatting On and OFF, to pick up pictures, to switch functions, change source files for Power Query or even to change a Dashboard.
In this session, I’ll be exploring different types of dropdown lists (remember the 5 Ds):
- Drop Lists.
A little Python + A little Azure SQL + A Little Excel JS API + A little Power BI = A National Magic
Mar Gines Marin
Join this session to learn more how we are using intelligence to unlock the power of Microsoft Excel for more users. We’ll cover capabilities such as Natural Language Queries, Recommended Charts, PivotTables, and Ideas, as well as how our approach aligns with Microsoft’s Responsible AI standards.
The Rise of XLOOKUP – The 3rd and Best in the Trilogy of VLOOKUP and INDEX MATCH
XLOOKUP is the new killer Excel feature, it will kill off VLOOKUP and INDEX MATCH. As at the end of July 2020, everyone on Microsoft 365 should have XLOOKUP available.
In May 2016, Wyn posted an Excel User Voice suggestion for a simpler and safer version of VLOOKUP and INDEX match.
Four years on he’s now demonstrating on why we needed XLOOKUP, how to use it and highlighting the true potential of this awesome new function.
Date Based Calculation with DAX: Time Intelligence
Year to date, Quarter to date, Previous Year to date, Rolling 12 months sales, sales same period last year and many other date-based calculations can be implemented using DAX functions in Power BI or Excel. In this session, you will learn how to write DAX measures that give you date-based calculation results.
- Visualizing time in PowerPoint
- Formatting time in Excel
- Visualizing time in Excel
- Line charts
- Gantt charts
- Clock charts.
Most Comprehensive Excel Example. Ever.
This session takes an age-old Excel problem for making lists more manageable by combining the new (dynamic arrays, new functions and operators), with the old (data validation, custom number formatting, conditional formatting), the ugly (deliberately creating errors, and using non-auditable functions like INDIRECT) and the under-used (Tables, text functions and TRANSPOSE).
Whether the example strikes a chord is not the issue; the emphasis is showing how many different areas may be brought together to work collaboratively. Bring your best speed-reading glasses, a healthy sense of humour and a seat belt, because you are going to have to strap in for this ride, baby.
How to Convert Address to GPS Coordinates
Frederic le Guen
Here’s how to convert address to GPS coordinate, and the other way round. It’s so easy to visualise data on a map when you have the GPS coordinates but when you have just the address, the localisation may not be accurate.
This session will show you how to convert your address to GPS with just two Excel formulae.
Convertir ses adresses en coordonnées GPS et l’inverse.
Il est très simple de visualiser la position de ses contacts sur une carte quand on dispose de coordonnées GPS. Par contre, avec une adresse standard, la position sur une carte est plus compliquée.
Cette session va vous apprendre à convertir vos adresses en coordonnées GPS avec seulement 2 formules Excel.
Dynamic Report Using CUBE Functions
- Data Prep and formatting
- Loading data into the Data Model
- Create Relationship
- DAX Measures
- Cross Tabular Report using CUBEMEMBER, CUBERANKEDMEMBER & CUBEVALUE Functions
Understanding Ranges and Tables: the Key to Building Structured Excel Models
Building an Excel model is a highly disciplined game. It doesn’t matter whether it is an easy or complex model, structuring your data is the base of creating efficient and reliable models. Getting to know how ranges and tables work will give you a design head start. The five layers of modelling model is used to understand the nature and structure of data in an Excel model.
In this session, Henk shows you the basics of modelling in Excel with ranges and tables, the way formulae work, and a lot of tips and tricks on how to use them.
- Five layers modeling in Excel
- Definition of ranges and tables in Excel
- Working with cell references and names.
Scenario Calculations with Power Query
Power Query has some neat options to make scenario calculations in Excel or Power BI very convenient. In this session, the benefits and basic building blocks will be shown.
What's New in Microsoft Excel
Guy Hunkin, Danielle Rifinski Fainman & Jonathan Kahati
Join this session to discover how modern Excel can help you tackle the challenges in modern work. Ask questions and get answers using natural language and AI, collaborate with others and do more in less time using Office Scripts. Simplify the creation of your spreadsheets with new Dynamic Arrays and XLOOKUP, unlock new scenarios with an enhanced integration with Power BI and more.
How to Use R Language in Excel
R is a very popular programming language used by statisticians, data scientists and analysts. With thousands of additional packages, stunning visualisations and ready-made analytical functions, it can be a very precious tool in your Excel workshop. During this session, Grzegorz will show you how to integrate R with your spreadsheet using BERT – Basic Excel R Toolkit. Be prepared for a lot of demos showing how R can use Excel and how Excel can use R.
The Magic of Conditional Formatting and More Useful Tips and Tricks in Excel
- Create basic formula
- Highlight cell when contract finish
- Highlight weekends or create holiday plannner
- Compare profit by year
Storytelling con datos en Excel
Learn how to give greater customisation to your reports and charts with Excel and its Power add-ons to be able to stand out in your profession.
Do your work with memorable Excel spreadsheets and help the easy interpretation of data to make better decisions.
Financial Modelling Tools
Tony de Jonker
During this session you will see how to use new and existing Excel tools to assist you with all kinds of financial reports. The following topics will be addressed:
- how to automatically allocate forecast numbers to a variable future time horizon based on several drivers using a minimum of unique formulae
- create an attractive break-even analysis using a dynamic chart that automatically displays the number of units and the break-even point while changing variables
- tips and tricks.
Building Truly Effective Charts & Graphs
Many people nowadays use Excel to visualise data. They create charts and then present them in PowerPoint. However, many times they leave them as they are and miss a huge opportunity – an opportunity to tune the chart just a little bit more and thus make it way more effective. This demo-only session presents the steps and the algorithm that everyone can follow to create and present effective data visualisations with the help of Excel and PowerPoint.
The Excel Charts Microsoft Forgot to Add
In this session, Jordan will show you some amazing tricks, interactivity, and design to take your data visualisation to the next level. We’ll build three interactive charts you can use right away. This session will emphasise important concepts like data visualisation, Power Query and just a little VBA. Attendees will receive all the files used and can immediately begin implementing the charts into their own work.
Creating the Ultimate Dynamic Crosstab Report with Array Formulae
Whilst Roger’s first and preferred method of analysing any data tables is to use PivotTables or Power Pivot, with the arrival of the new Dynamic Array formulae, it is easy to create a crosstab report with just a few simple formulae.
In this session, he will show not only how to use these formulae, but the end result will be a template into which the user can drop any data table and with no change of any formula, be able to create crosstab reports which are just as flexible as a PivotTable.
Solving Complex Problems with Merge in Power Query
Get creative with Power Query’s Merge functionality to solve complex problems in Excel. Aside from using merge to lookup values from other tables, you can use it to:
- transform messy data to proper tabular data
- search and replace bulk values
- compare an old version of a table to a newer version to find mismatches in data
- approximate match lookups
- and more!
Financial Reporting in Excel vs. Power BI
In this session which is relevant to everyone (not just accountants!), Rishi will look at how financial reporting has typically done in Excel, and some of the challenges this has traditionally posed. He will then take a comprehensive look at Power BI functionality including:
- automatically consuming files in a folder
- storytelling with data
- dynamics graphs and measures
- Waterfall charts and tables
- Row Level Security
- analysing data with Excel
- reporting commenting and subscriptions
Excel Speed Loop
You need two things to work fast in Excel: a process and core Excel skills. The process provides a formal way to approach and optimize your workbooks. The Excel skills enable you to automate manual tasks.
In this session, I will present the Excel Speed Loop process that will help you systematically optimize your workbooks for efficiency and accuracy. I will also demonstrate several key Excel skills (Power Query, PivotTables, Tables, MAX) that will help you automate manual tasks.
If you use Excel to update the same set of workbooks each day/week/month/quarter/year, this session will help you continuously improve your workbooks.
Smart VBA Macro Recording/Slim Macro's Opnemen
Jan Karel Pieterse
Power Query can’t handle OAuth 2 API’s, but we’ll see how we can use Power Automate’s custom connectors to call any OAuth2 API and return data.
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
- Alternative chart types.
Excel and Power Automate
Excel Virtually Global is an independent conference and is neither sponsored nor approved by Microsoft.