How to Prepare a Graph in Excel

Creating a graph in Excel can turn a confusing spreadsheet of numbers into a clear, compelling story. The real secret, however, isn't just knowing which button to click, it's about preparing your data correctly from the start. This guide will walk you through structuring your data, choosing the perfect chart for your message, and customizing it to look professional and easy to understand.

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

The Foundation: How to Structure Your Data for Graphing

Before you even think about charts, your data needs to be in a simple, organized format that Excel can easily understand. An effective graph is built on a foundation of clean data. If your data is messy - full of merged cells, empty rows, and inconsistent formatting - Excel will struggle to interpret it, and your graph will be impossible to create or inaccurate.

Think of it like building with LEGOs. You need the individual bricks to be clean and separate before you can construct anything meaningful. The same principle applies to your data.

The Golden Rule of Data Layout

For clean, graph-ready data, follow this one simple rule: organize your data in columns and rows.

• Columns represent categories or variables. Each column should have a unique, descriptive header in the first row (e.g., "Date," "Sales Rep," "Product Category," "Revenue").
• Rows represent individual records or entries. Each row should contain the data points related to a single item.

Here’s an example of a bad data layout. It’s formatted for a human to read at a glance, but it’s a nightmare for Excel's charting tools:

-- 2024 REGIONAL SALES -- Q1 North Region | Sarah | $15,000 | John | $12,500 South Region ---> Michael ---> $18,000 Q2 North Region | Sarah | $17,000 (and more inconsistent entries)

Notice the merged cells, empty spaces, and mixed headers. Trying to create a graph from this will result in errors. Instead, structure it like this:

Example of a Graph-Ready Data Table

Imagine you're tracking monthly sales across different social media platforms. Your clean data should look something like this:

This structure is perfect because:

• Row 1 contains clear headers. Excel knows "Month," "Platform," etc., are the labels for each category.
• Each column has one type of data. The "Ad Spend" column contains only currency, and "Conversions" contains only numbers.
• There are no empty rows or columns. It's a contiguous block of data.
• There are no merged cells. Each piece of information sits neatly in its own cell.

Once your data is structured this way, you've done 80% of the work. Now you can focus on telling your story visually.

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

Which Graph is Right? Choosing the Best Chart for Your Data

The type of chart you choose depends entirely on the story you're trying to tell. Are you comparing values? Showing a trend over time? Breaking down a total? Using the wrong chart can confuse your audience and obscure your message. Here are the most common chart types and when to use them.

Column and Bar Charts: For Comparing Categories

Column and bar charts are the workhorses of data visualization. They are excellent for comparing distinct items against each other.

• Column Chart (Vertical Bars): Use a column chart when you are comparing a few categories (usually less than 8-10). It's great for showing things like revenue by product, website traffic by source, or leads per sales rep. The height of each column makes it easy to see which category is bigger or smaller.
• Bar Chart (Horizontal Bars): A bar chart is essentially a column chart turned on its side. It's the better choice when you have long category names that would be cramped and hard to read vertically, or when you have a large number of categories to compare.

Use them to answer questions like: "Which marketing channel drove the most conversions last month?"

Line Charts: For Showing Trends Over Time

If your data involves a time series - like days, months, quarters, or years - a line chart is your best friend. It connects data points with a line, making it incredibly effective at showing trends, patterns, and fluctuations over a period.

You can use a line chart to track sales performance over a year, see the growth of website sessions month-over-month, or monitor stock prices. Adding multiple lines can help you compare trends between different categories (e.g., comparing the year-over-year revenue growth of two different products).

Use them to answer questions like: "How did our website traffic change throughout Q3?"

Pie Charts: For Showing Parts of a Whole

Pie charts are used to show composition - how individual parts make up a total. Each slice of the pie represents a percentage of the whole (which always adds up to 100%).

However, use them with caution. Pie charts become difficult to read when you have too many slices. As a rule of thumb, if you have more than 5 or 6 categories, a bar chart is a much better option. They work best when you want to highlight one or two dominant categories in relation to the whole.

Use them to answer questions like: "What percentage of our total Q1 revenue came from each region?"

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

Scatter Plots: For Showing Relationships and Correlations

A scatter plot (or XY chart) is used to show the relationship between two different numerical variables. Each dot on the chart represents an intersection of two numbers. It’s a powerful way to spot correlations and identify patterns in your data.

For example, you could plot advertising spend (on the X-axis) versus sales revenue (on the Y-axis) to see if higher ad spend correlates with higher sales. If the dots trend upwards from left to right, it suggests a positive correlation.

Use them to answer questions like: "Is there a relationship between a customer's age and their average order value?"

Step-by-Step: How to Create Your Graph in Excel

Once your data is clean and you've decided on the right chart type, creating it is straightforward. Let's create a simple column chart using our earlier marketing data example.

Goal: To create a column chart showing the total revenue generated by each platform for January.

1. Select Your Data: Click and drag your mouse to highlight the data you want to include in the graph. In our example, you would select the cells containing "Platform" and "Revenue" for the January entries. Don't just click the whole sheet - be specific about what you want to chart. It would look like this:

1. Navigate to the Insert Tab: At the top of the Excel window, click on the Insert tab in the ribbon.
2. Find the Charts Group: In the middle of the Insert ribbon, you’ll see a section called Charts. Excel even provides "Recommended Charts" which can be a good starting point if you're unsure.
3. Choose Your Chart Type: Click the icon for the chart you want. For our example, we'll click the icon for a column chart (it looks like a small set of vertical bars). A dropdown menu will appear showing different styles (2-D, 3-D, etc.). Click on the first option, "2-D Clustered Column."

Excel will instantly generate the chart and place it on your worksheet. It's that simple! But the default chart is rarely perfect. Now it's time to refine it.

Make it Pop: Customizing Your Graph for Clarity and Impact

A basic chart gets the job done, but a well-customized chart makes your data easy to digest and looks far more professional. When you click on your new chart, two new tabs will appear in the ribbon: Chart Design and Format. These are your control centers for customization.

You can also use the small "plus" (+) button that appears to the right of your selected chart to quickly add or remove elements.

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

Essential Customizations for a Professional Look:

1. Add a Clear Chart Title

Don't stick with the generic "Chart Title." Double-click on it and give your chart a descriptive name that tells the viewer exactly what they are looking at. Instead of "Revenue," be more specific, like "January Social Media Revenue by Platform."

2. Label Your Axes

What do the horizontal and vertical lines represent? Without labels, your audience is left guessing. Click the "plus" (+) icon next to the chart, and check the box for Axis Titles. Then, click on the new placeholder text to label your X-axis (e.g., "Platform") and your Y-axis (e.g., "Revenue ($)").

3. Include Data Labels

Sometimes it's helpful to see the exact value for each bar or data point without having to trace it back to the axis. Click the "plus" (+) icon and check Data Labels. This will place the numerical value directly on or above each column, making your chart instantly scannable.

4. Adjust the Legend

If you're only plotting one set of data (like our revenue example), you don't need a legend. You can click on it and press delete to free up space. However, if your chart compares multiple series (e.g., revenue for both January and February on the same chart), a legend is essential to distinguish them.

5. Refine Colors and Fonts

Under the Chart Design tab, you can quickly change the color scheme. Instead of using a random rainbow of colors, try to use brand colors or a simple, clean palette. Use color strategically - you can make one bar a different, brighter color to highlight a key data point, like the top-performing platform.

By investing just a few minutes in these simple customizations, you can elevate a bland, default chart into a powerful and professional communication tool.

Final Thoughts

Mastering chart preparation in Excel all comes down to two key skills: organizing your data in a clean, logical structure and choosing the right chart type to tell your story. With well-structured data and a few strategic customizations, you can transform complex information into clear, actionable insights every single time.

While Excel is powerful for single-source analysis, this process of downloading CSVs, cleaning data, and manually building reports can become incredibly time-consuming, especially when your data lives in different places like Shopify, Google Analytics, and Facebook Ads. We built Graphed to solve this very problem. We connect directly to all your marketing and sales tools, so instead of wrangling spreadsheets, you can just ask questions in plain English like, "Show me a chart of Shopify revenue vs. Facebook ad spend by month," and instantly get a live, automated dashboard that you never have to manually update again.