To chart spending by day of the week, first total your transactions for each date, add a helper column with =WEEKDAY(date, 2), then use AVERAGEIFS to average those daily totals for each weekday and plot the seven results as a column chart. The daily totals step matters: averaging raw transactions gives spend per purchase, not per day.
Why daily totals first?
Suppose Saturday has three small purchases of 100 and Sunday has one of 600. Averaging transactions says Saturday 100 and Sunday 600. Averaging days says Saturday 300 and Sunday 600. You want the second. Days with no spending must also appear as zero rows, or the averages come out too high.
Build the daily sheet
On a new sheet, put the first date in A2 and =A2+1 in A3, filled down for 91 days. Then add:
- Column B, spend that day: =SUMIFS(Data[Amount], Data[Date], A2)
- Column C, weekday number: =WEEKDAY(A2, 2), which gives Monday as 1 and Sunday as 7
- Column D, weekday name, if you want labels: =TEXT(A2, "ddd")
The 2 in WEEKDAY switches to a Monday-first week. Without it Excel counts Sunday as 1, which is easy to forget.
Average by weekday
In a summary block, list 1 to 7 in E2:E8 and names in F2:F8. In G2 write:
=AVERAGEIFS($B$2:$B$92, $C$2:$C$92, E2)
Copy it down to G8. The first range is what you average, the second is the criteria range, and E2 is the criteria. Select F2:G8 and insert a clustered column chart. Sort order stays Monday to Sunday, which is how people read a week.
| Weekday | Average daily spend (₹) | Days in sample |
|---|---|---|
| Monday | 410 | 13 |
| Tuesday | 380 | 13 |
| Wednesday | 395 | 13 |
| Thursday | 420 | 13 |
| Friday | 760 | 13 |
| Saturday | 1,150 | 13 |
| Sunday | 940 | 13 |
These are invented numbers for a 91-day window. In that case the pattern is clear: Friday to Sunday run well above midweek. Checking days in sample is =COUNTIFS($C$2:$C$92, E2), and a quick sanity check is that seven rows of 13 add up to 91.
How much can I trust a small sample?
Not much, if the window is short. Thirteen Saturdays is thirteen data points, and one wedding lunch or a festival shopping trip can lift the whole average. Before you act on the chart, look at the daily list for that weekday and see whether one or two big days explain the bar.
- Use at least three months of data, and a full year if you want festivals to even out.
- Compare the median as well: =MEDIAN(IF($C$2:$C$92=E2, $B$2:$B$92)). In older Excel confirm it with Ctrl+Shift+Enter.
- Treat rent, EMIs and annual premiums separately. They fall on a date, not a habit.
A useful extra is a weekday against weekend comparison. With weekday numbers in column C, =AVERAGEIFS($B$2:$B$92, $C$2:$C$92, "<=5") averages Monday to Friday, and the same formula with ">5" averages Saturday and Sunday. From the table above, weekdays average (410 + 380 + 395 + 420 + 760) / 5 = 473, and weekends (1,150 + 940) / 2 = 1,045.
Make the chart easy to read
Remove gridlines, show data labels with no decimals, and colour the highest bar differently from the rest. Give the chart a plain title such as Average spend per day, by weekday, last 13 weeks, so the window is visible on the picture.
Next step
Once the sheet works, paste fresh rows into your Data Table, extend the date list, and refresh. If you would like weekday patterns without maintaining the helper columns, SpendixAI has a Report builder where you can filter and group your records and download the result as CSV.
Checklist: dates are real dates, daily list has no gaps, each weekday has similar counts, and one unusual day has not decided the answer.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

