How to Do Data Analysis in Excel

Excel is much more than a tool for making simple lists - it’s a surprisingly powerful platform for analyzing data. If you’re trying to understand your business performance, there's a good chance you can find the answers you need without leaving your spreadsheet. This guide will walk you through the essential steps for conducting data analysis in Excel, from cleaning up your raw data to visualizing your findings.

Graphed

Still Building Reports Manually?

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

Watch the Demo

First Things First: Prepare Your Data for Analysis

Before you can find any meaningful insights, you need a clean, well-structured dataset. This is the most important step in the entire process because, as the old saying goes, "garbage in, garbage out." Taking a few minutes to prepare your data will save you hours of headaches later on.

1. Clean Your Data

Raw data is rarely perfect. It often comes with typos, inconsistencies, duplicates, and formatting errors. Here’s a quick checklist to clean it up:

• Remove Duplicates: If you have duplicate rows, your calculations will be wrong. Select your dataset, go to the Data tab, and click Remove Duplicates. Excel will find and delete any identical rows.
• Fix Inconsistencies: Use Find and Replace (Ctrl+H or Cmd+H) to standardize text. For example, if your 'State' column has "CA," "Calif.," and "California," you should replace them all with a single standard, like "California," so you can group your data accurately.
• Trim Extra Spaces: Sometimes data exported from other systems includes leading or trailing spaces that can mess up formulas and filters. The TRIM function is your best friend here. If your text is in cell A2, use the formula =TRIM(A2) in a new column to remove those pesky spaces.

2. Structure Your Data as an Excel Table

Once your data is clean, the secret to making analysis easy is to format it as an Excel Table. This isn't just about adding borders, it's a feature that supercharges your data.

To do this, click anywhere inside your data range and press Ctrl+T (or Cmd+T on a Mac). A dialog box will confirm your data range - just click "OK."

Why use a Table? A few reasons:

• Automatic Expansion: When you add new rows or columns, the table expands automatically, so your formulas and charts connected to it will update too.
• Easy Formatting: You get alternating stripes on your rows, which makes the data much easier to read.
• Simplified Formulas: Formulas that reference table columns are written with clear names (like [Revenue] instead of C2:C5000), which makes them easier to understand.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Core Techniques for Analyzing Data in Excel

With your data clean and structured, you can start digging for insights. These foundational techniques will handle the vast majority of your analysis needs.

1. Sorting and Filtering

These are the simplest yet most effective ways to start exploring your data. When you create an Excel Table, filter arrows are automatically added to your column headers.

• Sorting: Lets you arrange your data to spot patterns. Curious about your best-performing products? Sort your 'Revenue' column from "Largest to Smallest." Want to see your sales chronologically? Sort your 'Date' column from "Oldest to Newest."
• Filtering: Lets you temporarily hide data you don't want to see so you can focus on a specific subset. Click the filter arrow on a column header and uncheck everything except what you want to analyze. For example, you can filter your report to show only sales data for "Q4" or for a single sales representative.

2. Essential Data Analysis Formulas

Formulas allow you to perform calculations and answer specific questions. While there are hundreds of functions in Excel, a handful will do most of the heavy lifting.

Here are some of the most useful ones:

• For basic calculations:
• For conditional analysis:
• For merging data:

Go Deeper with PivotTables

If you need to analyze your data from multiple angles without writing complex formulas, PivotTables are the solution. They are Excel's most powerful analysis tool and allow you to summarize huge datasets interactively with simple drag-and-drop actions.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is a PivotTable?

A PivotTable is an interactive report that lets you quickly summarize, group, count, and average your data. You can "pivot" your data by dragging fields around to explore different questions. For example, within seconds you can switch from viewing sales by region to viewing sales by product category over time.

How to Create Your First PivotTable

Creating a PivotTable is surprisingly straightforward:

1. Click any cell inside your Excel Table.
2. Go to the Insert tab and click PivotTable. Excel will auto-select your table and prompt you to place the PivotTable in a new worksheet. Click OK.
3. A blank PivotTable and a PivotTable Fields pane will appear on the right side of your screen. This pane lists all your column headers (or "fields").
4. Drag and drop these fields into the four areas at the bottom of the pane:

For example, to see your total revenue by product category, simply drag 'Product Category' to the Rows area and 'Revenue' to the Values area. Just like that, you have a summary report!

Tell a Story with Charts and Graphs

Numbers and tables are great for analysis, but visualizations make your findings immediately clear to anyone who looks at them. A well-designed chart can tell a story that a spreadsheet alone never could.

Choosing the Right Chart

The key is to pick a chart type that best represents your data and the story you're trying to tell.

• Line Chart: Perfect for showing a trend over time. Use it to track things like website traffic per month or revenue per quarter.
• Column/Bar Chart: Ideal for comparing values across different categories. Use it to compare sales performance across different regions or marketing campaign results.
• Pie Chart: Use it to show parts of a whole (proportions). It works best with a small number of categories (less than five). For example, it's helpful for showing the percentage of total sales from each business line.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Creating an Excel Dashboard

Once you've created a few key charts and PivotTables, you can arrange them on a single sheet to create a simple dashboard. The goal of a dashboard is to provide a one-glance view of your most important key performance indicators (KPIs).

Place your main charts and summary tables on one sheet. You can also add "Slicers" from the PivotTable options, which are interactive buttons that allow you or your teammates to filter all the dashboard's data simultaneously, making it a dynamic and user-friendly reporting tool.

Final Thoughts

Excel provides an incredibly capable set of tools for anyone looking to make sense of their data. By learning how to properly clean and structure your data, leverage essential formulas, build PivotTables, and create compelling charts, you can transform a static spreadsheet into a dynamic hub for business insights.

However, the process of manually downloading CSVs from different platforms like Shopify, Google Analytics, and Facebook Ads, then cleaning and combining them in Excel every week, can quickly become repetitive and inefficient. That’s where we’ve found a new generation of tools to be a huge time-saver. By connecting our different data sources directly to Graphed, we can skip the spreadsheet busywork altogether. We just ask questions in plain English - like "create a chart comparing Facebook Ads spend vs. revenue by campaign" - and it instantly generates real-time dashboards that stay up-to-date automatically.