How to Calculate Average Order Value From an Order Export
Calculate average order value from an order export in Excel: pick the revenue, count each order once, build AOV by month and see why it differs from Shopify.
- Excel
- E-commerce
- Reporting Workflows

On this page
- What is average order value?
- Which revenue goes into average order value?
- How do you count orders when the export has one row per product?
- How do you calculate average order value by month in Excel?
- Why is the yearly AOV not the average of the monthly AOVs?
- Why doesn't your AOV match the platform's dashboard?
- Where does Glow Reports fit?
- Common questions
Average order value is revenue divided by the number of orders. The formula is one line, but an order export rarely hands it over cleanly. Most exports put each product of an order on its own row, mix shipping and tax into the total, and keep cancelled and refunded orders. So a plain AVERAGE over the amount column gives a figure that is wrong, and often far from the one the platform's dashboard shows. This guide sets which revenue to use, counts each order once, and builds average order value by month in Excel or Google Sheets.
What is average order value?
Average order value (AOV) is the revenue of a period divided by the number of orders placed in that period. It tells you how much a customer spends per checkout, and it moves with prices, discounts, bundles and the free shipping threshold.
=Revenue / Number of ordersTwo stores with the same revenue can have far apart AOVs. One sells 1,000 orders at 50, the other 250 orders at 200. The first pays for more packing and shipping per unit of revenue, so AOV sits next to revenue and order count in most monthly e-commerce reports.
AOV counts orders, not customers. One customer who buys three times adds three orders. Revenue per customer is a different figure and needs a customer ID.
Which revenue goes into average order value?
Pick one definition of revenue and write it next to the figure. The common choice is product sales after discounts, without shipping and tax. Then decide what to do with cancelled orders, refunds and test orders, and keep the same rules every month.
Shopify is an example of why this matters. In January 2023 it changed its AOV to gross sales minus discounts, excluding any adjustment made to the order after it was created. Before that, its AOV used the order's total sales value Shopify Changelog. Two people who compute "AOV" from the same store can get two answers if one uses the order total.
The choices to settle before you compute:
| Usual choice | Why | |
|---|---|---|
| Discounts | Subtract them | The customer never paid that amount |
| Shipping | Leave it out | It tracks carrier prices, not what the customer bought |
| Tax | Leave it out | It changes with the country, not with the basket |
| Cancelled and test orders | Leave them out, report cancellations on a separate line | Nothing was paid, or it was not a sale |
| Refunds | Keep AOV at order time, report refunds separately | A refund weeks later should not rewrite last month's AOV |
Shopify's order export shows why the columns matter. It has a column for the amount before shipping and taxes, separate and columns, a , a , a and a date Shopify Help Center. Dividing by the order count gives an AOV that includes shipping and tax.
How do you count orders when the export has one row per product?
Count each order ID once. Add a helper column that returns 1 on the first row of each order and 0 on the others, then sum it. In Excel 365, counts the distinct order IDs in one formula.
Shopify's export lists additional line items of an order on separate lines and leaves many fields blank on those lines Shopify Help Center. Other platforms repeat the order total on every line. Either way, the number of rows is not the number of orders.
Here is a small export with three orders and six rows:
| Order | Date | Line amount |
|---|---|---|
| #1001 | 2026-09-03 | 40 |
| #1001 | 2026-09-03 | 25 |
| #1002 | 2026-09-10 | 120 |
| #1003 | 2026-09-21 | 30 |
| #1003 | 2026-09-21 | 15 |
| #1003 | 2026-09-21 | 15 |
Revenue is 245 across 3 orders, so AOV is 81.67. A plain AVERAGE of the amount column returns 40.83, because it divides by 6 rows.
40.83 is what AVERAGE returns on that export, half the real AOV of 81.67.
With order IDs in column A, put this in a helper column and fill it down:
=IF(COUNTIF($A$2:A2,A2)=1,1,0)The range starts fixed at and grows by one row with each copy, so the formula counts how many times the ID has appeared so far Microsoft Support. On a large export this gets slow. UNIQUE is faster where you have it: it returns the list of distinct values of a range Microsoft Support, and COUNTA counts them.
If an export repeats the order total on every line instead of splitting it, sum the total only where the helper column is 1. Otherwise each order counts once per product. Remove true duplicate rows first, the ones created when two exports overlap. The steps are in how to clean data in Excel.
How do you calculate average order value by month in Excel?
Add a month column, then divide a SUMIFS by a COUNTIFS for each month. SUMIFS adds the revenue of the month, COUNTIFS counts the first rows of its orders, and the ratio is that month's AOV.
Say the export has order IDs in A, dates in B, line amounts after discounts in C and the helper column from above in D. Add a month in E with , which returns the first day of the date's month Microsoft Support. Format it as . Then list the months in G, starting at G2, and put this in H2:
=SUMIFS($C$2:$C$5000,$E$2:$E$5000,G2)/SUMIFS($D$2:$D$5000,$E$2:$E$5000,G2)SUMIFS adds the cells that meet every condition you give it Microsoft Support. The second SUMIFS adds the 1s of the helper column, which is the order count. To leave cancelled orders out, add a status column and one more pair of arguments to both parts, such as .
When the export has one row per order, the helper column is not needed. COUNTIFS counts the orders of the month Microsoft Support and returns the AOV directly Microsoft Support. Google Sheets has the same SUMIFS and COUNTIFS with the same argument order Google Docs Editors Help.
A pivot table gives the same monthly revenue and order counts without formulas. Grouping dates by month is covered in how to create a pivot table in Excel. Use the helper column as a value field with Sum to get the order count.
Why is the yearly AOV not the average of the monthly AOVs?
Because months have different numbers of orders. The yearly AOV is total revenue divided by total orders. Averaging the monthly AOVs gives a sale month with many small orders the same weight as a quiet month.
Take two months:
| Orders | Revenue | AOV | |
|---|---|---|---|
| September | 120 | 9,600 | 80.00 |
| October | 300 | 18,000 | 60.00 |
| Total | 420 | 27,600 | 65.71 |
The average of 80 and 60 is 70. The AOV of the two months together is 27,600 divided by 420, which is 65.71. Keep revenue and order count in the report, and compute any total row from them.
Why doesn't your AOV match the platform's dashboard?
Because the two figures use different revenue, different orders or different dates. Check the definition first, then the rows, then the cut-off.
- Revenue. Your sheet divides , which holds shipping and tax, while the platform uses sales after discounts. Shopify's own definition excludes adjustments made after the order was created Shopify Changelog.
- Orders. Your sheet counts rows instead of orders, or keeps test and cancelled orders the dashboard leaves out.
- Dates. The export and the dashboard cut the month at different times. Compare one day first. If a single day matches, the gap comes from the month boundaries.
Write the rule you chose under the figure, such as "product sales after discounts, without shipping and tax, cancelled orders excluded". Whoever reads the report next quarter then knows why it differs from the dashboard.
Where does Glow Reports fit?
The formulas above work in any copy of Excel or Google Sheets. Glow Reports helps when AOV is one figure in a monthly report and the export changes shape from one month to the next.
You upload the order export, CSV or Excel, and ask in plain language for average order value by month, without shipping and tax, cancelled orders excluded.
The AI writes the SQL, builds the charts and drafts the text. The SQL sits under each figure, so you can check that it counted distinct orders and not rows, and edit it when it used the wrong column. Reports export to PDF and stay in the workspace. The free plan gives 300 credits once, and a report costs 100 credits.
Common questions
How do you calculate average order value?
Divide the revenue of a period by the number of orders in the same period. With 27,600 in revenue and 420 orders, AOV is 65.71. Count orders, not export rows, and use the same revenue definition every month.
Should average order value include shipping and tax?
Usually not. Shipping follows carrier prices and tax follows the customer's country, so including them moves AOV for reasons unrelated to the basket. Shopify's AOV uses gross sales minus discounts, which excludes both.
Do refunds lower average order value?
It depends on your rule. Shopify's AOV ignores adjustments made after the order was created, so a refund does not change it. Many reports do the same and show refunds on their own line, so last month's AOV stays the same when a refund comes in later.
What is the difference between AOV and average basket size?
AOV is a value per order. Average basket size often means the number of items per order, which is line quantities divided by orders. Some teams use basket size for the value too, so say which one you mean.
I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.
More from El Mehdi BenaneUpload your order export to Glow Reports and ask for average order value by month, with the SQL behind each figure.
Keep reading
Ad Hoc Reporting Requests: How to Cut the Queue So Business Teams Answer Their Own QuestionsEl Mehdi Benane, 4 min read
How to Calculate Days Between Two Dates in Excel, and Fix DATEDIF #NUM!El Mehdi Benane, 4 min read
XLOOKUP vs VLOOKUP vs INDEX MATCH: Which One to Use on Your ExportsEl Mehdi Benane, 4 min read