Tracking Spending Patterns with AVERAGE and Basic Summaries

Why averages and simple summaries reveal spending patterns

When you look at a list of transactions, it’s easy to focus on individual purchases (“That coffee was only $4”). Patterns are harder to see because they live in the relationship between many rows: how much you spend on a typical day, whether weekends cost more than weekdays, which categories are consistently high, and whether your “normal” month is drifting upward.

In Excel, the AVERAGE family of functions and a few basic summary tools help you answer questions like:

• What is my average daily spend this month?
• What is my average transaction size?
• Do I spend more on weekends?
• What is my average spend per category?
• How many days did I spend nothing (or overspend)?

This chapter focuses on using AVERAGE, AVERAGEIF, AVERAGEIFS, and a small set of companion functions (COUNT, COUNTA, COUNTIF, MIN, MAX) to build clear summaries. You’ll create a “Spending Patterns” section that updates as you add new transactions.

Set up a clean transaction table for analysis

To track patterns, you need consistent fields. Use a single transaction list where each row is one transaction. A typical structure:

• Date (real Excel date)
• Description (text)
• Category (text)
• Amount (number; spending as positive values is simplest for averages)
• Account/Payment Method (optional)
• Notes (optional)

Convert the range into an Excel Table so formulas expand automatically when you add rows. Select any cell in the range and use Insert > Table, then confirm “My table has headers.” In the examples below, assume your table is named Transactions and has columns:

Continue in our app.

You can listen to the audiobook with the screen off, receive a free certificate for this course, and also have access to 5,000 other free online courses.

Or continue reading below...

Download the app

• Transactions[Date]
• Transactions[Category]
• Transactions[Amount]

Keep amounts as positive for expenses. If you also track income in the same table, consider adding a Type column (Expense/Income) or keep income negative; either way, be consistent so averages mean what you think they mean.

Understand what AVERAGE is actually calculating

AVERAGE returns the arithmetic mean: sum of values divided by the count of numeric values. It ignores blank cells, but it does not ignore zeros. That difference matters:

• If you want “average transaction size,” you usually want to average only the transaction amounts (zeros typically don’t exist).
• If you want “average daily spend,” you must decide whether days with no spending should count as zero (often yes, because they are part of your real life pattern).

Basic syntax:

=AVERAGE(Transactions[Amount])

This gives your average transaction amount across all rows in the table. It answers: “When I spend, how big is a typical transaction?” It does not answer: “How much do I spend per day?” because some days have multiple transactions and some have none.

Common pitfalls with averages

• Outliers: One large annual bill can inflate the average transaction size. You can still use averages, but interpret them alongside MIN/MAX or consider filtering to a time window.
• Mixed signs: If income is negative and expenses are positive, the average may drift toward zero and stop being meaningful. Use criteria-based averages (AVERAGEIF/AVERAGEIFS) to isolate expenses.
• Text numbers: If amounts are stored as text, AVERAGE will ignore them. Ensure Amount is numeric (format as Currency/Number).

Build a “basic summary” panel (transaction-level)

Create a small summary area on a separate sheet (for example, a sheet named Patterns). Start with transaction-level metrics that require no grouping.

Average transaction amount

=AVERAGE(Transactions[Amount])

If you track both income and expenses and want only expenses (positive amounts), use:

=AVERAGEIF(Transactions[Amount],">0",Transactions[Amount])

This averages only values greater than 0.

Smallest and largest transaction

=MIN(Transactions[Amount])

=MAX(Transactions[Amount])

If you have refunds or negative values, you may want to restrict to expenses:

=MINIFS(Transactions[Amount],Transactions[Amount],">0")

=MAXIFS(Transactions[Amount],Transactions[Amount],">0")

How many transactions?

Count numeric amounts:

=COUNT(Transactions[Amount])

Count non-empty dates (useful if Amount might be blank while you’re entering data):

=COUNTA(Transactions[Date])

Average transactions per day (within a period) becomes meaningful once you define the period and count days; you’ll do that later.

Average spending by category with AVERAGEIF

Category patterns are often more actionable than overall averages. You can compute the average transaction size per category (not total per category) using AVERAGEIF.

Step-by-step: create a category average table

Step 1: List categories. In Patterns, create a column with your categories (e.g., Groceries, Dining, Transport, Utilities). If you already maintain a category list elsewhere, reference it.

Step 2: Average transaction amount for each category. Next to each category name (assume the category is in cell A2), use:

=AVERAGEIF(Transactions[Category],A2,Transactions[Amount])

Copy down for all categories.

Step 3: Add a transaction count per category (context for the average). Averages are more reliable with more data. Use:

=COUNTIF(Transactions[Category],A2)

Step 4: Add a max transaction per category (spot spikes).

=MAXIFS(Transactions[Amount],Transactions[Category],A2)

Now you can see, for example, that Dining might have an average of $18 with a max of $95, which tells you there are occasional big meals driving volatility.

Interpretation tip: average transaction vs average monthly category spend

AVERAGEIF on the transaction list tells you “typical purchase size” in that category. That’s different from “typical monthly spend in that category.” To get monthly averages, you need to summarize by month first (grouping), then average those monthly totals. You’ll do a practical approach to that using a helper month field and AVERAGEIFS.

Average spending in a time window (this month, last 30 days)

Patterns are most useful when they reflect your current behavior. Instead of averaging across your entire history, calculate averages for a defined period.

Define start and end dates in cells

On Patterns, set:

• B1: Start Date
• C1: End Date

Then enter values such as the first and last day of the current month. If you prefer formulas for “current month,” you can use:

=EOMONTH(TODAY(),-1)+1

for the first day of the current month, and:

=EOMONTH(TODAY(),0)

for the last day of the current month.

Average transaction amount within the date range

Use AVERAGEIFS to apply multiple criteria: date between start and end, and optionally Amount > 0.

=AVERAGEIFS(Transactions[Amount],Transactions[Date],">="&$B$1,Transactions[Date],"<="&$C$1,Transactions[Amount],">0")

This answers: “When I spend during this period, what is my typical transaction size?”

Average spend per category within the date range

Assuming category name in A2:

=AVERAGEIFS(Transactions[Amount],Transactions[Category],$A2,Transactions[Date],">="&$B$1,Transactions[Date],"<="&$C$1,Transactions[Amount],">0")

This is useful for comparing “typical purchase size” in Groceries this month vs last month by changing the date range.

Daily spending patterns: average daily spend (including zero-spend days)

To understand your pace, you usually want an average per day that includes days you spent nothing. That requires a daily summary (one row per date) because your transaction table has multiple rows per day.

Step-by-step: build a daily totals table

Step 1: Create a list of dates for the period. On Patterns, create a column labeled Date (e.g., starting in E2). Fill a sequence from your Start Date to End Date. If you have Excel 365, you can generate it with:

=SEQUENCE($C$1-$B$1+1,1,$B$1,1)

This spills a vertical list of dates.

Step 2: Calculate total spend per day. Next to each date (assume date in E2), compute the day’s spending using SUMIFS (even though this chapter focuses on averages, you need daily totals to average them):

=SUMIFS(Transactions[Amount],Transactions[Date],E2,Transactions[Amount],">0")

Copy down alongside the date list (or let it spill if you’re using dynamic arrays with a separate approach).

Step 3: Average daily spend including zero days. Now average the daily totals column (assume totals are in F2:F32):

=AVERAGE(F2:F32)

This answers: “On an average day in this period, how much did I spend?” Because your date list includes every day, days with no spending appear as 0 and are included.

Average daily spend excluding zero-spend days (optional)

If you want “average spend on days when I spent something,” exclude zeros:

=AVERAGEIF(F2:F32,">0")

Comparing the two is insightful:

• Including zeros reflects your overall pace.
• Excluding zeros reflects how intense your spending is on active days.

Count zero-spend days

Zero-spend days are a pattern of their own:

=COUNTIF(F2:F32,0)

And the number of spend days:

=COUNTIF(F2:F32,">0")

Weekday vs weekend: average daily spend by day type

A common pattern is higher spending on weekends. You can quantify it by labeling each date as weekday/weekend and averaging daily totals by that label.

Step-by-step: add a day-type helper column

Step 1: Determine day of week. Next to each date in your daily table (date in E2), compute a weekday number (Monday=1 … Sunday=7):

=WEEKDAY(E2,2)

Step 2: Create a Weekend flag. In the next column, mark weekend as TRUE/FALSE:

=WEEKDAY(E2,2)>5

This returns TRUE for Saturday (6) and Sunday (7).

Step 3: Average daily totals for weekends vs weekdays. If daily totals are in F2:F32 and weekend flags in G2:G32:

=AVERAGEIF(G2:G32,TRUE,F2:F32)

=AVERAGEIF(G2:G32,FALSE,F2:F32)

Now you can see whether weekends are consistently more expensive, and by how much per day.

Average by each weekday (Mon–Sun)

To go further, average by weekday number. If weekday numbers are in H2:H32:

=AVERAGEIF($H$2:$H$32,1,$F$2:$F$32)

Copy across for 1 through 7. This can reveal patterns like “Fridays are my highest spend day” or “Mondays are low because I meal prep.”

Monthly patterns: average monthly spend (without repeating budget setup)

To understand whether your spending is trending up, you want monthly totals and then an average of those totals. This is different from averaging transactions because months have different numbers of transactions and different numbers of days.

Step-by-step: create a Month key and monthly totals table

Step 1: Add a Month key in the Transactions table. Add a new column named MonthKey that converts each transaction date to the first day of its month:

=EOMONTH([@Date],-1)+1

This produces a real date (e.g., 2026-01-01) that is easy to group and filter. Format it as mmm yyyy for readability.

Step 2: Create a list of months to summarize. On Patterns, list the MonthKey values you care about (e.g., the last 12 months). You can type the first month and fill down by adding one month each row using:

=EDATE(previous_cell,1)

Step 3: Calculate total spend per month. If the month key is in K2:

=SUMIFS(Transactions[Amount],Transactions[MonthKey],K2,Transactions[Amount],">0")

Step 4: Average monthly spend across the months listed. If monthly totals are in L2:L13:

=AVERAGE(L2:L13)

This answers: “Across these months, what is my typical monthly spending?”

Average monthly spend by category (powerful pattern view)

To see which categories are consistently heavy, you can compute monthly totals per category and then average those monthly totals. A simple approach is to build a small grid: months down the rows, categories across columns, each cell is a monthly total for that category, then average each category column.

Example: MonthKey in K2:K13, categories in M1:R1. In cell M2 (month row, category column):

=SUMIFS(Transactions[Amount],Transactions[MonthKey],$K2,Transactions[Category],M$1,Transactions[Amount],">0")

Copy across and down. Then average each category’s monthly totals (for Groceries in M2:M13):

=AVERAGE(M2:M13)

This gives “typical monthly spend in Groceries,” which is often more actionable than average transaction size.

Handling missing data and avoiding misleading averages

When AVERAGE returns an error

If there are no matching values (for example, no Dining transactions in the selected date range), AVERAGEIF/AVERAGEIFS returns #DIV/0!. That’s not a calculation mistake; it means “no data.” If you want a cleaner display, wrap the formula:

=IFERROR(AVERAGEIFS(Transactions[Amount],Transactions[Category],$A2,Transactions[Date],">="&$B$1,Transactions[Date],"<="&$C$1,Transactions[Amount],">0"),0)

Use 0 only if it makes sense for your report; sometimes leaving the error visible is a better reminder that you have no observations.

Use counts to judge reliability

Pair every average with a count of observations:

=COUNTIFS(Transactions[Category],$A2,Transactions[Date],">="&$B$1,Transactions[Date],"<="&$C$1,Transactions[Amount],">0")

An average based on 2 transactions is less stable than one based on 25.

Compare average to median (optional check)

If you notice that one or two big purchases distort your average transaction size, the median can be a useful comparison because it’s less sensitive to outliers. If you have Excel 365, you can compute a median for a filtered set using dynamic arrays (advanced), but even without that, you can at least compute the overall median:

=MEDIAN(Transactions[Amount])

If the median is far lower than the average, your spending has occasional spikes. That’s not “bad,” but it changes what “typical” means.

Turn summaries into a simple pattern dashboard section

Once you have the calculations, arrange them into a compact block that you can scan quickly. A practical layout:

• Period selector: Start Date, End Date
• Core metrics: Average daily spend (incl. zeros), Average daily spend (spend days only), Zero-spend days, Average transaction size, Max transaction
• Weekend vs weekday: Two averages side-by-side
• Category table: Category, Avg transaction (period), Count, Max transaction
• Monthly trend table: Month, Total spend, plus an average of monthly totals

Format tips that improve readability without changing the math:

• Format averages and totals as Currency with consistent decimals.
• Use conditional formatting on the category table to highlight the highest averages or highest max values.
• Sort categories by average transaction size or by average monthly spend (if you built the monthly grid) to surface the biggest drivers.

Practice scenario: answer three pattern questions with your sheet

Question 1: “Am I spending more on weekends?”

Use the daily totals table and the weekend flag. Compare:

• =AVERAGEIF(G2:G32,TRUE,F2:F32) (weekend average)
• =AVERAGEIF(G2:G32,FALSE,F2:F32) (weekday average)

If weekend average is higher, check whether it’s consistent (many weekends slightly higher) or driven by one big weekend purchase (look at the MAX daily total on weekends).

Question 2: “What’s my typical grocery trip this month?”

Set Start/End dates to the current month and calculate:

=AVERAGEIFS(Transactions[Amount],Transactions[Category],"Groceries",Transactions[Date],">="&$B$1,Transactions[Date],"<="&$C$1,Transactions[Amount],">0")

Then add the count:

=COUNTIFS(Transactions[Category],"Groceries",Transactions[Date],">="&$B$1,Transactions[Date],"<="&$C$1,Transactions[Amount],">0")

A typical grocery trip of $42 with 10 trips suggests a different strategy than $85 with 5 trips.

Question 3: “What is my average monthly spending over the last 6 months?”

List the last 6 MonthKey values, compute monthly totals with SUMIFS, then:

=AVERAGE(L2:L7)

If you see one unusually high month, compare the average to the median of those monthly totals (optional) to understand whether that month is distorting your “typical” month.