Best Workbook Design for Inventory, Prices, and Pivot Tables
Question details
The user needs advice on the optimal workbook structure for managing inventory, connecting quantities with pricing, and generating effective reports using Pivot Tables and charts.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Setting up a scalable inventory management and reporting system in a spreadsheet.
- Observed behavior
- Structuring source data into a normalized format to ensure easy maintenance and seamless integration with Pivot Tables and Data Models.
Ensure you have your raw inventory transactions and item pricing lists gathered into clear, separate datasets before beginning the consolidation process.
Create Normalized Tables and Merge via Power Query
Use a normalized data structure by separating transactions and pricing, then combine them using Power Query for robust Pivot Table reporting.
A normalized database approach is highly recommended for inventory. Instead of creating separate worksheets for each location or department, keep all transactions in one continuous table.
By keeping prices in a separate lookup table, you only need to update the price once, and the change will be reflected across all related transactions when merged.
Set up a single table containing columns for Date, Item, Quantity, Location, Department, and Area. Format this range as an official Table (Ctrl+T).
In a separate worksheet or area, create a table with a unique 'Item' key column and a 'Price' column.
Go to the Data tab, select 'From Table/Range' for both tables to load them into Power Query. Use the 'Merge Queries' feature to join the price table into the transaction table using the 'Item' column.
Load the merged data to the Data Model or as a new table. Go to Insert > PivotTable to group your data by item, location, department, and time period.

Connect Data Using Lookup Formulas
Use formula-based data modeling with XLOOKUP or VLOOKUP if you prefer a simpler approach without using Power Query.
Build Powerful Inventory Trackers with WPS Spreadsheet
WPS Spreadsheet offers advanced data handling capabilities, including full support for XLOOKUP, VLOOKUP, and dynamic Pivot Tables, making it the perfect tool to build scalable inventory management workbooks.
- 1. Format Your Data as Tables: Open WPS Spreadsheet, highlight your inventory data, and click 'Format as Table' from the Home tab.
- 2. Link Pricing with Formulas: Use the built-in XLOOKUP or VLOOKUP functions to seamlessly pull prices from your pricing list into your main inventory table.
- 3. Insert a Pivot Table: Navigate to the Insert tab, select 'PivotTable', and choose to place it on a new worksheet for reporting.
- 4. Create Dynamic Charts: With your Pivot Table selected, go to Insert > Chart to visually represent your inventory quantities and values over time.

Frequently Asked Questions
Why shouldn't I use separate worksheets for each department or location?
Using a single, normalized table for all transactions makes it much easier to summarize and analyze data across the entire organization using a single Pivot Table. Separate worksheets require complex consolidation and are harder to maintain.
Can I use VLOOKUP instead of XLOOKUP for the price table?
Yes, VLOOKUP works perfectly for this scenario. Just ensure your unique Item key is located in the very first column of your pricing lookup table.
How do I ensure my Pivot Table updates when I add new inventory data?
Format your source data as a Table (Ctrl+T) before creating the Pivot Table. When you add new rows to the Table, right-click anywhere inside the Pivot Table and select 'Refresh' to instantly include the new data in your report.




