Excel, Teams, Power BI, PowerPoint, Zoom, G Sheets, Meets: New tech features for Dec 20-Feb 21

My top 5

a. STOCKHISTORY: Get historical currency rates & stock prices directly in Excel

b. Power Automate Desktop: A macro recorder for your entire computer

c. Vertical tabs in Edge: Switch to vertical tabs to track many tabs

d. Pinned posts in Teams: Pin something for the entire channel

e. More stock photos: Office apps have thousands of photos, icons & now illustrations

Core Office Apps

New features are only available to the subscription version e.g., Excel 365. Within that there are a few release cadences with the monthly channel and semi-annual channels being the most common. To see or update your version, click File> Account. The monthly channel should now be on version 2102 (2021 month 02 with 03 coming soon) whilst the semi-annual channel received an update in Feb to version 2008 (all updates until Aug 2020). All updates listed for Excel, PowerPoint, Word, or Outlook relate to the monthly channel unless otherwise stated. Certain updates are for semi-annual subscribers and other updates are mentioned in previous blog posts.

Excel (monthly channel updates)

1. Power Query renames sheet: Loading a query will now rename the sheet to that query

2. Conditional format updates: Resize the pop up “Manage rules” box & duplicate a rule

3. Unhide many sheets: Right click sheet name, then unhide and Ctrl Click to multi-select

4. STOCKHISTORY function: Get historic exchange rates and stock prices with Excel’s take on the GOOGLEFINANCE function. Specify the stock ticker/currency pair, then the date & get the close price. Further customisations allow you to get other metrics (open, close, high, low or volume or even multiple), choose between daily, weekly, or monthly and even return a table with prices between a start & end date. I suggest first to type your stock/currency in a cell, then click Data> Stocks (under data types), as this will ensure that it is formatted in the right way, then reference the cell in the formula. The video below shows the approach in more details.

Excel (Semi-annual channel updates previously available for others):

5. Power Query new setting: Disable automatic data type detection for all files with this new option in the Power Query options menu.

6. Workbook stats: Click Review tab > Workbook stats to get an overview of cells with data, tables, pivots charts etc.

7. Sheet view: Filter for only yourself so simultaneous editors can see the data unfiltered.

8. Hex colour: New to the Semi-Annual channel, any “more colours” dialog box across office (fonts, shapes, backgrounds) now supports hex code as well as RGB code.

9. Stock images+: PowerPoint now has thousands of built-in stock photos via Insert> Icons. Recolourable icons (thousands more than previously available), cut-out people (a collection of different actors doing different poses), stickers (cartoon characters doing a variety of poses as you might see in Telegram or other apps) and the all-new illustrations category, these have one prominent colour and greyscale are new for everyone, recolour them and the greyscale stay the same whilst the prominent colour changes. In the last month, the stock photos and cut-out people categories have expanded too. All categories are available on Word, Excel & Outlook too.

10. Export transparent gifs: Motion design in PowerPoint got a big upgrade, you can now export gifs for a range of slides and have a transparent background. Making use of the morph transition, and the new built in looping videos can create immersive visuals social media ads, animated logos etc.

11. Transfer SVG graphics: Copy & paste svg graphics to & from other non-Office programs.

12. Presenter coach enhancements: The built in AI enabled presentation rehearsal coach can now be seen on PowerPoint Desktop for Current Channel & mobile users. Slide show tab> Rehearse with Coach, and it will call you out for presentation faux pas such as use of filler words, sensitive phrases, reading the slides, speaking pace. After your talk you get a report showing your pace over time and a summary of what you did well or could improve on. This feature had been available on PowerPoint Online previously and has four additional features on that platform, it now calls you out for speaking in a monotone voice, repeating words several times and recommends speech refinements. It added the ability to check your body language via access to your camera including eye contact, moving around too much, and keeping your face clear for online presentation situations.

Outlook:

13. Text predictions: Suggested words or phrases appear inline in grey in Word & Outlook, press tab to accept or type over it to ignore it.

14. Icons for file links: Outlook automatically replaces a file link with an appropriate icon for OneDrive/SharePoint files and checks for if the email recipient has access to the file.

Teams:

15. Slide sharing enhancements: If you select “PowerPoint” from the share tray, you can now:

a. Pass control to other presenters
b. See thumbnails of other slides and your notes pane created in PowerPoint
c. Switch to high contrast view for the visually impaired
d. Play animations, transitions, and videos smoothly for participants

16. Meeting link sharing: From the Participants pane, click “Share invite” with a new link to paste into any platform.

17. Emoji meeting reactions: Express yourself during a meeting without words

18. Approvals: Subordinates can make requests which managers can approve, for anything from holiday requests, expense reports, signatures etc. Click … on the left-hand navigation menu to add the approvals app.

19. Browse recent history: Hover over the “Back” button to get a popup with recent history.

20. Channel calendar: Schedule team wide events by adding a shared calendar as a tab.

21. Emails in Files: Drag an Outlook email to the “Files” tab within a channel as you would other documents.

22. Pinned posts: Click the 3 dots on a post within a channel to pin it for everyone in the channel. Retrieving pinned messages is a little hidden, users must click the ! icon on the top right and pinned posts appear. In Teams when you “pin” a channel, a chat or a person’s video in a meeting it only affects you whereas pinned files or posts can be viewed by everyone.

23. Every meeting online: When you click “New meeting”, a Teams meeting link is automatically added, this can be disabled from the options menu under File.

24. Meet now link to Teams: Launch an instant Teams meeting from Outlook. (note these two Teams features are unavailable for non-Teams users).

The last five features (as well as many more handy Teams tricks) are shown on this video:

Power BI

25. Publish to web default page: When you publish a Power BI report from the service publicly, you can choose which page pops up upon opening, and you can even have different links to different default page pop ups, you can also add a placeholder image.

26. Search bar: Power BI now has a search bar at the top just like Excel, Outlook etc. to find a command easily, this unfortunately doesn’t work with format actions from the format pane.

27. Cosmetic changes: The field view, model view and colour picker have been refreshed.

28. Small multiples: One of the top requests since Power BI’s launch is finally available. Multiple visuals can be created in a grid formation which share an axis which is currently supported for bar, column, line & area charts. You can specify the grid size, add borders & background colours. Activate via File> Options> Preview features to enable the feature.

Microsoft Forms

29. Text format: Bold, Italic & Underline your questions or answers

30. Specific user sharing: Make links only available for specific people, like OneDrive files

31. Response receipts: Recipients can save a copy of their form response for future reference, but the setting is off by default. Authors can choose Settings> Allow receipt… to turn it on.

Other Microsoft Apps:

32. OneDrive file size limit: Upload files up to 250MB to OneDrive or SharePoint now

33. Power Automate Desktop: A macro recorder covering your whole computer is now available for free for Windows 10 users, if you have repetitive tasks you can record your actions and a software bot would record them. E.g. copy or mimic a series of clicks between apps, onboarding tasks required every time etc. Download here and learn more here

34. Vertical tabs in Edge: Horizontal tabs can get crowded, click the switch on the top left to switch layout, see icons only or pin the vertical tab pane to see site too.

Non-Microsoft Apps

Google Sheets:

35. Macro converter: This add in will recode Excel VBA script to Sheets’ Apps Script

36. Named ranges: Rename cells or a range in the name box on top left & reference in formulas

Google Meets:

37. Closing the feature gap with Zoom/Teams: Raise hand, virtual backgrounds, mute all & end call for all, breakout rooms and attendance reports are now features within Meets

38. More live caption languages: Auto-transcribe when French, German, Portuguese, and Spanish is spoken (previously available in English only

Zoom:

39. Blur background: For a professional background, blur instead of using a custom background

40. Co-host breakout room privileges: Co-hosts can now replicate all host actions

41. Share video content: Click Share> Advanced & choose “Video”. Play/pause/mute etc. controls are built into Zoom and the optimal video settings are enabled automatically.

Coming soon

Ignite is Microsoft’s annual conference on 2 March had some big announcements. Power BI premium per user with enhanced capabilities (such as AI transformations in Power BI and large datasets optimisations) will be priced at $20 per user per month whilst Teams is introducing a host of new meeting features (pun intended) including low data mode for meetings in low bandwidth, enhanced reporting on meeting attendees, view only webinars with preregistration and “presenter mode” which overlays the presenter on top of slides with their background removed. The much requested “shared channels” which cross organisation collaborators can join and get full feature access to without switching to a guest account will be released later this year.