Pivot Table Issues

Best Workbook Design for Inventory, Prices, and Pivot Tables

Huma Ashraf ChHuma Ashraf Ch Nov 10, 2026 868 views

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.

Best Workbook Design for Inventory, Prices, and Pivot Tables
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.
Before you start

Ensure you have your raw inventory transactions and item pricing lists gathered into clear, separate datasets before beginning the consolidation process.

Solution 1Recommended

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.

1
Create the Main Transaction Table

Set up a single table containing columns for Date, Item, Quantity, Location, Department, and Area. Format this range as an official Table (Ctrl+T).

2
Create the Pricing Table

In a separate worksheet or area, create a table with a unique 'Item' key column and a 'Price' column.

3
Merge Tables using Power Query

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.

4
Build Pivot Tables and Charts

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.

Create Normalized Tables and Merge via Power Query
Scalability: Using Power Query and the Data Model allows your workbook to handle millions of rows efficiently without slowing down.
Efficient Data Management

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. 1. Format Your Data as Tables: Open WPS Spreadsheet, highlight your inventory data, and click 'Format as Table' from the Home tab.
  2. 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. 3. Insert a Pivot Table: Navigate to the Insert tab, select 'PivotTable', and choose to place it on a new worksheet for reporting.
  4. 4. Create Dynamic Charts: With your Pivot Table selected, go to Insert > Chart to visually represent your inventory quantities and values over time.
Fully compatible with Microsoft Excel (.xlsx) formats and table structuresSupports advanced lookup formulas like XLOOKUP for easy data modelingIntuitive drag-and-drop Pivot Table interface for quick reportingFree, lightweight, and fast performance even with large datasets
microsoft office alternative - wps office

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.