My top 5
- New Excel text functions TEXTSPLIT, TEXTBEFORE and TEXTAFTER
- New Excel array functions to resize, reshape, stack, and select. E.g., CHOOSECOLS & VSTACK
- PowerPoint’s Cameo lets you be inside the slide when presenting in any online meeting
- Write custom functions in Excel’s semi-annual channel with LAMBDA
- 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
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:
- 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.
- TEXTBEFORE does the opposite of TEXTAFTER. No diagram is included.
- 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:
- 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.
- 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
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.
ZoomNote some are automatic, others need to be turned on by administrators.
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.
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
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
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
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
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.
Join the Excel Community
Do you use Excel in your organisation? Are you using it to its maximum potential? Develop your skills and minimise spreadsheet risk with our Excel resources. Membership is open to everyone - non ICAEW members are also welcome to join.
- Excel, Teams, Power BI, PowerPoint, Zoom, G Sheets, Meets: New tech features for Dec 20-Feb 21
- Excel, Teams, Zoom, Google Workspace, Power BI++: New tech features for Mar-May 21
- New tech features for Jun Aug 21
- Microsoft Office 2021’s new features (Excel, PowerPoint, Word & Outlook)
- New tech features for Sep 21 - Feb 22
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.