Microsoft Ignite 2018 Excel Session Summaries

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
Screen Shot 2018-10-22 at 8.25.03 PM.png

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
Screen Shot 2018-10-22 at 8.36.06 PM.png
Dynamic array formula
Screen Shot 2018-10-22 at 8.36.30 PM.png
Dynamic array spill range

10 Excel features you wish you’d known earlier

1) Quick Analysis – Hover over icons to quickly visualize trends in your data
Screen Shot 2018-10-22 at 8.48.54 PM.png
2) Quick Analysis Totals – Totals icon let’s you get the total, average, count, etc. on the fly
Screen Shot 2018-10-22 at 8.49.58 PM.png
3) Quick Analysis Charts – Looks like a lot of Quick Analysis here. Nothing crazy here, just throw in charts.
Screen Shot 2018-10-22 at 8.52.41 PM.png
4) Forecast – Pretty neat button added to the Data > Forecast ribbon menu. Generates a quick and dirty forecast on the fly using the FORECAST function.
Screen Shot 2018-10-22 at 8.54.37 PM.png
5) Flash Fill – As you start typing in a value to extract let’s say the last name from a full name, Flash Fill detects what you are trying to do and fills it down for you.
Screen Shot 2018-10-22 at 8.58.29 PM.png
6) TextJoin – Pretty neat little function that let’s you take a list of values and put it into a cell separated by a delimiter (e.g. comma, spaces, etc.)
Screen Shot 2018-10-22 at 9.01.15 PM.png
7) Map Chart – Interesting to see Excel considers a map a new type of “chart.” A new button in the ribbon: Insert > Charts > Maps. If your data contains states/countries you can visualize the data on a map.
Screen Shot 2018-10-22 at 9.03.41 PM.png
8) New ways to conditionally format – Not super interesting here, basically adds new ways to conditionally format your list of data. The icon sets are interesting since it let’s you visualize your data as icons/emojis instead of numbers.
Screen Shot 2018-10-22 at 9.06.20 PM.png
9) PivotTables – I’m guessing this session was targeted towards beginner/intermediate Excel users or they ran out of features to talk about? I thought they were going to talk about new features to PivotTables, but it was literally an explanation of how PivotTables work even though they’ve been around for 10+ years 🤔.
10) Slicers – “A filter on steroids.” Just a quick way to get multiple filters on a data set. Good feature for filtering multiple charts along the same dimensions when you’re working on a dashboard.
Screen Shot 2018-10-22 at 9.12.00 PM.png

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

TL;DR: If you are tied to a multi-year Microsoft license, makes sense to utilize all the tools in the ecosystem. Given modern ETL pipelines that rely on open-source tools, the Microsoft ETL ecosystem feels pretty closed and not interoperable with other platforms.
  • 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
Screen Shot 2018-10-22 at 9.44.47 PM.png
Neat knowledge graph data
Screen Shot 2018-10-22 at 9.50.19 PM.png
What’s up with all these join types? Right Anti???
Screen Shot 2018-10-22 at 9.52.55 PM.png
I think I would need to practice this a few times…

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
Screen Shot 2018-10-22 at 10.02.41 PM.png
Take a picture of a table and it converts to a table in Excel!
Screen Shot 2018-10-22 at 10.21.33 PM.png
Transform + Flash Fill = 💲. You can see the results of the Flash Fill in the right-most merged column before you actually commit the formula. At the top of the screen, you can see the Transform formula that gets applied so you can learn about how the Flash Fill formula language works.
Screen Shot 2018-10-22 at 10.35.32 PM.png
Get scripts from GitHub links

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
Screen Shot 2018-10-22 at 10.50.47 PM.png
Context for new data types: Just having “nasdaq” near a value with “MSFT” means the MSFT value will automatically get converted to a ticker data type. Same goes for values you type near already created data types like cities.