How to Find Average in Excel: 7 Methods From Beginner to Power User

Finding an average in Excel sounds simple until your data set has blanks, errors, text values, or thousands of rows you do not want to manually highlight. The good news is Excel gives you at least seven distinct ways to calculate a mean, and the right one depends on what kind of data you are working with.

This guide walks through every method, shows you the exact keystrokes, and explains the gotchas that quietly skew your numbers. If you are completely new to formulas, start with the basics in our Excel skills guide and then come back. For a deeper look at the formula itself, the Excel AVERAGE formula walkthrough breaks down syntax in plain English.

The most common method is the AVERAGE function. Type =AVERAGE(A2:A100) in any cell and press Enter. Excel adds every numeric value in the range and divides by the count of those numbers. Text entries and blank cells are ignored, which is what most people want. A literal zero, however, is counted as a real value, so a row containing a 0 will pull your mean down. You can mix ranges and individual cells. =AVERAGE(A2:A10, C2:C10, F5) averages everything in both ranges plus cell F5 in one shot. That single-formula flexibility is why veterans rarely reach for a calculator.

The second-fastest method requires no typing at all. Click the cell where you want the result, open the Home tab, then click the small arrow beside the AutoSum button (the Sigma symbol). Choose Average from the dropdown. Excel guesses the range above or to the left and inserts the formula for you. Press Enter to confirm. AutoSum saves time when you are working with a tidy column of numbers, but always glance at the highlighted range before you accept it. Excel sometimes stops at the first blank cell, which means you might miss data further down the sheet.

The third method is the status bar. Highlight any group of cells containing numbers and look at the bottom right corner of the Excel window. You will see Average, Count, and Sum already calculated. This is the fastest way to peek at a mean without committing to a formula. Right-click the status bar to add or remove indicators such as Min, Max, or Numerical Count. The status bar approach is perfect for quick sanity checks during data review. You can also use it to verify that a formula you wrote returns the same answer as the built-in calculator.

What if you only want to average values that meet a condition? That is where AVERAGEIF earns its keep. The syntax is =AVERAGEIF(range, criteria, [average_range]). For example, =AVERAGEIF(B2:B100, ">0") averages only the positive numbers in column B. The first argument is the range to test, the second is the rule, and the optional third is the range of values you actually want averaged when the rule passes. Imagine column A holds salesperson names and column B holds revenue. =AVERAGEIF(A2:A100, "Maria", B2:B100) returns Maria's average revenue. Wildcards work too, so "Mar*" catches Maria, Marcus, and Mark.

Need more than one condition? Reach for AVERAGEIFS. Note the trailing S. The syntax flips the argument order slightly: =AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, ...). The values you want to average come first, followed by pairs of test ranges and rules.

Suppose you want the average revenue for Maria in Q1 only. Assuming column C stores quarter labels, you would write =AVERAGEIFS(B2:B100, A2:A100, "Maria", C2:C100, "Q1"). Excel only averages rows where both conditions are true. If no rows match, you get the #DIV/0! error rather than a zero, which is a useful warning that your filter is too tight.

Method six is the pivot table approach. Select your data, press Alt+N, V, T to launch the Insert PivotTable dialog, and drop your numeric field into the Values area. By default Excel sums it, but click the dropdown beside the field, choose Value Field Settings, and switch the summary function to Average.

Pivot tables shine when you need averages broken down by category. Drag a text field such as Department or Region into the Rows area and you instantly see one average per group. No formulas required, and the result updates when you refresh the pivot. If you have not built one before, our conditional formatting guide pairs well with pivots for visual reporting.

The seventh method handles a specific headache: averaging when zeros should be excluded. AVERAGE counts zero as a real number, which can distort metrics like average order value. The fix is =AVERAGEIF(A2:A100, "<>0"). The criteria means "not equal to zero", so Excel ignores those cells entirely. If your data also contains blanks that Excel mistakenly counts (rare, but it happens with imported CSV files containing empty strings), combine conditions in AVERAGEIFS with two criteria pairs. Yes, the syntax looks ugly, but it works reliably across every Excel version.

Errors derail averages faster than anything else. A single #N/A or #DIV/0! in your range causes AVERAGE to return that same error. The cleanest fix is AVERAGEIF with a numeric criterion, since it implicitly skips error values. Another option is the array formula =AVERAGE(IFERROR(A2:A100, "")) entered with Ctrl+Shift+Enter in older Excel versions, or just Enter in Excel 365 and 2021. You can also wrap the inner range in IFERROR before passing it to AVERAGE, which converts errors to empty strings that AVERAGE happily ignores. Test on a small sample first — array formulas can be slow on huge ranges.

A weighted average is different from a simple mean. If three exam scores are worth 20%, 30%, and 50% of the final grade, you cannot just average them. Use SUMPRODUCT instead. The formula =SUMPRODUCT(scores, weights) / SUM(weights) multiplies each score by its weight, totals the results, and divides by the sum of weights. Replace "scores" and "weights" with your actual ranges, such as B2:B4 and C2:C4. Our deep dive on weighted average in Excel covers more variations, including weighted averages across pivot data and grouped categories.

Sometimes you need a moving average, also called a rolling average. That is the mean of the last N values in a series, useful for smoothing out daily sales or stock prices. The cleanest formula uses OFFSET: =AVERAGE(OFFSET(B2,0,0,-7,1)) calculates the average of the current cell and the six cells above it.

Drag the formula down the column and you get a 7-day rolling mean. For larger datasets, Excel includes a Data Analysis ToolPak with a built-in Moving Average tool. Enable it from File > Options > Add-ins, then find it under Data > Data Analysis. It outputs the smoothed series and optionally a chart.

Averaging across multiple worksheets is another common need. If every sheet has the same layout and your value is always in cell B10, the 3D reference =AVERAGE(Sheet1:Sheet12!B10) averages B10 across all 12 sheets. The colon between sheet names creates a 3D reference, and Excel walks every sheet in between. This trick is gold for monthly reports, departmental summaries, or any workbook that follows a repeating template. The only catch is that the sheet order matters. If someone reorders the tabs, your formula silently grabs different sheets without warning you.

Beginners often confuse AVERAGE with AVERAGEA. Both calculate means, but AVERAGEA treats text values as zero and counts logical TRUE as 1 and FALSE as 0. AVERAGE ignores all non-numeric entries entirely. If your column contains the word "pending" mixed in with numbers, AVERAGE skips those rows, while AVERAGEA pulls your mean down by treating "pending" as zero.

Pick deliberately. Another gotcha: numbers stored as text. If you imported data and your numbers are left-aligned with a small green triangle, they are text strings. AVERAGE ignores them, returning a misleadingly small dataset. Fix it by selecting the range and choosing Convert to Number.

Want only the average of visible cells after applying a filter? Standard AVERAGE includes hidden rows. Use SUBTOTAL with function number 1 instead: =SUBTOTAL(1, B2:B100). The 1 tells SUBTOTAL to compute an average, and it automatically excludes rows hidden by a filter. If you want it to also ignore manually hidden rows, switch to function 101. SUBTOTAL is essential for filtered tables and dashboards where users toggle data on and off. The AGGREGATE function offers similar control with even more options, including ignoring errors and other nested SUBTOTAL formulas inside the range.

If your data lives in a structured Excel table (Ctrl+T), AVERAGE accepts column references. =AVERAGE(Sales[Revenue]) averages the Revenue column of the Sales table. The formula automatically expands as you add rows to the table — no more dragging or updating ranges by hand. Tables also pair perfectly with AVERAGEIF and AVERAGEIFS. =AVERAGEIF(Sales[Region], "West", Sales[Revenue]) reads almost like English. If you are not using Excel tables yet, you are leaving real productivity on the table — and breaking the connection to other dynamic features like slicers and the Data Model.

For people who hate formulas, the Quick Analysis tool offers a one-click solution. Select your data range and look for the small icon that appears at the bottom right. Click Totals, then Average. Excel inserts a formula in the row below, no typing required. The shortcut is Ctrl+Q if you prefer the keyboard. This works well for ad hoc reports and demos, but understand that it is just a friendly wrapper around the AVERAGE function. The result is identical to what you would get with a formula. The advantage is speed when you are presenting data live to an audience.

One last advanced trick: averaging the top N or bottom N values. Use =AVERAGE(LARGE(A2:A100, {1,2,3})) to get the mean of the top three values. The LARGE function returns the kth largest value, and the array constant tells it to return positions 1, 2, and 3. AVERAGE wraps the three results to give you a mean. Reverse the logic with SMALL for the bottom N. This is invaluable for trimmed-mean calculations, where analysts deliberately drop outliers before averaging. It is also how you build leaderboards that report "average of top performers" without manual sorting steps.

Whichever method you choose, sanity-check your result. Compare the answer from a formula with what the status bar shows when you highlight the same range. If they differ, you have hidden rows, errors, or text values that one calculation is treating differently. That two-second verification step prevents embarrassing mistakes in reports that go to executives. Once you are comfortable averaging, the natural next step is filtering and grouping your data. Our walkthrough on conditional formatting in Excel shows how to color-code values above or below the average automatically.

The TRIMMEAN function deserves its own moment. =TRIMMEAN(A2:A100, 0.1) calculates the average after excluding the top 5% and bottom 5% of values (the second argument is the total fraction trimmed, split equally between high and low). This is a statistician's favorite for noisy data sets where a handful of extreme outliers would otherwise distort the mean. Use it for survey scores, sensor readings, or anything where a few wild values do not represent the true center of the distribution. TRIMMEAN is built into every modern Excel version.

Sometimes the "average" you actually want is the median, not the mean. The mean adds everything and divides; the median is the middle value when the data is sorted. For skewed data like salaries or home prices, the median better represents the typical case because it ignores extreme outliers on either end. Use =MEDIAN(A2:A100) for the middle value or =MODE.SNGL(A2:A100) for the most frequently repeated value. Pick the right measure of central tendency for your story, not just the default.

Excel also exposes named ranges that simplify long formulas. Define a name like "Sales" for A2:A100 using the Name Box or Formulas > Define Name. Now =AVERAGE(Sales) works regardless of where Sales lives, and updating the range definition automatically updates every formula that references it.

Named ranges become essential in shared workbooks where formulas need to survive column insertions and reorganizations. They also make complex formulas readable: =AVERAGEIFS(Revenue, Region, "West", Quarter, "Q1") tells the next person who opens the sheet exactly what is being calculated.

Performance matters once your data sets cross 50,000 rows. AVERAGE itself is fast, but AVERAGEIFS with several criteria slows down because Excel evaluates each condition for every row. If you find yourself waiting, consider replacing the formula with a pivot table or, for repeated lookups, a helper column that pre-computes the condition once.

Lastly, document your averages. A small note in an adjacent cell explaining "Average excludes zeros and only counts Q1 sales" saves your future self hours of re-deriving the logic three months later. Pair it with a named range and the workbook becomes self-explanatory.

Beyond the built-in functions, Power Query offers a different path. Load your data with Get & Transform, then use the Statistics ribbon to compute an average in seconds. Power Query refreshes automatically when the source changes, making it the right choice for recurring monthly reports rather than one-off calculations.

VBA macros are another option for repeatable averaging tasks. A short macro using Application.WorksheetFunction.Average lets you build custom reporting that goes far beyond a single cell. For most people the formulas covered above are more than enough, but it is worth knowing the ceiling.

Master these patterns and the rest of your Excel formula work gets dramatically easier — averages are a stepping stone to nearly every analytical function in the application, including the lookup and aggregate families.