How to Run a Report in Excel

Building a report in Excel can feel like you’re either creating a work of art or about to pull your hair out. The good news is that you don’t need to be a spreadsheet guru to turn a mountain of raw data into a clean, insightful, and automated report. This guide will walk you through the practical steps to build reports that actually tell a story, help you make better decisions, and are easy to update.

We'll cover everything from prepping your data to using powerful features like PivotTables and charts, giving you a clear playbook for running reports in Excel.

First Things First: Prepare Your Data for Reporting

You can't build a strong house on a weak foundation, and you can't build a good report with messy data. Before you touch a single formula or chart, taking a few minutes to clean and structure your source data will save you hours of headaches later. Clean reporting data follows a few simple rules.

Structure Your Data in a Table Format

Your raw data should be set up in a simple, tabular format. This means:

One Header Row: Your first row should contain unique, descriptive titles for each column (e.g., "Date," "Product," "Region," "Sales Amount").
No Merged Cells: Merged cells are the enemy of sorting, filtering, and PivotTables. Unmerge any cells in your dataset.
No Blank Rows or Columns: Ensure there are no completely empty rows or columns within your data, as this can cause Excel to think your dataset has ended.
Consistent Data in Each Column: Every cell in a column should contain the same type of data. The "Date" column should only have dates, and the "Sales Amount" column should only have numbers.

A good rule of thumb is to imagine your data as a simple list. Each row is a single record (like one sale), and each column is an attribute of that record.

Format as an Official Excel Table

This is arguably the most important tip in this entire guide. Once your data is structured correctly, turn it into an official Excel Table. Don't just format it with colors - use the "Format as Table" feature.

Here’s how:

Click anywhere inside your data range.
Go to the Home tab and click "Format as Table," or simply press the shortcut Ctrl + T (or Cmd + T on Mac).
A small box will appear confirming the data range. Make sure "My table has headers" is checked if you have a header row.
Click OK.

Why is this a game changer? Excel Tables automatically provide:

Dynamic Range: When you add new rows or columns, the table expands automatically. Any charts or PivotTables based on it will include the new data after a refresh, without you having to manually update the source range.
Easy Formatting: You get clean, banded rows for better readability.
Built-in Filtering: Filter arrows are automatically added to each header.
Structured References: Formulas that reference the table use easy-to-read column names (like [Sales Amount]) instead of cell references (like C2:C500), making your formulas more understandable.

Clean Up Common Data Issues

Before moving on, quickly scan for common errors:

Remove Duplicates: Go to the Data tab and click "Remove Duplicates" to get rid of any identical rows.
Fix Typos and Inconsistencies: Use Find and Replace (Ctrl + H) to standardize entries. For example, replace "New York," "ny," and "N.Y." with a single consistent value like "New York."
Remove Extra Spaces: The TRIM function is perfect for this. Insert a temporary helper column and use the formula =TRIM(A2) to remove leading or trailing spaces from the text in cell A2, then drag it down. Copy and paste the results as values over the original column.

Method 1: Creating Reports with PivotTables

PivotTables are the heart and soul of Excel reporting. They let you summarize thousands of rows of data in seconds, all without writing a single formula. They’re called "pivot" tables because you can easily rotate - or pivot - your data to view it from different angles.

Building Your First PivotTable

With your data cleaned and formatted as an Excel Table, creating a PivotTable is incredibly simple.

Click any cell inside your Excel Table.
Go to the Insert tab and click "PivotTable."
A dialog box will pop up. Since you used an Excel Table, the Table/Range should already be correctly filled in with your table's name.
Choose where you want the PivotTable to be placed. "New Worksheet" is usually the best option to keep things organized. Click OK.

You’ll now see a blank PivotTable on the left and a "PivotTable Fields" pane on the right. This is your command center.

Understanding the PivotTable Fields Pane

The Fields pane is where the magic happens. It has a list of all your columns at the top and four areas at the bottom:

Filters: Fields you place here create a top-level filter for the entire report. Example: Filter the whole report by "Year."
Columns: Fields here will create the columns of your report. Example: Show each "Product Category" as a separate column.
Rows: Fields here will create the rows of your report. Example: List each "Sales Rep" on a new row.
Values: This is for the fields you want to calculate. Typically, this is where you put numbers. Example: Add "Sales Amount" to calculate the total sales.

To build your report, just drag and drop fields from the top list into these four areas. For example, to see total sales by region, you would:

Drag the "Region" field into the Rows area.
Drag the "Sales Amount" field into the Values area.

And just like that, Excel instantly summarizes your sales data and lists the total for each region.

Customize Your PivotTable Report

From here, you can continue to slice and dice the data.

Summarize Values Differently

By default, Excel will Sum numbers. To change this, click the field in the Values area, select "Value Field Settings," and choose a different calculation like Count (to count the number of sales), Average, Max, or Min.

Group Data

PivotTables make it easy to group data. If you have a date field in the Rows area, you can right-click any date and select "Group." From there, you can choose to group by Days, Months, Quarters, and Years. This is fantastic for seeing trends over time.

Add Slicers and Timelines for Interactive Filtering

Instead of using the clunky Filter drop-downs, you can add Slicers and Timelines to make your report interactive. These are essentially fancy, user-friendly buttons for filtering.

Click inside your PivotTable, go to the "PivotTable Analyze" tab, and click "Insert Slicer" or "Insert Timeline" (Timelines only work for date fields). Check the fields you want to filter by, and elegant filter boxes will appear on your sheet. Now anyone can filter the report by clicking buttons, without having to mess with the PivotTable itself.

Method 2: Building Custom Reports with Formulas

PivotTables are fast and flexible, but sometimes you need a report in a very specific layout that a PivotTable can’t create. In those cases, you can build a more rigid, custom report using some of Excel's powerful summarization formulas.

Key Report-Building Formulas

Your main tools for a custom summary report will be SUMIFS, COUNTIFS, and AVERAGEIFS. These let you sum, count, or average values that meet multiple criteria.

The syntax for SUMIFS is:

Let's say you want to find the total sales for "Laptops" in the "North" region.

sum_range: The column with the numbers you want to add up (e.g., your Sales Amount column).
criteria_range1: The column you want to check for the first condition (e.g., your Product Category column).
criteria1: The condition itself (e.g., "Laptops").
criteria_range2: The column for the second condition (e.g., your Region column).
criteria2: The second condition (e.g., "North").

Your final formula might look something like this:

Pulling Data with XLOOKUP

If your reporting requires you to pull in related information from another table, XLOOKUP is your best friend. It’s the modern replacement for VLOOKUP and is more powerful and flexible.

For example, you might have a sales transaction list but need to pull in the "Product Manager" from a separate lookup table. The formula would be:

Where lookup_value is the product ID in your sales list, lookup_array is the corresponding product ID column in your lookup table, and return_array is the product manager column you want to pull from.

Visualizing Your Report with Charts

Numbers are great, but visuals are better for spotting trends and communicating results quickly. A good report nearly always includes some charts.

You can create a chart from a PivotTable by clicking inside it, going to the "PivotTable Analyze" tab, and clicking "PivotChart." This creates a chart that is linked to your PivotTable, any filtering you do with a Slicer will automatically update the chart.

When creating charts, remember to:

Choose the Right Chart Type: Use a line chart for trends over time, a bar chart for comparing categories, and a pie chart only for showing parts of a single whole.
Keep it Simple: Remove unnecessary clutter like gridlines, borders, and distracting colors. Make sure your titles and labels are clear and easy to read.
Highlight What Matters: Use a contrasting color or a data label to draw attention to the most important data point or trend.

Final Thoughts

Learning how to run a report in Excel is a fundamental skill that transforms raw data into a powerful tool for making informed decisions. By starting with clean, structured data and using features like Excel Tables and PivotTables, you can build dynamic, insightful reports that are surprisingly easy to refresh and maintain.

While Excel is fantastic, piecing everything together can become tedious, especially when your data lives in different apps like Google Analytics, Shopify, Facebook Ads, or HubSpot. At Graphed, we simplified this process entirely. Instead of downloading CSVs and building PivotTables manually, you can connect your data sources in seconds and ask questions in plain English - like "Show me a dashboard of a Facebook Ads spend vs Shopify revenue by campaign for the last 30 days." We instantly build a live, real-time dashboard for you, so you can spend your time acting on insights, not just chasing them down.