Combine Daily Restaurant Sales Data in Excel with a PivotTable
Question details
Combine daily restaurant sales exports into one structured data set and summarize sales by menu item and day without manual item-by-item entry.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to analyze recurring daily restaurant sales data and compare menu item amounts across dates.
- Observed behavior
- Sales data is separated across daily tabs or exports, making it inefficient to create a complete menu-item and daily sales summary manually.
Make sure every daily export uses the same column layout, such as Date, Menu Item, and Amount. Add a Date column to each export if the sale date is not already included in the data.
Create one consolidated Excel table and summarize it with a PivotTable
Use this method when you can paste or append daily sales exports into a single worksheet. It provides a flexible summary by menu item, date, and sales amount.
A PivotTable works best when all records are stored in one continuous table rather than on separate daily tabs. Each row should represent one sales record, with consistent column headers.
Create a worksheet such as Consolidated Sales, copy each daily export below the previous export, and keep only one header row. Include columns such as Date, Menu Item, and Amount.
Click any cell in the consolidated data, then select Insert > Table. Confirm that My table has headers is selected, and click OK.
Select a cell in the table, then choose Insert > PivotTable. Select New Worksheet or Existing Worksheet for the PivotTable location and click OK.
In the PivotTable Fields pane, drag Menu Item to Rows, drag Date to Columns or Filters, and drag Amount to Values.
If Amount does not show as a total, open the Amount field settings in Values, choose Value Field Settings, and select Sum.
Use Power Query to append recurring daily sales exports
Use this option when new daily files or worksheets are added regularly and you want to reduce repeated copy-and-paste work.
Build a Restaurant Sales PivotTable in WPS Spreadsheets
WPS Spreadsheets lets you consolidate daily restaurant sales into a structured table and create a PivotTable to total menu-item sales by day. Its familiar spreadsheet interface helps you move from existing Excel workflows with minimal adjustment.
- 1. Prepare a consolidated worksheet: In WPS Spreadsheets, create a Consolidated Sales sheet and place all daily records in one continuous range with Date, Menu Item, and Amount headers.
- 2. Format the source data as a table: Select the full data range, then use the Insert tab and choose Table to keep the sales source organized.
- 3. Insert a PivotTable: Click a cell in the sales table, select Insert > PivotTable, choose where to place the report, and confirm.
- 4. Add the sales fields: In the PivotTable field list, place Menu Item in Rows, Date in Columns or Filters, and Amount in Values.
- 5. Set Amount to total sales: Open the Amount value settings and select Sum so the PivotTable displays total restaurant sales rather than a count.
Frequently Asked Questions
Should each day of restaurant sales be on a separate Excel sheet?
For PivotTable analysis, place all daily records in one consolidated worksheet or table. Use a Date column to identify the day instead of separating records into tabs.
Which fields should I add to a restaurant sales PivotTable?
Drag Menu Item to Rows, Amount to Values, and Date to Columns or Filters. Set Amount to Sum in Value Field Settings.
Why does my PivotTable show Count of Amount instead of Sum of Amount?
Excel treats the Amount column as text or mixed data. Convert the source Amount cells to numbers, refresh the PivotTable, then select Value Field Settings > Sum.
How do I include new daily sales data in an existing PivotTable?
Add the new rows directly below the existing records in the source table, then right-click the PivotTable and choose Refresh. If the source is an Excel Table, its range expands automatically.
Can Power Query combine daily restaurant sales files automatically?
Yes. In Excel, use Data > Get Data > From File > From Folder, select the folder containing matching daily exports, and use Combine & Transform Data before loading the combined table.
