How to Create a Summary Page in Excel

Scrolling through endless worksheets to find the numbers you need is a surefire way to lose your train of thought. Instead of hopping between tabs, a summary page centralizes your most important metrics in one dashboard-style view. This tutorial will walk you through a few different ways to create a summary page in Excel, from simple formula linking to a powerful, fully automated dashboard using PivotTables.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Create a Summary Page in Excel?

Before jumping into the "how," it helps to understand the "why." A well-designed summary page is more than just a convenience, it's a powerful reporting tool that offers several key benefits:

• At-a-Glance Insights: It provides a high-level overview of your business or project performance, letting you spot trends and key metrics instantly without digging through detailed tabs.
• Simplified Reporting: Stakeholders and leadership rarely need to see every single line item. A summary page gives them the bottom line, making your reports clearer, more concise, and more impactful.
• Reduced Manual Errors: By linking directly to your source data, you eliminate the need for manual copy-pasting. This dramatically reduces the risk of human error from grabbing the wrong number or pasting it in the wrong place.
• Single Source of Truth: It acts as a centralized dashboard where all key performance indicators (KPIs) live. When someone needs the latest numbers, they know exactly where to go.

Method 1: Quick and Easy with Simple Formulas

The most straightforward method for creating a summary page is to directly link to cells on other worksheets. This is perfect for when you need to pull specific, fixed values into a simple report.

Let’s imagine you have a workbook with separate sheets for each month's sales: "Jan_Sales," "Feb_Sales," and "Mar_Sales." Each sheet has the total revenue for the month in cell E22. Your goal is to create a "Q1 Summary" sheet that displays the total revenue for each month.

Step-by-Step Instructions:

1. Create Your Summary Sheet: Start by adding a new worksheet to your workbook and naming it "Q1 Summary." Set up your labels, for example, creating a small table with headers like "Month" and "Total Revenue."
2. Start the Formula: In the cell where you want to show January's revenue (let's say it's cell B2 on your summary sheet), type the equals sign = to begin your formula.
3. Navigate and Click: With the equals sign in place, don't press Enter. Instead, click on the "Jan_Sales" worksheet tab at the bottom of the screen. Now, click on cell E22 (the cell containing the total revenue). You'll see Excel automatically write the formula for you in the formula bar: 'Jan_Sales'!E22.
4. Press Enter: Hit the Enter key. Excel will take you back to your "Q1 Summary" sheet, and cell B2 will now display the total revenue from the "Jan_Sales" sheet.
5. Repeat for Other Months: Repeat the process for February and March, linking to cell E22 on their respective sheets.

The syntax for this link is always =SheetName!CellReference. You can either type this manually or use the point-and-click method described above.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

When Is This Method Best?

This approach is ideal for simple dashboards where you only need to pull a handful of static numbers. If you just need to display a few KPIs that are always in the same place, this is the fastest way to do it.

Limitations to Consider

While simple, this method isn’t very scalable. Linking dozens of cells one by one is tedious. Furthermore, if you insert a row or column on one of your source sheets, the cell reference (like E22) might no longer point to the correct data, which can break your summary page without you realizing it.

Method 2: Summing Across Multiple Sheets with 3D Formulas

What if you want to calculate a grand total from multiple sheets? For example, you want the total Q1 revenue by summing the monthly totals. A 3D reference formula is your best friend here. It allows you to perform calculations (like SUM, AVERAGE, COUNT) across a range of worksheets.

The one crucial requirement for a 3D reference is that all your source sheets must have an identical structure. This means the value you want to aggregate must be in the exact same cell on every sheet.

Continuing our example, let's say Total Revenue is in cell E22 on "Jan_Sales," "Feb_Sales," and "Mar_Sales." You want to calculate the total Q1 Revenue on your summary page.

Step-by-Step Instructions:

1. Arrange Your Sheets: For a 3D formula to work, the sheets you want to include must be positioned next to each other in the workbook tabs. Make sure "Jan_Sales," "Feb_Sales," and "Mar_Sales" are in a consecutive block.
2. Write the Formula: On your "Q1 Summary" sheet, click the cell where you want the grand total. Type the beginning of your formula, for example: =SUM(.
3. Select the Sheet Range: After the opening parenthesis, click on the tab for the first sheet in your range ("Jan_Sales"). Then, hold down the Shift key and click on the tab for the last sheet in your range ("Mar_Sales"). Excel will highlight all the sheets in between.
4. Select the Cell: While the sheets are highlighted, click on the cell you want to sum (E22). The formula bar will now show something like this: =SUM('Jan_Sales:Mar_Sales'!E22).
5. Close the Formula: Press Enter. You now have a single formula that sums cell E22 across all three monthly sheets.

=SUM(Sheet1:LastSheet!CellReference)

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Pro Tip: Create a Dynamic Range

The real power of 3D formulas is their ability to automatically include new sheets. If you were to add a "Mid-Jan_Promo" sheet and drag its tab between the "Jan_Sales" and "Mar_Sales" tabs, your =SUM() formula would instantly include the total from that new sheet without any edits required.

Method 3: Go Dynamic with Power Query and PivotTables

For the ultimate summary page, you need a method that is both dynamic and flexible. What if your data doesn't live in a single cell? What if each monthly sheet has a table of sales data with a varying number of rows? This is where manually linking cells falls apart. The solution is to consolidate all your data into a master table using Power Query and then summarize it with a PivotTable.

This approach transforms your summary from a static report into an interactive dashboard.

Step 1: Format Your Data as Tables

Before you begin, it’s a best practice to format your data on each source sheet as an official Excel Table. This makes it easier for Power Query to recognize and work with.

• On each monthly sheet, click anywhere within your data range.
• Go to the Insert tab and click Table (or press Ctrl + T).
• Ensure the "My table has headers" box is checked, then click OK.
• Give your table a descriptive name (e.g., "Jan_data") in the "Table Design" tab that appears.

Step 2: Consolidate Sheets with Power Query

Power Query is a data transformation tool built into Excel. We'll use it to stack all your monthly tables on top of each other.

1. Go to the Data tab. In the "Get & Transform Data" section, click Get Data > From Other Sources > Blank Query.
2. The Power Query Editor will open. In the formula bar, type the following formula and press Enter: = Excel.CurrentWorkbook() This function lists all the Tables and Named Ranges in your current workbook.
3. You will see a list of tables you created (e.g., "Jan_data," "Feb_data"). The table data itself is in the [Content] column. Click the expand button (two opposite-facing arrows) on the "Content" column header.
4. Uncheck "Use original column name as prefix" and click OK. Power Query will now combine or "append" all the rows from every table into one large master list. You now have a single, unified dataset!
5. Click the Close & Load To... dropdown button on the Home tab.
6. In the dialog box, select PivotTable Report and choose to place it on a New Worksheet. Click OK.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 3: Build Your Interactive Summary PivotTable

Now for the fun part. You have a blank PivotTable connected directly to your consolidated data.

1. In the "PivotTable Fields" pane on the right, you can drag and drop your data fields to build your summary. For example:
2. Instantly, you have a summary table breaking down sales by product and date, pulled from all your sheets combined.
3. To keep things up-to-date, simply go to the Data tab and click Refresh All whenever you add new data to your monthly tables. The Power Query will re-run, and your PivotTable will reflect all the new information.

Tips for an Effective Summary Page

Once you have the data sorted, presentation is key. Here are a few tips to make your summary page clear and easy to understand:

• Keep It Simple: Avoid clutter. Use plenty of white space, group related metrics together, and use clear, descriptive titles for every chart and table.
• Add Visuals: Complement your PivotTables with PivotCharts or craft standard Excel charts from your summary data. Bar charts, line graphs, and clear scorecards for KPIs turn a boring table of numbers into a digestible dashboard.
• Use Conditional Formatting: Make important data stand out. Use green/red icon sets to show positive or negative growth, or apply data bars to quickly visualize the scale of different numbers.
• Make it Interactive with Slicers: If you're using a PivotTable, insert Slicers (from the "PivotTable Analyze" tab). These are user-friendly buttons that allow anyone viewing the report to easily filter the data by category, region, salesperson, or any other dimension you've included.

Final Thoughts

Creating a summary page in Excel is an essential skill that transforms scattered data into actionable reports. Whether you’re using simple cell linking for a quick overview, 3D formulas for efficient calculations, or the dynamic duo of Power Query and PivotTables for a truly interactive dashboard, a centralized summary saves you time and helps you and your stakeholders make better decisions.

While these Excel methods offer powerful ways to build reports, they still often rely on manual steps, from structuring your sheets perfectly to remembering to hit "Refresh All." Bringing together data from other applications like Google Analytics or your CRM adds even more complexity. At a certain point, the time spent managing spreadsheets outweighs the benefits. We built Graphed to remove that friction. It’s a tool that automates this entire process by connecting directly to your sources, allowing you to build real-time dashboards just by describing what you need in simple English - no formulas, PivotTables, or manual data wrangling required.