How to Create a Report in Excel Using Worksheet Data

Turning a spreadsheet packed with raw data into a clear, insightful report can feel like a daunting task, but Excel has powerful tools that make it surprisingly simple. Forget spending hours manually calculating sums or copying and pasting data. This guide will walk you through transforming your worksheet data into a professional and interactive report using PivotTables, charts, and slicers, turning rows and columns of data into meaningful answers.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Start with a Solid Foundation: Prepping Your Data

Before you build anything, you need a solid foundation. The quality of your report depends entirely on the quality and structure of your source data. Spending just a few minutes cleaning and organizing your data first will save you from major headaches later.

1. Keep it Clean and Tidy

Your data should be organized in a simple, tabular format. This means:

• Each row is a single record or transaction. For example, one row for each sale, each website visit, or each customer support ticket.
• Each column is a specific attribute of that record. Think columns for "Date," "Sales Rep," "Product Category," "Revenue," and "Region."
• You have a unique header row. Make sure every column has a clear, descriptive, and unique title in the very first row.
• No blank rows or columns. Blank rows can cause Excel to think your dataset ends prematurely.

Take a moment to scan for common issues like typos (e.g., "Chicago" and "Chitown"), inconsistent formatting (e.g., dates formatted as text), and extra spaces before or after text. Use Excel's "Remove Duplicates" tool (found under the Data tab) to get rid of any identical rows.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

2. The Magic of Formatting as a Table

This is arguably the most important preparation step. Converting your data range into an official Excel Table unlocks several key features. It's not just about adding some fancy stripes to your spreadsheet, it’s about making your data dynamic and easier to work with.

To format your data as a Table:

1. Click any single cell inside your data range.
2. Go to the Insert tab on the Ribbon.
3. Click on Table.
4. Excel will automatically guess your data range. If it’s correct and you have headers, check the box for "My table has headers" and click OK.

Now, whenever you add a new row of data to the bottom, the Table automatically expands to include it. This means any report or chart you build from this Table will update automatically when you refresh it — no need to manually adjust ranges ever again.

The Heart of Excel Reporting: Mastering PivotTables

If you're building a report in Excel, PivotTables will be your best friend. A PivotTable is an interactive tool that allows you to quickly summarize huge amounts of data without writing a single formula. It helps you "pivot" or reorganize your data to highlight different perspectives and uncover insights.

Step 1: Insert Your PivotTable

With your data now formatted as an official Excel Table, creating a PivotTable is a breeze.

1. Click anywhere inside your Table.
2. Go to the Insert tab.
3. Click PivotTable. A new window will pop up.
4. Since you're using a Table, the Table/Range should already be filled in with your Table's name.
5. Choose to place the PivotTable in a New Worksheet to keep things organized. Click OK.

You’ll now see a blank PivotTable on a new sheet and a "PivotTable Fields" pane on the right side of your screen. This is where you’ll build your report.

Step 2: Build Your Summary Report

The "PivotTable Fields" pane is your control center. It contains a list of all your column headers and four boxes: Filters, Columns, Rows, and Values.

Let's say you want to see total monthly sales for each sales representative. Here's how you'd do it:

• Find "Sales Rep" in your field list and drag it into the Rows box. You'll instantly see a unique, alphabetized list of your sales reps appear in the rows of your PivotTable.
• Find "Order Date" and drag it into the Columns box. Excel is smart enough to automatically group the dates into months and quarters.
• Find "Revenue" (or whatever you're measuring) and drag it into the Values box. Because this field contains numbers, Excel will automatically calculate the Sum of Revenue.

Just like that, you have a summarized report showing a breakdown of revenue by representative and month. No complex SUMIFS formulas needed.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Refine a Few Key Details

Your summary is built, but you can make it much more readable with a few tweaks.

• Format Numbers: Right-click on any number in the revenue section of your PivotTable, select Number Format, and choose Currency. This makes your report look much more professional.
• Sort Your Data: Want to see your top-performing rep at the top? Right-click on a sales rep's name, go to Sort, and choose Sort Z to A based on the total revenue column.
• Use Descriptive Headers: The default headers like "Row Labels" aren't very useful. You can click directly into that cell and type a better header like "Sales Representative."

Make It Visual: Adding Charts to Your Report

Numbers in a table are great, but a chart can tell a story in seconds. A PivotChart is directly linked to your PivotTable, which means it will update automatically whenever your PivotTable data changes or is filtered.

Creating Your First Chart

Building on our PivotTable from the previous section:

1. Click anywhere inside your completed PivotTable.
2. Go to the PivotTable Analyze tab (this tab only appears when a PivotTable cell is selected).
3. Click on PivotChart.
4. A dialog box will appear, suggesting different chart types. For comparing reps, a Clustered Column or Bar chart works well. For showing sales over time, a Line chart is ideal. Let’s pick a Column chart. Click OK.

Your chart will appear on the sheet. Now, take a second to clean it up for better presentation:

• Give it a clear title. Click on "Total" and change it to something descriptive like "Monthly Sales Performance by Representative."
• Remove clutter. Those grey field buttons on the chart can be helpful for filtering but are often distracting in a final report. On the PivotTable Analyze tab, click the dropdown for Field Buttons and choose Hide All.

Putting It All Together: Your First Excel Dashboard

A dashboard is a single-screen view of your most important metrics and charts. It gives you (and your team) a quick, high-level overview of performance. The key to a good dashboard is interactivity.

First, create a new worksheet and call it "Dashboard." Then, cut and paste your PivotTable and PivotChart from their worksheet onto your new Dashboard sheet. Arrange them so they're easy to view.

Add Interactivity with Slicers

Slicers are basically stylish, interactive buttons that allow you to filter your PivotTables and PivotCharts. They are far more user-friendly than the standard filter dropdown menus.

1. Click on your PivotTable to select it.
2. On the PivotTable Analyze tab, click Insert Slicer.
3. A list of your data columns appears. Check the box for fields you want to filter with, like "Region" or "Product Category." Click OK.

You'll now see floating slicer panels you can move around your dashboard. When you click on "East" in the Region slicer, both your PivotTable and PivotChart will instantly update to show data for only the East region. But what if you have multiple charts? You can connect one slicer to control them all.

To connect a single slicer to multiple charts and tables, right-click the slicer, choose Report Connections, and check the boxes for all the PivotTables on your dashboard you want it to control.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Pro-Tips for Polishing Your Excel Reports

You now have a functional, interactive report. Here are a few final tips to make it even better.

• Use Conditional Formatting: Select a column of numbers in your PivotTable, go to the Home tab, and use Conditional Formatting to add color scales or data bars. This helps you quickly spot high and low values without having to read every single number.
• Add a "Refresh All" Button: When you add new data to your source Table, you'll need to refresh your reports. Go to the Data tab and click Refresh All. This updates every PivotTable and chart in your entire workbook.
• Freeze Panes: If you're looking at a large data set, freezing the top row can keep your headers visible as you scroll. On your data worksheet, select the cell directly underneath your header row (cell A2 in most cases), go to the View tab, and select Freeze Panes.
• Create Calculated Fields: If your raw data contains "Units Sold" and "Price Per Unit," but not "Total Revenue," you can create that metric directly in your PivotTable. Select your PivotTable, go to the Analyze tab, choose "Fields, Items, & Sets," and select "Calculated Field." Use a formula like =Price * Units Sold to include your data in the field list to add to values without altering your original data.

Final Thoughts

In short, creating a powerful report in Excel boils down to a clear process: prepare clean, tabular data, summarize it instantly with PivotTables, visualize key trends with PivotCharts, and add interactivity with slicers. Mastering this workflow moves you from being someone who just manages data to someone who uses it to tell a compelling story and drive decisions.

Of course, this process often repeats weekly or monthly and can become time-consuming, especially when your data lives across platforms like Google Analytics, Shopify, and Salesforce. To tackle this, we built Graphed to automate the entire reporting process from start to finish. Instead of downloading CSVs and building PivotTables, we let you connect all your data sources and create live dashboards simply by describing what you want to see, like "Show me a chart of Shopify sales this month, broken down by our top Google Ads campaigns." It allows your entire team to ask questions and get insights in seconds, without ever having to touch a spreadsheet again.