How to Analyze Data in Excel (2024 Beginner's Guide)

Most of the functionality in Excel you won’t ever use. They’re just there for edge cases you might come across. And that’s the great thing about Excel. It’s such a huge system that it’s very flexible for your data analysis needs. 

You just need to learn the core use cases and look up guides when you need to.

To get started in data analysis, here’s what you need to know:

Beginner Data Analysis Techniques in Excel for 2024

Despite being easy to learn, these are extremely useful data analytics techniques for all levels of data analysts. As a data scientist, I still personally use these day in, day out. 

Pivot Tables

Pivot tables are ultra important for all kinds of data analysis ranging from sales/marketing data to HR data.

But what exactly are pivot tables? In a nutshell, if you have questions about your dataset such as “which products are generating the most sales” and “who are my most loyal customers,” a pivot table will allow you to quickly answer those questions.

Example of a Pivot Table:

Let’s say we have a dataset like this:

With a pivot table, we can quickly summarize that there are 2 reds, 2 blues and 2 greys and 1 green car without having to manually count them.

How to create pivot tables:

1. Select all the data you want analyzed (CTRL + A to select everything)
2. Go to the Insert tab and select Pivot Table.
3. Drag and drop your variables into the rows, columns, and values field. 

Numerical measurements should go into the values field. Stuff like: Profit, revenue, number of sales.

Categorical variables should go into rows and columns. For instance, if you’re trying to figure out which products are generating the most sales, you put “products” into columns. Most times, columns are preferred over rows, although you can use these interchangeably. 

Since pivot tables are such a crucial component for most data analysis tasks, I highly recommend you do some practice with pivot tables.

You can find sample datasets to practice with on Kaggle. Otherwise, I’ve created some practice questions on Polymer Search.

For a more detailed breakdown of what a pivot table is and how to create one, I recommend you check out my guide: pivot tables for beginners. 

Charts/Graphs

Data visualization isn’t just for presenting data, it’s useful for visualizing trends, correlations and patterns in the data. 

For most analyses, the three most important charts are:

• Bar Charts 
• Scatter plots
• Time series

How to quickly create charts in Excel:

Select the data you want to visualize. 

Go to the “insert” tab. This is where most of your data analysis tools will be including charts and pivot tables.

Choose “recommended charts” and pick the one there if it suits your needs. Otherwise, go to “all charts” and find a suitable one there. There should be plenty.

Bar Charts:

Bar charts are the bread and butter of data analysis. You'll be creating hundreds of these!

Bar charts are ideal for studying the relationship between 2 variables: 

1. A categorical variable like “gender, product, payment type” 
2. A numerical measurement like “cost, profits, quantity.”

A bar chart will allow you to see the relationship between these things, for instance: how does “payment type” affect “profits?”

More advanced strategies for bar charts include slicing the bar chart. 

Let’s say we want to study the relationship between a third variable: gender. We want to see whether males or females tend to spend more using ewallet, cash or credit card:

In general, slicing works better if the third variable doesn’t contain too many categories like gender. 

Scatter Plots

Scatter plots allow you to see the correlation and distribution between 2 numerical variables. For instance: customer purchase power vs. household income.

A line of best fit can be drawn to help make predictions about the data. For example: “What would be a customer’s purchasing power if their household income was $60,000?”

How to create a line of best fit in Excel scatter plots:

1. Create your scatterplot.
2. Click on “Add Chart Element” at the top left.
3. Choose Trendline -> Linear

Time Series

Time series allow you to analyze trends over time and forecast the future.

It’s extremely similar to a line chart, but the x-axis is always time related whilst the y-axis is a numerical variable. 

Example of a time series that clearly shows that website traffic dips during the weekends:

Similar to scatterplots, a trend line can be drawn to make business predictions (often for forecasting sales).

To create a trend line in a time series graph:

1. Create your time series
2. Click “Add Chart Element”
3. Choose Trendline -> Linear Forecast

Data Manipulation and Data Cleaning

One of Excel’s biggest strengths is its ability to manipulate data in almost any way you want.

There are over 475 Excel formulas!

No other tool can compare to Excel’s ability to manipulate data however you want, unless you start coding using R or Python which can take months to learn and are unnecessary for most people.. 

Although 475 formulas might seem like a lot to learn, you don’t need to learn all of them. Instead, most Excel professionals just look up the formulas when they need to. Google is your best friend here.

The most basic arithmetic functions you’ll need to learn are:

SUM, COUNT, AVERAGE and MEDIAN.

=SUM(A1:A50) will tally up all values from A1 to A50. COUNT, AVERAGE and MEDIAN are structured the same way. Remembering how to write these will save a lot of time from having to look them up on Google. 

10 Common Excel Formulas Cheat Sheet

The most useful functions in Excel are the CountIfs and SumIfs functions:

1. =SUMIF: Sum all values that meet a criteria e.g. =SUMIF(C2:C20, “>2000”) will sum all values between cells C2:C20 that are over 2000.
2. =CountIF: Counts all values that meet a certain criteria e.g. =COUNTIF(C2:C200, “???es”) will count all 5 letter words that end in “es.”
3. =COUNTIFS: Counts all values that meet multiple criteria.
4. =SUMIFS: Sums all values that meet multiple criteria.
5. =LEFT: Tells you what the left side of a specific cell is. For example image cell C2 contains the word “data.” =LEFT(C2) will result in “d.” You can specific how many characters you want to grab as well: =LEFT(C2,3) will result in “dat.”
6. =RIGHT: Does the same thing as =LEFT, but grabs characters from the right.
7. =VLOOKUP: Looks up what value a cell corresponds to. For instance, you might have item IDs for products you’re selling. VLOOKUP will tell you what ID number the product correlates to.
8. =IF: Checks if a condition is met. Will return TRUE or FALSE.
9. =NETWORKDAYS: Calculates how many work days are between 2 specified dates.
10. =YEAR: Grabs the year from any date. E.g. 7-12-2024 becomes 2024.

Overall, data manipulation and cleaning will differ from task to task. Depending on the dataset, you might have to look up formulas whenever you need them. 

How to Analyze Data in Excel

Here's how to analyze data in Excel:

1. Load up the Analysis Toolpak add-in for Excel: File -> Options (at the bottom left) -> Add-ins -> Go -> Check the Analysis Toolpak and press OK.
2. Go to the “Data” tab and press “Analyze Data”
3. Choose the type of analysis you want to perform

You can perform ANOVAs, t-tests, z-tests, correlations, regression, descriptive statistics and more.

Note: The Analysis Toolpak is designed for people with intermediate statistics knowledge, but is capable of performing complex analyses.

How to Analyze Large Datasets in Excel

Using the analysis toolpak, you can perform factor analysis in Excel.

Factor analysis is a way to break down large datasets by combining multiple variables together. The key to factor analysis is to find covariance. 

Covariance means you’re looking for variables that strongly correlate to each other e.g. household income and willingness to spend might strongly correlate. 

Once you find these, you can group these variables into a single factor “purchasing power.”

How to Analyze Big Data in Excel

If you’ve ever tried to analyze big data in Excel, you’ll notice that by performing basic functions such as Vlookups will cause the program to freeze then crash. 

Excel isn’t meant for handling big data. Each spreadsheet has a limit of one million rows and even if your spreadsheet doesn’t go over this limit, there is a chance the program might crash during the calculations, wasting valuable time.

Luckily there are plugins and Excel spreadsheet templates for this. Power Pivot is a plugin that allows you to process over a million rows in Excel.

It’s set up for big data analysis, as it’s faster and you can connect data from multiple sources.

Sample Excel Data for Analysis

There are several places you can go for sample datasets for analysis. Here are 3 places:

1. Polymer Search
2. Kaggle
3. Reddit r/datasets

Overall

Data analysis might seem confusing, but once you dive into practice and experiment with stuff, you’ll pick it up really fast!

Excel is a fantastic tool for data cleaning and manipulation, but overall, it's easier to analyze your data through a tool like Polymer Search. You can think of Polymer as an "add-on" layer for Excel: You just upload your Excel file onto Polymer or start with our Excel Dashboard Template and it'll transform your spreadsheet into an interactive, user-friendly, database ready for analysis. Using CSV? Build live reports and dashboards with your CSV data and Polymer!

Analyzing data on Polymer is multiple times faster and more powerful than Excel, and best of all, you won't experience data fatigue.

Upload a spreadsheet to Polymer and get started analyzing your data like a data scientist.