Essential Excel Formulas for Profit and Expense Tracking: A Guide for Online Sellers
Why Excel is the Ultimate Tool for E-commerce Sellers
Many online sellers fall into the trap of seeing high sales volumes but finding no actual cash left in their bank accounts at the end of the month. Most of the time, this is due to overlooked hidden costs and a lack of structured accounting. While notebook tracking easily gets lost and enterprise accounting software is too expensive for beginners, spreadsheets are the perfect solution.
Programs like Microsoft Excel or Google Sheets are free, highly customizable, and automate all the math for you. This guide outlines the most essential Excel formulas online merchants should use to analyze their actual profitability in real-time.
Essential Excel Formulas You Need to Know
Managing your shop's numbers becomes simple once you master these 5 core formulas:
1. SUM (Totaling Data)
The most basic and frequently used formula to find your total sales, expenses, or inventory levels.
=SUM(range) (e.g., =SUM(B2:B20) to sum B2 through B20).
2. SUMIF / SUMIFS (Conditional Summing)
Allows you to sum data only if it meets specific criteria. For example, finding your total sales from Shopee specifically, or summing costs for a particular product category.
=SUMIF(range, criteria, [sum_range]) (e.g., =SUMIF(C2:C100, "Shopee", D2:D100)).
3. IF (Conditional Alerts)
Great for triggering automated warnings, such as notifying you when inventory is running low.
=IF(logical_test, value_if_true, value_if_false) (e.g., =IF(E2<5, "Restock Needed", "Stock Okay")).
4. VLOOKUP (Data Lookup)
Extremely useful for pulling product prices or customer details from a master product database sheet into your daily sales log based on the Product SKU.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Calculating Net Profit and Margins in Excel
To avoid hidden losses, make sure your sheet utilizes these exact calculations:
- Gross Profit:
=Revenue - COGS - Net Profit:
=Revenue - (COGS + Packaging + Shipping Cost + Platform Fees + Marketing Cost) - Net Profit Margin %:
=(Net Profit / Revenue) * 100
Using Ready-to-Use Premium Spreadsheet Templates
If you don't have time to build these spreadsheets from scratch, you can download premium templates directly from Gumrai. We offer pre-built sheets with built-in formulas tailored for Thai sellers, including our interactive Sales Dashboard, the Income & Expense Tracker, and our multi-channel Stock Management system. Simply input your raw data and let the spreadsheets do the rest.
FAQ
1. Should I use Microsoft Excel or Google Sheets?
If you need to collaborate with admins or teammates in real-time, Google Sheets is recommended. If you work offline, manage massive datasets, and want premium, polished local dashboards, Microsoft Excel is the ideal choice.
2. Why does my profit percentage show as a decimal like 0.25 instead of 25%?
This is a cell formatting issue. Right-click the cell, select Format Cells, and change the format to Percentage (%), or adjust your formula to multiply by 100.
Summary
Understanding and using Excel formulas shifts your business from emotion-driven decisions to data-driven growth. Reconciling your numbers daily plug leaks and builds long-term merchant profitability.
Want to set more accurate costs and selling prices?
Try Gumrai tools to calculate profit, set prices, and manage back-office tasks to make decisions faster.