100 new Excel features from the first half of the 2020s

My top 5

1. XLOOKUP does more than VLOOKUP AND is easier to use
2. GROUPBY could be Excel’s Pivot Tables 2.0
3. Formulas can now return multiple cells with Dynamic Arrays
4. Data validation autocomplete is great for en-masse data entry
5. See what changes users made cell by cell

Speed up

1. Ctrl Shift V: Paste values only with this new shortcut
2. Alt F12: Launch the Power Query editor faster
3. Right click search: Find any command
4. Unhide multiple sheets: Ctrl click to select
5. Duplicate conditional format rules: From the Rules Manager, you can also resize it
6. *Worksheet table of contents: Click View > Navigation pane
7. Speed up bloated files: Review tab > Check performance will scan for & remove cells which have been formatted but are out of the editable range (which causes bloating)

Data entry & importing

8. Autocomplete dropdown lists: Click Data tab > Data validation > List and select the range with the dropdown as you did previously, but now it autocompletes as you type.
9. *Add checkboxes: Insert tab > Checkbox. Select multiple and press spacebar to tick/untick them all
10. *Add Microsoft Form from Excel: Insert tab > Forms will launch the menu
11. Convert image of table into table: Data tab > From picture. Check/edit before you import

12. Import data from PDF: Data tab > Get data > From file > From PDF
13. Get data from Fabric: Data tab > Get data > from Fabric & Power Platform
14. Stop removing leading zeros & other auto conversions: File tab > Options > Data > Untick all under “Automatic data conversions” to allow entries like 06727161, 5MAR, 52709000000 etc.
15. *Insert image in cell: Cells can now contain images. Insert tab > Picture > Place in cell, give it alt text to be filterable. On right click you can move between the image hovering over cells and being inside the cell. Images display in PivotTables.
16. Type =IMAGE to convert a URL of an image into the image inside a cell.

Formula concepts

17. Evaluate on hover: Select parts of a formula and see intermediate result.
18. *Strikethrough on manual calculations: When using Formulas tab > Calculation options > Manual or partial, any formula not updated is crossed out.
19. Create defined names in a formula: Use =LET(name,…), function inside the formula
20. Dynamic arrays: The Excel team has rebuilt the formula engine from the ground up to allow for one formula to return values in multiple cells. Typing =B2:B5+C2:C5 used to return only B2+C2 in one cell but now it returns values in 4 cells with a blue outline around when selected. A few notes:
1. D3# refers to the dynamic array starting at D3. Top leftmost cell #
2. If any cells have values in the output range or you are in a Table, you will see the #SPILL error.
3. Dynamic arrays can be used as a source for charts or Power Query
4. Many new functions return arrays
5. Array of array calculations are not currently possible
6. Using Ctrl Shift Enter was the old way to return arrays, this is now redundant

21. Create custom functions: Excel is missing some built-in functions but now create your own e.g. to create =COUNTUNIQUE(Range), Go to Formulas tab > Name manager, type COUNTUNIQUE as the name and type the following into the “Refers to” box: =LAMBDA(Range,COUNTA(UNIQUE(Range))). LAMBDA can be tricky to use so I explain more in this video

Analytics

22. *Create a PivotTable-like output using a formula: =GROUPBY(Row_Fields, Values, Function,…). First, choose the field to categorise by, then the values to aggregate and third, the calculation. Later arguments are optional but I recommend showing headers. Although a few minor drawbacks, GROUPBY has certain benefits over PivotTables:
1. Automatic refresh
2. More aggregation types e.g. MEDIAN, ARRAYTOTEXT, or use LAMBDA for custom calculations
3. They work with all chart types. Some (e.g. Map/Box & whisker) don’t work with PivotTables
• This recent ICAEW article has more

23. *Create matrix/two dimensional PivotTables using =PIVOTBY
24. *=PERCENTOF was built for GROUPBY but can be used standalone too
25. =STOCKHISTORY can return the stock price or exchange rate of any asset for any day
26. Set your PivotTable defaults: Set styles, colours, total settings and more via File > Options > Data > The first option allows you to “Edit Default Layout”
27. Stock and currency data types auto refresh: Type in stock ticker/currency pair in a cell, and go to Data tab > Data types. Then right click > Data types > Refresh settings to amend.
28. Enhancements to Power BI outputs in Excel: Extract source tables from Power BI, create implicit measures or define linked data types in Power BI to appear in Excel
29. *Insert Python code: Type =PY or navigate to Formulas tab > Python and type Python code into the formula bar, press Ctrl enter to commit the code.
30. *Create advanced Python’s Seaborn data visualisations: Violin charts, heat maps, correlation plots and many other statistical charts are possible.

31. *QR code template using Python: Click File > New and search for QR code
32. *Random data generator template using Python: Click File > New and search. You can choose your columns from options such as City, Name, Date of birth, company and more.

1. Show changes cell by cell: Right click a cell, range or worksheet > Show changes. Edit history cell by cell will be shown, but the document must be saved on OneDrive/ SharePoint
2. A personalised filter/sort view: User View tab > Sheet view to see a different view to simultaneous editors or save your filter view to get back to easily.
3. Right click cells > Link to this range to share: Recipients will be directed there but can still access the entire file
4. Assign comment: @mention a colleague to assign a task
5. *Filter comments: View only active comments, those assigned to you etc.
6. Sensitivity labels: Set a document to be confidential, private etc. from Home tab > Sensitivity
7. Check accessibility tab: Identify components of your file which may be difficult to digest for people with disabilities (e.g. merged cells, poor contrast etc. by clicking Review > Check accessibility) on a new Ribbon Tab
8. *High contrast colours: On any colour picker, choose to only view high contrast colours
9. New office theme: Microsoft changed its default colour palette, font and shape outline thickness to improve accessibility.

Productivity

42. *Focus cell: Highlight the entire row and column of the active cell from View tab > Focus cell. Find & Replace triggers this too.

43. Improved cross workbook link dialog: Access via Data tab > Workbook links
44. Smooth scrolling: Your view no longer jumps if your row/columns are too wide for the screen
45. Ink gestures: Turn handwriting into data with Draw tab > Action pen. Making certain shapes can trigger Excel actions. Strikethrough deletes and circle cells to select
46. Use Java like coding with Office Scripts: Click the new Automate tab to access. Compared to VBA, this code works across more platforms but is currently missing several features.
47. Record macro with Office Scripts: Automate tab > Record Actions
48. Excel Live in Teams: When screen sharing a spreadsheet in a Teams meeting, you can use Excel Live and attendees can move around the spreadsheet as they wish
49. Power Query fuzzy cluster column: Using Table.AddFuzzyClusterColumn you can create a new column which will make similar text the same, e.g. France, france, frane, Franc all become France and you can specify how similar they need to be.

50. Stock images: Word, PowerPoint, Outlook and Excel now have thousands of stock images, icons, cutout people, stickers, cartoon people and illustrations on Insert tab > Icons.
51. Gifs can play on spreadsheets: Previously, the static image would be displayed
52. Set transparency in images: Click an image > Picture format > Transparency
53. Use hex code to set colour: Previously, only RGB codes were valid
54. Use a sketched line around a shape: A new shape outline option
55. Intact conditional formatting ranges: When you insert or paste new rows, conditional format rules no longer fragment (for the most part).

AI in Excel

56. Analyze data improvements: Get more patterns, clarification questions and more when using Home tab > Analyze data. Analyze Data can now also work when you have multi row headers
57. Get data from web can grab non-table like structures: Data tab > From Web now has enhancements that allow you to type examples so it can use AI to detect
58. Get AI to write formulas on chat GPT or copilot for free: For best results, be specific in your prompt. Copy the formula it returns and paste into Excel. Write an Excel formula which extracts every character before a space in cell H3 and make them upper case for example “Jack and Jill” becomes “JACK”. For best results, specify in your prompt that it’s an Excel formula, reference the cell and give an example.
59. Copilot paid: Purchase Copilot Pro for £19 per month or Copilot for Microsoft 365 for £24.70 per month and get copilot inside your Excel producing results in context, it works best on a Table or Tablelike structure. You can ask it to write formulas for you, auto highlight, filter, do text analysis and more. This feature is improving over time.

Functions

We have mentioned functions throughout this article, but any not previously mentioned are listed here:

60. =XLOOKUP is a more powerful yet easier VLOOKUP. =XLOOKUP(Lookup_Value,Lookup_Range,Return_Range,[optional_inputs]) has the following benefits over VLOOKUP:
    1. It can look left to right or right to left
    2. It can look vertically or horizontally
    3. Inserting a row or column will not break the formula
    4. The default is exact match, you don’t need to end with FALSE or 0
    5. The fourth optional input is an if not found clause

61. =TEXTBEFORE will extract text before a specified delimiter, you can skip delimiters from the left or the right
62. =TEXTAFTER will extract text after a delimiter
63. =ARRAYTOTEXT converts a range of text values into a comma separated list e.g. Harry, Frank, Ingrid, Jess
64. *=TRANSLATE(text,[language from], [language to]): If left blank, Excel autodetects the from language from and translates to your computer’s default language
65. *=DETECTLANGUAGE will guess the language of text
66. *=REGEXTRACT will extract text based on the complex REGEX language: E.g. it can extract an email address regardless of if it’s at the start, end or middle of the cell. Copilot (free or paid) can help write a formula using this code
67. *=REGEXTEST can test whether certain criteria is met in a cell using REGEX (e.g. does it have a phone number)
68. *=REGEXREPLACE can replace text using REGEX

The following formulas return a range using dynamic arrays

69. =UNIQUE(Range,[optionals]) can return a list of unique values, combine with COUNTA to perform a distinct count.
70. =SORT(Range,[optionals]) can return a sorted data range
71. =SORTBY(Array,By_array,[optionals]) can return a range sorted via a column outside the original range
72. =FILTER(Range, Include,[optional]) can return a filtered data range based on a criteria
73. =RANDARRAY(Rows,[optionals]) can create a random list. The final input specifies if they should be integers
74. =SEQUENCE(Rows, [columns], [start], [step]) can generate a list across rows & columns. By default it starts at 1 and increases by 1 but you can amend the default via optional inputs.
75. =CHOOSECOLS(Array, Col_num1,col_num2,[…]) returns only certain columns of a range in a specified order
76. =CHOOSEROWS does the same for rows
77. =VSTACK(Array1, Array2, […]) will stack tables vertically
78. =HSTACK will do the same but horizontally
79. =TOCOL(array, [optionals]) will reshape a cell range into a column
80. =TOROW will reshape a cell range into a row
81. =WRAPCOLS(Vector, wrap count, [optional]) will reshape a list into the specified number of rows
82. =WRAPROWS will reshape into n columns
83. =TAKE(Array, Rows, [Columns]) will keep the first n rows and drop the rest (where the user has specified the rows (n)). Use a negative number to keep the bottom rows
84. =DROP(Array,Rows, [Columns]) will drop the first n rows and keep the rest
85. =EXPAND(Array, Rows, [Columns], pad_with) will enlarge a dynamic array to specified size. Use pad_with with to specify what to include in empty cells.
86. =TEXTSPLIT will split over multiple cells by a specified delimiter, similar to text to columns but via a formula

LAMBDA helper functions

87. =REDUCE condenses a list of values into a single value with a LAMBDA
88. =SCAN scans an array applying a LAMBDAs to the values and returns an array that has every intermediate value.
89. =MAKEARRAY returns a range by a specified number of rows and columns by applying a LAMBDA
90. =MAP can apply a formula to each value and return the result
91. =BYROW takes an array and calls a lambda with the data grouped by each row
92. =BYCOL does the equivalent for a column

Excel Online only features

The above features have been all for Excel Desktop, but a few features are only in Excel Online at the time of writing

93. Formula by example using AI: Start typing in a column a transformation, and by the 3^rd one, Excel will sometimes guess what it is you’re doing. This works with text extraction, rounding or grouping numbers, extracting month or year from dates and more. It cannot be manually triggered so it doesn’t show up always
94. Aggregation formula suggestions: Type = under a column of numbers and Excel will guess you are trying to sum/average that column
95. Improved formula tooltip: Excel Online gives more information when entering a formula
96. Query pane search: Click Data tab > Queries and connections and get a search box.
97. Query group operations: Perform a query action on multiple queries within a group.
98. Date picker: Double click a cell with a date inside to launch a date picker.
99. Drag to freeze panes: Drag from the rectangle on the top left
100. Rearrange rows & columns via drag & drop
101. Clean data using AI for paying Copilot users: Select a table and click Data > Clean data and Copilot will make suggestions to make text consistent, such as removing whitespace, cONsiSTent capitalisation or similar names e.g. Martin, martyn, and Matin would become the same.

These five years have been immense, but with many new features in Beta, Excel’s not looking to slow down with new feature releases.