How to Calculate Return Rate in Excel如何在 Excel 中计算退货率

This guide is for ecommerce operators and analysts who have order-line or return-event data in Excel. It covers product returns—not investment returns, customer-repeat rate, or accounting rate of return.

本指南面向在 Excel 中处理订单行或退货事件数据的电商运营与分析人员。本文讨论商品退货率，不讨论投资收益率、客户复购率或会计收益率。

The words return, refund, and reversal are not interchangeable. A customer can receive a partial refund without sending an item back; an exchange creates a physical return while retaining some or all revenue; a cancellation can reverse quantity without a physical return. The returns vs refunds guide maps these events. Shopify's current sales-report documentation distinguishes physical-return fields from broader sales reversals, while Google Analytics 4 ecommerce documentation records refund events at transaction and item level. Those distinctions are why one denominator cannot answer every business question.

退货、退款和冲销不能互换使用。客户可能获得部分退款却不退回商品；换货会产生实体退回，但可能保留全部或部分收入；取消订单也可能减少数量，却没有实体退货。退货与退款指南对这些事件做了映射。Shopify 当前销售报表文档把实体退货字段与更广义的销售冲销区分开，而 Google Analytics 4 电商文档则在交易和商品层级记录退款事件。因此，一个分母无法回答所有业务问题。

Choose the denominator that matches the decision让分母与要做的决策保持一致

Use one of these three return-rate formulas使用以下三种退货率公式之一

Shopify’s current reporting documentation defines returned quantity rate as physically returned units divided by ordered units for a line item. It also warns that the broader reversed quantity can include refunds, returns, cancellations, and edits. Use the physical-return field when you mean product units that actually came back.

Shopify 当前报表文档把“退回数量率”定义为某订单行实体退回件数除以订购件数，同时提醒更广义的“冲销数量”可能包含退款、退货、取消和订单修改。如果你要衡量真正退回的商品件数，应使用实体退货字段。

One ecommerce cohort can produce three correct answers同一个电商订单群组可以得到三个正确答案

Assume a January delivery cohort contains 1,250 eligible orders, 1,800 delivered units, and $90,000 in gross merchandise revenue. After the declared return window closes, the linked return records show 150 orders with a physical return, 198 returned units, and $11,250 in refunded merchandise value. The dataset is synthetic and exists only to demonstrate the method.

假设 1 月送达订单群组包含 1,250 个符合口径的订单、1,800 件已送达商品和 90,000 美元商品销售额。在声明的退货窗口关闭后，与原订单连接的退货记录显示：150 个订单发生实体退货、198 件商品被退回、退款商品金额为 11,250 美元。这些均为演示方法所用的模拟数据。

None of these percentages is automatically “the right return rate.” The order rate is higher than the unit rate when many affected orders contain several kept items. The value rate rises above the unit rate when returned items are more expensive than the average sold unit. Report the three labels, not one unlabeled percentage.

这三个比例都不能自动被称为“唯一正确的退货率”。如果很多受影响订单仍保留了其他商品，订单退货率可能高于件数退货率；如果退回商品的价格高于平均售价，退款金额率可能高于件数退货率。应同时报告三个清晰名称，而不是只展示一个无标签百分比。

Calculate return rate in six reviewable steps用六个可审核步骤计算退货率

1. Write the decision first. Choose order rate for affected orders, unit rate for product operations, or value rate for revenue exposure.
2. Define eligible sales. State order status, channel, market, currency, date field, and whether the denominator uses sold, shipped, or delivered activity.
3. Declare the return window. Use the policy window or a documented analytical window and add enough processing time for late records.
4. Join returns to original sales. Match original order ID, line-item ID, SKU or variant, quantity, price, and currency. Keep unmatched rows in an exception report.
5. Separate event types. Classify physical return, refund without return, cancellation, exchange, edit, and chargeback before counting.
6. Calculate, reconcile, and segment. Reconcile totals to source systems, calculate the chosen rate, then compare SKU, category, channel, or cohort only when denominators are large enough to interpret.

1. 先写清决策问题。受影响订单用订单退货率；商品运营用件数退货率；收入风险用退款金额率。
2. 定义符合口径的销售。说明订单状态、渠道、市场、币种、日期字段，以及分母基于已售、已发货还是已送达。
3. 声明退货窗口。采用退货政策窗口或书面分析窗口，并为延迟入账留出处理时间。
4. 把退货连接回原始销售。匹配原订单 ID、订单行 ID、SKU 或变体、数量、价格和币种；未匹配记录进入异常表。
5. 区分事件类型。计算前先区分实体退货、无退货退款、取消、换货、订单修改和拒付。
6. 计算、对账并细分。先与源系统总数对账，再计算所选指标；只有分母足够时，才比较 SKU、品类、渠道或订单群组。

Match returns to the sale cohort that created them把退货归回产生它的原始销售群组

A calendar-period rate such as “returns processed in February ÷ sales made in February” mixes two populations. February returns may belong to January orders, while many February orders are still inside their return window. The distortion becomes larger during rapid growth, promotions, holidays, policy changes, or channel-mix shifts.

“2 月处理的退货 ÷ 2 月销售”这类日历周期口径混合了两组不同人群。2 月退货可能来自 1 月订单，而很多 2 月订单仍处在退货窗口内。在高速增长、促销、节假日、退货政策调整或渠道结构变化期间，这种偏差会更加明显。

Calculate ecommerce return rate in Excel step by step在 Excel 中逐步计算电商退货率

1. Structure the source data. Use one row per original order line and aggregate qualifying return quantities back to that line. If sales and return events arrive in separate exports, join or summarize the return events before calculating; do not append them beneath sales rows and repeat the denominator. Keep one field per column with a single header row. At minimum, include Original Order ID, Line Item ID, SKU, Order or Delivery Date, Eligible Units, Returned Units, Gross Merchandise Value, Refunded Merchandise Value, Return Date, and Event Type. Select the range and choose Insert → Table. Microsoft notes that Excel Tables use structured references that expand as rows are added; this is safer than repeatedly editing fixed ranges.

1. 整理源数据。每个原订单行只保留一行，并先把符合条件的退回数量聚合回该订单行。如果销售与退货事件来自不同导出文件，应先连接或汇总退货事件再计算；不要把退货事件直接追加到销售行下面并重复分母。每列只放一个字段，并使用单行表头。至少包含原订单 ID、订单行 ID、SKU、下单或送达日期、符合口径件数、退回件数、商品销售额、退款商品金额、退货日期和事件类型。选中区域后使用插入 → 表格。Microsoft 说明，Excel 表格的结构化引用会随新增行自动扩展，比反复修改固定区域更稳妥。

2. Calculate a row or summary rate. In a summary table, place eligible units in B2 and returned units in C2. Use:

2. 计算单行或汇总退货率。在汇总表中，把符合口径件数放在 B2，退回件数放在 C2。使用：

=IF(B2=0,NA(),C2/B2)

Format the result as Percentage. Returning #N/A when the denominator is zero keeps “not calculable” distinct from a genuine 0% return rate. Microsoft warns that IFERROR can hide errors; if you choose =IFERROR(C2/B2,0) for presentation, add a visible denominator-quality flag and retain the raw calculation for review.

把结果设置为百分比。分母为零时返回 #N/A，可以把“无法计算”与真实的 0% 退货率区分开。Microsoft 提醒，IFERROR 可能掩盖错误；若为展示使用 =IFERROR(C2/B2,0)，应同时增加清晰的分母质量标识，并保留原始计算供复核。

3. Use structured references. If the Excel Table is named ReturnsData, calculate the overall unit return rate with:

3. 使用结构化引用。若 Excel 表格名为 ReturnsData，可用以下公式计算整体件数退货率：

=IF(SUM(ReturnsData[Eligible Units])=0,NA(),SUM(ReturnsData[Returned Units])/SUM(ReturnsData[Eligible Units]))

4. Filter by date, channel, or SKU with SUMIFS. Put the start date in H2, end date in I2, and SKU in J2. For a delivery-cohort unit rate:

4. 使用 SUMIFS 按日期、渠道或 SKU 筛选。把开始日期放在 H2、结束日期放在 I2、SKU 放在 J2。按送达订单群组计算件数退货率可使用：

=LET(sold,SUMIFS(ReturnsData[Eligible Units],ReturnsData[Delivery Date],">="&H2,ReturnsData[Delivery Date],"<="&I2,ReturnsData[SKU],J2),returned,SUMIFS(ReturnsData[Returned Units],ReturnsData[Delivery Date],">="&H2,ReturnsData[Delivery Date],"<="&I2,ReturnsData[SKU],J2),IF(sold=0,NA(),returned/sold))

For versions without LET, place the two SUMIFS totals in separate cells and divide them. Microsoft documents SUMIFS for summing values that meet multiple conditions. Keep the same date field and SKU key in both numerator and denominator.

不支持 LET 的 Excel 版本，可把两个 SUMIFS 汇总结果放在不同单元格中再相除。Microsoft 文档说明，SUMIFS 用于对满足多个条件的值求和。分子和分母必须使用同一个日期字段与 SKU 键。

5. Treat order return rate separately. COUNTIFS counts rows that meet conditions, not distinct Order IDs. If one order can produce several lines, first create one order-level table or use the Data Model’s distinct count. Dividing return rows by total orders will overstate the rate. A PivotTable can summarize quantities by SKU or month, but calculate the final rate from summed returned units divided by summed eligible units—never average the row percentages.

5. 单独处理订单退货率。COUNTIFS 统计满足条件的数据行，不会自动对订单 ID 去重。如果一个订单可能包含多行，应先建立订单层级表，或使用数据模型的非重复计数。用退货行数除以订单数会夸大结果。数据透视表可以按 SKU 或月份汇总数量，但最终退货率应使用“退回件数之和 ÷ 符合口径件数之和”，不能直接平均各行百分比。

Download the return rate formula worksheet下载退货率公式计算表

Use the CSV worksheet to document one cohort at a time. It includes eligible and returned orders, units, gross merchandise value, refunded merchandise value, the three calculated rates, cohort dates, return-window maturity, exclusions, and a quality flag. Open it in Excel, convert the range to a Table, and replace the synthetic example with reconciled source data.

使用 CSV 计算表逐个记录订单群组。模板包含符合口径与已退货的订单数、件数、商品销售额、退款商品金额、三种计算结果、群组日期、退货窗口成熟度、排除项和质量标识。用 Excel 打开后可将区域转换为表格，并用已与源系统对账的数据替换模拟示例。

Handle partial refunds, exchanges, and cancellations explicitly明确处理部分退款、换货和取消订单

GA4 can record full and partial refunds with the refund event and recommends sending item IDs and quantities for item-level refund metrics. That makes GA4 useful for refund-event measurement, but a complete physical-return rate still needs fulfillment or returns data showing whether and when the item came back.

GA4 可以通过 refund 事件记录全额和部分退款，并建议传递商品 ID 与数量以生成商品层级退款指标。因此，GA4 适合衡量退款事件，但完整的实体退货率仍需要履约或退货系统提供商品是否以及何时退回的数据。

Calculate return rate by SKU only after checking sample size先检查样本量，再计算 SKU 退货率

Group eligible and returned units by the same stable key: ideally variant ID, then SKU, product ID, category, channel, market, and delivery cohort. A product with 2 returns from 5 eligible units has a 40% observed rate, but it should not outrank a product with 240 returns from 2,000 units solely because 40% is larger than 12%. Display the numerator and denominator beside the rate and set a review threshold appropriate to your sales volume.

应使用同一个稳定键对符合口径件数和退货件数分组：优先使用变体 ID，再依次考虑 SKU、商品 ID、品类、渠道、市场和送达订单群组。某商品 5 件中退 2 件，观察退货率为 40%；另一商品 2,000 件中退 240 件，退货率为 12%。不能只因为 40% 更高，就把前者排在损失优先级首位。应在退货率旁同时显示分子和分母，并根据销量设置复核阈值。

Use rate for comparability, returned units for operational workload, refunded value for revenue exposure, and contribution loss for prioritization. This page explains calculation; use the product return analysis workflow for deeper SKU diagnosis and the ecommerce returns metrics reference for the wider KPI set.

退货率用于可比性，退货件数用于评估运营工作量，退款金额用于衡量收入风险，贡献利润损失用于确定优先级。本页聚焦计算方法；更深入的 SKU 诊断可使用商品退货分析流程，完整 KPI 体系可查看电商退货指标参考。

Run eight data checks before publishing the percentage发布百分比前完成八项数据检查

• Document the numerator, denominator, date field, cohort, return window, currency, and exclusions.
• Count quantities rather than line-item rows.
• Deduplicate original order IDs for the order return rate.
• Separate physical returns from refunds, cancellations, exchanges, edits, and chargebacks.
• Reconcile eligible order and revenue totals to the source report.
• Report unmatched returns, missing SKUs, zero denominators, and duplicate events.
• Convert currencies before aggregating value rates across markets.
• Show both counts and rates; suppress or flag unstable small denominators.

• 记录分子、分母、日期字段、订单群组、退货窗口、币种和排除项。
• 统计商品数量，而不是订单行数。
• 计算订单退货率时，对原始订单 ID 去重。
• 区分实体退货、退款、取消、换货、订单修改和拒付。
• 把符合口径的订单总数和销售额与源报表对账。
• 报告未匹配退货、缺失 SKU、零分母和重复事件。
• 跨市场汇总金额率前先统一币种。
• 同时显示数量与比例；对不稳定的小分母隐藏或标记。

Sources, method, and commercial disclosure来源、方法与商业披露

The formulas on this page are editorial definitions designed to keep operational, customer-order, and financial questions separate. Excel behaviors are checked against current Microsoft documentation and return-event distinctions against current platform documentation. Each business must still align the workbook with its own return policy, accounting treatment, and source-system fields.

本页公式属于编辑定义，目的是区分运营、客户订单和财务问题。Excel 行为依据当前 Microsoft 文档核对，退货事件的区分依据当前平台文档核对。每家企业仍需结合自身退货政策、会计口径和源系统字段确定最终工作簿定义。

• Microsoft Support: Structured references with Excel Tables — explains table and column references that adjust as data changes.
• Microsoft Support: SUMIFS for multiple conditions — documents conditional aggregation used for cohort and SKU calculations.
• Microsoft Support: IFERROR — documents the function’s behavior and the errors it can replace.
• Microsoft Support: Create a PivotTable — recommends clean tabular data and explains refresh behavior.
• Shopify Help Center: Sales reports and physical-return metrics — defines returned quantity and distinguishes physical returns from broader reversals.
• Google Analytics for Developers: Measure ecommerce — documents transaction- and item-level refund events.

• Microsoft 支持：Excel 表格的结构化引用——说明会随数据变化自动调整的表格与列引用。
• Microsoft 支持：使用 SUMIFS 处理多个条件——说明订单群组与 SKU 计算所需的条件汇总。
• Microsoft 支持：IFERROR——说明该函数的行为及其会替换的错误类型。
• Microsoft 支持：创建数据透视表——建议使用整洁的表格数据，并说明刷新行为。
• Shopify 帮助中心：销售报表与实体退货指标——定义退回数量，并区分实体退货与更广义的销售冲销。
• Google Analytics 开发者文档：衡量电子商务——说明交易和商品层级的退款事件。

Frequently asked questions常见问题

Publish the definition beside the percentage把指标定义与百分比一起发布

A useful return-rate report lets another analyst reproduce the number. Keep the formula, eligible population, cohort dates, return window, exclusions, currency treatment, exception count, and last refresh beside the result. Once the rate is stable, connect it to the broader ecommerce returns analytics workflow to investigate SKU, reason, cost, and margin patterns, then place the metric in a repeatable ecommerce returns reporting cadence.

有用的退货率报告，应允许另一位分析人员复算结果。把公式、符合口径的人群、订单群组日期、退货窗口、排除项、币种处理、异常数量和最后刷新时间与结果放在一起。指标稳定后，再把它连接到完整的电商退货分析流程，继续调查 SKU、原因、成本和利润模式，并纳入可重复的电商退货报告节奏。