Ever felt stuck with dynamic array functions in Excel and wondered what alternatives you might have? You're not alone.
Dynamic arrays are a game-changer, offering functions like FILTER, SORT, and UNIQUE to streamline your data tasks. But what if you're not using Office 365? Don't worry; there are still ways to achieve similar results in older Excel versions.
So, whether you're sorting data, filtering for specific info, or seeking out unique values, there's always a way to get the job done, dynamic arrays or not. Ready to dive into the world of Excel alternatives and unlock new potential in your data handling?
Let's get started.
What are Dynamic Array Functions
Dynamic Array Functions in Excel, such as FILTER, SORT, and UNIQUE, allow for working with multiple values simultaneously within a single formula, marking a significant evolution in Excel's functionality. These functions automatically update their output based on changes in input data, enhancing efficiency and enabling more dynamic data analysis.
For example, the UNIQUE function can extract distinct values from a dataset, while SORT can organize data based on specified criteria. These advancements facilitate complex data manipulations and analyses, streamlining workflows and offering new possibilities for Excel users.
Examples of Excel Dynamic Array Functions
As part of the new functionality, 6 new functions were introduced in Excel 365 that handle arrays natively and output data into a range of cells. The output is always dynamic - when any change occurs in the source data, the results update automatically. Hence the group name - dynamic array functions.
These new functions easily cope with a number of tasks that are traditionally considered hard nuts to crack. For example, they can remove duplicates, extract and count unique values, filter out blanks, generate random integers and decimal numbers, sort in ascending or descending order, and a lot more.
Below you will find a brief description of what each excel array function does as well as the links to in-depth tutorials:
- UNIQUE - extracts unique items from a range of cells.
- FILTER - filters data based in the criteria you define.
- SORT - sorts a range of cells by a specified column.
- SORTBY - sorts a range of cells by another range or array.
- RANDARRAY - generates an array of random numbers.
- SEQUENCE - generates a list of sequential numbers.
- TEXTSPLIT - splits strings by a specified delimiter across columns or/and rows.
- TOCOL - convert an array or range to a single column.
- TOROW - transform a range or array into a single row.
- WRAPCOLS - converts a row or column into a 2D array based on the specified number of values per row.
- WRAPROWS - re-shapes a row or column into a 2D array based on the specified number of values per column.
- TAKE - extracts a specified number of contiguous rows or columns from the start or end of an array.
- DROP - removes a certain number of rows or columns from an array.
- EXPAND - grow an array to the specified number of rows and columns.
- CHOOSECOLS - returns the specified columns from an array.
- CHOOSEROWS - extracts the specified rows from an array.
- RACON -
- CONCATENATE - Use CONCATENATE, one of the text functions, to join two or more text strings into one string.
- SUMPRODUCT - The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays.
Additionally, there are two modern replacements of the popular Excel functions, which are not officially in the group, but leverage all the advantages of dynamic arrays:
XLOOKUP - is a more powerful successor of VLOOKUP, HLOOKUP and LOOKUP that can look up both in columns and rows and return multiple values.
XMATCH - is a more versatile successor of the MATCH function that can perform vertical and horizontal lookups and return a relative position of the specified item.
While we all get excited by new functions, the introduction of dynamic arrays is bigger than this; it’s a fundamental shift in how Excel (and Excel users) think about ALL formulas. In the remainder of this post, you get to understand the basics needed to start thinking in this new way.
Which versions of Excel have dynamic arrays?
Microsoft originally announced the change to Excel’s calculation engine in September 2018. For over a year, it was only available to those who signed up to test early releases of the new features. Regular subscribers on the Microsoft 365 monthly update channel started to receive the update from November 2019. Finally, in July 2020, those on the semi-annual channel of the Microsoft 365 subscription (mostly business users) also received dynamic arrays.
Microsoft has already confirmed this new functionality will not be available in Excel 2019 or prior versions. So, if you want it, then it’s time to upgrade to Excel 2021 or a Microsoft 365 license.
Excel dynamic arrays availability
Dynamic arrays were introduced at the Microsoft Ignite Conference in 2018 and released to Office 365 subscribers in January 2020. Currently, they are available in Excel 2021 and Microsoft 365 subscriptions.
Dynamic arrays are supported in these versions:
- Excel 2021
- Excel 2021 for Mac
- Excel 365 for Windows
- Excel 365 for Mac
- Excel for iPad and iPhone
- Excel for Android tablets
- Excel for Android phones
- Excel for the web
The Limitations of Dynamic Arrays
Dynamic array functions in Excel offer significant advantages but also come with limitations that users should be aware of. Here's an exploration of these limitations:
- Accessibility: Dynamic array functions are a cutting-edge feature available only in Office 365/Excel 365 and Excel 2021. Users of Excel 2019 and earlier versions don't have access to these functions, which can be a significant drawback for those not on the latest versions of Excel.
- Complexity and Learning Curve: While dynamic arrays introduce a more intuitive and efficient way to handle data in Excel, they also come with a learning curve. Users familiar with traditional Excel functionalities may need time to adapt to this new paradigm.
- Integration with Excel Features: Dynamic arrays have a nuanced relationship with other Excel features. For instance, while they work well when referencing Excel tables as sources, attempting to place a dynamic array formula inside an Excel table can result in a #SPILL! error.
- Use with Other Excel Tools: Dynamic arrays require careful handling when integrating with tools like charts, Pivot Tables, and data validation. Charts and Pivot Tables don't inherently calculate dynamic ranges, so incorporating spill ranges directly can lead to errors.
- Conditional Formatting Compatibility: Dynamic arrays and conditional formatting don't seamlessly integrate. If a spill range reference is used in conditional formatting, Excel converts it to a static range, losing the dynamic aspect.
Alternative 1: Using Pivot Tables
Pivot Tables can serve as an alternative to some dynamic array functions in Excel, offering a structured approach to data analysis without the need for formula-based dynamic arrays. While dynamic arrays automatically update and spill over ranges, Pivot Tables require manual refreshing to incorporate new data, which can be a limitation compared to the hands-off update approach of dynamic arrays.
Here's a step-by-step guide to using Pivot Tables as an alternative to dynamic array functions:
1. Creating the Pivot Table:- Insert a Pivot Table by selecting your data range and choosing 'Insert' > 'PivotTable.'
- Define the Pivot Table's placement in the workbook.
- Add fields to the Pivot Table's rows, columns, values, and filters to organize your data.
- Use the built-in sorting and filtering options to refine your data view.
- You can display unique values from that field by placing a field in the Pivot Table's rows area.
- The values can perform aggregations like sum, count, average, etc., on another field.
While Pivot Tables provide a robust solution for data analysis, introducing the PIVOTBY function offers a formula-based alternative that combines the flexibility of dynamic arrays with the structured summarization of Pivot Tables. The PIVOTBY function allows for creating dynamic summary reports using a single formula, offering various aggregation options and the ability to customize report layouts without manual refreshes.
These approaches offer different advantages: Pivot Tables for their interactive analysis capabilities and ease of use, and dynamic arrays (or PIVOTBY function) for their formula-based, automatic updating nature, catering to different user preferences and requirements in data analysis and reporting.
Alternative 2: Array Formulas in Older Excel Versions
Array formulas in older versions of Excel, known as "Ctrl-Shift-Enter" or "CSE" array formulas, are powerful tools that allow you to perform multiple calculations within a single formula. These traditional array formulas can simultaneously handle operations on various values and return single or multiple results.
While dynamic array formulas, available in newer versions of Excel, automatically spill over into adjacent cells, traditional array formulas do not have this spilling behavior. Instead, you need to highlight the output range, enter the formula, and then press Ctrl+Shift+Enter to execute it.
This will insert the formula between curly braces {}, indicating an array formula.
For instance, you can use a traditional array formula to transpose data, sum numbers that meet certain conditions, or count the number of characters across a range of cells. To edit or delete a multi-cell array formula, select the entire range containing the formula and make changes or press delete.
In practice, this means traditional array formulas require a bit more manual setup compared to dynamic arrays. For example, if you want to sum the 3 largest numbers in a range, you would use a formula like =SUM(LARGE(range, {1,2,3})), and remember to press Ctrl+Shift+Enter.
Although traditional array formulas can be less intuitive and require more steps to set up and modify, they remain a powerful tool for complex calculations in versions of Excel that do not support dynamic arrays.
Alternative 3: Combining Standard Excel Functions
Combining standard Excel functions like INDEX, MATCH, and IF can effectively mimic some of the functionalities of dynamic arrays, especially in older versions of Excel that do not support dynamic arrays. Here's how you can leverage these functions:
- Using INDEX and MATCH: The INDEX function returns a value from a specific position within a range, while MATCH finds the position of a specific value within a range. When combined, these two functions can perform powerful lookups, retrieving data from any position in a table, not just vertically as VLOOKUP does but also horizontally or any two-dimensional lookup.
- Two-Way Lookup: You can use INDEX and MATCH for a two-way lookup, especially when your data spans multiple rows and columns. For instance, you can find a student's score in a particular subject by matching the student's and subject names.
- Handling Left Lookups: Unlike VLOOKUP, which can only search to the right, combining INDEX and MATCH allows you to fetch data from a column to the left of the column that has the lookup value.
- Dealing with Multiple Criteria: You can also use INDEX and MATCH with multiple criteria by incorporating Boolean logic, creating a more advanced formula that can search based on several conditions.
- Dynamic Behavior: While these combined functions do not spill over like dynamic arrays in newer Excel versions, they can dynamically return values based on changing criteria, making them versatile tools for data analysis in older Excel versions.
Alternative 4: Utilizing Excel Add-ins
Excel add-ins can significantly enhance the functionality of Excel, introducing dynamic array-like features even in versions that do not inherently support them. Here's how some add-ins can enhance your Excel experience:
- Unito: This add-in offers a robust workflow management solution, allowing deep two-way integrations between Excel and other tools. It automates data synchronization across platforms, ensuring up-to-date information across all tools without manual updates.
- Power User: Aimed at boosting productivity, Power User provides over 500 templates for Excel, including dynamic diagrams and Gantt charts. It's designed to make spreadsheet creation quicker and more visually appealing.
- Lucidchart: This add-in is perfect for transforming data into presentations. It offers a simple drag-and-drop interface for building flowcharts and diagrams directly within Excel.
- SEOTools: For performance marketers, SEOTools is an invaluable add-in that brings a comprehensive SEO toolkit into Excel. It supports various SEO tasks and integrates with major tools and platforms, streamlining SEO data management.
- Supermetrics: This add-in is a boon for marketers. It automates the import of marketing data from numerous sources into Excel. It supports various data sources, from social media platforms to analytics tools, facilitating comprehensive marketing data analysis.
Alternative 5: External Tools and Software
Integrating external tools and software with Excel can significantly enhance data analysis capabilities. One of the most exciting integrations is Python in Excel, which allows users to leverage Python's powerful data analysis and visualization libraries within the Excel environment.
This integration enables advanced data analysis and the creation of sophisticated data visualizations right within Excel, marrying Python's versatility with Excel's user-friendly interface. Users can perform tasks like data manipulation, statistical modeling, and more, all within Excel's grid.
Another powerful tool for data analysis is R, renowned for its statistical analysis and visualization capabilities. While it has a steeper learning curve than Python, it's highly effective for specific statistical computing tasks and data visualization, supported by a vast library of packages.
For those focusing on data visualization and presentation, Jupyter Notebook offers an interactive, web-based platform that supports multiple programming languages, including Python and R. It's great for creating documents that combine live code, visualizations, and narrative text, making it an excellent tool for data presentation and tutorial creation.
In addition, tools like Apache Spark, Google Cloud AutoML, and SAS offer specialized capabilities ranging from big data processing to machine learning and statistical analysis, each with unique strengths and applications in data analysis.
Case Study: Real-world Application of Alternatives
In a real-world application of alternatives to dynamic arrays in Excel, we can consider a case where dependent dropdown lists are created without using dynamic array functions. This process involves using traditional Excel functions to create interactive and responsive dropdown lists that adapt based on user selection.
1. Problem Setup
- In a scenario with a list of Divisions and associated Apps, the goal is to create two dropdown lists where the selection in the first (Division) dictates the options in the second ().
- A cross-tabular data preparation table lists all Divisions and associated Apps.
- Complex formulas, like a combination of IF, INDEX, AGGREGATE, and OFFSET, are used to dynamically manage and coordinate the two dropdown lists based on user selection.
- The process is streamlined with dynamic arrays using functions like UNIQUE and FILTER.
- The UNIQUE function creates a distinct list of Divisions, simplifying the creation of the first dropdown.
- The FILTER function then creates a dependent list of Apps based on the selected Division, dynamically updating the second dropdown list.
- The dynamic array approach significantly simplifies the formulas, making them more readable and maintainable.
- It also improves the responsiveness of the dropdown lists, as the UNIQUE and FILTER functions automatically update the lists when new data is added or changed.
- Incorporating external tools or programming languages like Python can enhance Excel's data analysis capabilities, allowing for more sophisticated data manipulation and visualization tasks within the familiar Excel environment.
Tips for Transitioning to Dynamic Arrays
Transitioning to dynamic arrays in Excel involves understanding how these new functions can simplify and enhance your spreadsheet tasks. Here's a guide to help you transition smoothly:
- Understanding Spill Behavior: Dynamic arrays allow a single formula to return multiple values, which "spill" over into adjacent cells, known as the spill range. This behavior updates automatically with changes in source data, making your Excel work more intuitive and dynamic.
- Spill Range Operator (#): Use the spill range operator (#) after the cell reference to refer to the entire spill range. For instance, E5# would refer to all the values spilled from E5.
- Implicit Intersection Operator (@): The @ operator is used for implicit intersection, which helps Excel to return a single value when a formula could potentially return multiple values. This can be crucial when dealing with dynamic arrays in complex formulas.
- Leveraging New Functions: Familiarize yourself with the new dynamic array functions like UNIQUE, SORT, FILTER, SEQUENCE, and RANDARRAY. These functions can drastically simplify tasks requiring complex formulas or additional helper columns.
- Combining Functions: Dynamic arrays unlock the potential to combine multiple functions in previously impossible ways. For example, you can nest FILTER within SORT to filter and sort data with a single formula.
- Dynamic Data Validation Lists: Dynamic arrays make it easier to create data validation lists that automatically update when the source data changes. Utilize the spill range operator (#) to reference dynamic ranges for data validation.
- Dealing with Errors: Familiarize yourself with the new error types associated with dynamic arrays, such as the #SPILL! error, which occurs when there's an obstruction in the spill range. Understanding these errors will help you troubleshoot and maintain your spreadsheets effectively.
Final Thoughts
Understanding alternatives to dynamic arrays in Excel is crucial for those using older versions or seeking more traditional methods for data management. We've explored various strategies, from leveraging Pivot Tables and array formulas to integrating external tools like Python, offering versatile solutions for different Excel environments.
Now that you're equipped with this knowledge, you can enhance your data management practices, ensuring efficiency and adaptability in your Excel tasks. Dive deeper into these alternatives, apply them to your data challenges, and unlock new potential in your spreadsheets.
Continue your learning journey by exploring more on dynamic arrays and their alternatives to stay ahead in your data management game.
Keep Learning
» 13 Best Data Analytics Tools for Data Analysts
» Excel Master: Creating VBA Named Ranges in Microsoft Excel
» What’s New in Microsoft Office 2021?
» Microsoft Office 2021 Performance
» 13 Tips To Master Excel Without Breaking a Sweat