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

Rethinking Sorting and Filtering in Excel

6/1/2020

 

New Functions Provide Superior Options!

When sorting or filtering data in Excel, most users instinctively click the drop-down arrows at the top of a column to initiate the process.  Alternatively, they flock to the Data tab of the Ribbon and click on the Sort or Filter icons there. While there is certainly nothing wrong with either of these approaches, better options exist if you are using Excel provided through a Microsoft 365 or Office 365 subscription. In this tip, you will learn how to take advantage of these better options, namely Excel’s SORT and FILTER functions.

Origins of Excel's SORT and FILTER Functions

For Microsoft 365 and Office 365 subscribers, the SORT and FILTER functions first appeared in the 1907 release of Excel (July 2019.) Neither of these functions is available in Excel 2019 or prior. Assuming you have access to Excel provided through an Office 365 subscription, you should have access to both SORT and FILTER, along with several other functions that create dynamic arrays.

Dynamic arrays are one of the most significant developments in Excel to appear in the past decade. Before dynamic arrays, we had to enter (or copy) a formula into each cell where we needed a calculation. Some refer to this as the one formula, one value paradigm. With dynamic arrays, we can enter one formula that takes action on many cells at the same time, meaning many of our Excel processes just got significantly easier!

Using Excel's SORT and FILTER Functions

Excel’s SORT and FILTER functions are relatively simple and easy to use. The syntax for the SORT function appears below.

=SORT(Array, Sort_index, Sort_order, By_col)

The array is the range of data you want to sort. The sort_index is the column by which you want to sort the array. By default, the sort_index is the first column, but you can enter a numerical reference to any other column. The sort_order defaults to a value of “1,” representing ascending; you can change this to “-1” to sort in descending order. Finally, the by_col reference defaults to False to facilitate sorting by row. If you need to sort by column instead, change the value to True.

To illustrate working with the SORT function, consider the example presented in Figure 1. The SORT function used sorts the data in cells B2 through C51 on the second column (population), and it sorts the data in descending order. Notice that the formula places the results into E2 through F51. The procedure does not alter the source data range (B2 through C51) at all.
Picture
Figure 1 - Using Excel's SORT Function


Working With the Filter Function

Excel’s FILTER function is similar to SORT is some regards, but its fundamental purpose is decidedly different. That is, you can use FILTER to reduce the volume of data to only those rows that meet specific criteria. The syntax for FILTER appears below.

=FILTER(Array, Include, if_empty)

Like SORT, when using FILTER, the array argument is the range you wish to screen to meet specific criteria. The include argument allows you to specify your screening criteria.  Finally, you can use if_empty to indicate what gets returned if no records match the specified criteria.

Figure 2 illustrates working with the FILTER function in Excel. In this example, the formula filters the data in cells B2 through C51 to just those states with populations exceeding 7,000,000. Like the SORT function, the FILTER function does not disturb or otherwise rearrange the source data.
Picture
Figure 2 - Working with Excel's Filter Function
Notably, you can “nest” SORT and FILTER in the same formula for even better reporting and analyses. For example, the formula shown below filters the data first and then sorts the records that match the filter criteria.

=SORT(FILTER(E2:F51,F2:F51>7000000),2,-1)

Summary

Both SORT and FILTER are available to many Excel users, yet most remain unaware of their presence and, therefore, do not take advantage of these functions. Both these functions facilitate quicker and more robust analyses of data. They also allow you to take action on a range or table of data without affecting the data in that range or table. Further, they do not disturb or rearrange the source data, and you can nest them in the same formula for even more power. In sum, if you have access to SORT and FILTER, consider how you can use them to improve your reporting processes. If you don’t yet have access to these functions, stay alert for when they do become available to you so that you can take advantage of them at that time.


Tommy Stephens

Beaverton Furniture Assembly link
7/18/2022 08:19:36 pm

Thanks for postinng this


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  |  (905) 633-9772
© 2023 K2E Canada Inc. ALL RIGHTS RESERVED.