Excel, Teams, Zoom, PowerPoint, Power BI++ Sep-Nov 2020 new features

My top 5

This quarter we have polls and breakout rooms in Microsoft Teams, the new Edge comes preinstalled with every Windows 10 computer, motion design on PowerPoint with new export as gif options, smart narratives in Power BI and OneNote’s combined notes feed.

PowerPoint, Excel, Outlook & Word

The common release cycles in Microsoft 365 are: Beta channel with pre-release features (previously Insider), monthly channel and the semi-annual channel which gets updates in February & July with a 6m lag (hence none in the timespan covered here). 

Dark mode and new image support

1. Picture enhancements: Insert HEIC iPhone photos & paste svg to these apps directly

2. Dark mode from Windows: Office apps will inherit your system wide choice if you click File> Account> Theme> Use system setting. 

Excel: Next level functions LET & LAMDBA & custom data types++

3. Variables in functions: Previously only for Beta channel, Use LET to avoid repeating formula segments multiple times. 

• a. The function make up is usually LET(name, value, calculation)
• b. A simple example: Define Addnumbers to be B1+B2, then the calculation is Addnumbers+10 which becomes =LET(Addnumbers, B1+B2,Addnumbers+10)

4. Define your own functions: The LAMBDA function allows you to write your own custom functions previously only available via VBA. Add via Excel’s name manager. Beta

• a. Each input in the function can be either a parameter or calculation, then close brackets and immediately open them up again to put in test values. If the test works, paste the formula in Excel’s name manager
• b. Example: 1. Write in a cell =LAMBDA(Value, Change,IFERROR(Change/Value,0))(10,1), then copy it2. Formulas> Name manager> New 3. Paste the formula (removing the test parameters) and name it. 4. From the grid type the name (no tooltips so you must remember how parameters go in)

• c. Combine LET & LAMBDA so VLOOKUP can return “Unassigned” for blank records: =LAMBDA(LOOKUPVALUE,TABLEARRAY,COL,LET(SAFELOOKUP,
  VLOOKUP(LOOKUPVALUE,TABLEARRAY,COL,0),IF(SAFELOOKUP=
  "","Unassigned",SAFELOOKUP)))(E4,T4:U11,2) 

5. Custom data types: Data types allow users to expand a collapsed column, view as a card, refer to hidden columns with formulas or filters. Add custom data types in two ways:

• a. Power BI data types: From Power BI you can retrieve preconfigured data types in any workbook, so you have one version of the truth updated live for e.g., Inventory counts, staff details, store locations etc. Follow the steps in this video to enable it:

• b. Power Query data types: Excel’s Power Query editor allows you to collapse a table into a data type. Right click a column and choose “Create data type”, > “Advanced” and add the relevant columns. 

6. Save shapes as pictures: Also use for charts or a combination of both

7. Action pen: Hand draw text directly in cells, access from the Draw tab.

8. Org chart: Create a Visio diagram in Excel for an org chart, now available for all.

9. Unhide multiple sheets at once: Right click a sheet> Unhide > Ctrl + Click multiple. Beta

10. Conditional format dialog boxes: You can now duplicate a rule or resize the box. Beta

11. Auto-expanding dropdowns: Convert your list into an Excel Table (Insert> Table) before using it as a data validation source and it will expand. Beta

12. Power Query exportable templates: Right click a query on the queries pane> Export connection file, then reuse it in Power Platform dataflows. Beta

Word & Outlook

13. True dark theme: White text on a black background is applied if you choose a “Black” theme in Word and Outlook (but other viewers will see it as black text on white). Beta

14. Text predictions: When typing a phrase there are pre-suggested phrases which appear in grey, pressing Tab can lock them in (also available in Word). Beta

15. In line editing: Inline spelling & grammar suggestions as Editor keeps improving in Word. Beta

Outlook: AI enabled daily briefing & mobile email reader++

16. Play my emails: Click the play button on Outlook for iOS or Android. It reads through key parts & gives context, then archive or flag directly from the same screen

17. Grammar & Style suggestions: Outlook has adopted the improved Editor which Word introduced some months ago, you can now see extra blue & gold lines for suggestions.

18. Signature/settings sync: Now saved on the cloud

19. Translate a phrase: Select text> right click> Translate

20. Daily briefing email: Outlook scans your previous emails for possible tasks which are written in, then you can make them as done, not relevant or add as task. I really benefit from it. The daily email also suggests booking focus time and possible documents for the day’s meetings.

21. Every meeting online: New Outlook meetings now have a Teams link by default.  Beta

PowerPoint: Built in illustrations & videos, and create a moving logos++

22. Export gif improvements: Choose a range of slides when you click File> Export> Create animated gif and optionally take a transparent background. All animations and transitions are maintained, the morph transition makes cool animated logos.

23. Presenter coach on Desktop: Get live feedback on speaking pace, reading the slides, filler words, inclusive language etc. This PPT Online first tool (explained in the video now comes to Windows. Beta

24. Looping videos: Dozens of stock videos set to “loop until stopped” and “play automatically” can be found via Insert>Icons> Videos. They look great on a title slide or in between sections. Beta

25. Illustrations: Artwork in greyscale plus one prominent colour. Click Insert> Icons> Illustrations, then you can change the prominent colour (even colour match a logo with PowerPoint’s eyedropper tool). Beta

Teams meetings: Breakout rooms, polls, externally share recordings ++

26. Breakout rooms: Split participants into simultaneous sub-meetings with this highly anticipated feature great for education and conferences. The meeting organiser can click the new button on the top right menu. Specify the number of rooms and assign people automatically or manually). The Organiser opens the rooms, can move freely between them, and make an announcement to all rooms and close the rooms when done. Participants can use all Teams features within the breakout rooms and return to the main room (unless forbidden by the organiser), a separate chat (with any shared files) is saved for every breakout room. For now, participants cannot move freely, organisers cannot pre-assign participants, move them during the session, or share manage permissions.

27. Share recordings externally: Navigate to the new automatic folder called “Recordings” to access videos & set sharing permissions like any OneDrive file. Recordings folder is in the root OneDrive folder or in SharePoint for Channel meetings. Gradual rollout

28. New Together mode scenes: Noisy backgrounds can detract from a meeting experience so superimpose the people on a communal background (in an auditorium, a bar, underwater). Click 3 dots> Together mode, then on bottom left “change scene” gives new options. Great for meeting photos. 

29. 3rd party apps in meetings: Open the meeting in Teams’ Calendar view and click + to add an app, just like for a channel (only available once the meeting is already created and when at least one person has been added as a participant).

30. Polls in meetings: Add the Forms app (instructions above) so you can poll meeting participants (with single or multi select options). Create first then launch by clicking the Forms icon (1) then “launch” on the side bar (2). Organisers can export results which shows who voted which answer too.

31. Speaker attribution: The Live captions feature (click 3 dots> “Turn on live captions”) how shows who says what

32. Prevent unmute: A new organiser option

33. Spotlight someone: Right click a person (or yourself) then every attendee’s screen is focused on them

34. Noise suppression: Go to Settings> Devices and choose from the options to use this new AI feature. Gradual rollout

Teams general: Tasks, templates ++

35. New status options: Duration options & “Appear offline” choice

36. Share existing OneDrive files: Previously attaching a file in the chat would upload that onto OneDrive instead click the paperclip icon to share

37. Notifications facelift: New look plus you can toggle off “show message preview” on Desktop

38. Multilingual spellchecker: Automatic detection of language for suggestions

39. Pinned posts & files: All can see at the top of a channel. Click 3 dots by a post or file> Pin

40. Tasks in Teams: Add Planner as an app in Teams to combine tasks from  Microsoft To Do, Outlook and Planner in a table view in the new “Assigned to me” view. Personally, I find this is still quite limited compared to Outlook tasks and flagged emails do not yet sync

41. Pre-built templates: Create a new Team with predefined channels, tabs, and apps. Admins can also create their own templates as well as Microsoft’s defaults

Zoom: Share screens, breakout rooms enhancements & view enhancements++

42. Share multiple screens: Simultaneously share. Toggle which to see or use dual monitor view & see both. Invited participants can bypass waiting room however

43. Security changes: Either waiting room or passcode is mandatory & end to end encryption can be turned on from online settings (but must be done by every participant)

44. Noise suppression: Like in Teams you can choose from auto, high, medium, or low

45. Self-select Breakout Room: Participants can freely

46. Multi pin/spotlight: Up to 9 people will show for yourself (pin) or all attendees (spotlight)

47. Custom view control: Hosts can broadcast their gallery view so attendees see their layout

Office Online Apps: Forms on mobile, OneNote feed, Lists app++ 

48. OneNote feed: Get all your notes in one place as it syncs from Sticky notes, Android notes and Outlook notes (which can then link from your iOS notes). Click the top right button in OneNote to open the Feed pane. 

49. Forms on mobile: Office iOS/Android app now includes Forms files

50. Share Forms with specific users: Select specific users to share Forms with

51. Print blank form: For filling out separately

52. New Edge is default browser: The look and feel of Google Chrome with closer integration to Microsoft 365 apps, faster page loads, better security and the innovative Collections feature, key features are explained in this video:

53. New Microsoft Lists app: SharePoint Lists with a cleaner UI inspired by 3rd party apps like Air Table. Navigate through your app launcher or add as a Teams App. Lists’ requires a defined data type for each column (including Excel’s options plus multi select drop down lists, date pickers, images and more), switch views, add formatting. Consumers can enter data through either a grid view or a form. Excel is compared to Lists here:

Google Sheets: One click column stats, and AI clean up tools

54. Column stats: This nifty tool on the Data menu displays a bar chart & stats for a selected column. Screenshots show how the text & numerical columns which differ, explained with the Explore feature in this video:

55. Cleanup suggestions: Select data, Data> Cleanup suggestions, Sheets uses AI to identify possible errors like duplicates, excess whitespace, similarly named fields (UK vs United Kingdom).

56. Smart Fill: Type two or more text entries and Smart Fill will pre-suggest formulas for you. It can use text transformations like LEFT, MID, FIND and even VLOOKUP. Excel’s Flash Fill works with more variations but Sheets suggests the formulas keeping it dynamic.

I could not replicate some documented outcomes when testing but I am confident they will evolve.

Power BI: Total labels, zoom sliders, anomaly detection, auto-narratives ++

You must turn on preview features from the options menu for certain tools.

57. Helper watermarks: Find helper icons on page 1 before you add content like on PowerPoint, or even add some sample data

58. Total labels for stacked charts: Previously you could only add data labels for each point but now you can add a total label in the formatting pane

59. Q&A updates: The AI based question & answer visual now supports arithmetic (e.g., “sales” less “costs”, exporting data and partial matches (e.g., Alsace sales can retrieve Alsace France sales)

60. Visual zoom slider: Consumers can zoom into a date range in a timeseries chart. Continuous numerical fields are less useful. Toggle on/off the zoom slider on the format pane.

61. Rectangle selection: If you want to select a few points on a chart you can lasso select by holding down control. Preview feature

62. Auto table detection: If you source data from Excel or JSON, Power BI will pre-suggest what looks like a table and apply required steps (remove top rows, columns etc.) to get you there faster. Preview feature

63. Bind slicers to parameters: Selecting a slicer can changes a Power Query parameter. Turning this on can be tricky & only works for direct query. Preview feature

64. Updated model view: Updated icons, and several configuration options for the table cards. Preview feature

65. Anomaly detection: From a timeseries line chart, click the analytics pane (1) then “Find Anomalies” (2). Once enabled, specify the sensitivity % to see how much of an outlier a point must be to qualify (3). You may get insights into your data by choosing which dimensions could cause anomalies (4), and finally click on a teardrop in your chart to launch the anomaly pane (5) which gives ideas of what may be causing the anomaly. Preview feature

66. Smart Narratives visual: Auto-generate a written summary. Access in two ways, right click a visual> Summarize for that specific visual or add from the visualisations pane to summarize the whole page. You can delete a paragraph that is not useful and even add your own (which could contain dynamic values). Underlined values are dynamic depending on any applied filters. Preview feature

The last three (as well as other AI tools in Power BI) are featured in this video:

That is all for now, but we are be expecting dynamic views & PowerPoint Live in Teams, small multiples in Power BI, and enhancements to Edge in December 2020.