[et_pb_section fb_built=”1″ admin_label=”section” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row admin_label=”row” _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Excel is usually the preferred option for data storage and transformation tasks, especially if you work with large datasets. If your daily routine involves analyzing or interpreting thousands of rows, then it can become overwhelming or, even worse, completely inefficient. If you work in Finance or Sales, it\u2019s likely that you\u2019ll need to identify patterns to make predictions. This will require aggregating cell ranges<\/a> or summarizing key data while keeping errors or broken formulas to a minimum.<\/p>\n In this article, you\u2019ll learn how to aggregate data in Excel using the AGGREGATE function by getting an in-depth explanation of the AGGREGATE function; then, a detailed view of the formula syntax; and a step-by-step illustration of how to calculate the sum and average of data using the AGGREGATE formula and Excel Power Query<\/a>.<\/p>\n [\/et_pb_text][et_pb_text module_id=”what-is-the-aggregate-formula-in-excel” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Excel introduced the AGGREGATE function in the 2010 version. You can use this function to aggregate data in Excel for different purposes, including sums or averages, while having the option to ignore hidden rows and errors.<\/p>\n The Excel AGGREGATE function can be expressed in two different ways, as a reference formula or an array. The great thing is you don\u2019t need to worry about which one to use since Excel will select the right one for you automatically based on your input parameters.<\/p>\n =AGGREGATE(function_num, options, ref1, [ref2], …)<\/p>\n or<\/p>\n =AGGREGATE(function_num, options, array, [k])<\/p>\n Below, you\u2019ll find what each parameter stands for:<\/p>\n [\/et_pb_text][et_pb_text module_id=”how-to-aggregate-data-in-excel” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You can aggregate data in Excel by using the reference formula or the array formula, as shown above. Here, you’ll learn how to use the reference form of the AGGREGATE formula to average data in Excel.<\/p>\n AGGREGATE functions can help you perform calculations to return a single value as a summary. The five most common functions are SUM, AVERAGE, COUNT, MAX, and MIN which are also easily accessible through an \u201cAutoSum\u201d button placed in the Home tab. However, these functions are basic and don\u2019t allow for more complex criteria. This is how you can use the AGGREGATE function in Excel to get accurate results.<\/p>\n Let\u2019s say you need to find the average loan amount given to your clients. However, you\u2019d like to ignore the errors included in the cell range. Applying the AVERAGE function to this range would result in an error. However, let\u2019s see what happens if we combine it with AGGREGATE.<\/p>\n 1.<\/strong> Type in \u201c=AGGREGATE(" in the first empty cell following your dataset. As you can see, Excel automatically prompts the two types of AGGREGATE formulae. Here, we will follow the reference syntax, since AVERAGE is one of the functions (1 to 13) that use it.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/how-to-aggregate-data-in-excel-type-aggregate.webp” alt=”How to aggregate data in Excel \u2014 =AGGREGATE( typed next to a loan dataset, with both formula forms in the tooltip” title_text=”=AGGREGATE( typed next to a loan dataset, with both formula forms in the tooltip” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 2.<\/strong> Click on the function_num parameter, and Excel will display the list of functions to choose from.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/how-to-aggregate-data-in-excel-function-num.webp” alt=”How to aggregate data in Excel \u2014 AGGREGATE tooltip with the function_num argument highlighted” title_text=”AGGREGATE tooltip with the function_num argument highlighted” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 3.<\/strong> Since we want to calculate the AVERAGE, we need to select \u201c1\u201d. Once you finish, type in the comma to proceed to the next parameter.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/how-to-aggregate-data-in-excel-average-function-1.webp” alt=”How to aggregate data in Excel \u2014 =AGGREGATE(1, entered to pick AVERAGE as the function” title_text=”=AGGREGATE(1, entered to pick AVERAGE as the function” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 4.<\/strong> Click on the options parameter and select \u201c6\u201d to ignore errors. Type in the comma to include the next parameter.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/how-to-aggregate-data-in-excel-ignore-errors-option.webp” alt=”How to aggregate data in Excel \u2014 =AGGREGATE(1,6, with option 6 to ignore error values” title_text=”=AGGREGATE(1,6, with option 6 to ignore error values” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 5.<\/strong> Click on the ref1 parameter and select the range you\u2019d like to calculate with your mouse. Enter the closing parenthesis to complete the formula and press \u201cEnter\u201d.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/how-to-aggregate-data-in-excel-select-range.webp” alt=”How to aggregate data in Excel \u2014 =AGGREGATE(1,6,F2:F43) with the LOAN AMOUNT column selected” title_text=”=AGGREGATE(1,6,F2:F43) with the LOAN AMOUNT column selected” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 6.<\/strong> As you can see, the AGGREGATE function has returned the AVERAGE of the values in the selected cell range without any errors.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/how-to-aggregate-data-in-excel-aggregate-result.webp” alt=”How to aggregate data in Excel \u2014 AGGREGATE returning an average loan amount of 4410.36″ title_text=”AGGREGATE returning an average loan amount of 4410.36″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now that you\u2019ve seen how easy it is to aggregate data using the AGGREGATE function to average values, let\u2019s see how you can aggregate data in Power Query.<\/p>\n If you need to summarize data from various sources, using Excel Power Query is the best option. Here, you\u2019ll see how to include a \u201cNew Source\u201d in Power Query and apply aggregate features to perform basic calculations such as SUM or AVERAGE.<\/p>\n 1.<\/strong> Go to Data > Get Data > From Table\/Range.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/aggregate-data-excel-power-query-get-data.webp” alt=”Aggregate data in Excel with Power Query \u2014 Data tab with Get Data and From Table\/Range on the ribbon” title_text=”Data tab with Get Data and From Table\/Range on the ribbon” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 2.<\/strong> You can select this option directly from the ribbon group, as shown below.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/aggregate-data-excel-power-query-from-table-range.webp” alt=”Aggregate data in Excel with Power Query \u2014 From Table\/Range selected in the Get and Transform Data group” title_text=”From Table\/Range selected in the Get and Transform Data group” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 3.<\/strong> Excel will prompt the \u201cCreate Table\u201d settings box to confirm the range you\u2019d like to edit in Power Query.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/aggregate-data-excel-power-query-create-table.webp” alt=”Aggregate data in Excel with Power Query \u2014 Create Table dialog confirming the data range and headers” title_text=”Create Table dialog confirming the data range and headers” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 4.<\/strong> In the Power Query Editor, go to Home > New Source > File to add data from a different file.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/aggregate-data-excel-power-query-new-source-file.webp” alt=”Aggregate data in Excel with Power Query \u2014 Power Query Editor with the New Source and File menu open” title_text=”Power Query Editor with the New Source and File menu open” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 5.<\/strong> Select the file to import data from and then click on \u201cImport\u201d.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/aggregate-data-excel-power-query-import-file.webp” alt=”Aggregate data in Excel with Power Query \u2014 Import Data window with the CLIENTS 2022 workbook selected” title_text=”Import Data window with the CLIENTS 2022 workbook selected” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 6.<\/strong> In the \u201cNavigator\u201d window, you\u2019ll find a folder containing the new data to import into Power Query. Select the new table source and click \u201cOK\u201d.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/aggregate-data-excel-power-query-navigator.webp” alt=”Aggregate data in Excel with Power Query \u2014 Navigator window previewing the data table from CLIENTS 2022″ title_text=”Navigator window previewing the data table from CLIENTS 2022″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 7.<\/strong> Go to Transform > Group By.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/aggregate-data-excel-power-query-group-by.webp” alt=”Aggregate data in Excel with Power Query \u2014 Group By dialog grouping by LOAN AMOUNT with Count Rows” title_text=”Group By dialog grouping by LOAN AMOUNT with Count Rows” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 8.<\/strong> This will prompt a new window where you can specify the columns you\u2019d like to group. To use the aggregate features click on the \u201cAdvanced\u201d option.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/aggregate-data-excel-power-query-group-by-advanced.webp” alt=”Aggregate data in Excel with Power Query \u2014 Group By dialog with the Advanced option selected” title_text=”Group By dialog with the Advanced option selected” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 9.<\/strong> Select the first column to group from the drop-down menu. Here, I\u2019ve selected \u201cLOAN AMOUNT\u201d. If you need to include more columns, click on the \u201cAdd grouping\u201d button below.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/aggregate-data-excel-power-query-add-grouping.webp” alt=”Aggregate data in Excel with Power Query \u2014 SUM column set to Sum of LOAN AMOUNT in the Group By dialog” title_text=”SUM column set to Sum of LOAN AMOUNT in the Group By dialog” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 10.<\/strong> To aggregate data according to your preferred calculation, simply type in a name in the \u201cNew column name\u201d, select the \u201cOperation\u201d, and then the \u201cColumn\u201d containing the values you\u2019d like to calculate. Click on the \u201cAdd aggregation\u201d button to include as many operations as needed.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/aggregate-data-excel-power-query-add-aggregation.webp” alt=”Aggregate data in Excel with Power Query \u2014 second AVG aggregation with the column list open” title_text=”Second AVG aggregation with the column list open” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 11.<\/strong> Once you\u2019ve selected your criteria to aggregate, click \u201cOK\u201d.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/aggregate-data-excel-power-query-click-ok.webp” alt=”Aggregate data in Excel with Power Query \u2014 SUM and AVG aggregations on LOAN AMOUNT and INTEREST RATE” title_text=”SUM and AVG aggregations on LOAN AMOUNT and INTEREST RATE” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 12.<\/strong> As shown below, Power Query automatically outputs the result from all aggregate functions you selected. Moreover, in the \u201cQuery Settings\u201d right-side pane, it lists all the actions, i.e., queries, taken to arrive at this result.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/aggregate-data-excel-power-query-result.webp” alt=”Aggregate data in Excel with Power Query \u2014 grouped table with LOAN AMOUNT, SUM, and AVG columns” title_text=”Grouped table with LOAN AMOUNT, SUM, and AVG columns” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”conclusion” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n One of the most attractive aspects of Excel is the wide range of formulae and calculation features to help users transform and process large amounts of data quickly and easily. Although using Power Query may require more advanced knowledge in Excel, the basic AGGREGATE functions already unlock powerful operations that could not be done manually.<\/p>\n You should now feel comfortable with the AGGREGATE formula in Excel and the basic functions. What\u2019s more, you should now be able to apply the formula depending on what you need to calculate.<\/p>\n If you would like to learn more about the data processing and transformation features Excel has to offer, read through any of the following articles:<\/p>\n AGGREGATE isn’t the only way to summarize a range. For totals that depend on a condition, try SUMIF in Excel<\/a>. To group and total a large table without writing formulas, build a pivot table in Excel<\/a>. And if the numbers you need sit in several workbooks, you can consolidate Excel files automatically<\/a> with Sheetgo, an AI-powered data automation platform that keeps the combined data up to date. Try Sheetgo for free<\/a> with your own files.<\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" Aprenda a agregar dados no Excel com a fun\u00e7\u00e3o AGGREGATE, que pode ignorar erros e linhas ocultas, ou com Agrupar por no Power Query.<\/p>","protected":false},"author":2,"featured_media":265422,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[51],"tags":[],"class_list":["post-265403","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-features"],"acf":[],"permalink_manager":null,"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/265403","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=265403"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/265403\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/265422"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=265403"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=265403"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=265403"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}What is the AGGREGATE formula in Excel?<\/h2>\n
AGGREGATE formula syntax<\/h3>\n
\n
How to aggregate data in Excel?<\/h2>\n
How to use the AGGREGATE function in Excel?<\/h3>\n
How to aggregate data using Excel Power Query?<\/h3>\n
Conclusion<\/h2>\n
\n