How to Create a Restaurant Dashboard in Excel

Managing a restaurant means juggling dozens of moving parts, from tracking daily sales and food costs to scheduling staff and monitoring inventory. An Excel dashboard can turn that chaos of scattered data into a clear, visual command center for your business. This guide will walk you through building a powerful restaurant dashboard from scratch, even if you’re not an Excel wizard.

Why Your Restaurant Needs a Dashboard

Before jumping into the spreadsheet, it’s helpful to understand what a dashboard actually accomplishes. At its core, a dashboard gives you a high-level, visual overview of your restaurant's most important metrics (often called Key Performance Indicators, or KPIs) in one place. Instead of digging through multiple reports from your POS system, accounting software, and online ordering platforms, you get an at-a-glance view of your business health.

This helps you:

• Spot Trends Instantly: See if Saturday sales are trending up, if a new menu item is a hit, or if food costs are slowly creeping higher.

• Make Data-Driven Decisions: Instead of relying on gut feelings, you can decide whether to run a promotion, adjust staff schedules, or launch a new marketing campaign based on hard numbers.

• Improve Efficiency: Identify your busiest and slowest hours to optimize staffing, or pinpoint your top-selling items to streamline inventory and kitchen prep.

Step 1: Gather and Organize Your Data

A dashboard is only as good as the data powering it. Your first step is to collect the raw information from your various systems. For a restaurant, this data typically comes from a few key sources:

• Point of Sale (POS) System: This is your primary source for transaction data. You'll want to export CSV or Excel files that include details like date, time, receipt number, items sold, quantity, price, and payment method. Many systems can also provide data on which server handled the transaction.

• Inventory Management Software: This provides your food cost data (cost of goods sold or COGS), helping you track waste and profit margins on specific dishes.

• Scheduling/Payroll Software: This is where you’ll get labor costs. You can use this to calculate your labor cost percentage and measure staff efficiency.

• Online Ordering Platforms (UberEats, DoorDash, etc.): These platforms have their own analytics, but exporting their data allows you to see the full picture of your delivery and takeout business alongside your dine-in sales.

For this tutorial, we will focus primarily on POS data, as it's the foundation of most restaurant dashboards. Consistency is key here. Get into a weekly routine of exporting your data and adding it to a master spreadsheet. Create a new Excel workbook and make a blank sheet named "Data" where you will paste all your raw, exported sales information.

Best Practice: Use a Clean Data Structure

Organize your "Data" sheet into a simple, clean table structure. Each row should represent a single item sold in a transaction. Your columns should look something like this:

• Date: The date of the sale (e.g., 2023-11-15)

• Time: The time of the sale

• Day of Week: (You can add this with an Excel formula)

• Transaction ID: A unique ID for each customer check

• Category: The item's menu category (e.g., Appetizer, Entree, Drink)

• Item Name: The name of the product sold (e.g., "Classic Burger")

• Quantity: How many units were sold

• Price: The price per item

• Total Sale: (Price x Quantity)

• Cost: The cost per item (Cost of Goods Sold)

• Total Cost: (Cost x Quantity)

• Server Name: The employee who made the sale

Once you've pasted your data into the "Data" sheet, click anywhere inside the data set and press Ctrl + T (or Cmd + T on a Mac) to format it as an official Excel Table. Give your table a name (e.g., "SalesData") in the "Table Design" tab. Using a Table makes your data dynamic, meaning any new rows you add will automatically be included in your analysis.

Step 2: Define Your Key Metrics (KPIs)

Now that your data is organized, decide what you want to measure. Staring at a huge dataset can be overwhelming. Focusing on a handful of core KPIs will keep your dashboard clear and actionable.

Common Restaurant KPIs

• Total Revenue: The total amount of money generated from sales.

• Average Check Size: Total Revenue / Total Number of Transactions. This tells you how much a typical customer spends.

• Sales by Category: Breakdown of sales by food, beverage, appetizers, etc., helping you see what's driving revenue.

• Top Selling Menu Items: Which dishes are your stars?

• Food Cost Percentage: (Total Cost of Goods Sold / Total Revenue) * 100. This is crucial for profitability.

• Labor Cost Percentage: (Total Labor Cost / Total Revenue) * 100. Another key profitability metric. (We'll skip this for our POS-only example, but it's a vital KPI).

Step 3: Build the "Engine" with PivotTables

PivotTables are the powerhouse behind an interactive Excel dashboard. They allow you to summarize, group, and calculate your data without writing complex formulas. We'll create our PivotTables on a new sheet called "Calculations" to keep our workbook tidy.

Creating Your First PivotTable

1. Go to your "Data" sheet and click anywhere inside your data Table.

2. Go to the Insert tab and click PivotTable.

3. In the dialog box, ensure your table ("SalesData") is selected and choose to place the PivotTable in an "Existing Worksheet." Click the location box and then select cell A1 in your "Calculations" sheet. Click OK.

A new PivotTable will appear. Now let's use it to calculate Total Revenue by Date:

• Drag the "Date" field into the Rows area.

• Drag the "Total Sale" field into the Values area.

You now have a summary of sales for each day! You can repeat this process to create separate PivotTables for other key metrics, like:

• Sales by Category: "Category" in Rows, "Total Sale" in Values.

• Top Selling Items: "Item Name" in Rows, "Quantity" and "Total Sale" in Values.

• Average Check Size: This one is a bit trickier. You need the total number of unique transactions. In the PivotTable, drag "Transaction ID" to the Values area. Right-click it, go to "Value Field Settings," and choose "Distinct Count." Now you have your transaction count to calculate the average check size. You can then do a simple formula in a separate cell: =[Total Revenue Pivot Cell] / [Distinct Count Pivot Cell].

Your "Calculations" sheet will start to fill up with these small summary tables that will feed all the visuals on your dashboard.

Step 4: Design Your Dashboard View

Finally, the fun part. Go to a new, blank sheet and name it "Dashboard." This is where you'll bring everything together and create your visual report.

1. Add Chart Visualizations

Charts are the easiest way to understand trends. To create one, go back to your "Calculations" sheet and click on one of your PivotTables (like the sales by date one).

• From the PivotTable Analyze tab, click PivotChart.

• Choose a suitable chart type. A Line Chart is perfect for showing sales trends over time.

• Clean up your chart by removing unnecessary buttons (field buttons), and giving it a clear title like "Daily Sales."

• Once your chart looks good, right-click it, select Cut, then go to your "Dashboard" sheet and Paste it.

Repeat this process for your other metrics. Good chart choices include:

• Bar or Column Chart: For comparing your top-selling items or sales by categories.

• Pie or Donut Chart: To show the percentage breakdown of sales by category (e.g., 60% food, 30% beverage, 10% other).

2. Add C-Level Metric Cards

Your dashboard should always display your most important, high-level numbers prominently. We'll create "cards" for metrics like Total Revenue and Average Check size.

• On your Dashboard sheet, a cell for your metric. You can make it look nice later by adding a fill color and a border.

• Click on the cell and click in the formula bar, type "=", then navigate to your "Calculations" sheet and click on the single cell that contains your total revenue figure from the PivotTable.

The cell on your dashboard is now dynamically linked to your data. When you refresh your data, this number will update automatically. You can format the text to be large and bold to make it stand out. Repeat this for your other main KPIs.

3. Make it Interactive with Slicers

Slicers are filters that let you (or your team) easily slice and dice the data. This is what makes a dashboard truly interactive.

1. Select any of your dashboard charts.

2. On the PivotChart Analyze tab, click "Insert Slicer."

3. A dialog box will appear with all your data fields. Check the boxes for the fields you want to filter by — "Day of Week," "Server Name," and "Category" are great ones to start with. Click OK.

4. The slicers will appear on your sheet. Move and resize them to fit your layout. Now, clicking "Friday" on the Day of Week slicer will automatically filter all connected charts to show only Friday's data.

Pro Tip: To connect a slicer to multiple PivotCharts (so one click filters every chart), right-click the slicer, choose "Report Connections," and check the box for every PivotTable in your workbook.

Maintaining and Using Your Dashboard

Your dashboard is now ready to go! To keep it current, you just need to paste the latest data exports into your "Data" Table. After adding the new data, go to the "Data" tab in Excel's ribbon and click "Refresh All." All your PivotTables, charts, and metric cards will update instantly.

Set aside time each week to review the dashboard. Look for patterns. Is a certain server consistently achieving a higher average check size? Maybe they have up-selling techniques to share with the team. Are appetizer sales low on Tuesdays? Perhaps a mid-week promotion could help.

Final Thoughts

Building a restaurant dashboard in Excel transforms your raw data into actionable business intelligence. By consolidating information from your POS and other systems into one visual report, you gain an incredible ability to track performance, understand trends, and make smarter decisions that improve both your customer's experience and your bottom line.

We know that even with a great process, manually exporting CSVs and wrestling with PivotTables still takes up valuable time each week. At Graphed, we created a tool to eliminate this entire process. Instead of manually exporting data, you connect your POS, accounting software, and ad platforms directly. Then you can create live, real-time dashboards by simply asking questions in plain English, like "Show me my sales by hour compared to last weekend" or "Which menu items have the best profit margin this month?" This turns hours of weekly reporting into seconds, ensuring your dashboard is always up-to-date automatically.