Graphed LogoGraphed
Login

How to Create a Monthly Report in Excel

Cody Schneider•

Building a monthly report in Excel can feel like running on a treadmill. Every month, you’re back at it, wrangling CSVs and wrestling with pivot tables just to get a clear picture of what happened. This guide will show you how to build a structured, reusable monthly report in Excel, moving you from manual data entry to creating clear, actionable insights.

Start with a Plan: What Story Are You Telling?

Before you open Excel, you need a plan. A great report tells a story about your performance, and like any good story, it needs a plot and an audience. Rushing this step is why most reports end up as ignored attachments in someone’s inbox.

Ask These Three Questions First

To avoid creating a report no one reads, start by answering these basic questions:

  • 1. What is the goal of this report? Are you trying to justify marketing spend, track sales team performance, or provide a high-level overview for leadership? Your goal determines what you measure. For example, a marketing report might focus on leads and cost per acquisition, while a sales report will focus on deal velocity and revenue.

  • 2. Who is the audience? Your stakeholders define the level of detail. An executive team wants a one-page summary with top-line KPIs, while a marketing manager needs a granular breakdown of campaign performance. Always build the report for them, not for you.

  • 3. Which Key Performance Indicators (KPIs) matter most? Based on your goal and audience, define the 3-5 metrics that truly measure success. Don't drown your reader in data. Focus on metrics that are directly tied to business outcomes.

Common Monthly Report KPIs

Here are a few examples to get you started:

  • Marketing: Website Sessions, Leads Generated, Conversion Rate, CPC (Cost Per Click), CPA (Cost Per Acquisition).

  • Sales: New Deals Created, Win Rate, Average Deal Size, Sales Cycle Length, Revenue.

  • E-commerce: Total Sales, Average Order Value (AOV), Customer Acquisition Cost (CAC), Website Conversion Rate.

Gathering and Organizing Your Data

With a clear plan, you can now start gathering your data. This is often the most time-consuming part, but good organization here will save you hours down the road.

Identify and Export Your Data

Your monthly performance data is likely scattered across multiple platforms. List out where you need to get your numbers from:

  • Website Data: Google Analytics (or a similar tool) for website traffic, user behavior, and goal completions.

  • Ad Performance: Facebook Ads Manager, Google Ads, LinkedIn Ads, etc., for spend, clicks, and conversions.

  • Sales Data: Your CRM (like Salesforce or HubSpot) for deals, pipeline, and attributed revenue.

  • E-commerce Data: Shopify or WooCommerce for orders, revenue, and product performance.

For your first report, you’ll need to export data for at least two months (the current month you're reporting on and the previous month for comparison). Export these files as CSVs, which are easily imported into Excel.

Set Up Your Excel File for Success

A structured Excel file is a lifesaver. Instead of dumping everything into one sheet, create separate tabs for a clean, organized workflow. This simple setup prevents confusion and makes your report easier to update each month.

  1. Raw Data Tab: This sheet is exclusively for your raw, unedited data. Create a tab for each data source (e.g., "GA Data," "Facebook Ads Data") and paste the contents of your CSV files here. Never do calculations directly in this tab. It’s your single source of truth that you can always refer back to.

  2. Calculations Tab: This is your workshop. Here, you'll use formulas to pull information from your raw data tabs, perform calculations (like totals, averages, and Month-over-Month changes), and structure it in a way that's easy to visualize.

  3. Dashboard Tab: This is the final report - the clean, presentable dashboard your audience will see. It will contain only charts, formatted KPIs, and key insights. It should not contain any raw data or complex formulas.

Pro-Tip: Once you paste your data into the "Raw Data" tab, turn it into an Excel Table (select the data and press Ctrl + T). This makes formulas more dynamic and easier to read.

Building Your Calculation Engine

The "Calculations" tab is where you’ll transform messy data into clean, report-ready metrics. Here, you'll summarize your raw information into small tables that will power the charts on your dashboard.

Summarizing Data with Key Formulas

Let's say your "GA Data" tab has daily website session data. In your "Calculations" tab, you’ll want to create a small table that aggregates this.

Use formulas like SUMIF, COUNTIF, and VLOOKUP to pull and aggregate your data. For example, to get total website sessions for the month from your "GA Data" table, you could use:

To get sales revenue tied to a specific marketing campaign from your CRM data, you might use:

Calculating Month-over-Month (MoM) Change

Showing MoM change provides critical context. It tells your audience if performance is improving, declining, or staying flat. The formula is simple:

In a cell next to your current month's "Total Sessions," add a column for "MoM Change" and use the formula. Format the cell as a Percentage to make it easy to read.

Designing Your Excel Dashboard

Now for the fun part: making your data visual. On your "Dashboard" tab, you'll link to the summary tables in your "Calculations" tab to create charts, KPI cards, and text summaries.

Choose the Right Chart for the Job

The goal is clarity, not complexity. Pick visuals that tell the story best:

  • Line Charts: Perfect for showing trends over time (e.g., website traffic over the last 12 months).

  • Bar Charts: Ideal for comparing values across categories (e.g., sales per channel).

  • Pie Charts: Use sparingly for showing parts of a whole where you have fewer than five categories (e.g., lead source breakdown). Bar charts are often a better choice.

How to Create a Chart:

  1. In your "Calculations" tab, select the summary table you created.

  2. Click the Insert tab in the Excel ribbon and choose your desired chart type from the Charts group.

  3. Cut (Ctrl + X) the new chart and paste (Ctrl + V) it onto your "Dashboard" tab.

  4. Resize and position it as needed. Customize titles, labels, and colors to make it clear and professional.

Highlight Your Top KPIs

Your most important numbers should stand out. Instead of making people hunt for them in a table, create KPI "cards" at the top of your dashboard.

You can create these by simply typing your KPIs (e.g., Total Revenue) into formatted cells, then in the cell next to it, referencing the corresponding value from your “Calculations” tab. For example, in cell B2 you could type =, then navigate to your “Calculations” tab and click the cell with your total revenue figure. Now, your KPI card updates automatically.

Add Context: The "Why" Behind the Numbers

A report with just charts is only half-finished. Data shows you the "what" (sessions went down by 10%), but your job is to explain the "why."

Write a Simple Summary and Next Steps

At the top or bottom of your dashboard, include two simple text boxes:

  1. Executive Summary: Write 2–3 bullet points summarizing the key takeaways. What was the biggest win of the month? What was the biggest challenge? Be direct and to the point.

  2. Recommendations/Next Steps: Based on the data, what do you suggest the team do next month? This turns your report from a passive document into an action plan. For example, "Site traffic from the blog campaign was down due to a lower publishing cadence. Recommendation: Increase blog output to three posts per week next month."

This analysis is what makes you a valuable partner, not just a data entry machine. It’s your opportunity to provide strategic direction backed by data.

Final Thoughts

Creating a monthly report in Excel is a process of disciplined data gathering, thoughtful analysis, and clear communication. By structuring your file correctly and focusing on actionable insights, you can build a reporting system that provides real value month after month, rather than just being a tedious chore.

Of course, the manual process of downloading CSVs and updating your Excel sheets every month still takes a lot of time away from the actual analysis. We built Graphed to completely remove that initial drudgery. With our tool, you connect your data sources - like Google Analytics, Shopify, and your ad platforms - just once. From there, you can build real-time, auto-updating dashboards simply by describing what you want in plain English, getting back your hours to focus on the insights that really move the needle for your business.