Graphed LogoGraphed
Login

How to Create a Monthly Report in Google Sheets

Cody Schneider•

Building a monthly report in Google Sheets shouldn’t involve endless hours of downloading CSVs and copy-pasting data. You can create a clear, useful, and largely automated report that updates itself without all the manual busywork. This article walks you through setting up a professional monthly report, from organizing your data to building dynamic formulas and visuals.

Start with a Strong Foundation: Structure Your Google Sheet

The biggest mistake people make is mixing their raw data with their finished report. This gets messy fast and makes formulas complicated. A smarter approach is to use two separate tabs from the beginning.

Create two tabs at the bottom of your Google Sheet:

  • Raw Data: This is where all your source data will live. Whether you're pasting it from a CSV, syncing it from another app, or using an import formula, all the messy, detailed records go here. Your report tab will pull summaries from this sheet.

  • Monthly Report: This is your presentable dashboard. It will contain clean tables, key metrics, and charts. This tab should be easy for a stakeholder to read and understand at a glance, with no raw data cluttering the view.

Best Practices for Your Raw Data Tab

To make the formulas later on much easier, keep your raw data clean and consistent. Follow these simple rules:

  • Use Headers: Have one header row at the top (and only one). Names should be clear and simple (e.g., “Date,” “Campaign,” “Revenue,” “Leads”).

  • Consistent Columns: Make sure each column contains the same type of data all the way down. Don’t mix text and numbers in the same column.

  • Proper Date Formatting: Ensure your date column is formatted as a date (go to Format > Number > Date). This is essential for building monthly summaries.

Getting Your Data into Google Sheets

Now that your tabs are set up, you need to get your source information into the "Raw Data" tab. You have a few options, ranging from simple to more automated.

Option 1: The Classic Copy-Paste or CSV Import

This is the most straightforward method. You can manually copy data from another source or import a CSV file directly.

To import a CSV:

  1. Click on your "Raw Data" tab.

  2. Go to File > Import.

  3. Click on the "Upload" tab and select your CSV file.

  4. Choose an Import location, like "Replace current sheet" or "Append to current sheet."

Pro Tip: Using "Append to current sheet" lets you add new data each week or month without deleting the old records, which is great for historical tracking.

Option 2: Pull Data with IMPORTRANGE

If your source data already lives in another Google Sheet, IMPORTRANGE is your best friend. This formula pulls data from one sheet into another, and it updates automatically when the source sheet changes.

Here’s the formula structure:

=IMPORTRANGE("spreadsheet_url", "tab_name!range")

For example, to import columns A through F from a "Sales Data" tab in another spreadsheet, you'd use:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aBcDeFg_sample_URL_here/edit", "Sales Data!A:F")

The first time you use it, you'll need to click on the cell and hit "Allow access" to connect the two sheets. This creates a simple, semi-automated pipeline without needing add-ons.

Formulas Are Your Friends: How to Summarize Data

With data flowing into your "Raw Data" tab, you can now start summarizing it on your "Monthly Report" tab using dynamic formulas. This eliminates the need for manual calculations or pivot tables.

For our examples, let's assume your "Raw Data" tab has these columns:

  • Column A: Date

  • Column B: Campaign Name

  • Column C: Revenue

  • Column D: Leads

Make Your Report Dynamic with Date Cells

To avoid updating formulas every month, create two "helper cells" on your "Monthly Report" tab to define the date range. This makes your entire report updatable with a single change.

  • In cell B1, enter the first day of the month you want to report on (e.g., 10/1/2024).

  • In cell B2, use the EOMONTH formula to automatically find the last day of that month:

=EOMONTH(B1, 0)

Now, all your other formulas can reference these two cells, so when you change the date in B1 to "11/1/2024," your entire report will update for November.

Calculate Your Key Metrics with SUMIFS and COUNTIFS

The ...IFS family of formulas lets you add or count values that meet specific criteria, like being within your selected month.

1. Calculating Total Revenue for the Month

To add up all revenue from the "Raw Data" sheet that falls between your start and end dates:

=SUMIFS('Raw Data'!C:C, 'Raw Data'!A:A, ">= "&B1, 'Raw Data'!A:A, "<= "&B2)

Let's break that down:

  • 'Raw Data'!C:C: The range you want to sum (the Revenue column).

  • 'Raw Data'!A:A: The first criteria range (the Date column).

  • "<= "&B2: The second criteria - look for dates less than or equal to your end date.

  • ">= "&B1: The first criteria - look for dates greater than or equal to your start date.

2. Counting Total Leads for the Month

The logic is exactly the same, but we use COUNTIFS instead of SUMIFS since we're counting rows, not summing a value.

=COUNTIFS('Raw Data'!D:D, ">0", 'Raw Data'!A:A, ">= "&B1, 'Raw Data'!A:A, "<= "&B2)

Here, we added one small check: 'Raw Data'!D:D, ">0" ensures we only count rows where a lead was actually generated.

3. Calculating Metric by Campaign

You can also use SUMIFS to break down performance by specific criteria, like campaign name. List your campaign names in Column A of your report, and then you can use this formula to get the revenue for each one individually.

Assuming "Search Campaign 1" is written in cell A5 on your report tab:

=SUMIFS('Raw Data'!C:C, 'Raw Data'!A:A, ">= "&$B$1, 'Raw Data'!A:A, "<= "&$B$2, 'Raw Data'!B:B, A5)

Notice the dollar signs ($B$1, $B$2) which "lock" the date cells, allowing you to drag this formula down next to your list of campaigns without the date references breaking.

Visualize Your Data with Charts and Scorecards

Numbers in a table are good, but charts tell a story. Google Sheets makes it simple to visualize your summarized data.

Create Trendlines with a Line or Bar Chart

Once you’ve built a small summary table (e.g., revenue by week or month), you can easily turn it into a chart.

  1. Select the data in your summary table, including the headers.

  2. Go to Insert > Chart.

  3. Google Sheets will suggest a chart type. The Chart editor on the right allows you to switch between line charts (for time-based trends), bar charts (for comparisons), and pie charts (for percentage breakdowns).

  4. Customize the titles, colors, and axes under the "Customize" tab in the editor.

Highlight Top-Level KPIs with Scorecard Charts

For big-picture numbers like Total Revenue or Total Leads, a Scorecard chart is fantastic. It displays a single, prominent metric.

  1. Click on an empty cell where you want the scorecard to appear.

  2. Go to Insert > Chart.

  3. In the chart editor, under Chart Type, scroll down and select Scorecard chart.

  4. For the "Data range," select the single cell containing the metric you calculated (e.g., your total monthly revenue formula).

  5. You can even set a "comparison value," like the previous month's total, to show period-over-period change.

Sharing Your Report Effectively

A report is useless if nobody sees it. Once your report is built, sharing the live document is much better than emailing a static PDF or screenshot. When you share the live Google Sheet, stakeholders always see the most current version.

Click the big blue "Share" button at the top-right and enter the email addresses of your team members or stakeholders. Set their permissions to "Viewer" so they can see the report but not accidentally edit your formulas.

By giving them viewer access, they can always pull up the most current numbers you've processed without you having to re-export and re-send the report every time.

Final Thoughts

Creating a monthly report in Google Sheets using a separate data tab and simple formulas like SUMIFS elevates your reporting from manual grunt work to an efficient, streamlined process. By tying your calculations to dynamic date cells and adding clean charts, you get a repeatable report that gives clear insights in a fraction of the time.

As your business grows, you'll find the biggest bottleneck becomes getting data from multiple platforms into that "Raw Data" tab. Instead of juggling CSVs from Shopify, Google Analytics, and Facebook Ads, we built Graphed to automate that entire first step. It connects directly to your data sources, then lets you build real-time dashboards and reports simply by describing what you want in plain English, completely eliminating the need for formulas.