New Microsoft features 2019 Q4: Excel, Teams, Power BI

New Microsoft features of 2019 Q4 & Jan 2020

A new blog post with a quarterly digest of an exhaustive list of new features across the Microsoft board, Excel, PowerPoint, Outlook, Word, Teams, Power BI, OneDrive, Forms, OneDrive Windows etc. with accompanying images, gifs and videos from my YouTube channel. This doesn't include bug fixes nor every single feature, and with a lot of ground to cover I’ll just scratch the surface on each feature.

My top 5

It's a comprehensive list so I’ll start by introducing my top 5 new features, I dive deeper into each in their own section.

  1. Enhanced home view in Excel, PowerPoint & Word that allows you to collapse doc templates & recommended documents, a small update but I use this dozens of times per day
  2. PowerPoint’s AI-enabled presentation coach feature to practice talks with you
  3. Being able to record Teams meetings which automatically get transcribed using AI and are searchable. As you can use the "Join now" function from Calendar, I’m starting to ask why not do this every meeting always!
  4. Power BI’s ability to customise a theme
  5. Excel’s workbook statistics feature for a quick overview of how many formulas, PivotTables, etc.

Microsoft Office

Microsoft Office features are only available if you have the subscription version e.g. Excel 365, and within that, there are many release cadences ranging from semi-annual to multiple times per week with Office Insider. To see your version, click File> Account. Blue items across MS Office are covered in this video:

Excel (Blue items are shown in the above video)

               

  1. Workbook Statistics: A simple feature in the Review tab shows you some details about your Excel file, such as shown in the image below. Most are self-explanatory. One of note is “End of sheet”, If you have a large file size, this may be a large number letter!
  2. Column profiling in Power Query: You can switch on this option to see the breakdown of values vs nulls vs errors, and to get some distribution charts based on text values (like a Pivot Chart),
  3. Excel Online’s Sheet view: Collaboration is great but if two people are in the same table and one filters it, the other gets lost. Sheet view allows you to create a view which only you see (selecting filter & sort options), screenshot below.
  4. Q&A: Ask a question about your data and Excel returns a table, chart or value. This feature is a subsection within the “Ideas” feature. Click Home> Ideas and you see a sidebar. If you are part of the Office Insiders program, you should see “Ask a question about your data”. You can ask things like:
  • Sales by country: This would create a table with a sum of the values
  • Top 5 countries by sales for bags in a bar chart: This filters the data for product = bags, returns top 5 sorted and puts in a bar chart.
  • Average sales for bags in Plymouth: This returns a single value
  • Median sales for bags in Plymouth. This interestingly does a MEDIANIFS calculation that isn’t available in Excel neither through Pivot Tables nor functions. DISTINCT COUNT works too!

  1. XLOOKUP (labeled the successor to the iconic VLOOKUP) just came out of pre-release and was released for Office 365 users in the last breaths of Jan 2020! XLOOKUP asks to specify a "lookup value", "lookup array" and "return array", so it is superior to VLOOKUP in a few ways. 1. It can look right to left, or left to right. 2. It doesn't break if you add new columns, 3. It can act to replace an HLOOKUP and a VLOOKUP, 4. It defaults to exact match and has more elaborate options for approximate lookup, 5. You can specify an "If not found" value. On the negative side though it can be slower to write as you must select your table array twice, rather than once with VLOOKUP.
  2. Office Scripts (a new take on the age-old Macro feature for Excel Online) was announced to be coming soon.
  3. The game-changing Dynamic arrays were finally released on Office 365’s monthly channel. A formula can now return multiple cells in its results, and it comes with 6 new functions notably FILTER, UNIQUE and SORT. Check out my video on Dynamic Arrays’ 5 best and 5 worst aspects. I won’t go into depth on these huge features but you can read more on these last three features here

Outlook (again blue items means video above has demos)

  1. Enhanced notifications for Comments in Word, PowerPoint & Excel
  2. New search: A new search bar that allows new kinds of filters (one of my favourites is filtering for emails that only contain xlsx attachments).

Word

  1. Follow up: Word may finally have a decent solution for placeholder text. Select a few words and you can right-click for either “Follow up” with placeholders that make the text visible and searchable on a tab on the Find pane.
  2. Rewrite: You also have on right-click, “rewrite” for some cool AI fuelled inspiration.

.                         

PowerPoint

  1. Export as GIF: Last feature released 30 Dec 2019. You can now export a presentation as a GIF file, go to File> Export > GIF video export options has been available for a while.
  2. Ink replay: You can run ink replays as animations on slideshow mode
  3. Search in Icons: I absolutely love this feature, it’s the 2020’s professional answer to Clipart. They are vector images meaning you can change the colour & resize and they don’t become pixelated.
  4. Design Ideas keeps getting better. Now it can look up HD images & use clever AI fuelled cropping to give you a slick design based on keyphrases, see gif below for demo.
  5. Presentation Coach: PowerPoint Online has a spectacular new feature that uses AI to listen to you rehearse and provide feedback in a number of areas such as pace, reading the slides, filler words (like urm, basically, etc.) and inclusive language. I had a lot of fun testing out the feature out, screaming swear words or non-inclusive terms at my computer, e.g. say “you guys”, “manpower” and for some reason “master” plus other terms I won’t write here and you’ll get told off!) I love the Presentation Coach feature and have used it over 20 times for myself plus 10+ other presenters just in Jan 2020, the video below explains more.

                              

All Office apps

  1. Sensitivity labels: You can now tag a document with a sensitivity label if it's confidential (with certain characteristics)
  2. Save as picture: You can now “Save as picture” from Word and Excel. This has been doable from PowerPoint for years, and I use it frequently there as it’s my de facto graphic design app.
  3. Accessibility features have been enhanced across the products.
  4. Home collapsible sections: I really love this feature. The File> Home section has collapsible sections (I collapse new doc & recommended) and right-click options (see gif below) .

 

Teams

Teams received some upgrades with more planned soon.

  1. Private channels: You can add private channels under a Team and invite-only some but not all Team members to them
  2. Pinning: You can pin Teams, channels or other features to have them show on top.
  3. One post, multiple channels: Publish the same post in multiple channels.
  4. New meeting features: You can now mute meeting chats or assign presenter & attendee roles in meetings
  5. Enhanced external user features: I’ve been waiting for this for a while, you can now share screens, give/take control of screens, @mention people, change formats & priorities and add GIFs.
  6. Teams – record meeting: When in a meeting on Teams, click the 3 dots then “Start Recording”. This records your voice and visuals from the screen (great for screen shares). When the meeting ends, it automatically gets uploaded to Microsoft Stream which not only stores the video but also uses AI to transcribe your entire meeting and makes it searchable! The voice recognition isn’t perfect but good enough for searchability. I have started doing this on many of my meetings, even face to face ones where I pop open Teams solely to record the meetings (create an arbitrary meeting from the Calendar in Teams and choose "Meet Now"). Supported languages (updated in Oct 2019) are English, Chinese, French, German, Italian, Japanese, Portuguese and Spanish.

Power BI

  1. Personalise visuals pane: If you don’t like some built-in visual types, but you always use certain custom visuals like Box & Whisker or hierarchy slicer, you can set your default visuals with right-click options. Advanced tip: If you remove a default visual but occasionally want to use it again without resetting all visuals, you can make it in the Q&A visual (see below) then convert it to a normal visual!
  2. There is a new ribbon in preview, which is a flat design similar to the other Office Apps ribbons.

  1. Customise theme: Have default company theme colours, fonts, etc. You can set up your own theme within Power BI under “Customise theme”. This feature is less sophisticated than you get in e.g. PowerPoint but still useful.

  1. Extract "Web from examples" allows you to extract data from URLs or images now which has a cool use case that I mention in the video below.
  2. AI transformations in Power Query: If you are lucky enough to have Power BI premium, the query editor has AI transformations such as sentiment or key phrase extractions on text or image recognition for images.
  3. You can amend some extra format settings for the KPI visual
  4. Clickable URL formats can be applied to columns in a table or matrix
  5. Conditional formatting continues to improve with the capability being added to “buttons”
  6. Query Diagnostics in Power Query can identify slow queries. It’s still a primitive feature that is hard to use but will simplify over time.
  7. Data profiling grouping: Power Query’s data profiling now can group dates, text lengths and + or – numbers, works in both Power BI and Excel.
  8. Q&A reimagined: Rather than a button, its now a visual type with many customisation types that you can train and monitor users frequently asked questions.
  9. Decomposition tree visual: One of the most highly requested features for Power BI uses AI to break down a measure into certain dimensions. E.g. Sales for female may be 63% but how does this number change across regions, and you can drill down further e.g. region = Glasgow may be 35% female sales, year 2018 for Glasgow may contribute to the low value, but how does it break down by product type. The video explains all three AI visuals including Q&A plus Decomposition tree

     

Note: Some of these are “Preview features” so you may have to click File> Options> Preview features to enable them.

Other Apps

  1. New Edge browser: Microsoft Edge was re-released based on the same engine as Google Chrome. So far it seems to be similar except that my Edge runs faster, the search bar is bigger and you can personalise your look & feel, many new exciting features are planned. You need to download it manually for now, but it’ll be installed as part of Windows installations soon. It is also cross-platform working on Windows 7-10, Mac, iOS & Android.
  2. Search in Windows: An updated Search box gives you Search history & suggestions
  3. Microsoft Flow became Power Automate to fit better within the Power Platform together with Power BI & PowerApps.
  4. The Power Platform has a fourth member "Power Virtual Agents" which can create AI chatbots with no code
  5. Excel has a new add-in called “data visualiser” which creates a Visio diagram based on an Excel table
  6. Stream now allows capabilities to trim videos directly within the app and replace a video but keep the same URL.
  7. OneDrive file request: You can send someone a special link that requests them to enter files in that link, consider a “request for proposal” link you can ask people to upload things in there for instance.
  8. File upload in Microsoft Forms: “File Upload” is a new question type, but it only works if you share it within your organisation.

We'll be back soon with new features of 2020 Q1!

Anonymous