Excel, Teams, Power BI, PowerPoint, Zoom, G Sheets, Meets: New tech features for Mar-May 2022

My top 5

1. We can now author queries in dataflows and then load them into Excel
2. Excel conditional format rules do not fragment anymore
3. Power BI’s field parameters mean we can make a measure slicer or a field slicer
4. Pageless mode in Google docs will pave the way for scrollable docs without page limits
5. Create Loop components in Teams chats, edit them without copy/paste & amend

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 current channel and semi-annual channels being the most common. To see or update your version, click File> Account. The current channel should now be on version 2205 (2022 month 05 (May) with 06 (June) coming soon) whilst the semi-annual channel received an update in Feb to version 2108 (all updates until Aug 2021), but since the semi-annual channel was not updated in the three-month period, we cover only the current channel updates here. This new monthly digest is a great resource.

Excel Windows current channel

1. Get data from Dataflows: If you have Power Queries which you want reused across workbooks then data flows is the perfect way to do that, its essentially a Power Query online which you can then bring in to Power BI files and now into Excel files, but you do need to have a Power BI pro license (or premium) to work with dataflows. I cover it on my video here:

2. Get data from Dataverse: Should your org use this, Excel can retrieve data from here

3. format rules no longer fragment: Classically, when you had conditional formatting applied to say cells $A$3:$A$10, and you did certain actions like inserting, pasting other cells, pasting formats, the range would change and you may see $A$3:$A$5,$A$6:$A$10 or something similar, and if you use the file over time it could get very messy. Now it won’t anymore in these scenarios, and it will keep the range $A$3:$A$10. The main exception that I have seen is if you copy cells without conditional formats to an area which has it then these newly copied cells will not get the formats applied unless you paste special and choose the “All merging conditional formats” option.

4. Auto-refresh on stock data types: You’ve been able to get current stock prices or exchange rates in Excel for a while with linked data types. You can set it to refresh every five minutes (or change the settings). Type in stock or currency names into cells then click Data tab > Stocks (under the data type menu). Next right click on it and choose “Refresh settings” to get this popping up. Note this works the same well on Excel Desktop & Online

5. Faster filtering: Behind the scenes changes mean large filter lists work faster

6. Office Scripts button: If you use Office Scripts to run script, Excel Online’s version of VBA then you can add a button from Excel Online and when the file is opened on Excel Desktop it can run the script.

Other Excel platforms

7. Web external link improvements: External links can now be changed or broken in Excel for the web like in Desktop

8. Web refresh Queries: You can refresh certain Power Query queries.

9. Mac Find all: The Find dialog box now looks the same as Windows with a “Find All” option

10. Mac Power Query editor: The beta channel of Mac now includes the full Power Query editor, which should have been announced to be coming to Excel online soon. This has the same look as Power Query Online (which is within Dataflows on Power BI Online).

11. Record improvements: PowerPoint revamped its recording studio, click Slide show tab > Record to launch it, you will now see:

• Top recording bar
• Export options to make a video directly from this record view including a 4k video
• Navigation & inking options
• View toggle: Slide view, presenter view or the new teleprompter view
• Video options: With options to show or hide your background

Word current channel

12. Dictate in comments: The dictation option, which was previously unavailable in comments, now works well.

OneNote current channel

OneNote design overhaul: OneNote for desktop now looks more like Office & Windows 11. You’ll see colour scheme changes, new icons, and the Share button on the top left as we’re used to from other apps. Note this is rolling out gradually so you may not see it yet, but when it does you may also see:

13. Better inking: If you use inking within Office then OneNote is getting a big upgrade to be similar to other Office apps. You now have a ruled (like PowerPoint), ink to shape, the ability to define your own pens etc.

14. Dictation: As in Outlook, Word, and PowerPoint you can now dictate via your voice instead of typing.

Teams meetings:

15. Presenter mode control: When you share your screen using one of the presenter view options (which allow you to be in front of your content), you can now resize your presenter video or move it from right to left. Because you need to do this whilst presenting, it works best if you are working with two screens.

16. Firefox improvements: If this is your browser of choice, you can now screenshare including audio.

Teams non meeting features

17. LinkedIn integration: On the chats of each person you will see a LinkedIn tab (in addition to the previous ones Chat, Files, Organisation etc.) allowing you to browse their LinkedIn directly without leaving Teams.

18. Suggested Replies in chat: As in other chat apps, Teams will propose options for replies.

19. Loop components: From any Teams’ chat click the doughnut-like loop icon, then choose which component you would like (Task list, paragraph, table etc. see more on the list below). This can then be edited either inline in Teams (and updates appear in the same place or on a web browser (soon Outlook will also allow editing inline).

Zoom

20. Central polls library: If you regularly ask the same poll question, store it here then reuse, a personal favourite of mine as I feel polls should be heavily featured across meetings

21. Breakout room enhancements: Hosts can now share audio with content to all breakout rooms and monitor activity from the main room

22. Raise hand & thumbs up gesture: When you raise a physical hand or give a thumbs up, Zoom will do the electronic version for you.

23. Avatars: Becoming very metaverse, you can now apply a premade avatar effect to your video

24. Twitch livestream: Adding to the previous options for Facebook & YouTube.

25. Invitee list in Participants pane: As with Teams, you can now see the invitees who have not attended on the participants list

26. Webinar enhancements: If you use the paid Webinar version of Zoom you can now brand your session, allow for emoji reactions (like you have in meetings) and hosts can move to backstage view to discuss things outside of what is being shown to participants

Google apps for business Google Docs

27. Pageless view: In a world where we rarely print things anymore why are things paginated? No more having to worry about page breaks or images which are too wide, click File> Page set up to set it up.

28. Multi select text & format: Like in other apps you can now select different sections of text and apply format changes to them.

29. Shortcuts for bold, hyperlinks etc.: If you type double ** before and after text or __ that makes the text bold and italic respectively. It also works for strikethrough, headings, hyperlinks etc. see more details here.

30. Type @Dropdown: This will generate a user defined dropdown list in your file. Google is adding to its @gestures first in Docs then in Slides & Sheets at a slower pace

31. Emoji react to suggestions: As well as comment on an addition, you can emoji react for a quick sentiment.

Google Sheets

32. Up to 10M cells: Sheets’ cell limit is doubled to 10M now, far fewer than Excel’s but should be enough for almost all use cases.

33. Formula autocorrect: Sheets’ will intelligently suggest a formula change if it seems like you’ve made an error, examples include INDEX MATCH instead of VLOOKUP, locking cells using $ or extending a SUM range over omitted cells

Other Google Apps

34. New files count towards storage: Newly created files using Google Docs, Slides, Sheets, Jamboard, Forms, or Drawings & existing files modified since May 2022 will count towards your limitations.

35. Booking appointment screen: If you’re tired of the needless back & forth to find a suitable time to meet, you can now create a webpage whereby others (external or internal) can see your available slots and then choose one, get an email confirmation & appointment slot and if relevant a Google Meet online link. I have personally used this feature with Calendly (a third-party app) for over five years and it’s been very helpful. Outlook has a similar feature in testing stages at the moment. Note this isn’t available to all. Google Workspace Enterprise Standard & Plus, Business & Plus, Nonprofits & education packages have access to it. Power BI Desktop Key updates are listed here, but for all updates see here. Items with * are preview features that might need to be enabled via File > Options > Preview features on Power BI Desktop.

36. Canvas zoom: A slider on the bottom right allows you to zoom in/out as you can in other apps

37. Error bars*: Add up/down error bars to line or column charts to indicate the margin of error, add these with fixed numbers of via a formula in the analytics pane of each visual.

38. Field parameters*: This fantastic feature allows you to make a slicer of category fields or measures so that in one click you can switch total sales to profits, or alternatively you can switch between a breakdown by region to a breakdown by product. Click Modelling > New parameter > Field to get started. This is definitely changing the way I build Power BI as I’ve been using measure slicers with a heavy workaround on almost every report I build*

Power BI (Other)

39. Datamarts – Power BI desktop but online: A Power BI premium license holder is able to create a new Datamart, which replicates much of the Power BI Desktop experience but via web browser access including Power Query, creating table relationships, writing measures using DAX (but no calculated columns or tables) as well as the usual experience to build visuals.

40. Embed PBI live into PowerPoint: You can insert a Power BI page into a PowerPoint slide that you can interact with without leaving PowerPoint. You need to install a PowerPoint add in first though, from PowerPoint, click Insert > Get add-ins > Power BI then insert the object that will ask for a URL. Next go to the Power BI service to fetch the report URL. This has all the interactions you expect but the main downside is you cannot navigate across pages, but you can insert a page navigator visual for that instead. More is explained in this video:

This wraps up the quarter, Check out next quarter where Loop (mentioned above - now available in Teams) should get an upgrade next quarter, along with some exciting new functions in Excel which have already been released for the Beta Insiders and the semi-annual channel will get their six-monthly update.