{"id":97577,"date":"2025-04-01T15:37:44","date_gmt":"2025-04-01T15:37:44","guid":{"rendered":"https:\/\/goodwriterz.com\/site\/?p=97577"},"modified":"2025-04-01T15:37:44","modified_gmt":"2025-04-01T15:37:44","slug":"how-to-use-the-groupby-function-in-excel","status":"publish","type":"post","link":"https:\/\/goodwriterz.com\/site\/how-to-use-the-groupby-function-in-excel\/","title":{"rendered":"How to Use the GROUPBY Function in Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_68_1 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >\u062c\u062f\u0648\u0644 \u0627\u0644\u0645\u062d\u062a\u0648\u064a\u0627\u062a<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 eztoc-toggle-hide-by-default' ><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-groupby-function-in-excel\/#Quick_Links\" title=\"Quick Links\">Quick Links<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-groupby-function-in-excel\/#The_GROUPBY_Syntax\" title=\"The GROUPBY Syntax\">The GROUPBY Syntax<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-groupby-function-in-excel\/#GROUPBY_in_Action_Using_the_Required_Arguments_Only\" title=\"GROUPBY in Action: Using the Required Arguments Only\">GROUPBY in Action: Using the Required Arguments Only<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-groupby-function-in-excel\/#GROUPBY_in_Action_Using_the_Optional_Arguments\" title=\"GROUPBY in Action: Using the Optional Arguments\">GROUPBY in Action: Using the Optional Arguments<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-groupby-function-in-excel\/#Total_Depth\" title=\"Total Depth\">Total Depth<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-groupby-function-in-excel\/#Sort_Order\" title=\"Sort Order\">Sort Order<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-groupby-function-in-excel\/#Filter_Array\" title=\"Filter Array\">Filter Array<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-groupby-function-in-excel\/#Field_Relationship\" title=\"Field Relationship\">Field Relationship<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<div>\n<div class=\"w-table-content\">\n<h3 class=\"title icon i-flash\"><span class=\"ez-toc-section\" id=\"Quick_Links\"><\/span>Quick Links<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<div class=\"table-content\">\n<div class=\"w-table-content-list\">\n<ul class=\"table-content-list\">\n<li class=\"table-content-element icon\">\n<div class=\"table-content-link\">\n                                        <a rel=\"nofollow noopener\" target=\"_blank\" href=\"#groupby-in-action-using-the-required-arguments-only\">GROUPBY in Action: Using the Required Arguments Only<\/a>\n                                        <\/div>\n<\/li>\n<\/ul>\n<ul class=\"table-content-list\">\n<li class=\"table-content-element icon\">\n<div class=\"table-content-link\">\n                                        <a rel=\"nofollow noopener\" target=\"_blank\" href=\"#groupby-in-action-using-the-optional-arguments\">GROUPBY in Action: Using the Optional Arguments<\/a>\n                                        <\/div>\n<\/li>\n<\/ul><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<p>Excel&#8217;s GROUPBY function lets you group and aggregate data based on certain fields in your table of data. It also offers arguments that allow you to sort and filter your data, so you can tailor the output to meet your specific needs.<\/p>\n<p>    <!-- Not injecting Ads due to No-Ads mode. --><\/p>\n<section class=\"emaki-custom-block emaki-custom-note\" data-nosnippet=\"\">\n<div class=\"emaki-custom note\" id=\"custom_block_1\">\n<div class=\"custom_block-content note\">\n<p>Although you can use pivot tables to achieve similar outcomes to the GROUPBY function, the GROUPBY function refreshes automatically if your data changes or is reordered, and it also lets you embed more functions for more refined data sorting.<\/p>\n<\/p><\/div>\n<\/p><\/div>\n<\/section>\n<p><!-- Not injecting Ads due to No-Ads mode. --><\/p>\n<h2 id=\"the-groupby-syntax\">\n                        The GROUPBY Syntax<br \/>\n               <\/h2>\n<p>The GROUPBY function has eight arguments:<\/p>\n<pre>=GROUPBY(<em>a<\/em>,<em>b<\/em>,<em>c<\/em>,<em>d<\/em>,<em>e<\/em>,<em>f<\/em>,<em>g<\/em>,<em>h<\/em>)<\/pre>\n<p>Arguments <em>a<\/em> to <em>c<\/em> are required:<\/p>\n<ul>\n<li>\n                                        <em>a <\/em>(row fields): The range (one or more columns) containing the values or categories by which the data is to be grouped.\n                        <\/li>\n<li>\n                                        <em>b<\/em> (values): The range (one or more columns) containing the values that aggregate the data.\n                        <\/li>\n<li>\n                                        <em>c<\/em> (function): A function used to aggregate the values in argument <em>b<\/em>.\n                        <\/li>\n<\/ul>\n<p>Arguments <em>d<\/em> to <em>h<\/em> are optional, and you can learn more about these in the final section of this article:<\/p>\n<ul>\n<li>\n                                        <em>d<\/em> (field headers): A number that specifies whether you selected headers in arguments <em>a<\/em> and <em>b<\/em>, and whether they should be displayed in the output.\n                        <\/li>\n<li>\n                                        <em>e<\/em> (total depth): A number that determines whether the output should display totals.\n                        <\/li>\n<li>\n                                        <em>f <\/em>(sort order): A number that indicates how the result is ordered.\n                        <\/li>\n<li>\n                                        <em>g<\/em> (filter array): An array-oriented formula that filters out unwanted information.\n                        <\/li>\n<li>\n                                        <em>h<\/em> (field relationship): A number that specifies the field relationships when multiple columns are provided in argument <em>a<\/em>.\n                        <\/li>\n<\/ul>\n<h2 id=\"groupby-in-action-using-the-required-arguments-only\">\n                        GROUPBY in Action: Using the Required Arguments Only<br \/>\n               <\/h2>\n<p>If you&#8217;re overwhelmed by the large number of arguments that the GROUPBY function has, it&#8217;s important to note at this stage that the GROUPBY function works perfectly well even if you only populate arguments <em>a<\/em>, <em>b<\/em>, and <em>c<\/em>. So, first, I&#8217;ll show you how the GROUPBY function works with these three arguments alone.<\/p>\n<p>Let&#8217;s imagine you own a chain of restaurants that serve up different dishes from different cuisines, and you&#8217;ve totted up the total sales and average customer rating for each cuisine-dish combination.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\">\n<figure>\n<p>                <img data-recalc-dims=\"1\" decoding=\"async\" width=\"750\" height=\"550\" loading=\"lazy\" alt=\"An Excel table containing various cuisines and dishes, with numerical representations of their sales and customer ratings.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/How-to-Use-the-GROUPBY-Function-in-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/How-to-Use-the-GROUPBY-Function-in-Excel.png?resize=750%2C550&#038;ssl=1\" style=\"height:auto;max-width:100%\" title=\"\"><\/p>\n<\/figure><\/div>\n<\/p><\/div>\n<p> While these figures are useful, maybe you&#8217;re more interested in how the data compares across different categories. Specifically, you might want to find out the total cash each cuisine brings in, and the average customer rating for each type of dish.<\/p>\n<section class=\"emaki-custom-block emaki-custom-note\" data-nosnippet=\"\">\n<div class=\"emaki-custom note\" id=\"custom_block_14\">\n<div class=\"custom_block-content note\">\n<p>Because the GROUPBY function returns spilled arrays, you can&#8217;t use formatted Excel tables for their results.<\/p>\n<\/p><\/div>\n<\/p><\/div>\n<\/section>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\">\n<figure>\n<p>                <img data-recalc-dims=\"1\" decoding=\"async\" width=\"750\" height=\"550\" loading=\"lazy\" alt=\"Two data extraction tables in Excel that will be populated using the GROUPBY function.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521855_136_How-to-Use-the-GROUPBY-Function-in-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521855_136_How-to-Use-the-GROUPBY-Function-in-Excel.png?resize=750%2C550&#038;ssl=1\" style=\"height:auto;max-width:100%\" title=\"\"><\/p>\n<\/figure><\/div>\n<\/p><\/div>\n<p> Let me take a moment to explain why GROUPBY would be my function of choice for executing these tasks for this specific dataset. If each cuisine and each dish only appeared once in the table, you would simply use the filter buttons to reorder and analyze your data. However, since cuisines and dishes are repeated, using the GROUPBY function will let you pull data from within common categories together, giving you a clearer whole-picture idea of the sales and rating distributions.<\/p>\n<p>To find out the sales totals for each cuisine, in cell F2, type:<\/p>\n<pre>=GROUPBY(<\/pre>\n<p>Since you&#8217;re looking to group the data by cuisine, select the cells containing this variable, and add a comma. In this case, because the data is in a formatted Excel table called TabFood, a structured reference to the column name is added to my formula:<\/p>\n<pre>=GROUPBY(TabFood(Cuisine),<\/pre>\n<div class=\"display-card  article article-card small  no-badge  active-content                      \" data-include-community-rating=\"false\" id=\"7609-44c6-8ff6f28f90dc\" data-nosnippet=\"\">\n<div class=\"w-img \">\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  img-featured-4-pin-single-size-featured-secondary\">\n<figure>\n<p>                <img data-recalc-dims=\"1\" decoding=\"async\" width=\"1200\" height=\"675\" loading=\"lazy\" alt=\"Microsoft Excel logo on a green background\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/How-to-Use-the-GROUPBY-Function-in-Excel.jpg\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/How-to-Use-the-GROUPBY-Function-in-Excel.jpg?resize=1200%2C675&#038;ssl=1\" style=\"height:auto;max-width:100%\" title=\"\"><\/p>\n<\/figure><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<p>                    <span data-field=\"label\" class=\"article-card-label\">Related<\/span><\/p>\n<div class=\"w-display-card-content regular article-block\">\n<h5 class=\"display-card-title \">\n<p>\t\t\tHow to Name a Table in Microsoft Excel<\/p>\n<\/h5>\n<p class=\"display-card-excerpt\">&#8220;Table1&#8221; doesn&#8217;t explain much&#8230;<\/p>\n<\/p><\/div>\n<\/p><\/div>\n<p>\u200b\u200b\u200b\u200b<\/p>\n<p>Then, since you&#8217;re looking to see the total sales for each of those cuisines, select the cells containing these figures, and add another comma:<\/p>\n<pre>=GROUPBY(TabFood(Cuisine),TabFood(Sales),<\/pre>\n<p>The final mandatory argument is the function to be used on the aggregating data. In this case, since you want to find out the <em>total<\/em> sales for each cuisine, you need to insert the SUM function, and close the parentheses:<\/p>\n<pre>=GROUPBY(TabFood(Cuisine),TabFood(Sales),SUM)<\/pre>\n<section class=\"emaki-custom-block emaki-custom-tip\" data-nosnippet=\"\">\n<div class=\"emaki-custom tip\" id=\"custom_block_27\">\n<div class=\"custom_block-content tip\">\n<p>As well as using straightforward functions like SUM and AVERAGE in argument <em>c<\/em>, you can also use Excel&#8217;s LAMBDA tool to create a function tailored to your needs.<\/p>\n<\/p><\/div>\n<\/p><\/div>\n<\/section>\n<p>When you press Enter, you&#8217;ll see that Excel has aggregated the total sales for each cuisine. Since you haven&#8217;t included any of the optional arguments in the GROUPBY function, the data is sorted in alphabetical order according to the values in column F by default, and there&#8217;s a total row at the bottom of your extracted data.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\">\n<figure>\n<p>                <img data-recalc-dims=\"1\" decoding=\"async\" width=\"750\" height=\"306\" loading=\"lazy\" alt=\"The GROUPBY function being used in Excel to aggregate total sales by cuisine.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521855_16_How-to-Use-the-GROUPBY-Function-in-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521855_16_How-to-Use-the-GROUPBY-Function-in-Excel.png?resize=750%2C306&#038;ssl=1\" style=\"height:auto;max-width:100%\" title=\"\"><\/p>\n<\/figure><\/div>\n<\/p><\/div>\n<p> As the values in column G are financial, select the data and click the &#8220;Accounting&#8221; icon in the Number group of the Home tab on the ribbon.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\">\n<figure>\n<p>                <img data-recalc-dims=\"1\" decoding=\"async\" width=\"750\" height=\"550\" loading=\"lazy\" alt=\"Some figures in Excel are selected, and the Accounting button is highlighted.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521855_876_How-to-Use-the-GROUPBY-Function-in-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521855_876_How-to-Use-the-GROUPBY-Function-in-Excel.png?resize=750%2C550&#038;ssl=1\" style=\"height:auto;max-width:100%\" title=\"\"><\/p>\n<\/figure><\/div>\n<\/p><\/div>\n<p> Now, you want to find out the average customer rating for each type of dish, and the process for doing so is very similar.<\/p>\n<p>In cell I2, type:<\/p>\n<pre>=GROUPBY(<\/pre>\n<p>Next, select the cells containing the category by which you want to group the data. In this case, it&#8217;s the different dishes. Remember to add a comma after each argument to move to the next.<\/p>\n<pre>=GROUPBY(TabFood(Dish),<\/pre>\n<p>Now, select the cells containing the data to be aggregated, and add another comma:<\/p>\n<pre>=GROUPBY(TabFood(Dish),TabFood(Customer rating)<\/pre>\n<p>Finally, because your aim on this occasion is to find out the <em>average<\/em> customer rating for each dish type, the function argument needs to be AVERAGE.<\/p>\n<pre>=GROUPBY(TabFood(Dish),TabFood(Customer rating),AVERAGE)<\/pre>\n<div class=\"display-card  article article-card small  no-badge  active-content                      \" data-include-community-rating=\"false\" id=\"b722-41ec-8eb53440b88d\" data-nosnippet=\"\">\n<div class=\"w-img \">\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  img-featured-4-pin-single-size-featured-secondary\">\n<figure>\n<p>                <img data-recalc-dims=\"1\" decoding=\"async\" width=\"1200\" height=\"800\" loading=\"lazy\" alt=\"Microsoft Excel logo on a colorful background.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521855_697_How-to-Use-the-GROUPBY-Function-in-Excel.jpg\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521855_697_How-to-Use-the-GROUPBY-Function-in-Excel.jpg?resize=1200%2C800&#038;ssl=1\" style=\"height:auto;max-width:100%\" title=\"\"><\/p>\n<\/figure><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<p>                    <span data-field=\"label\" class=\"article-card-label\">Related<\/span><\/p>\n<div class=\"w-display-card-content regular article-block\">\n<h5 class=\"display-card-title \">\n<p>\t\t\tHow to Calculate Average in Microsoft Excel<\/p>\n<\/h5>\n<p class=\"display-card-excerpt\">Just another way Excel&#8217;s usefulness is above average.<\/p>\n<\/p><\/div>\n<\/p><\/div>\n<p>After you press Enter, Excel will average the customer ratings for each dish type. Again, in the absence of any optional arguments, the data is sorted in alphabetical order according to the values in the left-hand column by default, and there&#8217;s a handy total row at the bottom.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\">\n<figure>\n<p>                <img data-recalc-dims=\"1\" decoding=\"async\" width=\"750\" height=\"462\" loading=\"lazy\" alt=\"The GROUPBY function being used in Excel to aggregate average rating by dish.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521855_218_How-to-Use-the-GROUPBY-Function-in-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521855_218_How-to-Use-the-GROUPBY-Function-in-Excel.png?resize=750%2C462&#038;ssl=1\" style=\"height:auto;max-width:100%\" title=\"\"><\/p>\n<\/figure><\/div>\n<\/p><\/div>\n<p> As the values in column J are decimalized averages, tidy up the number of decimal places on display by clicking the &#8220;Increase Decimal&#8221; and &#8220;Decrease Decimal&#8221; buttons in the Number group of the Home tab.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\">\n<figure>\n<p>                <img data-recalc-dims=\"1\" decoding=\"async\" width=\"750\" height=\"602\" loading=\"lazy\" alt=\"A range of cells in Excel containing decimalized numbers is selected, and the Increase and Decrease Decimal buttons are highlighted.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521855_558_How-to-Use-the-GROUPBY-Function-in-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521855_558_How-to-Use-the-GROUPBY-Function-in-Excel.png?resize=750%2C602&#038;ssl=1\" style=\"height:auto;max-width:100%\" title=\"\"><\/p>\n<\/figure><\/div>\n<\/p><\/div>\n<section class=\"emaki-custom-block emaki-custom-note\" data-nosnippet=\"\">\n<div class=\"emaki-custom note\" id=\"custom_block_46\">\n<div class=\"custom_block-content note\">\n<p>If you&#8217;re happy with your GROUPBY result at this stage, you can stop reading here. However, continue reading to learn about GROUPBY&#8217;s optional arguments.<\/p>\n<\/p><\/div>\n<\/p><\/div>\n<\/section>\n<h2 id=\"groupby-in-action-using-the-optional-arguments\">\n                        GROUPBY in Action: Using the Optional Arguments<br \/>\n               <\/h2>\n<p>Even though the GROUPBY function having five optional arguments alongside the three required arguments makes it seem more complicated, these additional options are actually only there to help you create an output that is more tailored to your needs. What&#8217;s more, you can choose which optional arguments you want to use and skip over the ones you don&#8217;t.<\/p>\n<p>Below, I&#8217;ll cover each of the optional arguments, so you can see how they will affect your data when you choose to include them.<\/p>\n<section class=\"emaki-custom-block emaki-custom-note\" data-nosnippet=\"\">\n<div class=\"emaki-custom note\" id=\"custom_block_50\">\n<div class=\"custom_block-content note\">\n<p>Use a comma to jump from one argument to the next. For example, if you want to include the fourth and sixth arguments, but not the fifth, type <strong>(fourth argument),,(sixth argument)<\/strong>. The fifth argument would have been between where the two commas are positioned, but since there&#8217;s nothing in that space, Excel knows you&#8217;ve deliberately left this argument blank.<\/p>\n<\/p><\/div>\n<\/p><\/div>\n<\/section>\n<p>In the examples I used above, I typed the output column headers manually, since they&#8217;re not included in the result by default. However, if you want your output data to include the column headers as well as the data they contain, use the field headers argument.<\/p>\n<p>Start by typing your GROUPBY formula, including the first three (required) arguments. In this case, let&#8217;s assume you want to group the cuisines by average customer ratings:<\/p>\n<pre>=GROUPBY(A1:A21,D1:D21,AVERAGE<\/pre>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\">\n<figure>\n<p>                <img data-recalc-dims=\"1\" decoding=\"async\" width=\"886\" height=\"453\" loading=\"lazy\" alt=\"Unformatted data and their headers are added to a GROUPBY formula.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521856_201_How-to-Use-the-GROUPBY-Function-in-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521856_201_How-to-Use-the-GROUPBY-Function-in-Excel.png?resize=886%2C453&#038;ssl=1\" style=\"height:auto;max-width:100%\" title=\"\"><\/p>\n<\/figure><\/div>\n<\/p><\/div>\n<p> Notice how the header rows are included within the selections. Indeed, when selecting your data for the first two arguments, you should think ahead about whether you want your output data to duplicate the headings in your table.<\/p>\n<section class=\"emaki-custom-block emaki-custom-note\" data-nosnippet=\"\">\n<div class=\"emaki-custom note\" id=\"custom_block_57\">\n<div class=\"custom_block-content note\">\n<p>Row fields and values arguments must be the same size. If you select headers in one, you must select headers in the other.<\/p>\n<\/p><\/div>\n<\/p><\/div>\n<\/section>\n<p>Finally, type a comma to move to the field headers argument and type:<\/p>\n<ul>\n<li>\n                                        <strong>1<\/strong> if you have selected the headings in the first two arguments, but you don&#8217;t want them to show in the result,\n                        <\/li>\n<li>\n                                        <strong>2<\/strong> if you haven&#8217;t selected headings in the first two arguments, but you want Excel to generate generic headings in the result, or\n                        <\/li>\n<li>\n                                        <strong>3<\/strong> if you have selected the headings in the first two arguments, and you want Excel to show them in the result.\n                        <\/li>\n<\/ul>\n<p>Here&#8217;s the result when I type:<\/p>\n<pre>=GROUPBY(A1:A21,D1:D21,AVERAGE,<strong>3<\/strong>)<\/pre>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\">\n<figure>\n<p>                <img data-recalc-dims=\"1\" decoding=\"async\" width=\"870\" height=\"481\" loading=\"lazy\" alt=\"The output of a GROUPBY formula that includes the field headers argument.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521856_131_How-to-Use-the-GROUPBY-Function-in-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521856_131_How-to-Use-the-GROUPBY-Function-in-Excel.png?resize=870%2C481&#038;ssl=1\" style=\"height:auto;max-width:100%\" title=\"\"><\/p>\n<\/figure><\/div>\n<\/p><\/div>\n<p>I can now format my duplicated column headings so that they&#8217;re clearly distinguishable from the data, just like in the original table.<\/p>\n<div class=\"table-container\">\n<table border=\"1\" cellpadding=\"1\" cellspacing=\"1\" align=\"\" summary=\"\">\n<thead>\n<tr>\n<th scope=\"col\" style=\"width:50%\">\n<p>    <span style=\"width:50%;height:0px\" \/><\/p>\n<p>The Benefit of Including Field Headers<\/p>\n<\/th>\n<th scope=\"col\" style=\"width:50%\">\n<p>    <span style=\"width:50%;height:0px\" \/><\/p>\n<p>The Drawback of Including Field Headers<\/p>\n<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>\n<p>If you change the headings in your original table, the output headings will adopt those changes.<\/p>\n<\/td>\n<td>\n<p>You can&#8217;t change the output headings if you want to make them more specific than the original table headings.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table><\/div>\n<h3 id=\"total-depth\">\n            Total Depth<br \/>\n    <\/h3>\n<p>The total depth argument lets you decide whether you want the result to display grand totals and, if you do, whether they should sit at the top or the bottom of your data. This argument also lets you choose whether to show subtotals.<\/p>\n<p>For the total depth argument, type:<\/p>\n<ul>\n<li>\n                                        <strong>0<\/strong> if you don&#8217;t want any totals or subtotals to be displayed,\n                        <\/li>\n<li>\n                                        <strong>1<\/strong> if you want only the grand total to be displayed at the bottom of the result,\n                        <\/li>\n<li>\n                                        <strong>2<\/strong> if you want subtotals to appear at the bottom of each result category, and a grand total at the bottom of the overall result,\n                        <\/li>\n<li>\n                                        <strong>-1<\/strong> if you want only the grand total to be displayed at the top of the result, or\n                        <\/li>\n<li>\n                                        <strong>-2<\/strong> if you want subtotals to appear at the top of each result category, and a grand total at the top of the overall result.\n                        <\/li>\n<\/ul>\n<section class=\"emaki-custom-block emaki-custom-note\" data-nosnippet=\"\">\n<div class=\"emaki-custom note\" id=\"custom_block_69\">\n<div class=\"custom_block-content note\">\n<p>The options to display subtotals (2 and -2) only work if the row fields argument contains more than one column of data (in other words, subfields).<\/p>\n<\/p><\/div>\n<\/p><\/div>\n<\/section>\n<p>In this example, I&#8217;ve typed:<\/p>\n<pre>=GROUPBY(A1:B21,C1:C21,SUM,,<strong>2<\/strong>)<\/pre>\n<p>\u200b\u200b\u200b\u200b\u200b\u200bwhich uses commas to skip over the field categories argument, and tells Excel that I want subtotals to appear beneath each category and a grand total at the bottom of the data. I&#8217;ve then applied direct formatting to the subtotal rows to make the data easier to read.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\">\n<figure>\n<p>                <img data-recalc-dims=\"1\" decoding=\"async\" width=\"980\" height=\"624\" loading=\"lazy\" alt=\"The total depth argument in the GROUPBY function in Excel is used to display subtotals in the output data, which are formatted in blue.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521856_625_How-to-Use-the-GROUPBY-Function-in-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521856_625_How-to-Use-the-GROUPBY-Function-in-Excel.png?resize=980%2C624&#038;ssl=1\" style=\"height:auto;max-width:100%\" title=\"\"><\/p>\n<\/figure><\/div>\n<\/p><\/div>\n<h3 id=\"sort-order\">\n            Sort Order<br \/>\n    <\/h3>\n<p>The sort order field lets you tell Excel whether and how you want to sort the result. Using this argument really highlights why the GROUPBY function can be more useful than using a pivot table: as soon as you change any data in your original table, the whole output data reorders according to the sort order argument, whereas pivot tables require a manual refresh.<\/p>\n<p>The number you enter for this argument represents the column in the result. For example, if you type <strong>1<\/strong>, this will sort the result by the first column in ascending or alphabetical order. On the other hand, typing <strong>-1<\/strong> will sort the result by the first column in descending or reverse alphabetical order.<\/p>\n<p>In this example, I&#8217;ve typed:<\/p>\n<pre>=GROUPBY(A1:A21,C1:C21,SUM,,,<strong>-2<\/strong>)<\/pre>\n<p>which sorts the second column (sales) in descending order.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\">\n<figure>\n<p>                <img data-recalc-dims=\"1\" decoding=\"async\" width=\"866\" height=\"445\" loading=\"lazy\" alt=\"The output of a GROUPBY formula that sorts the data by column 2.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521856_259_How-to-Use-the-GROUPBY-Function-in-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521856_259_How-to-Use-the-GROUPBY-Function-in-Excel.png?resize=866%2C445&#038;ssl=1\" style=\"height:auto;max-width:100%\" title=\"\"><\/p>\n<\/figure><\/div>\n<\/p><\/div>\n<h3 id=\"filter-array\">\n            Filter Array<br \/>\n    <\/h3>\n<p>The filter array argument is less likely than the previous optional arguments to be used, though it can come to the rescue if your original data table contains rows that could interrupt your data.<\/p>\n<p>In this example, the years in cells A2, A8, and A17 interrupt the GROUPBY function&#8217;s result.<\/p>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\">\n<figure>\n<p>                <img data-recalc-dims=\"1\" decoding=\"async\" width=\"870\" height=\"506\" loading=\"lazy\" alt=\"The output of a GROUPBY formula in Excel is interrupted by some cells containing years.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521856_99_How-to-Use-the-GROUPBY-Function-in-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521856_99_How-to-Use-the-GROUPBY-Function-in-Excel.png?resize=870%2C506&#038;ssl=1\" style=\"height:auto;max-width:100%\" title=\"\"><\/p>\n<\/figure><\/div>\n<\/p><\/div>\n<p> I can use the filter array argument to tell Excel to ignore any cells in column A that contain numbers through the ISNUMBER function:<\/p>\n<pre>=GROUPBY(A1:A24,C1:C24,SUM,,,,<strong>ISNUMBER(A1:A24)=FALSE<\/strong>)<\/pre>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\">\n<figure>\n<p>                <img data-recalc-dims=\"1\" decoding=\"async\" width=\"868\" height=\"547\" loading=\"lazy\" alt=\"The ISNUMBER function is used in the filter array argument of the GROUPBY function to discount years in column A.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521856_350_How-to-Use-the-GROUPBY-Function-in-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521856_350_How-to-Use-the-GROUPBY-Function-in-Excel.png?resize=868%2C547&#038;ssl=1\" style=\"height:auto;max-width:100%\" title=\"\"><\/p>\n<\/figure><\/div>\n<\/p><\/div>\n<div class=\"display-card  article article-card small  no-badge  active-content                      \" data-include-community-rating=\"false\" id=\"86c5-4560-9cb440658cb4\" data-nosnippet=\"\">\n<div class=\"w-img \">\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  img-featured-4-pin-single-size-featured-secondary\">\n<figure>\n<p>                <img data-recalc-dims=\"1\" decoding=\"async\" width=\"1200\" height=\"675\" loading=\"lazy\" alt=\"Microsoft Excel logo on a green background\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/How-to-Use-the-GROUPBY-Function-in-Excel.jpg\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/How-to-Use-the-GROUPBY-Function-in-Excel.jpg?resize=1200%2C675&#038;ssl=1\" style=\"height:auto;max-width:100%\" title=\"\"><\/p>\n<\/figure><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<p>                    <span data-field=\"label\" class=\"article-card-label\">Related<\/span><\/p>\n<div class=\"w-display-card-content regular article-block\">\n<h5 class=\"display-card-title \">\n<p>\t\t\tHow to Use the IS Functions in Microsoft Excel<\/p>\n<\/h5>\n<p class=\"display-card-excerpt\">Test your data with several simple functions.<\/p>\n<\/p><\/div>\n<\/p><\/div>\n<h3 id=\"field-relationship\">\n            Field Relationship<br \/>\n    <\/h3>\n<p>Finally, the field relationship argument controls how data is grouped when the row fields argument references more than one column.<\/p>\n<p>In this example, when the field relationship argument contains <strong>0<\/strong> (which is the default if the argument is omitted), GROUPBY returns a hierarchical result table, with each column individually represented with separate rows of data.<\/p>\n<pre>=GROUPBY(A1:B21,C1:C21,SUM,,,3,,0)<\/pre>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\">\n<figure>\n<p>                <img data-recalc-dims=\"1\" decoding=\"async\" width=\"982\" height=\"665\" loading=\"lazy\" alt=\"An Excel sheet containing the GROUPBY function with the field relationship argument set to 0.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521856_574_How-to-Use-the-GROUPBY-Function-in-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521856_574_How-to-Use-the-GROUPBY-Function-in-Excel.png?resize=982%2C665&#038;ssl=1\" style=\"height:auto;max-width:100%\" title=\"\"><\/p>\n<\/figure><\/div>\n<\/p><\/div>\n<p>On the other hand, when the field relationship argument contains <strong>1<\/strong>, GROUPBY returns a result table that ignores hierarchy and sorts each column independently. In other words, categories aren&#8217;t nested, which is why you also can&#8217;t include subtotals in the result when you choose this field relationship option.<\/p>\n<pre>=GROUPBY(A1:B21,C1:C21,SUM,,,3,,1)<\/pre>\n<div class=\"body-img landscape \">\n<div class=\"responsive-img  image-expandable  img-article-item\">\n<figure>\n<p>                <img data-recalc-dims=\"1\" decoding=\"async\" width=\"980\" height=\"504\" loading=\"lazy\" alt=\"An Excel sheet containing the GROUPBY function with the field relationship argument set to 1.\" data-img-url=\"https:\/\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521857_333_How-to-Use-the-GROUPBY-Function-in-Excel.png\" src=\"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521857_333_How-to-Use-the-GROUPBY-Function-in-Excel.png?resize=980%2C504&#038;ssl=1\" style=\"height:auto;max-width:100%\" title=\"\"><\/p>\n<\/figure><\/div>\n<\/p><\/div>\n<hr \/>\n<p> As well as using SUM and AVERAGE in the GROUPBY function argument, you can use the PERENTOF function, which turns the data into percentages to show the proportion a subset makes up of a whole dataset.<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Quick Links GROUPBY in Action: Using the Required Arguments Only GROUPBY in Action: Using the Optional Arguments Excel&#8217;s GROUPBY function lets you group and aggregate data based on certain fields in your table of data. It also offers arguments that allow you to sort and filter your data, so you can tailor the output to &#8230; <a title=\"How to Use the GROUPBY Function in Excel\" class=\"read-more\" href=\"https:\/\/goodwriterz.com\/site\/how-to-use-the-groupby-function-in-excel\/\" aria-label=\"Read more about How to Use the GROUPBY Function in Excel\">\u0625\u0642\u0631\u0623 \u0627\u0644\u0645\u0632\u064a\u062f<\/a><\/p>\n","protected":false},"author":1,"featured_media":97578,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[24],"tags":[39,313,288],"class_list":["post-97577","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-microsoft","tag-microsoft","tag-microsoft-365","tag-microsoft-excel"],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/goodwriterz.com\/site\/wp-content\/uploads\/2025\/04\/1743521859_How-to-Use-the-GROUPBY-Function-in-Excel.png?fit=1920%2C1080&ssl=1","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/posts\/97577","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/comments?post=97577"}],"version-history":[{"count":1,"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/posts\/97577\/revisions"}],"predecessor-version":[{"id":97579,"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/posts\/97577\/revisions\/97579"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/media\/97578"}],"wp:attachment":[{"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/media?parent=97577"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/categories?post=97577"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/goodwriterz.com\/site\/wp-json\/wp\/v2\/tags?post=97577"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}