How to Design an Excel Inventory Tracker for Daily, Weekly, and Monthly Sales in WPS Office
Question details
The user wants to design a structured Excel inventory workbook that records product sales and calculates daily, weekly, and monthly totals automatically instead of entering totals manually.
- Product
- WPS Spreadsheets (Excel)
- Device & OS
- not provided
- Scenario
- Managing product sales and inventory reporting across daily, weekly, and monthly periods.
- Observed behavior
- No structured workbook exists yet; the current approach would require maintaining totals manually on separate sheets.
Before you begin, prepare product IDs, product names, opening stock quantities, and a consistent date format for all sales transactions. Confirm that your sales data has a valid Date column, because daily, weekly, and monthly grouping depends on real date values.
Set Up Products and Sales as Excel Tables
Use this solution first because Excel Tables automatically include new rows and keep your formulas and summaries consistent.
Structured tables are the foundation of an inventory tracker. Store product information once in a Products table and keep all sales transactions in a separate Sales table so you can summarize without re-entering totals.
Open WPS Spreadsheets, click a blank worksheet, and enter the column headers: Product ID, Product Name, Opening Stock, and Replenishments. Select the range, go to Insert > Table, check My table has headers, and click OK.
Add a second worksheet named Sales, enter the columns Date, Product ID, Quantity Sold, and Transaction Details. Select the data range and press Ctrl+T to format it as an Excel Table.
Click inside each table, go to the Table Design tab, and set the table names to Products and Sales so formulas are easier to read.
Summarize Daily, Weekly, and Monthly Totals with SUMIFS
Use this solution when you want formula-based totals that update automatically from the Sales table.
Build a PivotTable for Fast Daily, Weekly, and Monthly Views
Use this solution for interactive summaries and less formula maintenance.
Build Your Daily, Weekly, and Monthly Inventory Tracker in WPS Spreadsheets
Build the tracker in WPS Spreadsheets with Excel Tables for products and sales, then add SUMIFS formulas or a PivotTable with date grouping. The workbook can remain compatible with Microsoft Excel and updates automatically as new rows are added.
- 1. Download and install WPS Office: Go to the WPS Office download page, download WPS Office Free, and install it on your computer.
- 2. Open WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open a blank workbook or an existing .xlsx inventory file.
- 3. Create structured inventory tables: Use Insert > Table to create Products and Sales tables with the columns described in this guide.
- 4. Add summary formulas or a PivotTable: Use SUMIFS or Insert > PivotTable to calculate daily, weekly, and monthly sales totals automatically.
- 5. Save as an Excel workbook: Go to Menu > Save As and choose Excel Workbook (*.xlsx) to keep the file compatible with Microsoft Excel.
Frequently Asked Questions
Can I calculate daily, weekly, and monthly sales automatically from one sales table?
Yes. Format the Sales data as an Excel Table, then use SUMIFS with date criteria or create a PivotTable and group the Date field by days, months, and a set number of days for weekly totals.
How do I add new sales rows without updating every formula manually?
Format the sales data as an Excel Table with Ctrl+T. Formulas and PivotTables that reference the entire table expand automatically when you add new rows.
How do I calculate current inventory after sales and replenishments?
Subtract total sold for each product from opening stock and add replenishments. Use SUMIFS on the Sales table for total sold and reference the Replenishments column or a separate replenishments table for stock added.
Do I need to type totals on separate daily, weekly, and monthly sheets?
No. Keep one Sales table and use summary sheets with SUMIFS formulas or one PivotTable with date grouping. This avoids manual totals and keeps all periods in sync.
Is this inventory tracker compatible with Microsoft Excel?
Yes. WPS Spreadsheets saves files as .xlsx, so the inventory workbook can be opened and edited in Microsoft Excel without conversion.
