Excel, Teams, Power BI, Zoom, Google Sheets++: New tech features for Dec 2022-Feb 2023

• Read the previous update

My top 5

a. IMAGE function in Excel’s current channel
b. TEXTSPLIT and VSTACK for Excel Semi-annual channel users
c. Power Query editor in Excel is now available for Mac
d. Data validation rule manager & coloured chips in Google Sheets
e. In-meeting translations for Zoom, Meet and Teams premium

Core Office Apps

New features are only available to the subscription Microsoft 365 version. 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 2302, which is the year 2023 and month 02 (February) with 03 (March) coming later this month, whilst the semi-annual channel received an update in February to version 2208 (all updates until August 2022), so the updates are reported here. The Insider or Beta features are only available for a small selection of people who sign up to help test features, so they haven’t been reported explicitly, but they give a good idea of what is coming next.

Excel Windows current channel

Excel publishes a digest every month, December 2022, January and February 2023.

1. Chart from dynamic array: If the array grows or shrinks, the chart will follow, but, as opposed to Pivot Charts, arrays work with Excel’s newer charts too.
2. Assign comment: Assign a task after you @mention them in a comment, they get notified on Outlook.
3. =IMAGE function: Convert an image URL into an image inside a cell. It unfortunately doesn’t work for custom images, even SharePoint/OneDrive images and can sometimes a #BLOCKED! error appears if Microsoft thinks that you don’t have access to it, (even if Google Sheets’ IMAGE function works). Optionally, add Alt text and specify size. An image can be passed through a function (e.g. VLOOKUP) but for now, filters and PivotTables will show the Alt text instead of the image. More is in the video below.
4. Generate QR codes: A cool use case for IMAGE is generating a QR code from any website with =IMAGE("https://chart.googleapis.com/chart?chs=300x300&cht=qr&chl="&CELL)

Excel Semi-annual channel 

5. Get data from dataflows: Paid Power BI accounts’ dataflows (created via Power Query Online) can now be reused across multiple Excel via this new connector
6. Get data from Dataverse
7. Smooth scrolling: Excel will no longer jump to the next complete row
8. Home > Analyse data enhancements: “Did you mean”, some pre-set suggestions and improved their pattern detection in this feature
9. 3 new text functions:
   a. TEXTSPLIT breaks up text by delimiter into multiple cells
   b. TEXTAFTER extracts based on delimiter, previously only achievable with complex nested functions such as =RIGHT(SEARCH(LEN…), combine with CELL to get the linked worksheet name in a cell. TEXTBEFORE is also new.

10. 11 new array functions including:
    a. VSTACK can append tables similar to Power Query, useful to append the header row onto a FILTER output.
    b. =CHOOSECOLS(Range,4,2,1), will start with the 4th column, then 2nd, then 1st. HSTACK can achieve the same but reference the ranges (HSTACK is also a horizontal VSTACK).
    c. =TAKE(Range,3,-4) will keep the first 3 rows and last 4 columns, DROP will remove the first N and keep the rest. =CHOOSEROWS(Range,1,6,3) keeps those rows in that order and EXPAND will grow an array to a specified size.
    d. TOCOL/TOROW will convert a 2D array into a single column/row.
    e. WRAPROWS/WRAPCOLS converts a row/column into 2D matrix with specified dimensions.

The Jun-Aug 2022 update article here went into more detail on these also shown in the videos below.

11. New combinations: The new Excel functions allow new concepts such as populating an output table based on parameters.

Excel for Mac

12. Power Query editor: Seven years after Windows, Excel for Mac now has a Power Query editor. All Windows features are available plus certain new ones (Rank column, Cluster column, Mark column as Key) plus an additional Schema view option. Mac also broadened their Power Query data sources but currently has fewer than Windows (pdf, folder and others are missing).

Word Current channel

13. Assign comments: Similarly to Excel, you can assign a comment after @mentioning them.
14. Editor on selection: Right click some text and choose Editor to check only that selection for spelling, grammar and other aspects.
15. Toggle edit/review mode: On the top right, you can now toggle between Editing, Reviewing and Viewing mode, Reviewing merely turns on the “Track changes” feature, but is easier to access, this mimics the Google Docs experience.

Word Semi-annual channel

16. Track just your changes: Click Review > Track changes > Just Mine to activate
17. Text predictions: As you write, Word attempts to autocomplete for you, if you find this distracting, you can disable it from the status bar on the bottom left of your screen.

18. Lock objects: To block moving objects, right click it > Lock objects.
19. Recording studio: Click Slideshow > Record to get a new window, features not previously available include a teleprompter view (with slide notes) and blur background.

20. Loop components: Create paragraphs, lists, tables etc. that can be edited inline (or on a website) without replying to the email. Feature enhancements will come when Microsoft Loop is fully launched.

21. Notifications pane: Click the bell on the top right to see notifications based on customisations.

Teams

22. Notifications pane: Click the bell on the top right to see notifications based on customisations.
23. Any emoji reaction: A large library to emoji react to messages
24. Enhanced @ mentions: @Everyone tags all or add people to a chat @their name
25. Stream to Meta Workplace Live: Live stream meetings and webinars
26. Co-organisers can manage breakout rooms: Assign the role in Meeting Options
27. Call back option: After missing a call, a call back option is available
28. Multiple question polls: Multiple questions can be launched at one time. This is often done unintentionally though (e.g., when the user intends to launch multiple polls)

29. Microsoft Teams premium is now available with these standout features:
    a. Live translations for up to forty languages
    b. New meeting templates (client calls, brainstorming etc.)
    c. AI generated chapters in recordings and personalized timeline markers when your name is mentioned
    d. Branded colours and custom backgrounds in meetings
    e. Watermarking across the shared content and additional privacy
    f. A virtual green room & other webinar feature enhancements
    g. Coming soon features include a chat GPT AI generated meeting notes and task list

Enable Teams Premium for a discounted price of $7 per user per month (for a year) which rises to $10 after 30 June 2023.

Microsoft Forms

30. Images in question & choices: Add any image to a question or to choices in a choice question
31. Additional theming: More theme options are available
32. Background music: Choose for music to be playing when respondent is completing the form

Zoom

Note that certain features are for free customers, others are for paid versions only.

33. Auto in meeting translation: translate speech between over 20 languages, more here.
34. Enhanced in meeting chat: Reply to messages, send images and emoji react to them
35. Bigger breakout rooms limits: Up to 100 rooms for up to 1000 people
36. Poll dependent rooms: Assign to breakout rooms based on results of polls
37. Follow the host: Meeting hosts can force participants to see their exact view of the gallery.
38. Participant captions: Enable captions without the host
39. Speaker attribution in captions: Meeting captions specify who said what
40. Branded waiting rooms: Add an image to the waiting room
41. Custom human avatars: Create video game-like avatars to replace your video feed
42. Countdown timer: Indicate how long is left in the meeting
43. In meeting poll edits: Create and edit polls inside a meeting, rather than on Zoom’s website
44. Q&A in regular meetings: Enable this feature (previously only for webinars) via zoom settings online.

Google Meet & chat

45. In-meeting reactions
46. Include captions in a video recording
47. Live translations: Business packages allow more meeting auto-translation languages French, German, Portuguese and Spanish can be translated into English, and from English to the same as well as Swedish, Chinese or Japanese
48. View notes: See Google Slide speaker notes when presenting
49. All Google Spaces chats threaded: A Teams like behaviour

50. Eyedropper colour: Click the icon to pick up the colour from elsewhere on your screen and apply to text, backgrounds etc. Google has it on Slides, Docs, Forms and Sheets, but Microsoft has it on PowerPoint only
51. Follow someone else when co-authoring in Slides
52. Add code in Docs: Insert > Building Blocks > Code, to be easily copied from there.
53. Pin table headers in Docs: Right click the table header row, then pin, similar to freeze panes in spreadsheets
54. View ¶: In Google Docs choose View > Show non-printing characters such as new paragraphs, breaks, spaces and tabs

Google Sheets

55. Data validation enhancements: See a rules manager like for conditional formatting, colour code dropdown options and toggle dropdowns between arrows and chips, the new default which can look messier for long options

56. Improved CSV imports in Sheets: Split into columns based on ; and improved localisation detection
57. =LET in Sheets: Create variables in a Sheets formula with =LET(name1,value1, [name2/value2], ..., result) to make it more understandable as Excel launched in 2020, more information is here.
58. New Sheets functions: TOCOL, TOROW, VSTACK, HSTACK, WRAPCOLS, WRAPROWS, CHOOSECOLS and CHOOSEROWS work the same as Excel’s mentioned above. Google has equivalents to others besides the rarely used DROP and EXPAND.
    
    

    Excel	Google Sheets
    TAKE	ARRAY_CONTSRAIN
    TEXTSPLIT	SPLIT
    TEXTBEFORE/TEXTAFTER	REGEXTRACT

59. Two additional new functions in Sheets (11 total): =MARGINOFERROR provides details for a given confidence interval and EPOCHTODATE converts unix epoch timestamps to datetime equivalents.
60. Places and dates Smart Chips: Type @picadilly circus to get a pop-up map, type @tomorrow to get tomorrow’s date etc. or enable from Insert > Smart Chips
61. Finance smart chips: Type a stock, bond, commodity or currency pair to get a pop up. Unlike Excel you cannot extract the price or other details into the grid though.

Power BI

62. Slicer type moved to format pane: Toggle between list, tiles, dropdown and date options on format pane
63. OFFSET, INDEX and WINDOW DAX functions: Reference other rows in a table via relative (OFFSET) or absolute (INDEX) references or reference a range with WINDOW
64. New LINEST and LINESTX functions: Perform linear regression with the Excel-like functions
65. Enhanced RLS editor: Row level security (RLS) can now be managed with no or limited DAX
66. Indent text in textboxes
67. New accessible themes: Access high contrast themes via the View menu
68. Customise page navigator: Choose which pages show or are hidden in the navigator, customise via Insert > Buttons > Page navigator > Page options
69. Export single visual to PowerPoint: From Power BI service, click the 3 dots by a visual then Export to PowerPoint, the PowerPoint tool is also now generally available, and can auto create an AI driven written summary of your data from within PowerPoint.
70. Export refreshable table to Excel: From Power BI service, click the 3 dots by a visual to get to this option, more is on the video below.
71. On visual formatting: Double click an item and format that item, chart axis, legend, chart title, bars data labels and more work on bar, column, line, area, combo and scatter charts. It can be fiddly so try clicking around until it shows. Add/switch visuals and open the format from different places too (the preview feature must be turned on though)
72. Remove panes: In View tab you can select or deselect any pane including format, data and filter panes
73. Subtitles & divider: Add to any visual from the format pane
74. Clear all slicers button: Insert > Button > Clear all slicers to have this on your file

Plenty of updates across the board despite the Christmas period, but the majority were for spreadsheet tools this time round.