Excel 365's New Dynamic Arrays: A Major Advancement or Pointless?
Microsoft recently introduced a new feature in Excel 365 called Dynamic Arrays, which has been touted as a major advancement in spreadsheet functionality. The new feature allows functions to accept a range argument, making it possible to perform calculations on an entire range of cells instead of just individual cells. However, some users are questioning whether this new feature is really necessary, as tables in Excel already offer well-implemented drop-downs and other features that make working with data in spreadsheets more efficient.
What are Dynamic Arrays?
Dynamic Arrays are a new feature in Excel 365 that allow functions to accept a range of cells as an argument, rather than just individual cells. This makes it possible to perform calculations on an entire range of cells at once, rather than having to manually enter each cell reference. For example, the new SORT function can be used to sort an entire range of cells in ascending or descending order, without having to manually select and sort each individual cell.
=SORT(A1:C10, 1, TRUE)
How are Tables Different?
Excel tables have long offered a number of features that make working with data in spreadsheets more efficient. For example, tables have built-in drop-downs that make it easy to filter and sort data, without having to manually enter complex formulas. Tables also automatically expand and contract as data is added or removed, making it easy to keep track of data and ensure that formulas are always applied to the correct range of cells.
<table>
<thead>
<tr>
<th>Header 1</th>
<th>Header 2</th>
<th>Header 3</th>
</tr>
</thead>
<tbody>
<tr>
<td>Data 1</td>
<td>Data 2</td>
<td>Data 3</td>
</tr>
<tr>
<td>Data 4</td>
<td>Data 5</td>
<td>Data 6</td>
</tr>
</tbody>
</table>
Dynamic Arrays are a major advancement in Excel 365, but they may not be necessary for all users. Tables in Excel already offer a number of features that make working with data in spreadsheets more efficient, including built-in drop-downs for filtering and sorting data, and automatic expansion and contraction as data is added or removed. Whether or not Dynamic Arrays are right for you will depend on your specific needs and how you use Excel.
References
- Microsoft. (2021). Dynamic arrays in Excel. https://support.microsoft.com/en-us/office/dynamic-arrays-in-excel-1de057ee-46b7-4854-aa85-69f0f3549e36
- Microsoft. (2021). Excel tables. https://support.microsoft.com/en-us/office/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c