Excel 2024 includes several powerful features most users never touch: the IMAGE function and in-cell pictures, 14 text and array functions such as TEXTSPLIT, VSTACK, TAKE and CHOOSECOLS, charts that follow dynamic arrays, LET, XLOOKUP's extra arguments, multi-sheet unhide, Workbook Statistics and the Accessibility ribbon. Copilot and Python in Excel are Microsoft 365 subscription features, not part of Excel 2024.
Written by the Softkeyworld technical team · Updated October 2026
Most people use perhaps a tenth of Excel. SUM, a few filters, the odd VLOOKUP, and copy-paste for everything else. Excel 2024, the perpetual version included in Office 2024 and Office LTSC 2024, ships with a set of tools that can replace whole afternoons of manual tidying, yet they stay invisible because nothing on the ribbon advertises them.
Every feature below has been checked against Microsoft's documentation for the perpetual Excel 2024 release. Some arrived new in 2024. Others came in Excel 2021 and carried forward, but they are still unknown to most users. We have left out anything that only exists in the Microsoft 365 subscription version, and we explain that difference at the end.
Key takeaways
- The 14 new text and array functions in Excel 2024 replace most of the helper columns and Power Query steps people use for reshaping data.
- Charts can now follow dynamic arrays, so they resize as your results grow or shrink.
- Small quality-of-life tools such as multi-sheet unhide, Workbook Statistics and hex colour entry save time every day.
- Copilot, Python in Excel and newer functions such as GROUPBY are subscription-only.
At a glance: the 15 features
| # | Feature | First in perpetual Excel | Best for |
|---|---|---|---|
| 1 | IMAGE function | 2024 | Product lists, catalogues |
| 2 | Pictures placed in cells | 2024 | Sortable tables with photos |
| 3 | TEXTSPLIT | 2024 | Splitting imported text |
| 4 | TEXTBEFORE / TEXTAFTER | 2024 | Extracting names, domains, codes |
| 5 | VSTACK / HSTACK | 2024 | Combining monthly sheets |
| 6 | TAKE / DROP | 2024 | Top-N lists, trimming headers |
| 7 | CHOOSECOLS / CHOOSEROWS | 2024 | Reordering report columns |
| 8 | TOCOL, WRAPROWS and friends | 2024 | Reshaping grids and lists |
| 9 | Dynamic charts | 2024 | Dashboards that resize |
| 10 | LET | 2021 | Readable, faster formulas |
| 11 | XLOOKUP's hidden arguments | 2021 | Last-match lookups, clean errors |
| 12 | Unhide multiple sheets | 2021 | Inherited workbooks |
| 13 | Workbook Statistics | 2021 | Auditing unfamiliar files |
| 14 | Hex colour entry | 2021 | Brand-accurate reports |
| 15 | Accessibility ribbon | LTSC 2024 (incl. Pro Plus) | Shared and public documents |
Pictures and images
1. The IMAGE function
=IMAGE(source, [alt_text], [sizing], [height], [width]) pulls a picture from a web address straight into a cell. The source must be an HTTPS URL to a supported image format. Sizing 0 fits the image to the cell, 1 fills it, 2 keeps the original size and 3 uses your own height and width. The practical win: point it at a column of product image URLs, for example =IMAGE(B2,A2,0), and you get a visual catalogue that sorts and filters like ordinary data. Always fill in the alt text argument. Screen readers use it, and it shows when an image fails to load.
2. Pictures placed in cells
Floating pictures are the reason product sheets break when you sort them. Excel 2024 can place a local picture inside a cell instead: Insert > Pictures > Place in Cell. You can also convert an existing floating picture by right-clicking it and choosing Place in Cell. The image then behaves as cell content. It moves with sorting, filtering and resizing, and stays inside its table row.
The new text functions
Microsoft first announced these in its text and array functions announcement. They reached perpetual Excel with the 2024 release.
3. TEXTSPLIT
Text to Columns as a formula, and it updates when the source changes. =TEXTSPLIT(A2,", ") splits a comma-separated cell across columns. Add a row delimiter as the third argument and a single cell can become a full table. Set ignore_empty to TRUE to skip doubled delimiters, which are common in exported CRM and e-commerce data.
4. TEXTBEFORE and TEXTAFTER
These replace the old LEFT/FIND/LEN combinations. =TEXTAFTER(A2,"@") returns the domain of an email address. =TEXTBEFORE(A2," ",-1) uses a negative instance number to return everything before the last space, which is handy for removing surnames or file extensions. The final argument, if_not_found, lets you return a blank instead of #N/A.
The new array functions
5. VSTACK and HSTACK
VSTACK appends ranges on top of each other, and HSTACK places them side by side. For a workbook with one sheet per month, =VSTACK(Jan!A2:D100,Feb!A2:D100,Mar!A2:D100) builds a live quarterly list without copy-paste. Wrap it in FILTER to drop blank rows: =FILTER(VSTACK(...),INDEX(VSTACK(...),,1)<>"").
6. TAKE and DROP
=TAKE(SORT(A2:C500,3,-1),10) gives a top-10 list in one formula. Negative numbers count from the end, so =TAKE(A2:C500,-5) returns the last five rows, which is useful for "latest entries" panels. DROP does the opposite and removes rows or columns. It is ideal for stripping a header or a totals row from a stacked range.
7. CHOOSECOLS and CHOOSEROWS
Need columns 1, 4 and 2 of a 20-column export, in that order? =CHOOSECOLS(Table1,1,4,2). No hidden columns, no rearranging the source. CHOOSEROWS works the same way for rows, and negative indexes count from the end.
8. TOCOL, TOROW, WRAPROWS, WRAPCOLS and EXPAND
These complete the set of 14. =TOCOL(B2:M20,1) flattens a grid into one column and skips blanks (use 2 to skip errors, 3 for both). Combined with UNIQUE, it is the quickest way to get a distinct list from a messy rota. WRAPROWS goes the other way and turns a long list into a grid, for example =WRAPROWS(A2:A61,4) for label sheets. EXPAND pads an array to a fixed size, which keeps print layouts stable.
Smarter formulas and charts
9. Charts that follow dynamic arrays
According to Microsoft's What's new in Office 2024 page, Excel 2024 charts can reference dynamic arrays and "automatically update to capture all data when the array recalculates". In practice, build your result with FILTER, SORT or UNIQUE, then chart the spilled range. When the filter returns 8 rows instead of 12, the chart follows. Before this, you needed named ranges built from OFFSET to get the same effect.
10. LET
LET names parts of a formula so you write them once. =LET(sales,SUM(B2:B100),cost,SUM(C2:C100),(sales-cost)/sales) is easier to read than repeating the SUMs, and Excel calculates each named value only once. On large models we have seen this make a noticeable difference to recalculation time, and it makes formulas far easier to hand over to a colleague.
11. XLOOKUP's hidden arguments
Many people switched to XLOOKUP but only use its first three arguments. The fourth, if_not_found, replaces IFERROR wrappers: =XLOOKUP(E2,A:A,C:C,"Not listed"). The sixth, search_mode, set to -1, searches from the bottom, so it returns the most recent match in a log. VLOOKUP simply cannot do that.
Quality-of-life tools
12. Unhide multiple sheets at once
For years, unhiding meant one sheet at a time or a macro. Now, right-click any sheet tab, choose Unhide, and Ctrl-click or Shift-click several sheets in the dialog. It is invaluable for inherited workbooks with a dozen hidden lookup tabs. Note that sheets set to "very hidden" through VBA still will not appear.
13. Workbook Statistics
Review > Workbook Statistics shows the number of sheets, cells with data, formulas, tables, PivotTables, charts and comments, for the current sheet and the whole workbook. It is the first thing we open when auditing an unfamiliar file or working out why a workbook has grown to 40 MB.
14. Hex colour codes
Brand guidelines give hex codes, not RGB triplets. In any colour picker, choose More Colours > Custom and type the code, such as #0A3D62, into the Hex box. It works for cell fills, fonts, shapes and chart series, so reports match your brand exactly.
15. The Accessibility ribbon
Microsoft lists the improved accessibility tools for Office LTSC 2024, the release that Professional Plus belongs to. Select Review > Check Accessibility and Excel opens a dedicated Accessibility tab alongside the checker pane. It gathers alt text, cell styles, table names and colour tools in one place. If your workbooks go to customers, councils or public bodies, it is the fastest way to catch missing alt text and low-contrast colours before someone else does.
Putting them together: a worked example
These functions are most useful in combination. Here is a task we see all the time: three monthly order exports on separate sheets, each with columns for order ID, customer email, product and value. You want a single list of the ten largest orders, showing only the customer's domain, the product and the value.
- Stack the months:
LET(all,VSTACK(Jul!A2:D500,Aug!A2:D500,Sep!A2:D500), ...) - Remove empty rows:
clean,FILTER(all,CHOOSECOLS(all,1)<>"") - Sort by value and keep the top ten:
top,TAKE(SORT(clean,4,-1),10) - Return the domain, product and value:
HSTACK(TEXTAFTER(CHOOSECOLS(top,2),"@"),CHOOSECOLS(top,3,4))
Written as one LET formula, that is a single cell that updates whenever a monthly sheet changes. Point a bar chart at the spilled result and, thanks to dynamic chart support, the chart stays correct as well. In older versions the same job needed helper columns, a manual sort and a fresh copy-paste every month.
What Excel 2024 does not include
Microsoft is explicit that a Microsoft 365 subscription contains everything in Office 2024 "along with many more features". The headline differences for Excel:
- Copilot in Excel requires a Microsoft 365 subscription with Copilot access. It is not part of perpetual Excel 2024.
- Python in Excel runs in Microsoft's cloud and is a Microsoft 365 feature.
- Functions released after the 2024 feature set, such as GROUPBY and PIVOTBY, are subscription-only. Perpetual versions receive security updates, not new features.
That is a real trade-off, not a reason to avoid either option. If you want AI features and continuous updates, the subscription makes sense. If you want a fixed, fully featured Excel with no recurring cost and support until October 2029, Office 2024 is the better fit. Our comparison of Microsoft 365 versus one-time Office purchases covers the wider decision, and our Office 2019 vs 2021 breakdown shows what earlier versions lack. One timely note: Microsoft lists Office 2021 support as ending on 13 October 2026, so if you are still on 2021, now is the time to plan the move.
Which perpetual edition you choose matters less for Excel than you might expect. The new functions and dynamic charts are part of Excel 2024 itself. If you only need Word, Excel, PowerPoint and Outlook, and want the option of installing on either a PC or a Mac, Office 2024 Home and Business is the lighter option. Professional Plus adds Access and the LTSC-specific extras.
Frequently asked questions
Does Excel 2024 have TEXTSPLIT, VSTACK and TAKE?
Yes. They are among the 14 text and array functions Microsoft lists as new in Excel 2024. Excel 2021 and older perpetual versions do not have them.
Is Copilot included in Office 2024?
No. Copilot requires a Microsoft 365 subscription. Office 2024 is a one-time purchase without AI features.
Can Excel 2024 run Python?
Not through Python in Excel, which is a Microsoft 365 cloud feature. You can still use external Python tools to read and write .xlsx files.
Does the IMAGE function work offline?
IMAGE retrieves pictures from HTTPS URLs, so it needs an internet connection to load them. For offline files, use Insert > Pictures > Place in Cell with local images.
Will files using these functions open in older Excel?
They open, but cells using new functions show #NAME? in versions that lack them. Share values (Paste Special > Values), or confirm the recipient has Excel 2024 or Microsoft 365.
How long is Excel 2024 supported?
Microsoft's lifecycle for Office 2024 and Office LTSC 2024 runs to 9 October 2029, with security updates throughout.
Our recommendation
If your work involves cleaning exports, building reports or maintaining shared workbooks, the 2024 functions alone justify upgrading from Office 2019 or 2021. Office 2024 Professional Plus includes Excel alongside Word, PowerPoint, Outlook, OneNote and Access. Our Office 2024 Pro Plus installation and activation guide takes you from download to first launch, and if you are setting up a new PC as well, the Office 2024 Pro Plus and Windows 11 Pro bundle covers both in one order.
Related reading
| Resource | Why read it |
|---|---|
| Office 2024 Pro Plus setup guide | Download, install and activate Excel 2024 |
| Microsoft 365 vs one-time Office | Subscription or perpetual: which suits you |
| Office 2019 vs Office 2021 | What older versions are missing |