How to reconcile Shopify, Etsy and Amazon sales in one spreadsheet

You export three files. You paste them into one sheet. The total looks plausible, so you use it. That last step is where the month goes wrong, because a multi-channel total can be wrong by a lot while still looking completely reasonable.

Here is the order that catches it, and the four places it silently breaks.

First: the same column has three names

Before summing anything, write down which column in each file holds the money and the product. They are never called the same thing.

Two traps live in this table.

Shopify's order-level fields go blank, not repeated. A Shopify export has one row per line item. On the second and later rows of the same order, the order-level fields — Name, Total, Financial Status, Paid at — are left blank. Shopify's own documentation puts it this way: "Many of the fields are left blank to indicate that multiple items were purchased on the same order."

Two consequences, and people get bitten by both:

• Filtering on Financial Status silently drops line items. A filter for paid keeps only the first row of each order, so the rest of a three-item order vanishes from your product totals.
• Summing Total is right per order but useless per product, because the money sits on the first row only. For per-product revenue use Lineitem price × Lineitem quantity.

Before filtering by status, fill the blanks down (select the column → F5 → Special → Blanks → type = and the cell above → Ctrl+Enter), or filter on the line-item columns instead.

Third-party export apps behave differently — some repeat the order total on every row instead of blanking it. Check one order with three items before you trust either shape.

Amazon's file is tab separated, not comma separated, even though it ends in .csv or .txt. Open it with a comma parser and every row becomes one long cell.

Second: refunds are marked three different ways

This is the biggest single source of overstated revenue.

• Shopify writes refunded or partially_refunded in Financial Status
• Etsy writes Canceled — with one L, which matters if you filter by exact text
• Amazon writes Cancelled — with two

Filter on a contains match, lowercased, against a list: cancel, refund, return, void, chargeback. An exact-match filter will miss partially_refunded and quietly keep the money.

Check this by counting rows, not by looking at the total. If dropping refunds removes zero rows, your filter is broken — not your month.

Third: the amount is text, not a number

Etsy exports currency as $1,250.00. That is a string. SUM over a column of strings returns 0 — not an error, not a warning. Zero.

Test it before trusting anything:

=ISNUMBER(D2)

FALSE means text. Strip the symbol and separators first:

=VALUE(SUBSTITUTE(SUBSTITUTE(D2,"$",""),",",""))

The same trap catches dates. If your date column is left-aligned, it is text, and every month-based filter you write on it silently matches nothing.

Fourth: gross is not what you get paid

Even a correct gross total is not your revenue. Each channel takes a different cut, and they are not close to each other:

Confirm your own rates in each seller dashboard — they change, they vary by category, and there are per-order and payment-processing charges on top that no single percentage captures. Apply the rate per channel, never one blended rate across the whole sheet. A blended rate hides the fact that your highest-revenue channel may be your lowest-profit one.

The order to work in

1. Map columns per file — write down which is which before summing
2. Convert amounts and dates from text to numbers, verify with ISNUMBER
3. Drop cancelled and refunded rows with a contains filter, then count how many were dropped
4. Total by product and by channel
5. Apply per-channel fee rates to get net
6. Compare gross against the settlement report each store gives you

Step 6 is the one people skip. It is also the only step that tells you whether the other five worked.

Summary

• The same column has a different name in every export — map before you sum
• Shopify blanks order-level fields on extra line items — filtering by status drops them
• Amazon's export is tab separated
• Refund wording differs — filter with contains, and count what you dropped
• $1,250.00 is text, and SUM over text is 0, not an error
• Apply per-channel fees, never one blended rate