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

My top 5

1. New Excel text functions TEXTSPLIT, TEXTBEFORE and TEXTAFTER
2. New Excel array functions to resize, reshape, stack, and select. E.g., CHOOSECOLS & VSTACK
3. PowerPoint’s Cameo lets you be inside the slide when presenting in any online meeting
4. Write custom functions in Excel’s semi-annual channel with LAMBDA
5. Shared channels in Teams lets external people access a channel without switching tenants

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 2208, which is the year 2022 and month 08 (August) with 09 (September) coming soon whilst the semi-annual channel received an update in August to version 2202 (all updates until February 2022), both channels received updates so we will explicitly say which are for which version. The images show features by release cadence, “Next semi-annual channel” is the current channel list, and the Insider or Beta features are only available for a small selection of people who sign up to help test features, but they give a good idea of what is coming next.

Excel Windows current channel

It has been a busy quarter for Excel which now publishes a digest every month, June, July and August.

1. Analyze data enhancements: Ask about trends, pick from alternative questions & more improvements, read here for more. Yes its spelled the American way.

2. Aggregate with Power BI connected PivotTables: Use SUM, AVERAGE etc. on numerical fields and COUNT for text fields

3. Three new text functions:

1. TEXTAFTER will extract every character before a specified delimiter, previously this was only possible with a combination of the RIGHT, SEARCH and LEN functions. Options for error handling and skipping an instance number, (e.g. everything after the 3rd ; from the start or end) are available. I generally recommend ending with “” (an empty string) for the “if not found” clause to avoid errors.

1. TEXTBEFORE does the opposite of TEXTAFTER. No diagram is included.
2. TEXTSPLIT splits a comma delimited list (or space delimited) across multiple cells by the delimiter, this will return a multi cell dynamic array (like the “Text to columns” feature but formula controlled), options exist to split into rows, ignore empty, case or pad. This diagram shows these more advanced scenarios.

4. Array stacking functions:

1. VSTACK appends different arrays vertically e.g. to combine several tables into one master table. Ensure the columns are in equivalent places first. Note that blanks are converted to zeroes, add an IF formula around it to get around this.
2. HSTACK does the same horizontally, useful for reordering columns

5. Reshape Arrays with WRAPCOLS, WRAPROWS, TOROW, TOCOL. Convert one column of cells to a rectangle with multiple rows or columns with WRAPCOLS/WRAPROWS, add padding to control what empty cells in the region show. Alternatively convert from a rectangle of cells into one single row or column with TOROW, TOCOL respectively.

6. Reorder arrays with CHOOSECOLS and CHOOSEROWS: Its often helpful to select columns and reorder them when using SORT and FILTER function. With CHOOSECOLS first specify the array, then the reference number of the column you want to appear first, followed by the next and so on. Use CHOOSEROWS in the same way.

7. Resize Arrays with TAKE, DROP and EXPAND: TAKE will keep specified rows. =TAKE(Range,3,-2) will return the top 3 rows and first 2 columns from the right, =TAKE(Range,-3, 2) will return the 3 bottom rows and 2 leftmost columns (note when positive and negative numbers are used). DROP will remove specified rows in the same way. EXPAND (which I personally haven’t yet found a use case for) will make an array larger by padding it with selected text. =EXPAND(Range, rows (must be > rows in dataset), columns (must be > columns in dataset), What to pad with, leave blank for #NA or specify what you wish, but use speech marks if its text.

8. New capabilities from new functions: Some use cases are in videos below including getting a filtered list with chosen columns, creating a table of contents page and more.

Excel semi-annual channel:

9. Smooth scrolling: Scrolling down through rows with large height, scroll doesn’t jump anymore

10. Write your own Excel functions: If you want some non-existent functions, you can create your own with the LAMBDA & it’s six helper functions. I have used this for DISTINCTCOUNT, TEXTJOINIF and others I created. A deep dive is in one of my previous blogs.

11.Currencies data type: Type USD/GBP and click Data tab > Currency, Excel will return the current price & other info. This feature isn’t new but has its own data type now it was previously incorporated into the Stocks data type. Also note that Wolfram data types have stopped being available to all Excel users.

Other Excel platforms

Excel for the web

12. Share link to range: Right click a range of cells > Get link to navigate better

13. Protected ranges: Click Review > Manage protection to lock certain parts from editing. Its simpler yet offers more options than the Desktop version offering ways to add the unlocked range from the menu directly and easily pause or stop it.

14. Search in Pivot Fields list

15. Control Slicer connections: Click Slicer > PivotTable connections

16. Link to another workbook: Click on a cell in a different workbook within a formula to link them as we do on Desktop

17. Group queries: From the connections pane, right click to group queries and refresh an entire group (which cannot be done from Desktop)

18. Format by character: Change each character’s format rather than just at cell level

19. Edit legacy files: Edit files made using legacy data connections or Shared Workbook features

20. Delete chart elements easier: Click on an aspect and press delete on your keyboard

21. Sort by colour & icon: From filter menu see these new options

22. Formula bar on multiple lines

PowerPoint semi-annual channel

23. Accessibility tab: Click Review > Accessibility checker, and a new tab appears in the ribbon. Accessibility checker can find missing slide titles, poor contrast & more

PowerPoint monthly channel

24. Lock objects: Right click an object and lock it so you don’t accidentally move it when working on a slide.

25. Cameo: You can add an object to a slide which is a camera of you, and it will appear when you present. This can be useful for sharing your screen to present on any online meeting app (Google Meet, Zoom, Teams etc.). This video explains how to set it up.

Word Semi-annual channel

26. Track just my changes: Switch it on just for you from Review > Track changes dropdown

27. Inline editor: Right click a selection of text and review spelling/grammar etc. just for that selection

28. Rewrite suggestions: Right click a sentence or phrase for search suggestions

29. Voice search: Find commands or items by voice by clicking the microphone in the search bar

Outlook 

30. Notifications pane: Click the bell on the top right to show things like flights, deliveries, mentions in email or apps (Word/Excel etc.) is new for current channel users

31. Immersive reader: Make email reading more accessible, by changing colours, spacing and more is new in the Semi-annual channel

32. Loop components: Previously in Teams, now you can add them to emails with Outlook from Web or the Insider version of Outlook Desktop. Choose your Loop component (Checklist, bulleted list, numbered list, paragraph, table, task list or Q&A). This can then be edited by others either inline in Outlook, in Teams (and updates appear in the same place or on a web browser. The full Loop experience is expected to launch soon, but for now these components work well.

Zoom

33. Breakout room enhancements: Broadcast mic to all & search for participants

34. Persistent gallery: If you have a layout of people in certain places that you like, reuse it later

35. Whiteboard enhancements: New shapes, dark mode & more

36. Stop others saving the in-meeting chat: Admins can allow for enhanced security

37. Zoom profile cards: Click a person’s name and see their card including time zone details

38. Reuse video filters: Set colour filters, frames, and foreground effects, to apply to all future meetings

39. Transcript improvements: Speaker attribution in download transcript & multi-language translate & captions in webinars.

Other apps

40. Forms – Convert a PDF or Word file to Forms: Click “Import” from the Forms homepage and then edit to fine tune

41. Forms – templates: Click from categories on the Forms homepage for prebuilt forms, then edit to fine tune

42. OneNote - solve maths equations: Type 3x+9=15, then Insert > Math (which in UK means maths) to solve for x (x=2 in this case) including the steps. Use with complex problems too

43. OneNote - draw tab updates: Undo/redo or change the background from this updated tab

Google Suite

1. Google Drive – cut & paste files online & see file location for each file

Google Tasks – star important tasks

3. Google Docs – Assign a task within Docs

4. Manage Docs notifications: Choose from options from Tools >Notifications

5. Edit Office Docs offline. Work offline mode now applies to Excel, Word, and PowerPoint files /p>

6. Connect Google Sheets to BigQuery: Grab data from BigQuery into Google Sheets

Google Meet

7. Pin multiple videos: See them always and picture in picture mode when working elsewhere.

8. Anonymous replies to polls: An option when creating poll questions

9. Live stream: Broadcast to YouTube Live

Teams

10. Join meeting via ID or Passcode: Do this from web, desktop or mobile

11. Shared channels: Collaborate with people outside your organisation without the cumbersome method to switch tenant back & forth, all will be shared, files, chats, different tabs. An external member must first be added onto your Azure Active Directory. More information is here on Shared Channels and here on adding external people 

12. Chat with self: Send files and other things between devices efficiently and securely

13. Better browser meetings: Live transcripts, dynamic views, and meeting controls on top to catch up with the Desktop app

14. Collaborative annotation: Click the pen to launch annotation on any shared screen. Draw around items you click or even doodle and let collaborators do the same.

15. Remove from history: Remove a call from the call history list

Power BI

16. Conditional format data labels: Red labels if low, green if high etc. or even link it to other measures. Click the Fx button next to the data label colour

17. Table visual navigation improvements: Pg up/down shortcuts work as well as Home and End for first/last. Improved selection is also available with shift & up/down arrows.

18. Header tooltip formatting: Apply Bold, Italics or Underline to the text in header tooltips

19. Metric Visual in Power BI Desktop: Add a visual from this Power BI premium feature

20. Error bars: Set up/down arrows either as an absolute value, percentage, percentile, or standard deviation or alternatively use your own custom measures. Add image 

21. Data in Space: Point your mobile phone to certain places and get a PowerBI pop up augmented reality experience (that is associated with that geo location). This must be set up by your admin, read more here.

An update heavy quarter for Excel in particular ends there, check out the next bog in three months for new updates, where we expect some great updates to Google Sheets, Excel, Teams and hopefully the collaboration enabling new Microsoft Loop application.