Top 5 Hidden Features in Excel 2021 You Are Not Using (2026)

Introduction

Microsoft Excel 2021 includes powerful features that most users never discover, missing opportunities to work faster, analyse data more effectively, and automate repetitive tasks. While many Excel users stick to familiar functions and workflows learned years ago, Excel 2021 introduced significant capabilities that fundamentally change how you can approach spreadsheet work.

These hidden features aren’t obscure tricks with limited application; they’re substantial productivity enhancements that solve common problems millions of users face daily. Whether you’re creating financial reports, analysing business data, managing inventories, or building complex models, these underutilised features can reduce hours of manual work to minutes of automated processing.

The challenge is discoverability. Excel’s interface doesn’t prominently advertise these capabilities, and many users never explore beyond the ribbon tabs they use regularly. Documentation exists, but busy professionals rarely have time to read through feature lists looking for improvements to workflows that already function, even if inefficiently.

This guide highlights five genuinely useful Excel 2021 features that deliver immediate productivity gains once you understand how to use them. Each feature solves real problems with practical applications across business, finance, education, and personal productivity scenarios. By learning these capabilities, you’ll work smarter, reduce errors, and accomplish tasks that previously seemed impossible or required complex workarounds.

1. XLOOKUP: The Ultimate Lookup Function

XLOOKUP replaces VLOOKUP, HLOOKUP, and most INDEX/MATCH scenarios with a single, more powerful and flexible function that’s easier to use and more reliable.

Why VLOOKUP Frustrated Users for Years

Traditional VLOOKUP has significant limitations:

Left Column Requirement: VLOOKUP only searches the leftmost column of a range, forcing you to reorganise data or use complex INDEX/MATCH combinations when lookup columns aren’t positioned left.

Column Number Fragility: VLOOKUP uses column index numbers (1, 2, 3, etc.) that break when you insert or delete columns in your data range.

No Right-to-Left Lookup: VLOOKUP cannot look to the left of the lookup column, a common requirement that forced users to learn INDEX/MATCH.

Error-Prone Syntax: Absolute vs. relative references, approximate vs. exact match parameters, and column counting create opportunities for mistakes.

How XLOOKUP Solves These Problems

XLOOKUP introduces a modernised approach:

Search Any Column: Look up values in any column and return results from any other column, regardless of position.

Resilient to Changes: XLOOKUP references ranges, not column numbers, so inserting or deleting columns doesn’t break your formulas.

Bidirectional Lookup: Search and return values in any direction—left, right, up, or down.

Better Default Behaviour: XLOOKUP defaults to exact match (the most common need), eliminating a frequent source of errors.

Built-in Error Handling: Specify custom messages for lookup failures without nested IF/ISERROR combinations.

XLOOKUP Syntax and Examples

Basic Syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Simple Example:

Find an employee’s department based on their ID:

=XLOOKUP(E2, A:A, C:C)

• E2: Employee ID you’re looking up
• A:A: Column containing all employee IDs
• C:C: Column containing departments to return

With Error Handling:

=XLOOKUP(E2, A:A, C:C, "Employee not found")

Returns “Employee not found” instead of #N/A when the ID doesn’t exist.

Approximate Match Example:

Find tax bracket based on income:

=XLOOKUP(E2, A:A, B:B, , 1)

The 1 parameter finds the exact match or next smallest item, perfect for tax brackets, commission tiers, or pricing tables.

Search from Bottom:

=XLOOKUP(E2, A:A, C:C, , , -1)

The -1 search mode starts from the last item and works backwards, useful for finding the most recent transaction or the latest entry.

Practical Applications

Customer Database Lookup: Instantly retrieve customer details (phone, address, account status) from customer ID without complex formulas.

Product Information Retrieval: Look up product prices, descriptions, inventory levels, or specifications from product codes.

Commission Calculations: Find appropriate commission rates based on sales amounts using an approximate match.

Data Consolidation: Pull information from multiple tables into summary reports without complex nested formulas.

Pricing Tables: Look up tiered pricing based on quantity ordered or customer classification.

Migration from VLOOKUP

Converting existing VLOOKUP formulas to XLOOKUP is straightforward:

Old VLOOKUP:

=VLOOKUP(A2, B:F, 3, FALSE)

New XLOOKUP:

=XLOOKUP(A2, B:B, D:D)

XLOOKUP is clearer (you see exactly which column returns data), more reliable (column insertions don’t break it), and more flexible (works regardless of column positions).

2. Dynamic Arrays: Formulas That Spill Automatically

Dynamic arrays fundamentally change how Excel formulas work, allowing single formulas to return multiple results that automatically fill adjacent cells.

Understanding Dynamic Array Behavior

Traditional Excel formulas return one value to one cell. Dynamic arrays allow formulas to return multiple values that “spill” into neighboring cells automatically:

Automatic Expansion: When a formula returns multiple results, Excel automatically populates adjacent cells without copying formulas.

Spill Range: The area filled with results is called the spill range, indicated by a blue border.

Single Formula Control: You only maintain one formula in the top-left cell; all spilled results update automatically when you modify the formula.

Spill Error Protection: If something blocks the spill area (existing data, merged cells), Excel shows a #SPILL! error alerting you to the blockage.

Dynamic Array Functions

Excel 2021 includes several functions designed specifically for dynamic arrays:

FILTER Function:

Return only rows meeting specific criteria:

=FILTER(A2:D100, C2:C100>1000)

Returns all rows where column C exceeds 1000, automatically showing all matching rows without manual filtering.

SORT Function:

Sort data dynamically:

=SORT(A2:D100, 3, -1)

Sorts the range by column 3 in descending order. Results update automatically when source data changes.

SORTBY Function:

Sort by criteria that aren’t in the result:

=SORTBY(A2:B100, C2:C100, -1)

Returns columns A and B, sorted by column C values (which aren’t included in results).

UNIQUE Function:

Extract unique values automatically:

=UNIQUE(A2:A100)

Returns each unique value from the range once, removing duplicates dynamically.

SEQUENCE Function:

Generate number sequences:

=SEQUENCE(10, 1, 100, 10)

Creates 10 rows, 1 column, starting at 100, incrementing by 10: 100, 110, 120…

Practical Applications

Automated Filtering Dashboards:

Create dynamic reports that update automatically:

=FILTER(Sales_Data, (Sales_Data[Region]=E2) * (Sales_Data[Date]>=F2))

Shows sales for a selected region and date range without manual filtering.

Automatic Unique Lists:

Generate dropdown list sources that update automatically:

=UNIQUE(SORT(A2:A1000))

Creates alphabetically sorted unique values from your data for data validation lists.

Dynamic Top 10 Reports:

=SORT(FILTER(A2:C100, C2:C100>LARGE(C2:C100, 10)), 3, -1)

Automatically shows top 10 results by column C, updating as data changes.

Calendar Generation:

=SEQUENCE(7, 5, DATE(2026, 1, 1), 1)

Creates a 7-row by 5-column calendar starting January 1, 2026.

Combined Function Power:

=SORT(UNIQUE(FILTER(Products, Category=E2)), 1, 1)

Filters products by category, removes duplicates, and sorts results—all in one formula that spills results automatically.

Working with Spill Ranges

Reference Entire Spill Range:

Use the spill range operator (#) to reference all split results:

=SUM(A2#)

Sums all values in the spill range starting at A2, regardless of how many rows it contains.

Spill Range in Charts:

Charts referencing spill ranges update automatically as results expand or contract, creating truly dynamic visualisations.

3. LET Function: Name Variables and Simplify Complex Formulas

The LET function allows you to assign names to calculation results within formulas, making complex formulas readable, maintainable, and more efficient.

The Problem with Traditional Complex Formulas

Complex Excel formulas often repeat the same calculation multiple times:

=IF(VLOOKUP(A2,$F$2:$G$100,2,0)*1.15>500, VLOOKUP(A2,$F$2:$G$100,2,0)*1.15*0.9, VLOOKUP(A2,$F$2:$G$100,2,0)*1.15)

This formula:

• Repeats VLOOKUP(A2,$F$2:$G$100,2,0) three times
• Performs the lookup three separate times (inefficient)
• Is difficult to read and understand
• Is error-prone when modifications are needed
• Requires changing the same expression in multiple places

How the LET Function Works

LET allows you to name intermediate calculations and reuse them:

Syntax:

=LET(name1, value1, [name2, value2, ...], calculation)

Same Formula with LET:

=LET(
    BasePrice, VLOOKUP(A2,$F$2:$G$100,2,0),
    AdjustedPrice, BasePrice*1.15,
    IF(AdjustedPrice>500, AdjustedPrice*0.9, AdjustedPrice)
)

Benefits:

• VLOOKUP executes only once (more efficient)
• Clear variable names explain what each value represents
• Easy to modify—change BasePrice calculation once
• Readable—you can understand the logic at a glance

Practical Examples

Tax Calculation with Multiple Tiers:

=LET(
    Income, B2,
    TaxBracket1, MIN(Income, 50000) * 0.1,
    TaxBracket2, MAX(MIN(Income, 100000) - 50000, 0) * 0.15,
    TaxBracket3, MAX(Income - 100000, 0) * 0.25,
    TaxBracket1 + TaxBracket2 + TaxBracket3
)

This formula calculates progressive tax across multiple brackets with clear, named variables.

Commission Calculation:

=LET(
    Sales, C2,
    BaseCommission, Sales * 0.05,
    Bonus, IF(Sales > 50000, 1000, 0),
    Total, BaseCommission + Bonus,
    Total
)

Named variables make the commission structure transparent and easy to modify.

Complex Text Processing:

=LET(
    FullName, A2,
    FirstSpace, FIND(" ", FullName),
    LastSpace, FIND(" ", FullName, FirstSpace+1),
    FirstName, LEFT(FullName, FirstSpace-1),
    MiddleName, MID(FullName, FirstSpace+1, LastSpace-FirstSpace-1),
    LastName, RIGHT(FullName, LEN(FullName)-LastSpace),
    LastName & ", " & FirstName & " " & LEFT(MiddleName,1) & "."
)

Processes “John Michael Smith” to “Smith, John M.” with readable intermediate steps.

Performance Benefits

LET improves performance when formulas contain repeated expensive operations:

Without LET:

• Each repeated calculation executes separately
• XLOOKUP, VLOOKUP, SUMIF, complex calculations run multiple times
• Larger datasets see a significant performance impact

With LET:

• Each named variable calculates once
• Results are reused wherever the variable appears
• Dramatically faster on large datasets or complex calculations

Debugging and Maintenance

Easier Debugging:

Instead of this:

=IFERROR(INDEX(Data,MATCH(1,(A2=Criteria1)*(B2=Criteria2),0),3),"")

Use this:

=LET(
    Row, MATCH(1,(A2=Criteria1)*(B2=Criteria2),0),
    Result, INDEX(Data, Row, 3),
    IFERROR(Result, "")
)

You can test each variable independently to identify where formulas fail.

Simplified Updates:

When business rules change, modify the named variable once instead of finding and changing repeated expressions throughout complex formulas.

4. XLOOKUP with Dynamic Arrays: Multi-Column Lookups

Combining XLOOKUP with dynamic array capabilities creates powerful multi-column lookup solutions that were previously complex or impossible.

Traditional Multi-Column Lookup Challenges

Previously, returning multiple columns from a lookup required:

• Multiple separate VLOOKUP formulas (one per column)
• Complex array formulas with INDEX/MATCH
• Manually copying formulas across columns
• Formulas that break when columns are inserted or rearranged

XLOOKUP Multi-Column Returns

XLOOKUP can return multiple columns in a single formula that spills results:

Syntax:

=XLOOKUP(lookup_value, lookup_array, return_array)

When return_array includes multiple columns, XLOOKUP returns all columns and spills them horizontally.

Example:

Lookup employee ID and return multiple employee details:

=XLOOKUP(F2, A:A, B:E)

• F2: Employee ID to find
• A:A: Column containing employee IDs
• B:E: Four columns of employee data (Name, Department, Title, Email)
• Results: All four columns spill horizontally automatically

Vertical Spilling with Multiple Matches

XLOOKUP can return all matching rows (not just the first match):

=FILTER(B:E, A:A=F2)

While FILTER is better for multiple rows, combining XLOOKUP with other dynamic array functions creates powerful solutions:

=XLOOKUP(F2:F5, A:A, B:E)

Looks up multiple values (F2 through F5) and returns all their corresponding multi-column results in a spilled array.

Practical Applications

Customer Information Retrieval:

=XLOOKUP(CustomerID, Customers[ID], Customers[[Name]:[Phone]])

One formula retrieves customer name, address, email, and phone from a customer ID, spilling all information horizontally.

Product Details for Orders:

=XLOOKUP(OrderItems, ProductDB[SKU], ProductDB[[Description]:[UnitPrice]])

Returns product description, category, price, and stock status for order line items.

Multi-Column Data Consolidation:

=XLOOKUP(UNIQUE(A2:A100), A2:A100, B2:F100)

Extracts unique values from column A and returns their associated multi-column data from B:F.

Two-Way Lookup (Row and Column)

XLOOKUP can perform both row and column lookups:

=XLOOKUP(G2, A:A, XLOOKUP(G3, B1:Z1, B:Z))

• G2: Row lookup value
• G3: Column lookup value
• Returns the intersection cell value

This replaces complex INDEX/MATCH/MATCH formulas with readable syntax.

Combining with Other Functions

Dynamic Summary Tables:

=LET(
    Products, UNIQUE(Data[Product]),
    Details, XLOOKUP(Products, Data[Product], Data[[Category]:[Stock]]),
    HSTACK(Products, Details)
)

Creates a dynamic summary showing each unique product with its associated details.

Conditional Multi-Column Lookup:

=XLOOKUP(F2, IF(G2=Data[Region], Data[ID]), Data[[Sales]:[Profit]])

Returns multi-column results only for matching region criteria.

5. STOCKHISTORY and Data Types: Live Data Integration

Excel 2021 introduced linked data types that connect spreadsheets to live data sources, with STOCKHISTORY providing automatic stock market data.

Understanding Linked Data Types

Linked data types connect cells to online databases, retrieving multiple related data points:

Stocks Data Type:

• Current price, change, market cap, P/E ratio
• Historical prices, dividends, and trading volume
• Company information and financial metrics

Geography Data Type:

• Population, area, GDP
• Time zones, capitals, leaders
• Geographic and demographic data

How It Works:

1. Type a stock symbol (MSFT) or location (Paris)
2. Convert to a linked data type
3. Excel queries Microsoft’s databases
4. Access multiple data fields through the data card icon
5. Data refreshes automatically to stay current

STOCKHISTORY Function

STOCKHISTORY retrieves historical stock prices directly into spreadsheets:

Syntax:

=STOCKHISTORY(stock, start_date, end_date, [interval], [headers], [properties])

Example:

=STOCKHISTORY("MSFT", DATE(2025,1,1), DATE(2026,1,15))

Returns Microsoft’s daily stock prices from January 1, 2025, through January 15, 2026, including:

• Date
• Open price
• High price
• Low price
• Close price
• Trading volume

Practical Applications

Investment Portfolio Tracking:

=STOCKHISTORY(A2, TODAY()-365, TODAY(), 2)

Retrieves weekly stock prices for the past year, enabling portfolio value tracking over time.

Performance Analysis:

Calculate return on investment automatically:

=LET(
    History, STOCKHISTORY("AAPL", DATE(2025,1,1), TODAY()),
    StartPrice, INDEX(History, 2, 5),
    CurrentPrice, INDEX(History, ROWS(History), 5),
    (CurrentPrice - StartPrice) / StartPrice
)

Dividend Tracking:

=STOCKHISTORY("MSFT", DATE(2025,1,1), TODAY(), 0, 1, 6)

The 6 The property code retrieves dividend payments instead of price data.

Automated Charts:

STOCKHISTORY results spill into ranges that update automatically. Charts based on these ranges refresh with current data without manual intervention.

Multi-Stock Comparison:

=STOCKHISTORY({"MSFT"; "AAPL"; "GOOGL"}, DATE(2025,1,1), TODAY())

Retrieves historical data for multiple stocks simultaneously for comparative analysis.

Using Stock Data Types

Converting to Stock Data Type:

1. Type a ticker symbol (AAPL)
2. Select the cell
3. Click Data > Stocks
4. Excel converts the symbol to a linked stock data type

Accessing Data Fields:

Click the data card icon that appears next to converted stocks to see available fields:

• Price
• Change
• Change %
• Previous close
• 52-week high/low
• Market cap
• P/E ratio
• Dividend yield

Extracting Specific Fields:

=A2.Price

Where A2 contains a linked stock data type, this retrieves the current price.

=A2.[Market cap]

Retrieves market capitalisation.

Refreshing Data

Automatic Refresh:

• Linked data types refresh periodically when Excel is open
• Frequency depends on the data source and Excel settings

Manual Refresh:

• Data > Refresh All
• Or right-click the data type and select Refresh

Refresh Settings:

• File > Options > Data
• Configure automatic refresh intervals and behaviour

Geographic Data Types

Similar to stocks, geography data types provide demographic information:

Example:

Type “France” in a cell, convert to Geography data type, then extract:

=A2.Population
=A2.[GDP per capita]
=A2.Capital
=A2.[Time zone]

Practical Uses:

• Market research and demographic analysis
• Sales territory planning
• International business comparisons
• Educational projects and research

Getting Started with These Features

Checking Feature Availability

These features are available in:

• Microsoft Office 2021 (perpetual license)
• Microsoft 365 subscriptions
• Excel for Microsoft 365 (web version for some features)

Verify your Excel version:

1. File > Account
2. Check “About Excel” under Product Information
3. Version 16.0.14xxx or higher includes these features

Learning Resources

Microsoft Support:

• support.microsoft.com/excel provides official documentation
• Search for specific function names (XLOOKUP, LET, STOCKHISTORY)
• Video tutorials and step-by-step guides available

Practice Files:

• Create sample data to practice each feature
• Start with simple examples before complex formulas
• Experiment with combinations of functions

Formula Bar Tips:

• Press Alt + Enter within the formula bar to add line breaks
• Makes complex formulas with LET more readable
• Helpful for debugging multi-line formulas

Integration into Daily Workflows

Start Small:

• Replace one VLOOKUP with XLOOKUP to see the difference
• Convert one repeated calculation to use LET
• Experiment with FILTER on a small dataset

Gradual Adoption:

• As you encounter needs these features solve, implement them
• Don’t force-convert working formulas unnecessarily
• Focus on new workbooks and significant formula updates

Document Your Formulas:

• LET’s named variables serve as built-in documentation
• Comment complex formulas to explain business logic
• Share knowledge with team members to multiply benefits

Frequently Asked Questions (FAQs)

Do these features work in older versions of Excel?

No, these specific features (XLOOKUP, dynamic arrays, LET, STOCKHISTORY, data types) require Excel 2021 or Microsoft 365. They are not available in Excel 2019, 2016, or earlier versions. If you share workbooks with users on older Excel versions, they’ll see #NAME? errors where these functions appear, as the older versions don’t recognise the function names. For compatibility, you must use traditional functions (VLOOKUP, INDEX/MATCH) when sharing with older Excel versions.

Will formulas using these features work when shared with others?

This depends on what Excel version your recipients use. If they have Excel 2021 or Microsoft 365, formulas will work perfectly. If they use Excel 2019 or older, formulas with new functions will show errors. For shared workbooks with uncertain recipient versions, consider using traditional functions or providing results as values rather than formulas. Alternatively, save workbooks in Excel 97-2003 format, which forces you to use only compatible functions (though this limits functionality significantly).

Are dynamic arrays slower than traditional formulas?

Dynamic arrays are generally faster than equivalent traditional approaches because they calculate once and spill results, rather than requiring copied formulas that each calculate independently. A single FILTER formula returning 100 rows is significantly faster than 100 individual IF formulas checking the criteria. However, extremely large spill ranges (thousands of rows/columns) can impact performance. For most business use cases, dynamic arrays improve performance while dramatically simplifying formula management.

Can I use XLOOKUP with Excel tables?

Yes, XLOOKUP works excellently with Excel tables (formatted ranges created with Ctrl+T). You can reference table columns by name for clearer formulas: =XLOOKUP(F2, Sales[OrderID], Sales[CustomerName]). Table column references adjust automatically when you add or remove rows, making formulas even more resilient. Combining XLOOKUP with structured table references creates the most maintainable, readable lookup formulas possible in Excel.

Does STOCKHISTORY require an internet connection?

Yes, STOCKHISTORY and linked data types (Stocks, Geography) require internet connectivity to query Microsoft’s online databases. Without internet access, these features cannot retrieve or refresh data. Once data is retrieved, it persists in the workbook, so you can view previously downloaded stock history offline. However, the data won’t update until you reconnect to the internet and refresh. For completely offline environments, you must use alternative data sources or manual data entry.

Can I combine multiple hidden features in one formula?

Absolutely! Combining features creates powerful solutions. For example:

=LET(
    UniqueProducts, UNIQUE(FILTER(Data[Product], Data[Region]=G2)),
    Details, XLOOKUP(UniqueProducts, Data[Product], Data[[Price]:[Stock]]),
    SORT(HSTACK(UniqueProducts, Details), 2, -1)
)

This formula filters by region, extracts unique products, looks up multi-column details, and sorts results—combining FILTER, UNIQUE, XLOOKUP, SORT, and LET. These features are designed to work together, enabling solutions that were previously impossible or extremely complex.

Conclusion

Excel 2021’s hidden features XLOOKUP, dynamic arrays, LET function, multi-column lookups, and live data integration—represent substantial productivity improvements that most users haven’t discovered. These aren’t gimmicks or edge-case tricks; they’re fundamental enhancements that solve everyday problems across business analysis, financial modeling, data management, and reporting workflows.

XLOOKUP eliminates the frustrations users experienced with VLOOKUP for decades, providing flexible, resilient lookup capabilities that work regardless of data organization. Dynamic arrays fundamentally change how formulas work, allowing single formulas to generate entire result sets that update automatically as data changes. The LET function makes complex formulas readable, maintainable, and more efficient by naming intermediate calculations. Multi-column lookups simplify data retrieval that previously required multiple formulas or complex array expressions. Live data integration through STOCKHISTORY and data types connects spreadsheets directly to authoritative online databases.

The productivity gains from mastering these features are substantial tasks that required hours of formula copying, manual updates, and complex nested functions now complete in minutes with simpler, more reliable formulas. Error rates decrease because formulas are clearer and more resilient to data changes. Maintenance becomes easier because formula logic is transparent through named variables and self-documenting function names.

Start incorporating these features gradually into your daily Excel work. Replace your next VLOOKUP with XLOOKUP to experience the difference. Convert a complex formula with repeated calculations to use LET and see how much clearer it becomes. Experiment with FILTER to create a dynamic report that updates automatically. Each feature you master adds another powerful tool to your Excel capability, compounding your productivity improvements over time.

For users still working with Excel 2019 or earlier versions and wanting access to these productivity-enhancing features, upgrading to Excel 2021 or Microsoft 365 provides immediate value through time savings and enhanced capabilities. For legitimate Microsoft Office digital licences with these features included, choose trusted, authorized providers with verified credentials to ensure you receive genuine software that delivers full functionality and proper support.