Excel Formulas & Functions

How to Average in Excel While Excluding Zero Values

Learn practical methods to compute averages in Excel while excluding zero values. This guide covers AVERAGEIF, AVERAGEIFS, and dynamic array solutions with FILTER for Excel 365, plus tips for handling edge cases. According to XLS Library, excluding zeros improves data accuracy in dashboards and reports.

XLS Library Team

April 28, 2026·5 min read

Excel Tips Formula Syntax Data Cleaning Excel Formulas

Exclude Zeros in Averages - XLS Library — Photo by Kindel Media via Pexels

Why zeros distort averages and when to exclude them\nZeros in datasets are often placeholders for missing values or non-applicable entries. If you search for 'average excel exclude 0', you'll learn that zeros distort the computed mean, potentially skewing dashboards and reports. The goal is to calculate a mean that reflects only meaningful observations, not placeholders. In practice, excluding zeros helps you compare datasets on a like-for-like basis, especially when values of zero indicate missing data or no activity. Excel offers several robust approaches tailored to your version and workflow, from simple one-liners to dynamic array formulas. According to XLS Library, accurate data summaries hinge on correctly handling sentinel values like 0.\n\n`excel\n=AVERAGEIF(A2:A100, "<>0", A2:A100)\n`\n\nThis formula uses the same range for criteria and average values, ignoring zero entries so only non-zero data contribute to the result. For datasets with clean numeric values, this approach is reliable, widely supported across Excel versions, and easy to audit in shared workbooks.

Method 1: AVERAGEIF for non-zero values\nThe AVERAGEIF function is purpose-built for conditional averaging. It lets you specify a range, a criterion, and, optionally, an average_range. The simplest form excludes zeros by using the criterion "<>0". This section demonstrates both common variants and explains when to use each.\n\n`excel\n=AVERAGEIF(A2:A100, "<>0")\n`\nThis variant uses the same range for both the data and the criteria, which keeps formulas compact. If you want explicit control over the averaged data, you can provide the average_range as well:\n`excel\n=AVERAGEIF(A2:A100, "<>0", A2:A100)\n`\nBoth forms produce the same result, but the second is clearer when you have a separate data column and summary column.

Method 2: AVERAGEIFS for multiple criteria\nWhen zeros must be excluded under additional conditions, AVERAGEIFS is a robust choice. It averages values in a target range based on multiple criteria ranges. For example, you might only include rows where a flag column equals "Yes" and the value column is non-zero.\n\n`excel\n=AVERAGEIFS(A2:A100, A2:A100, "<>0")\n`\nTo add a second criterion, such as a status column B2:B100 = "Active":\n`excel\n=AVERAGEIFS(A2:A100, A2:A100, "<>0", B2:B100, "Active")\n`\nThese patterns scale to more complex datasets while preserving the exclusion of 0s from the final average.

Method 3: Dynamic arrays with FILTER (Excel 365)\nIf you are on Excel 365 or Excel 2021+, dynamic array functions let you filter the data first, then compute the average. This approach is particularly convenient for large datasets or when you want to compose multiple filters.\n\n`excel\n=AVERAGE(FILTER(A2:A100, A2:A100<>0))\n`\nYou can combine FILTER with other criteria, for example excluding zeros and removing blanks by using: `FILTER(A2:A100, (A2:A100<>0) * (A2:A100<>""))`. For readability, you can also use LET to name the filtered array:\n`excel\n=LET(nonZero, FILTER(A2:A100, A2:A100<>0), AVERAGE(nonZero))\n`

Related Articles

← More in Excel Formulas & Functions