How to Create a Restaurant Reporting Dashboard in Excel

Creating a Restaurant Reporting Dashboard in Excel is an essential step for any restaurant owner or manager who wants to monitor performance, manage costs, optimize operations, and make data-driven decisions. Excel’s versatility, especially with features like PivotTables, Power Query, and charts, makes it a powerful tool for building dashboards without the need for expensive software or data analyst.

This guide will walk you through step-by-step how to design, structure, and build a comprehensive restaurant reporting dashboard in Excel, covering everything from sales tracking to inventory, labor, and customer data.

🧱 PART 1: PLANNING YOUR DASHBOARD

🔍 Step 1: Define the Purpose

First, determine what key metrics and reports you want to track. This will shape your data structure and dashboard layout.

Typical restaurant metrics include:

• Sales Overview: Daily/weekly/monthly sales, sales by category (food vs drinks), sales by server/cashier
• Cost Metrics: Food cost %, labor cost %, average cost per item
• Inventory Reports: Usage rate, shrinkage, reorder points
• Customer Data: Number of covers, average spend per customer, peak times
• Performance KPIs: Net profit, gross margin, ticket size, staff productivity

📊 PART 2: DATA STRUCTURE & COLLECTION

To create a dynamic dashboard, you need well-organized, consistent data tables.

📁 Recommended Data Tables:

1. Sales Data Table
   • Date
   • Invoice Number
   • Item
   • Category (Food, Beverage, Dessert, etc.)
   • Quantity Sold
   • Price
   • Total Amount
   • Server/Employee Name
   • Payment Method
2. Inventory Data Table
   • Item Name
   • Category
   • Starting Inventory
   • Purchases
   • Ending Inventory
   • Cost Per Unit
3. Labor Data Table
   • Employee Name
   • Role
   • Date
   • Hours Worked
   • Hourly Rate
   • Total Wage
4. Customer Count Table (Optional)
   • Date
   • Total Customers Served
   • Total Orders
   • Avg Order Value
5. Expenses Table
   • Date
   • Expense Category (Utilities, Rent, Marketing)
   • Amount
   • Description

✅ Best Practices for Data Tables

• Use Excel Tables (Insert → Table) for automatic expansion and reference.
• Keep the data normalized (don’t mix calculations or summaries in raw data).
• Use consistent date formatting and avoid merging cells.

🛠️ PART 3: BUILDING THE DASHBOARD

Step 1: Create a “Dashboard” Sheet

Name a new tab Dashboard where you will pull all key summaries, metrics, and visualizations.

Step 2: Set Up Your Layout

Split your dashboard into sections such as:

• Revenue Overview
• Category Breakdown
• Labor & Cost Metrics
• Sales Trends
• Top-Selling Items
• Profit Summary
• Customer Behavior

Use Excel’s Grid Layout to design clean, structured blocks for each section.

📐 PART 4: BUILDING CALCULATIONS AND VISUALS

Each visual or number on your dashboard will usually be tied to a PivotTable, formula, or chart.

💰 Revenue Overview

Metrics:

• Total Sales (Today, Week, Month)
• Daily Sales Trend
• Sales by Category

How to Build:

1. Insert PivotTable from your Sales Data table.
2. Rows: Date
3. Values: Sum of Total Amount
4. Create a line chart for daily trend
5. Filter by current month using slicers

Formulas:

• Total Sales This Month:
  =SUMIFS(Sales[Total Amount], Sales[Date], ">=1-Apr-2025")
• Average Daily Sales:
  =AVERAGEIFS(Sales[Total Amount], Sales[Date], ">=1-Apr-2025")

🍕 Category Sales Breakdown

Metrics:

• Sales by Category (e.g., Food, Drink, Dessert)

Visualization:

• Use a Pie Chart or Bar Chart from a PivotTable

1. Insert PivotTable from Sales Table.
2. Rows: Category
3. Values: Sum of Total Amount

🧑‍🍳 Labor Cost Metrics

Metrics:

• Total Hours Worked
• Total Labor Cost
• Labor Cost % of Sales

Formulas:

• Labor Cost %:
  =SUM(Labor[Total Wage]) / SUM(Sales[Total Amount])
• Avg Hourly Wage:
  =AVERAGE(Labor[Hourly Rate])

📦 Inventory Usage & Cost

If you track beginning and ending inventory:

Formulas:

• Inventory Used = Starting Inventory + Purchases – Ending Inventory
• Food Cost = Inventory Used × Unit Price
• Food Cost % = Food Cost / Total Sales

Use a bar chart to show cost breakdown by ingredient category.

🌟 Top Selling Items

Visualization:

• Create a bar chart showing top 5 or 10 best-selling menu items by quantity or revenue.

How to Build:

1. PivotTable from Sales Data
2. Rows: Item
3. Values: Sum of Quantity Sold or Sum of Total Amount
4. Sort by largest to smallest
5. Use Top 10 Filter

📈 Monthly or Weekly Trends

Goal: Show how your sales, costs, and profits trend over time.

1. PivotTable from Sales with rows by week or month.
2. Use:
   • Sales
   • Labor Cost
   • Food Cost
   • Net Profit (Sales – Labor – Food)
3. Create a line chart or stacked column chart

📍 PART 5: ADDING INTERACTIVITY

🔄 Use Slicers

Slicers add clickable filters for your PivotTables and charts.

Common slicers:

• Date
• Item Category
• Server/Employee
• Payment Method

How to Add:

• Select a PivotTable → PivotTable Analyze → Insert Slicer

📌 Use Drop-Downs for Period Selection

Use data validation lists and formulas like =SUMIFS() to calculate dynamically based on selected month, category, or staff member.

🧾 PART 6: KPI BOXES

At the top of the dashboard, add large, colored KPI cards showing quick stats.

Examples:

KPI	Formula Example
Total Sales Today	=SUMIFS(Sales[Total Amount], Sales[Date], TODAY())
Monthly Sales	=SUMIFS(Sales[Total Amount], Sales[Date], ">=1-Apr-2025")
Labor Cost %	=Labor Cost / Sales
Avg Order Value	=Total Sales / Total Orders
Net Profit	=Sales - Food Cost - Labor - Expenses

Format these with large font, background fill, and conditional formatting to highlight good/bad values.

🎨 PART 7: DESIGN & PRESENTATION

Visual Tips:

• Use consistent fonts and colors.
• Avoid gridlines and clutter.
• Use conditional formatting to highlight performance (e.g., red for negative, green for growth).
• Align charts and KPI cards symmetrically.
• Group sections with colored headers or shapes.

Use Color Coding:

• Green = Good (e.g., high sales, low costs)
• Red = Needs Attention (e.g., rising costs)
• Blue = Informational (neutral)

⚙️ PART 8: AUTOMATION & UPDATES

1. Use Power Query to Automate Data Imports

If your sales system can export CSV or Excel files, Power Query can:

• Import files from a folder
• Clean and combine data
• Refresh all data with one click

2. Use Macros (Optional)

For automation like:

• Refresh all reports
• Save PDF version of dashboard
• Clear slicers or filters

📥 PART 9: EXPORTING & SHARING

• Use “Page Layout” view to prepare for printing.
• Export as PDF to share with stakeholders.
• You can also save snapshots for daily/weekly review.

🗃️ PART 10: ADVANCED IDEAS

If you want to go beyond basic dashboards:

• Power Pivot: Enables advanced modeling (e.g., relationships between multiple tables)
• Timeline Slicers: For scrolling through time visually
• Goal vs. Actual: Set targets and track performance variance
• Forecasting: Use Excel’s Forecast Sheet feature to predict trends
• Dashboard Template: Save your layout as a reusable Excel template (.xltx)

✅ SAMPLE DASHBOARD SECTIONS OVERVIEW

Section	Key Features
KPI Overview	Sales Today, Monthly Revenue, Avg Order, Net Profit
Sales by Category	Bar or Pie Chart by Food, Drink, Dessert, etc.
Daily/Weekly Sales Trend	Line chart showing sales over time
Top Menu Items	Bar chart of best-sellers
Labor Metrics	Hours worked, total wages, labor %
Food Cost Report	Inventory usage and cost %
Customer Metrics	Customer count, order value, peak time
Expense Tracker	Total expenses by category

🎁 BONUS: DOWNLOADABLE TEMPLATE STRUCTURE (Manual Creation)

You can manually structure the workbook with the following tabs:

Sheet Name	Purpose
SalesData	Raw daily transaction data
LaborData	Staff hours and payroll info
InventoryData	Stock tracking, purchases
Expenses	Operating expenses
KPIs	Formulas to feed dashboard
Dashboard	Visuals, charts, and summary stats

📌 FINAL TIPS

• Keep your Excel file clean and light by using Tables and avoiding excessive formulas.
• Regularly back up your data sheets before updates.
• Always verify calculations (especially for financials) and test slicers.
• If your restaurant grows, consider migrating to Power BI or a POS-integrated analytics tool for deeper insights.