how to analyze data in Excel without being a data scientist

most Excel tutorials assume you already know what you are doing. this one does not.

if you have a spreadsheet full of data and you want to understand what it means — find patterns, answer questions, make a chart — this guide walks you through the whole process from a blank file to actual insights.

no statistics degree required. no macros. no VBA. just the features that cover 90% of real business analysis work.

what “analyzing data” actually means in Excel

data analysis in Excel means answering questions. specifically, questions like:

• which product sold the most last quarter?
• what is my average customer value by region?
• are sales going up or down month over month?
• which marketing channel drove the most conversions?

the process is always the same: clean the data, summarize it, visualize it, and draw a conclusion. Excel has a tool for each step.

step 1: clean your data before you analyze anything

this is the step that separates people who get correct results from people who get garbage. bad data produces misleading analysis, and Excel will not warn you.

remove duplicate rows

in the Data tab, click “Remove Duplicates.” select the columns that define a unique record. click OK. Excel removes exact duplicate rows and tells you how many it removed.

do this before any other analysis.

convert your data to a Table

click anywhere inside your data. press Ctrl+T (or Cmd+T on Mac). confirm the range. click OK.

this does four important things: it locks in your column headers, it auto-expands when you add new rows, it makes formulas easier to read, and it makes pivot tables work properly when your data changes.

every analysis task in this guide assumes your data is in a Table.

freeze the header row

if your data is not in a Table and you are scrolling down, go to View → Freeze Panes → Freeze Top Row. this keeps the column headers visible while you scroll.

check for blank cells

press Ctrl+End to find the last cell in your dataset. if the last row is far below where you expect your data to end, you have blank rows mixed in. use Ctrl+G → Special → Blanks to select all blank cells, then delete those rows.

standardize text

if text entries are inconsistent (“NYC”, “New York”, “new york city” for the same city), analysis will treat them as different categories. use Find & Replace (Ctrl+H) to standardize, or use the TRIM() function to remove extra spaces and PROPER() to standardize capitalization.

step 2: pivot tables — turn raw rows into instant summaries

a pivot table takes a flat spreadsheet and lets you summarize it any way you want, in seconds, without writing a formula.

how to create a pivot table

1. click anywhere inside your Table
2. go to Insert → PivotTable
3. confirm the data range and choose a new sheet
4. click OK

you will see an empty pivot table and a field list on the right.

building your first summary

the field list has four areas: Rows, Columns, Values, and Filters.

• drag the field you want to group by (e.g., “Product”) to Rows
• drag the numeric field you want to summarize (e.g., “Revenue”) to Values
• Excel automatically sums the revenue for each product

you now have a revenue-by-product summary in under 60 seconds.

common pivot table moves

changing the calculation: click the Values dropdown → “Value Field Settings” → choose Sum, Count, Average, Min, Max, or % of total.

grouping dates: if your data has a date column, drag it to Rows. Excel offers to group by year, quarter, and month automatically.

filtering: drag any field to Filters and use the dropdown at the top of the pivot table to filter the entire analysis.

sorting: click the dropdown arrow next to any row label and sort by value.

step 3: formulas every analyst uses

you do not need to memorize 200 Excel formulas. these five cover 80% of business analysis tasks.

SUMIF — total values that match a condition

=SUMIF(range, criteria, sum_range)

example: total revenue from a specific product.
=SUMIF(A:A, "Widget A", C:C) — adds up all values in column C where column A says “Widget A.”

COUNTIF — count rows that match a condition

=COUNTIF(range, criteria)

example: how many orders came from “New York.”
=COUNTIF(B:B, "New York")

AVERAGEIF — average values that match a condition

=AVERAGEIF(range, criteria, average_range)

example: average order value for a specific customer segment.
=AVERAGEIF(D:D, "Enterprise", E:E)

VLOOKUP — look up a value from another table

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

example: find the product category for a product ID.
=VLOOKUP(A2, ProductTable, 2, FALSE) — finds A2 in the first column of ProductTable and returns the value from column 2.

modern Excel: use XLOOKUP instead. it is simpler and more flexible.
=XLOOKUP(A2, ProductTable[ID], ProductTable[Category])

IF — make a decision based on a condition

=IF(logical_test, value_if_true, value_if_false)

example: flag orders above $1,000.
=IF(E2>1000, "High Value", "Standard")

step 4: charts that tell the story

pivot tables give you numbers. charts make the numbers understood by everyone else.

how to create a chart from a pivot table

1. click anywhere inside your pivot table
2. go to Insert → PivotChart
3. choose your chart type
4. click OK

the chart stays linked to the pivot table — filter the pivot and the chart updates automatically.

which chart type for which analysis

what you are showing	chart type
trend over time	Line chart
comparison between categories	Bar or Column chart
parts of a whole	Pie or Donut chart (use sparingly — max 5 segments)
distribution of values	Histogram
relationship between two variables	Scatter plot

avoid 3D charts. they look impressive but distort proportions and make values harder to read.

cleaning up a chart

delete the chart title placeholder and write a specific one: “Revenue by Product Q1 2026” beats “Chart 1.”

right-click the chart → Format Chart Area → remove the border.

delete the gridlines if they are cluttering the view. click once to select, press Delete.

add data labels if the exact values matter: right-click the bars/lines → Add Data Labels.

step 5: basic formulas for deeper analysis

once you have the core pivot analysis working, these formulas handle the analysis questions pivot tables cannot.

PERCENTRANK — where does a value sit relative to the group

=PERCENTRANK(array, x)

useful for ranking customers, products, or salespeople relative to the full set without sorting the entire table.

CORREL — does a relationship exist between two columns

=CORREL(array1, array2)

returns a value between -1 and 1. above 0.7 or below -0.7 suggests a meaningful relationship. use this to test whether two metrics move together — e.g., whether marketing spend and revenue are correlated.

FORECAST.LINEAR — project a future value from historical trend

=FORECAST.LINEAR(x, known_y's, known_x's)

basic linear projection. not sophisticated statistics, but useful for a quick revenue forecast from historical monthly data.

what to learn next

you have cleaned data, built a pivot table, applied key formulas, and created a chart. that is the full beginner workflow.

next steps depend on where your analysis hits a wall:

• more data than Excel handles comfortably: move to Google Sheets for collaboration or to Python pandas for larger datasets — see Python pandas tutorial for beginners
• repetitive cleaning tasks: learn Power Query (Data → Get & Transform Data) to automate data preparation
• data visualization beyond charts: see best data visualization tools for solopreneurs 2026
• collaborative analysis: see Excel vs Google Sheets for data analysis

the full analyst toolkit — SQL for database queries, Python for large datasets, Power BI for dashboards — builds on this foundation. but for most business analysis questions, what you have learned here is sufficient.