Build Multi-Location Sales Reports in Excel and Power BI
Question details
Create filterable sales reports from QuickBooks exports for five restaurant locations.

- Product
- Microsoft Excel, Power Query, Power Pivot, and Power BI
- Device & OS
- not provided
- Scenario
- Analyze more than 10,000 exported sales rows by week, weekday, location, and sales type, using totals, averages, charts, and user-selected filters.
- Observed behavior
- The desired reports should consolidate the restaurant data and make comparisons across locations, dates, and sales categories easy.
Check that each QuickBooks export contains a date, location, sales category, and numeric sales amount, and use consistent column names across exports. Decide whether missing category entries mean zero sales or unavailable data before calculating daily averages.
Build an Excel dashboard with Power Query and PivotTables
Use Excel to clean repeat exports and create filterable sales summaries and charts.
Power Query can repeat the import and cleaning steps when new exports are available. Power Pivot and the Data Model are useful when you need relationships between tables; their availability depends on your Excel edition.
A PivotTable average of transaction rows is not necessarily an average of daily sales. For an average daily total, first aggregate sales by date and the relevant location and category, then average those daily totals. Include missing days as zero only if that matches your reporting rules.
In Excel, go to Data > Get Data > From File and choose the appropriate source. If similarly structured exports are stored together, use From Folder to combine them; ensure each row identifies its restaurant location.
In the Power Query Editor, set the date and sales amount data types, standardize location and category names, and use Add Column > Date to create weekday and week fields. Include a year field so weeks from different years can be distinguished.
Select Home > Close & Load or Close & Load To. If you need relationships with other tables and your Excel edition supports it, choose the option to add the data to the Data Model.
Select Insert > PivotTable and place location, weekday, week, or sales category in Rows or Columns. Add the sales amount to Values and use Value Field Settings to select Sum or Average as appropriate.
Select a PivotTable, then use PivotTable Analyze > PivotChart and Insert Slicer to add visualizations and filters for fields such as location and sales type. Use Data > Refresh All after updating the source exports.

Create an interactive sales dashboard in Power BI
Use Power BI when interactive dashboard reporting is the priority.
Create filterable restaurant sales reports in WPS Spreadsheets
Open the QuickBooks Excel exports in WPS Spreadsheets and use PivotTables, charts, and filters to summarize sales by location, date, and category.
- 1. Open the sales export: In WPS Spreadsheets, select Open and choose the QuickBooks Excel file. Check that dates, locations, categories, and sales amounts have consistent column headers.
- 2. Prepare the data: Combine similarly structured exports into one table and add year, week, and weekday columns as needed. For daily averages, first summarize sales by date, location, and category.
- 3. Create a PivotTable: Select the data range, choose Insert > PivotTable, and place location, week, or category fields in the report. Add sales amounts to Values and choose the appropriate summary calculation.
- 4. Visualize and filter results: Select the summary and use Insert > Chart to create a chart. Use PivotTable filters or worksheet filters to focus on the locations and sales types you want to compare.

Frequently Asked Questions
Can Excel handle more than 10,000 sales rows?
Yes. More than 10,000 rows is within Excel's worksheet limit. Use Data > Get Data to prepare the exports and Insert > PivotTable to summarize them.
How do I compare the same weekday across several weeks?
Add weekday, week, and year fields in Power Query using Add Column > Date. In a PivotTable, place weekday and week in Rows or Columns and add the sales amount to Values.
Will a PivotTable average show average daily sales?
Not necessarily. If each row is a transaction, Average calculates the average transaction amount. Aggregate by date and the relevant location and category first, then average the daily totals.
How do I update the report when new QuickBooks exports arrive?
Add or replace the source files in the location used by your Power Query connection, then select Data > Refresh All in Excel. Keep the export columns consistent so the query can process them.
Should I use Access instead of Excel for these reports?
Excel is a practical starting point for these exports and supports Power Query, PivotTables, and charts. Access may be useful if you need a relational database for storing or managing the data, but it is not required to build the reports.




