---
title: "How to calculate refund rate from a Shopify orders export (and by sales channel)"
url: "https://everynumber.app/resources/shopify-refund-rate"
description: "Refund rate from a Shopify orders export can mean paid orders with any refund divided by paid orders, or refunded money divided by sales. Count each order once, since extra items get their own rows. Leave out test and canceled orders, and say which date you used. Shopify's sales reports record returns on the day they were processed."
updated: "2026-11-05"
---

# How to calculate refund rate from a Shopify orders export (and by sales channel)

You exported your orders from Shopify, opened the CSV in Excel or Google Sheets, and want one number: how often orders get refunded. With a Financial Status column and a Refunded Amount column, it looks like a 2-minute job. Then the count doesn't match Shopify's analytics, and nobody agrees on whether a partial refund counts.

This guide covers 2 definitions, the rows to leave out, which date to count on, and formulas overall and by sales channel. Facts about the export come from Shopify's help pages. Where they're silent, you'll see what to check in your own file.

## What is refund rate, and which version do you need?

Refund rate means 2 different things:

- **By orders.** Paid orders with any refund ÷ paid orders. It shows how often something goes wrong.
- **By money.** Refunded amount ÷ sales (the Total of paid orders). It shows how much revenue goes back.

Say your October export has 1,846 rows for 1,212 orders. After you leave out test, canceled, and unpaid orders, 1,182 paid orders remain. 41 of them have a refund, 22 full and 19 partial, and together they returned $1,486 of $58,960 in sales.

| Version | Counted | Divided by | Rate |
|---|---|---|---|
| Orders with any refund | 41 orders | 1,182 paid orders | 3.5% |
| Fully refunded orders | 22 orders | 1,182 paid orders | 1.9% |
| Refunded money | $1,486 | $58,960 in sales | 2.5% |
| Mistake: refunds ÷ rows | 41 orders | 1,846 rows | 2.2% |

The last row is a common mistake: each extra item of an order gets its own row, so dividing by rows makes the rate look better than it is. Pick a version, put it in the metric's name ("Refund rate, orders"), and write it in your [metrics dictionary](/resources/metrics-dictionary-template).

## Which Shopify export columns do you need for reporting?

Of the export's many columns, you need these. Shopify's help page describes each one:

| Column | What Shopify says it holds | Use it for |
|---|---|---|
| Name | The order number in your admin | Counting each order once |
| Created at | When the customer completed the order | The order date |
| Financial Status | Paid, authorized, refunded, and so on | Which orders had a refund |
| Total | The total cost of the order | The money base |
| Refunded Amount | Any refund applied to the order | Refunded money |
| Canceled at | When the order was canceled | Leaving out cancellations |
| Source | For example web, draft order, POS | Sales channel |
| Email, Tags | The customer's email; the order's tags | Finding test orders |

Shopify defines the Refunded payment status as the full amount the customer paid going back to them, and Partially refunded as less than that. Check how Financial Status spells both in your file before you copy the formulas below.

Shopify puts each additional line item of an order on its own row and leaves many of the fields blank there. Check which ones in your file, and check that Name is on every row, since it ties an order's rows together. The column list also has no refund date and no per-item refund column.

## Which orders should you leave out?

Each of these skews the rate. When you take an order out, move every row with its Name to a tab called Left out, with the reason, so you can show what you left out and why.

| What | Why it skews the rate | What to do |
|---|---|---|
| Extra item rows | An order with 3 items is 3 rows | Count first rows only |
| Test orders | In the export, not in Shopify's sales reports | Delete or tag, then leave out |
| Canceled orders | A refund at cancellation reads as Refunded | Leave out, count separately |
| Unpaid or voided orders | Payment wasn't captured in full | The formulas below skip them |
| A totals row someone added | Reads as one huge order | Delete it |
| Blank amounts on item rows | Filling them repeats an order's Total | Leave blank, sum first rows |

Test orders need the most care. Shopify says they're left out of its sales reports but included in your orders export, and the export has no test column. Shopify suggests placing test orders with an email address ending in @example.com, so search Email for it. Test orders paid with the Test payment gateway or Shopify Payments in test mode can be deleted. Orders through third-party providers in test mode aren't treated as test orders, and a test with a real payment provider is a real order you cancel and refund. Tag both as test in Shopify.

When you cancel an order and refund it in full, Shopify changes the payment status to Refunded, so cancellations would count as refunds. Keep them out of both sides and report them as their own number. To find them, filter Canceled at for cells with a date.

## Which date should a refund count on?

A refund has 2 dates: when the order was placed and when the money went back. The orders export only has the first. For the second, choose Export transaction histories instead of Export orders. In that file, Kind says whether each transaction was a sale, refund, or authorization, and Created At holds its date, time, and offset from UTC.

| Count by | It answers | Date column | Best for |
|---|---|---|---|
| Order date | How many October orders were refunded? | Created at, orders export | Products, channels, campaigns |
| Refund date | How much did we refund in October? | Created At, transaction history | Same dates as Shopify's reports |

Shopify's sales reports date things the same way: a sales reversal (the newer name for returns, which also covers full refunds without a return) shows as a negative value on the day it was processed.

With Kind in column C, Status in D, Amount in E, and Created At in F, October's refunds by refund date are `=SUMIFS(E:E, C:C, "refund", D:D, "success", F:F, "2026-10*")`. Check the words your file uses in Kind and Status first. Shopify says this file holds captured payments only.

The order-date version keeps moving, since an order placed on October 30 can be refunded in November. Say October reads 3.5% on November 2 and 4.1% on December 1. Recalculate each month once, after a fixed wait such as 30 days, and note the export date.

## How do you calculate it in Excel or Google Sheets?

Set up the sheet once:

1. On a copy of the export, move test and canceled orders to the Left out tab.
2. On a new tab called Work, paste 6 columns in this order: A Name, B Created at, C Financial Status, D Total, E Refunded Amount, F Source.
3. Type First in G1, enter `=COUNTIFS($A$2:A2, A2)` in G2, and fill it down. It shows 1 on each order's first row, 2 on its second, and so on.
4. Compare one fully refunded order in Shopify admin with its first row, and confirm what Total and Refunded Amount show before you trust the money version.

Then, for orders placed in October 2026:

- **Paid orders.** `=COUNTIFS(G:G, 1, B:B, "2026-10*", C:C, "paid") + COUNTIFS(G:G, 1, B:B, "2026-10*", C:C, "*refunded")`
- **Orders with any refund.** `=COUNTIFS(G:G, 1, B:B, "2026-10*", C:C, "*refunded")`
- **Refunded money.** `=SUMIFS(E:E, G:G, 1, B:B, "2026-10*")`
- **Sales.** `=SUMIFS(D:D, G:G, 1, B:B, "2026-10*", C:C, "paid") + SUMIFS(D:D, G:G, 1, B:B, "2026-10*", C:C, "*refunded")`

Divide refunded orders by paid orders, and refunded money by sales. For full refunds only, use `"refunded"` without the asterisk.

COUNTIFS and SUMIFS use only the rows that meet every condition, and their ranges must be the same size, so whole columns are simplest. In both Excel and Google Sheets, the asterisk is a wildcard for any characters: `"*refunded"` catches refunded and partially refunded, and `"2026-10*"` catches every Created at that starts with 2026-10. That works when Created at is text starting with the date. If your spreadsheet turned it into real dates, export one month at a time with Orders by date and drop the date condition.

### How do you compare this month with last month?

Copy the formulas and change 2026-10 to 2026-09. Compare complete months, or export the same days of each month with Orders by date. On the order-date version, September has had an extra month to collect refunds, so October looks better until it catches up.

## How do you find refund rate by sales channel?

Source is the export's closest thing to a sales channel: Shopify describes it as the order's source, for example web, draft order, or POS. Its sales reports name the channel or app the customer used instead (Draft Orders for sales that began as draft orders), so the words may differ. List the values in Source once and write down what each means for your store.

Then add the channel as one more condition. For web, refunded orders are `=COUNTIFS(G:G, 1, B:B, "2026-10*", C:C, "*refunded", F:F, "web")`, divided by the paid-orders formula with `F:F, "web"` added to both parts. SUMIFS works the same way.

With the same hypothetical October (your Source values may be spelled differently):

| Source | Paid orders | With a refund | Refund rate |
|---|---|---|---|
| web | 1,004 | 33 | 3.3% |
| pos | 142 | 2 | 1.4% |
| draft order | 36 | 6 | 16.7% |
| All channels | 1,182 | 41 | 3.5% |

Draft orders look alarming at 16.7%, but that's 6 orders. Show the count next to each channel's rate ("6 of 36") and watch 2 or 3 months before you act on a small channel.

## Where Every Number fits

If you'd rather not rebuild this every month, upload the same CSV at [everynumber.app/try](/try). No account is needed: a file up to 10 MB becomes a dashboard in seconds and is deleted after 24 hours unless you save it. When one order spans several rows, as in this export, Every Number counts orders and rates by order number, once each. Every number has a receipt: its definition, the rows it used, and the rows it left out with the reason. See the [Shopify orders export page](/exports/shopify-orders) and the [orders and refunds example](/examples/ops-fulfillment).

It isn't a live connection to Shopify. You upload an export, so the numbers are as fresh as your last one. If Shopify's own reports already answer your question, you don't need another tool.

## Frequently asked questions

### Does a partial refund count as a refunded order?

In the order version, yes. After a partial refund, Shopify changes the order's status to Partially refunded, and `"*refunded"` counts it. Show full refunds as a second number if your team cares about them. The money version counts each partial refund at its actual size.

### Why doesn't my refund rate match Shopify's reports?

Shopify's help names several reasons. Test orders are in your export but not in its sales reports. Sales reports include returns, refunds appear in the Payments finance report, and reversals land on the day they were processed. A custom refund with no products refunded or restocked shows in the export but not in the Sales report. More causes are in [why dashboard numbers don't match](/resources/why-dashboard-numbers-dont-match).

### Should I divide by Total or Subtotal?

Total, in most cases. Shopify's Subtotal is before shipping and taxes, and a refund can include shipping fees, so dividing Refunded Amount by Subtotal can overstate the rate. Whichever you pick, write it into the definition.

### What is a good refund rate for a Shopify store?

It depends on what you sell, your prices, and your channels, and none of the Shopify pages cited here publish a benchmark. Compare against your own last 3 to 6 months, by channel, with the same definition and waiting period. To track it weekly, see [how to build a weekly KPI dashboard](/resources/weekly-kpi-dashboard-google-sheets).

## Sources

1. [Exporting orders](https://help.shopify.com/en/manual/fulfillment/managing-orders/exporting-orders) — Shopify Help Center. Checked November 4, 2026.
2. [Understanding your order statuses](https://help.shopify.com/en/manual/fulfillment/managing-orders/order-status) — Shopify Help Center. Checked November 4, 2026.
3. [Refunding orders](https://help.shopify.com/en/manual/fulfillment/managing-orders/refunding-orders) — Shopify Help Center. Checked November 4, 2026.
4. [Canceling orders](https://help.shopify.com/en/manual/fulfillment/managing-orders/canceling-orders) — Shopify Help Center. Checked November 4, 2026.
5. [Placing a test order](https://help.shopify.com/en/manual/checkout-settings/test-orders) — Shopify Help Center. Checked November 4, 2026.
6. [Processing a test order](https://help.shopify.com/en/manual/checkout-settings/test-orders/processing-test-order) — Shopify Help Center. Checked November 4, 2026.
7. [Sales reports](https://help.shopify.com/en/manual/reports-and-analytics/shopify-reports/report-types/default-reports/sales-report) — Shopify Help Center. Checked November 4, 2026.
8. [Sales discrepancies in Shopify Analytics](https://help.shopify.com/en/manual/reports-and-analytics/discrepancies/sales-discrepancies) — Shopify Help Center. Checked November 4, 2026.
9. [COUNTIFS function](https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842) — Microsoft Support. Checked November 4, 2026.
10. [SUMIFS function](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b) — Microsoft Support. Checked November 4, 2026.
11. [COUNTIFS](https://support.google.com/docs/answer/3256550) — Google Docs Editors Help. Checked November 4, 2026.
12. [SUMIFS function](https://support.google.com/docs/answer/3238496) — Google Docs Editors Help. Checked November 4, 2026.
13. [COUNTIF](https://support.google.com/docs/answer/3093480) — Google Docs Editors Help. Checked November 4, 2026.