K2E Canada Inc
  • Home
  • TRAINING
    • WEBINARS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • TECHNOLOGY CONFERENCE
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG
  • Home
  • TRAINING
    • WEBINARS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • TECHNOLOGY CONFERENCE
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG

Excel Dynamic Arrays

1/10/2021

 
A Dynamic Array formula is any formula that has the potential to return multiple results. There are several new functions in Excel for Office 365 that have this ability, including Sort, Filter, and Unique. In this post we will have a look at Sort and Filter.

The first thing to keep in mind is that the results from these Dynamic Array formulas will “spill” into adjacent cells; therefore, we need to plan for the possible spill range by allowing ample space in adjacent cells. This spilling is by design; and, in the event the spill range is not large enough for the results, Excel returns a #SPILL! error. 

The second item to consider is that references used in Dynamic Arrays between workbooks need both workbooks to be open; Excel returns a #REF! if they are not.

Spill

To understand better what spill means and how dynamic arrays work, let’s start with the data below and create a formula to summarize the information.
Picture
The data is in a table named Payments. In cell G5, enter the formula =payments and press Enter. The data is now in G5:J22 using this single formula. While in the data range, you will notice an outline around the data indicating that it is part of a dynamic array. The formula is only in cell G5 and, therefore, all other cells in the array will show the formula as a light gray in the formula bar as shown below.
Picture
As shown below, in the event of other data residing in the range used by the dynamic array formula – for example, cells M12:O12 – completing the formula will result in the #SPILL! error. Keeping cell L5 highlighted, Excel will show where the data will spill, providing clear guidance on which data is causing the error. To resolve the problem, either move the dynamic array formula cell or clear the data in the spill area. Additionally, as shown below, Excel provides an error-checking tool and a quick way to Select Obstructing Cells if you want to move them outside the spill range. Once you clear the obstructing cells from the spill range, the dynamic array formula should complete, unless one of the following four issues arises.
  1. The spill range includes merged cells;
  2. The dynamic array formula is inside of a table;
  3. The system is out of memory; and
  4. The sequence is volatile, such as with a RANDBETWEEN sequence.
Picture


Spill Range Reference

With only the one cell in the spill range containing the formula, the question becomes how to refer to all of the data in the range. The answer is to use the spill range operator which is the # symbol. In the previous example, to refer to the entire spill range, the reference would be =G5#. This does have a limitation in that it is not supported when linking to an external workbook.

Sort

The process to sort by formula before Dynamic Arrays was very complicated and, therefore, seldom used. With the Dynamic Array function SORT, the process is simply to use the formula below.
=SORT(array,[sort_index], [sort_order],[by_col
Using similar sample data, as in the previous example, in cell G2, input the formula =SORT(sortdata,3,1). The data is in a table named sortdata; the number 3 is the column to use as the sort, and the number 1 is for ascending order.

If you want to sort by more than one index, you can use an array constant. For example, to sort the data by Account ascending and Amount descending, the formula would be =SORT(SortData,{3;4},{1;-1}). The results of these formula-based sorts are shown below.
Picture


SortBy

The SORTBY function allows you to sort the contents of an array based on the values in a corresponding array. The syntax for the function is =SORTBY(array, by_array1, sort_order1,….). For example, to do the same sort as we did with the array constant, the formula would be as follows.
=SORTBY(SortData,SortData[Account],1,SortData[Amount],-1)

Filter

The next dynamic array function is FILTER, which allows filtering of data into a new array. The syntax for the function is =FILTER(array, include, [if_empty]). To have more than one condition, use the plus sign between them for either and the multiply sign between them for both.
​
As an example, in Figure 18, we want to retrieve all rows from the table named FilterData that have the item stamps. The formula would be as shown below.
=FILTER(FilterData,FilterData[Item]=I1,"Item not found")
​To retrieve the information related to stamps in the sales department the formula is as follows.
=FILTER(FilterData,(FilterData[Item]=I1)*(FilterData[Department]=I2),""
Picture
Dynamic Arrays at the time of creating this post is only available in the Office 365 Monthly Channel (Targeted)
dynamic_arrays.xlsx
File Size: 33 kb
File Type: xlsx
Download File

Ward Blatch


Comments are closed.

    Categories

    All
    2022
    Accountant
    Accounting Software
    Accounting Solutions
    Adobe
    Advisory
    AI
    Artificial Intelligence
    Automation
    Backup
    Bitcoin
    Blockchain
    Business Automation
    Business Continuity
    Business Intelligence
    Business Management
    Cloud Computing
    Collaborate
    Collaboration
    Colonial
    Computer
    COVID 19
    COVID-19
    CPA
    CRM
    Cryptocurrency
    Customer Relationship Management
    Cybersecurity
    Cyptocurrencies
    Dext
    Doc.It
    Ecommerce
    Emerging Technologies
    Entrepreneurs
    Excel
    Forecasting
    Forecast Sheet
    Google
    Grammarly
    HR
    Internal Controls
    Internal Priorities
    KPIs
    LET
    Links
    Management Reports
    Microsoft 365
    Microsoft Office 2021
    Microsoft Teams
    Office
    Office 365
    Online Shop
    Outsourcing
    Pandemic
    Paperless
    Personal Computer
    PivotTables
    Power Automate
    Power BI
    PowerPoint
    Productivity
    QuickBooks
    Quickbooks Online
    Ransomware
    Receipt Bank
    Remote Work
    RPA
    Sage 50
    Security
    Small Business
    Small Business Accounting
    Small Business Accounting Software
    Stockhistory
    Technologies
    Technology
    Tech Update
    Windows
    Windows 10
    Windows 11
    Word
    Work At Home
    Workflow
    Work From Home
    Xcm
    Zoho
    Zoho CRM

    Authors


    Ward Blatch
    Ward provides consulting and training services as the Managing Director of K2E Canada Inc. He joined K2E Canada in 2005 and is responsible for the Canadian operations of this international consulting group, which provides professional development technology education for accountants across Canada and the US. Ward lives in rural Nova Scotia and can be reached at ward@k2e.ca.

    Tommy Stephens
    Tommy is one of the shareholders in K2 Enterprises, affiliating with the Firm in 2003 and joining as a shareholder in 2017. At K2, Tommy focuses on creating and delivering content and is responsible for many of the Firm's management and marketing functions. Tommy resides in the metro Atlanta area. You may reach him at tommy@k2e.com.

    Randy Johnson
    Randy is a nationally recognized educator, consultant, and writer with over 40 years experience in Strategic Technology Planning, Accounting Software Selection, Paperless, Systems and Network Integration, Business Continuity and Disaster Recovery Planning, Business Development and Management, Process Engineering and outsourced managed services. Randy can be reached at randy@k2e.com


    Bernie Smith
    Bernie coaches businesses to develop meaningful KPIs and present their management information in the clearest possible way to support good decision making. As the owner of Made to Measure KPIs, he has worked with major organisations including HSBC, Airbus, UBS, Barclays, Credit Suisse, Lloyds and many more.

    RSS Feed

Training & Education

Webinars
Road to Excellence Online Seminar
Seminars
Conferences
Instructors

Tech News

K2E Canada's Tech Update Newsletter

More

Privacy Policy
About
Contact

K2E Canada Inc.  |  484 Scarlett Crescent  |  Burlington, ON L7L 5M2  |  (902) 200-9207
© 2022 K2E Canada Inc. ALL RIGHTS RESERVED.