Taking Advantage Of Excel's GROUPBY Function
Not long ago, I wrote about Excel’s PIVOTBY function – a recently published Excel feature that allows you to build formula-based PivotTables in Excel. When Microsoft added PIVOTBY to Excel, they also added another new function, GROUPBY. Read on, and you will discover how to use GROUPBY to summarize information quickly and easily.
What Is GROUPBY?
Like Excel’s SUM, SUBTOTAL, PIVOTBY, and other functions, GROUPBY is a feature in Excel that you can use to summarize information. However, GROUPBY differs from these other features in that it allows you to quickly and easily aggregate data by various categories, such as product line, region, customer, etc. In other words, you can use GROUPBY to quickly generate a data summary filtered to specific criteria.
How Do I Use The Function?
Only three arguments are necessary when creating a formula based on this function. First, you must specify the range of data that serves as your table that serves as your rows. Second, you must specify the range of data that serves as the numeric values you wish to summarize. Finally, specify the aggregation function to use, such as SUM.
To illustrate, consider the formula shown below.
=GROUPBY(Table1[Month],Table1[Sales],SUM)
You could use this formula to summarize the amounts in a table’s “Sales” column by month. Upon creating the formula, your output might resemble that shown in Figure 1.
Of course, other options exist when creating a GROUPBY formula. For example, you can choose whether you want to display field headers. You can also choose how many levels of subtotals you want to display. Of course, Microsoft provides a detailed discussion of all the required and optional arguments associated with GROUPBY which you can view at GROUPBY function – Microsoft Support.
Advantages Associated With GROUPBY
One of the chief advantages of GROUPBY is the function’s ability to quickly summarize large volumes of data based on the criteria you specify. Further, GROUPBY can present the summarization in an easy-to-read and understandable format. Although the function does not provide all the features of an Excel-based PivotTable or a formula that uses the PIVOTBY function, it is quite useful for situations where you need relatively simple data summarizations based on grouping criteria you define.
Potential Accounting And Financial Uses Of GROUPBY
As shown in Figure 1, GROUPBY can quickly and easily generate summarizations that do not require you to sort or filter the data. Some accounting-oriented examples of using GROUPBY include the following:
- Summarizing expenditure transactions by expense category,
- Creating a report that illustrates sales by salesperson, by month,
- Generating an analysis of time worked by job, by team member, and
- Analyzing capital expenditures by project, by month
Summary
GROUPBY is an excellent new feature available in Excel. Using this function, you can quickly create summaries of transactional data without needing to sort, filter, or otherwise manipulate the data in advance. Therefore, the next time you need this type of functionality, consider using this function to speed up the process of creating your summary while simultaneously reducing the likelihood of an error.
At K2 Enterprises, our commitment lies in providing unwavering support and expert instruction to CPAs. Explore the wealth of resources on our website, where you’ll find valuable insights on selecting the most suitable accounting software, ensuring your firm is equipped with the right tools for the journey ahead. If you work in accounting or finance, K2 Enterprises provides continuing education programs to enhance your skills and credentials. Need help learning how to solve your business’s accounting technology needs and selecting the right software for accounting or CPA Firms? Visit us at k2e.com, where we make sophisticated technology understandable to anyone through our conferences, seminars, or on-demand courses.
