What’s new in Excel?
You get to learn about what is newly available in Excel! Not all professionals using Excel make the time and effort necessary to stay on top of improvements made to the software. After all, it is not easy to keep up with all the new additions given the fast pace in which this tool has evolved.
By attending events like the Global Excel Summit, at least once a year you can get an overview of what is new and how you can leverage Excel’s new capabilities at work.
Leila Gharani went through some of the most recent additions to Excel, covering new features like Focus Cell, True Dark Mode, the new TRIMRANGE function and operator, the Regular Expression functions REGEXEXTRACT, REGEXREPLACE and REGEXTEST, the new GROUPBY and PIVOTBY functions, and Python in Excel (which is now natively available in Excel with the capability to read data brought into the file with Power Query). Leila also mentioned the new Checkboxes feature, the TRANSLATE function, and Copilot.
What’s coming up next?
Not only do you get a summary of what’s new, but you may also get a glimpse of what is coming next. Chris Webb, a member of the Fabric Customer Advisory Team at Microsoft, came to talk about how Power BI, Fabric and Excel fit together to boost productivity for any company size, but the most spectacular revelation from Chris to the Excel crowd might have been the one about Power Query’s editing capabilities coming soon to Excel for the Web. This new addition will certainly open new ways of developing solutions in Excel online.
AI and Copilot
One of the greatest investments by Microsoft in the recent past has been in AI, with Copilot being the tool integrating AI into Microsoft's applications. Several speakers covered Copilot’s capabilities, demonstrating how to use artificial intelligence in Excel to generate formulas, create charts, and transcribe handwritten data into spreadsheets.
David Fortin gave examples of how to take advantage of Copilot, including asking Copilot to create Excel formulas to solve a specific example, create charts from data provided, and transcribe handwritten data into a spreadsheet. Some features are exclusive to the premium version, but others are available in Copilot’s free version.
Using AI is a requirement for everyone to stay on top of productivity. Even if you are an expert, it is critical to learn how to provide Copilot with prompts that will make it generate the desired results and use this to help complete tasks faster.
George Mount talked about how Copilot and Python, now available from within Excel, can boost not only the professional’s productivity but also the type of outputs that can now be generated in Excel that were not possible or at least easy to accomplish before. George also showed a couple of use case scenarios demonstrating how to use Python in Excel and explained how Python and Copilot together in Excel can allow users to uplevel their data analysis game.
Financial Modelling
Several presentations were geared towards financial modellers. If you work in this area
Gary Knott talked about cash flow methods in Excel. He introduced different methods of how to use Excel for budgeting, covering forecasting, cash flow management, and expense tracking. He showcased templates and functions that streamline budgeting tasks, making financial planning more efficient. Liam Bastick played “Deal or no deal” to discuss best practice modelling tips. Danielle Stein Fairhurst showed us how to take advantage of PowerPoint for financial presentations. Ian Schnoor and Rob Langrick shared their best techniques to solve disparate practical problems faced by finance professionals as they model company cashflows and valuations, including getting to know the file well when you need to audit it and finding errors and irregularities in those models. Ian and Rob also talked about the job roles in the Finance industry and how Excel is the go-to tool across several mathematical areas like monitoring and risk analysis for example.
Best Practices
Best practices when working with Excel and Power BI is a big topic for data analysts and anyone building reports and developing solutions for others to use. Carlos Barbosa showed how some very “basic” skills like filtering and sorting are fundamental for a first approach to get to know the data you are dealing with on a project. After this preliminary investigation, the Excel professional should know if the data available can be trusted and the parameters and parameter ranges that are involved in the project. Then, when data is ready for analysis, Carlos presented Pivot Tables as one of the powerful tools available in Excel for data aggregation and summarization. All this will work even better and more dynamically if we use Excel Tables. He also reinforced how important it is to cross-check your data and confirm your report results.
Carlos’s presentation was also about how to bring Excel and Power BI together to take advantage of the best each tool has to offer, and demonstrated how some tasks can be accomplished with both tools.
Data Models
Data Models are fundamental for analysis and automation that involve multiple tables and data collected from multiple sources. You can work with Data Models in both Excel and Power BI. In both programs, it is important to understand what a data model is, how to structure one by correctly creating relationships between the several tables and how to create the measures that will be used for analysis. Before all this, data must be imported, properly cleaned up and on tabular layout. Power Pivot, DAX and Power Query are the tools to learn if you want or need to work with data models in Excel or Power BI.
Carolina Lago talked about the importance of creating a single source of truth for your financial data and reporting, and suggests starting with Power Pivot in Excel to build your first data model and dashboard to combine and analyse data from multiple sources. Carolina demonstrates how to use Power Query, Power Pivot and DAX to create a data model in Excel. Carolina also explains why she recommends starting with Excel for your first data models and goes over the scenarios where you may need to move from Excel to other technologies.
Power Query
Power Query is useful for anyone dealing with data that requires layout changes before it can be consumed, regardless of being a one-time or recurring task. Power Query can import data from multiple sources and load the resulting tables into an Excel table or into a data model. Maria Braga presented an introduction to Power Query and how to use it to automate your data importation procedures to update your reports. Power Query is extremely powerful because it can reduce drastically the time spent in cleaning and preparing data for reporting. These time savings are even more valuable when we are referring to recurring procedures.
When progressing in problem solving complexity using Power Query, dealing with the M language it is inevitable so that we can use functions that are not available on the user interface and optimize queries for faster refreshing. Melissa De Korte talked about how to debug, solve and avoid the most common errors in Power Query.
Important to note that Power Query is available also in Power BI and other Microsoft programs and platforms.
DAX
DAX is the language that allows us to create custom measures in Power Pivot in Excel to then use in pivot tables for our reports. DAX is also part of Power BI. Chandeep Chhabra gave an entertaining presentation on the CONCATENATEX function in Power BI showing us how to evolve in detail using that function to achieve different results. Chandeep won the award for the best in-person presentation.
Power BI
If you read this article until now, you already saw Power BI mentioned a few times. Power BI is one of the natural progressions from Excel to scale reports and enhance data visualization. Many professionals leverage Power BI analyze in Excel to seamlessly integrate datasets, perform deeper insights, and transform raw data into structured, interactive dashboards. The integration of Power BI with other Microsoft tools like Teams, Power Apps, and Excel create a whole new world of possibilities for how data is made available and consumed.
Gustaw Dudek guided us through his recommendations related to the creation of dashboard mockups during the requirements gathering and testing processes of a project, to then transform them into beautiful and efficient dashboards.
Charts
Power BI dashboards are attractive and mindblowing. Interactivity capability is one of the first things that make these dashboards so interesting and useful, along with other Power BI characteristics like the ability to share reports only with different levels of access by user. However, Power BI may not always be accessible to all the teams - lack of knowledge and cost could be two reasons. Chandoo explains that the core functionality that exists in Power BI to allow for the chart interactivity, also exists in Excel, and since Excel is a tool with which most users are more familiar, and it does not require any extra license other than a regular Microsoft license, knowing how to add extra interactivity to the “regular” Excel charts, can be the way to go in some scenarios.
Chandoo exemplified how to create that interactivity on a bar chart in a one step-by-step example. The solution involves loading the data to the data model, using Power Pivot to create the necessary custom measures, creating Power Pivot Tables and applying slicers to those. All these steps may seem a lot to learn for a beginner, but these are the same things that a Power BI developer needs to know if creating the solution from scratch.
By using several chart settings in Excel, Chandoo showed how to create the same visual result that we get on Power BI where, when we filter, we still see the representation of the total values on transparent columns.
Chandoo also showed the behind-the-scenes techniques that he used to create other interactive visuals in Excel. These involved conditional formatting, in-cell checkboxes and several functions, including dynamic array functions like GROUPBY, FILTER, CHOOSECOLS, MAP and LAMBDA.
Charts are useful for decision makers because they can represent your data visually and convey a story, or detect patterns or values outside the norm in a much faster way than when looking through the numbers only.
Anyone in a role involving data analysis, is very likely to need to create charts. The important thing to understand is that a lot of work might be needed before the data is ready for charts to be built. As per Chandoo’s example, we see that building a chart is not just about picking the chart type we want and clicking a couple of buttons. It is about knowing how to clean your data with Power Query first, and then knowing what a data model is and how to build one in Power Pivot, knowing enough of DAX to build your measures, and then bringing Excel functions and features together to achieve the visual effect intended.
Sessions like Chandoo’s presentation provide the big picture to those who are still at the beginning of their learning journey in Excel, showing them the different learning directions they may need to look into to evolve their skills. This session also highlights the power of Excel and the things one can achieve with it when we are knowledgeable and get creative.
Dynamic Array Functions
Dynamic Array Functions and the changes on the Excel engine that allow most classic functions to work with dynamic arrays, make calculations and reporting in Excel much more powerful and dynamic than before. LET and LAMBDA along with all the other new functions open a whole new world of possibilities for analysts and solution developers.
Diarmuid Early explained how we can create our custom functions with LET and LAMBDA. Before we had to use VBA to create custom functions in Excel. With LET and LAMBDA we can now create custom functions that are easily shareable and work in Excel online.
Alan Murray proposed that we “ditch” pivot tables in favour of the functions GROUPBY and PIVOTBY to aggregate and report. These functions allow for automatic report updating without the need for refreshing that pivot tables require. And by combining GROUPBY and PIVOTBY with other functions and Excel features like checkboxes, data validation, custom formatting, filters and slicers, we can develop some very interesting and dynamic Excel solutions.
Automation in / with Excel
Automation is the drive of today’s efficiency and productivity. VBA was for many years the go-to language for programming in Excel (and the other Microsoft applications.) It is still quite used today, but with the rise of moving data and files to the cloud, and the issue of security posed by malintended actors using VBA, Microsoft has been making other languages and technologies available to address the challenges that VBA cannot handle.
Bob Umlas showed how to create a wizard using VBA.
Mark Proctor talked about how to combine Power Automate and Office Scripts with Excel to achieve the dream of automating a procedure without having to click one single button.
Power Automate runs a flow of steps based on a trigger. Some examples of triggers, among many others, are receiving an email, creating a file in a folder and submitting a form. When a defined trigger action happens, the flow will run automatically. Office Scripts is a programming language primarily based on TypeScript, which is a superset of JavaScript. Office Scripts run online, and therefore allow for automation in some scenarios that VBA does not.
What else can Excel do?
Using Excel just for fun was Richard Sumner’s proposal. He showed things he has built in Excel just because it makes him feel relaxed. Pixel art, calculations for game scoring, plotting locations on a map, clocks and unusual charts, board games and sport games simulators, strategy games, learning games for children were some of the examples. Richard invited Excel professionals to push Excel’s boundaries, be creative and not be afraid to try new things. He promises that by doing so, one will learn new things that will become useful when working in a business case scenario later on.
Networking Opportunities
Besides the opportunity to meet some of the Excel Experts you have been following and learning from, you will be able to connect with other professionals from your business industry and different ones, and will get to know how people use Excel in different ways than yours. It can be a great opportunity to expand your network and perhaps uplevel your career. If not for anything else, you will certainly have fun connecting with other like-minded professionals at the event. Networking spaces and dedicated time frames are available for both the in-person and the online experiences of the event.
Other technologies available to expand your work and results in Excel
The Global Excel Summit brings together many different players of the Excel industry. This event is also an opportunity to learn about other tools available on the market that allow you to expand your Excel knowledge, skills and results.
That was the case with Sheetcast, the lead sponsor of the event. Celia Alves’s presentation on Sheetcast’s behalf showed how the Sheetcast add-in for Excel allows us to transform our workbooks into web applications.
Who should attend the event?
As you can see, The Global Excel Summit has something for anyone using Excel and interested in expanding their skills. Data analysts, business analysts, accountants, bookkeepers, office administrators, project managers, financial analysts and other finance professionals, marketing analysts, sales managers, operations managers, human resources analysts and managers, supply chain analysts, administrative assistants, research analysts, investment bankers, budget analysts, customer service managers, logistics coordinators, procurement specialists, quality assurance analysts, real estate analysts and risk managers are only a few of the roles who could benefit from attending a spectacular event like this.
