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

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.

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.)

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.

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.

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.

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.” 🙄

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

Neat knowledge graph data

What’s up with all these join types? Right Anti???

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

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

Take a picture of a table and it converts to a table in Excel!

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.

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

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.

Testimonials

It’s often said there are no shortcuts in life. Sure, maybe this is true. But with KeyCuts I’ve learned how to manipulate formats and basic functions in Excel such as autosum, sorting, and paste special without using my mouse and cutting down the time I spend in spreadsheets.

I used Excel on a PC extensively at my previous job, but became a Mac user for the first time when I went to business school. KeyCuts has been super helpful in teaching me how to use the shortcuts I had become reliant on in Excel for Mac. Truly a great product!

I really enjoy using my KeyCuts keyboard cover! In addition to providing helpful Excel shortcuts for my Mac, it protects my keys from wear and tear that typically happens when I carry my laptop from place to place. I get so many compliments on my keyboard cover that I’ve started gifting them to clients!

KeyCuts has helped expedite my work because the shortcuts are ready at my fingertips. It’s also helped me transition from a PC to a Mac. The cover is thin and tactile and protects my computer. Love the neon pink — it adds flash and gets compliments.

I’m in a position where I must frequently use both Macs and PCs. Prior to KeyCuts, if I had to do anything with spreadsheets, I would just have to dig up a PC and turn it on. Now that I have KeyCuts, my productivity is worlds better, and I feel like I’m finally able to get my money’s worth out of the Mac.

MISSION KeyCuts makes you faster and more productive on the job with Excel keyboard shortcuts. Through keyboard cover products, classes, and custom consulting, KeyCuts will help you become the Excel guru you've always wanted to be.