Step-by-Step AllChinaBuy Spreadsheet Guide for New Buyers
A complete walkthrough for building your first allchinabuy spreadsheet from scratch. Track every order, cost, and delivery timeline with zero confusion.
Get the Free Template
If you have never built an allchinabuy spreadsheet before, the idea can feel overwhelming. Columns, formulas, conditional formatting, dashboards — it sounds like a data science project. In reality, a basic functional tracker takes under twenty minutes to set up and saves hours of confusion every week. This guide walks through every step, from blank workbook to fully operational order tracker, with screenshots and examples along the way.
Step 1: Choose Your Platform
Start with Google Sheets if you want cloud access and mobile editing. It auto-saves, works offline with setup, and syncs across all your devices. Excel is better for complex formulas and offline-only workflows. Both work equally well for beginners.
Create a new blank spreadsheet. Name the first tab "Orders" and the second tab "Dashboard". This two-tab structure is the foundation every experienced buyer uses.
Step 2: Build Your Column Headers
In row one of the Orders tab, enter these headers across columns A to L: Order ID, Date Ordered, Seller Name, Item Description, Item Price (USD), Shipping Cost, Customs/Fees, Total Cost, Shipping Method, Tracking Number, Status, Notes.
These twelve columns cover every piece of information a global buyer needs. Do not skip any of them, even if you think you will not use Notes yet. Six months later, you will thank yourself.
Step 3: Add Calculation Formulas
In the Total Cost column (column H), enter =E2+F2+G2 for the first data row. This adds Item Price + Shipping + Fees automatically. Copy the formula down as you add rows.
Apply conditional formatting to the Status column. Go to Format > Conditional Formatting and set rules: "Delivered" turns green, "Shipped" turns yellow, "Pending" stays white, "Problem" turns red. This visual system lets you scan fifty orders in under ten seconds.
Step 4: Create Summary Dashboard
Switch to the Dashboard tab. In cell A1, type "Total Orders". In B1, enter =COUNTA(Orders!A2:A1000). This counts every order in your tracker.
Add rows for Pending Orders, Delivered Orders, Total Spent, and Average Order Value using COUNTIF, SUM, and AVERAGE formulas. These four numbers give you a complete health check of your buying activity in seconds.
| Formula | Purpose | Where to Place |
|---|---|---|
| =COUNTA(Orders!A2:A1000) | Count total orders | Dashboard B1 |
| =COUNTIF(Orders!K2:K1000,"Pending") | Count pending orders | Dashboard B2 |
| =SUM(Orders!H2:H1000) | Sum total spending | Dashboard B3 |
| =AVERAGE(Orders!H2:H1000) | Average order value | Dashboard B4 |
Step 5: Test with Real Data
Enter your last three real orders as test data. Fill every column, confirm the Total Cost formula works, and check that conditional formatting triggers correctly. If something looks wrong, fix it now before you have one hundred rows of data.
Once verified, add a new order the next time you buy something. The habit of logging immediately, before you close the seller page, is the difference between an accurate tracker and a useless one.
Pro Tips
- 1Log orders immediately after payment. Waiting even one hour increases the chance you forget item details or seller names.
- 2Use the same date format everywhere. Mixed formats break sorting and filtering later.
- 3Back up your spreadsheet monthly. Google Sheets auto-saves, but corruption can still happen.
