Ready to Shop Smarter?
Use the cnshopper spreadsheet to track every order, compare prices, and never miss a delivery again. Then head to our main store for the best deals.
Once you have mastered the basics of the cnshopper spreadsheet — ten columns, simple formulas, daily sorting — it is time to unlock the next level. These advanced tips are used by buyers managing 500+ orders per month, resellers running full-time operations, and teams collaborating across time zones. None of them require a computer science degree, but each one saves serious time.
1. Master the QUERY Function
QUERY is the single most powerful function for spreadsheet power users. It lets you pull, filter, and sort data from your main tab into summary views without manual copy-pasting. For example, this formula creates a live summary of all pending orders from Supplier A:
=QUERY('Orders'!A:L, "SELECT * WHERE K='Pending' AND B='Supplier A'", 1)Place that in a new tab named "Pending by Supplier". Now every time you update a status in the main tab, this summary updates automatically. No more manual filtering every morning.
2. Use ARRAYFORMULA for Auto-Fill
Tired of dragging formulas down every time you add a row? ARRAYFORMULA applies a formula to an entire column automatically. Put this in the top cell of your Profit column:
=ARRAYFORMULA(IF(LEN(A2:A), M2:M - J2:J, ""))Now every new row you type into automatically calculates profit, with no formula dragging required. The IF(LEN(A2:A)) part ensures blank rows do not show zeroes.
3. Build a Supplier Scorecard
Create a new tab named "Suppliers". Use COUNTIF to count how many orders you placed with each supplier. Use AVERAGEIF to find their average shipping time. Use SUMIF to see total spend. Now you have an objective scorecard that settles "which supplier is best" arguments with data, not memory.
| Metric | Formula Example |
|---|---|
| Total Orders | =COUNTIF('Orders'!B:B, A2) |
| Avg Shipping Days | =AVERAGEIF('Orders'!B:B, A2, 'Orders'!N:N) |
| Total Spent | =SUMIF('Orders'!B:B, A2, 'Orders'!J:J) |
| On-Time Rate | =COUNTIFS(B:B, A2, K:K, "Delivered") / COUNTIF(B:B, A2) |
4. Conditional Formatting for Profit Margins
Go beyond basic status colors. Format your Profit column so cells turn green when profit exceeds $20, yellow when it is between $5-20, and red when it is below $5. This instantly shows you which items are worth reordering and which suppliers are eating your margins.
5. Archive Old Data Without Losing It
Sheets slow down past 5,000 rows. The fix is not deleting — it is archiving. Create a tab named "2025 Archive". Cut and paste completed orders older than six months. In your main tab, use QUERY to pull only rows where Status is not "Delivered". Your active view stays fast, and your historical data stays searchable.
Related Guides
Frequently Asked Questions
Will advanced formulas slow my sheet?
Can I connect my sheet to shipping APIs?
What is QUERY and why do power users love it?
Should I learn Apps Script?
Start Using the CNShopper Spreadsheet
Whether you are a beginner or a seasoned reseller, the cnshopper spreadsheet is the simplest way to stay organized, save time, and protect your profits.
Download a free template, explore our guides, and start tracking your orders the smart way today.