In case you missed the Microsoft Ignite 2018 conference, there were a TON of new features launched for Microsoft Excel (granted you need to have a certain level of subscription on Office365 to take advantage of the new features). I did a recap of all the sessions related to Excel from the Ignite conference, hope you get a chance to watch the sessions!

Summary of New Excel Features

• Dynamic arrays to replace array-entered formulas ー a pretty big paradigm shift in how you will write formulas (start getting used to the #SPILL error message 🤣)
  • To make this more complicated, cell reference in dynamic arrays will contain hashtags (#)
• Get and Transform your dat in Power Query is super powerful when combined with the Flash Fill feature ー predicts how you want to transform your data and let’s your preview the transformation before you actually commit it
• Reduced file sizes due to dynamic arrays (only write formula once) and optimized lookup formulas  🙌
• New data types seem interesting only if you’re a stock broker (tickers) or economist (geography/census data)

Experience the power of Excel Online anywhere

• Access anywhere, similar interface, working with the latest version
• Shareable link with permission setting → again, Google Sheets anyone?
• Threaded comments and @mentions
• Simplified ribbon toggle, thank God!
• Can create data validation and PivotTables now in Excel Online
• Bottom line: Google Sheets had these features 5 years ago. seemed like it’s just to get parity with Google Sheets

New functions and calculation capabilities in Excel

• Introduction of dynamic arrays is a pretty big paradigm shift for Excel users
  • Formulas can “spill” into other cells with a blue border around the spilled cells denoting which cells are in the “spill range”
  • A little bit like array-entered arrays with CTRL+SHIFT+ENTER because you can’t overwrite a formula in the spill range → not all cells in the spill range have a formula
  • Spill range referencing → instead of doing H5:H8, you do H5# to get a dynamic reference (crazy!)
• 7 new functions:
  • FILTER – Take data from your table and filter on a certain dimension. Results in spills vertically and horizaontally
  • UNIQUE – Unique data set from table
  • SORT – Does what you expect it to do. FILTER and SORT part of calc engine.
  • SORTBY – Used in conjunction with RANDARRAY to randomly sort a list of values
  • RANDARRAY – Good for Monte Carlo simulations. Random values by x rows and y columns.
  • SEQUENCE – Generate a sequence of numbers from 1 to n
  • SINGLE – Didn’t cover in the video

10 Excel features you wish you’d known earlier

Power BI, Excel, and Office 365: Unlocking value of enterprise data

• The obligatory data-driven quote to tee things up: “Customers want to gain business insights to make better data-driven decisions.” 🙄
• Lifecycle of crunching data to sharing analysis with your team: Excel → Power BI → Office 365 (e.g. Teams – Microsoft’s version of Slack)
• Pretty cool to see your Excel files and Power BI reports inside of Teams
• If you see a chart from a Power BI report that you want to drill down into, you can open the source data model from the cloud inside Teams
• The Power BI report was created through Power Query Editor, feels like a more user-friendly version of writing macros
• As a data modeler, you have to know when seeing data in Power BI is better than Excel (and vice versa) → basically dependent on the number of rows of data
• Cleaning data in Power Query is similar to cleaning date in OpenRefine

Be a data ninja: Best practices for analytics using Microsoft Excel

• Good tip: “Keep data, calculations, and visualizations separate” → foundation for building any web app 👍
• Questionable tip: “Bing is your friend: search and you will find.” → I get it, it’s a Microsoft conference
• Flash Fill definitely gets rid of the need to do text manipulation involving CONCATENATE, MID, FIND, LEFT, and RIGHT
• New data types have been hyped for some time which pulls external data from the Microsoft Knowledge Graph
• While pulling data into Power Query looks easy, there are multiple join types which can be confusing if you don’t know SQL
  • Once it’s set up though it’s a great substitute for VLOOKUP as it’s much easier to reconcile data and find rows that don’t match between two tables
  • Probably makes sense when you have hundreds of thousands of rows. If it’s a small dataset, Power Query is overkill
• The Dynamic Calendar Pattern is pretty neat where you use Power Query.
  • Allows you to create a self-updating table instead of creating a static table where you’re incrementing days one at a time with a formula
  • Still a lot of data cleansing going on just to get a dynamic range of dates

What is new in Microsoft Excel

• TL;DR: Transform and Flash Fill are probably the coolest features to help you cleanse your data and do some basic ETLing
• Excel is on 500 million devices worldwide each month, 1.5 billion file opes per day, 20,000 formula commits per second (!!!)
• Insert data from picture is a new paradigm where you don’t do data entry, but rather fix data issues with the table returned from the picture (since it won’t be perfect)
• Dynamic arrays actually reduce the file size since you’re only writing one formula that is “spilled” to other cells
  • No more dragging and filling down your formulas to the last row in your data set
• New data types are geography and stocks; performance on loading 500+ stock tickers was pretty fast! You just enter in the value like any other value but as long as Excel knows the cell is one of the new data types, Excel “converts” the value to a rich data type
• Pulling external data via “Get Data” from a 3rd-party has increased the number of connections including MySQL db, Postgres db, JSON, SAP HANA, etc.
• The “Transform” feature combined with Flash Fill makes it easy to create new columns of merged data for novices with the “Column from Example> in Power Query. You can then see the actual Flash Fill formula that results from the smart autofill
• Performance improvement on lookup formulas! Showed an example of a spreadsheet that would take ~9 minutes to calc with all the lookup formulas that got cut down to 3-4 seconds with the new version of Excel
  • Made a comment about how the saved time leads to better energy savings overall
• Working on private views!!!
• Discussed the “strong GitHub integration” where you can put in the URL of GitHub link to get a custom function from the repo
• You can stream real-time data into Excel 😃, showed a video of an Office customer in the inventory tracking business showing data getting streamed into Excel

Advanced analytics in Excel: Get deep insights from your data including AI-powered capabilities

• Really great point about tools adapting to humans instead of other way around;
  • Today: Expect people to learn and adapt to software and tools
  • Tomorrow: Expect software and tools to adapt to people, and accelerate their job to mastery
• Interesting question about new data types: what does it mean for two data types to be equal to each other?
  • The ticker data type is an object consisting of key → value pairs
  • When comparing two MSFT ticker objects together, for instance, you’re comparing all values of the objects together
  • Looks like the common pattern here is you’ll need to constantly refresh your data to ensure you’re not comparing old data with new
• Context for new data types is important for whether Excel will convert the value to a new data type
• Will Ideas make lives easier for analysts? Seems like this AI feature generates nice charts, but perhaps it will merely inspire analysts to create the charts they really want to create?
• Looks like formatting doesn’t “spill” over with dynamic arrays, need to tweak your formatting around the “largest expected size”
• No more A1:A20-like reference with dynamic arrays, you need to start using the hashtag (#) ー funny that hashtags typically denote errors in Excel