Exploratory data analysis in Google Sheets: a step-by-step guide
Exploratory data analysis is the first look at a dataset: what is in it, how the values are distributed, which groups differ and what looks wrong. This guide walks through it in Google Sheets in six steps: check and clean the data, calculate descriptive statistics, summarize by group, filter, chart, and look for outliers and relationships, with the formula for each step.
Exploratory data analysis, EDA, is the first look at a dataset before any conclusion is drawn from it: what the columns hold, how the values are distributed, how the groups differ and what looks wrong. In Google Sheets it comes down to six steps.
| Step | Question | Tools |
|---|---|---|
| 1. Check and clean | Is the data complete and consistent? | Column stats, Data cleanup, COUNTBLANK |
| 2. Describe | What is typical, and how wide is the spread? | AVERAGE, MEDIAN, STDEV, QUARTILE |
| 3. Group | How do the categories differ? | SUMIF, COUNTIF, pivot tables, QUERY |
| 4. Filter | What does one segment look like? | Filters, SUBTOTAL |
| 5. Chart | What shape does the data have? | Column, line, histogram, scatter charts |
| 6. Question | What stands out, and what moves together? | Conditional formatting, IQR, CORREL |
The numbers in steps 1 to 3 come from a test table of ten sales with a date, a sales rep and an amount.
Step 1. check and clean the data
Step 1. check and clean the data
A summary of dirty data is a wrong summary. Before calculating anything:
- Look at each column. Data > Column stats opens a side panel with statistics for the columns of the sheet, one column at a time.
- Count what is missing.
=COUNTBLANK(B3:E12)returns the number of empty cells in a range. - Count the categories.
=COUNTUNIQUE(C3:C12)returned 3 for our three sales reps. A larger number than expected usually means variants of one name: “Sarah”, “sarah “, “Sara”. - Remove duplicates and stray spaces. Data > Data cleanup has Remove duplicates and Trim whitespace. See remove duplicates and TRIM and CLEAN.
- Check the types. Numbers and dates stored as text are left-aligned and are ignored by the statistical functions. Compare
=COUNT(D3:D12), which counts numbers only, with=COUNTA(D3:D12), which counts everything; they should be equal for a numeric column. See COUNT and COUNTA.
Step 2. descriptive statistics
Step 2. descriptive statistics
One small table of formulas describes a numeric column. The results are from our ten amounts.
| Measure | Formula | Result |
|---|---|---|
| Number of values | =COUNT(D3:D12) |
10 |
| Mean | =AVERAGE(D3:D12) |
2,485 |
| Median | =MEDIAN(D3:D12) |
2,200 |
| Most frequent value | =MODE(D3:D12) |
2,750 |
| Minimum | =MIN(D3:D12) |
1,500 |
| Maximum | =MAX(D3:D12) |
4,500 |
| Range | =MAX(D3:D12) - MIN(D3:D12) |
3,000 |
| Standard deviation | =STDEV(D3:D12) |
872.75 |
| First quartile | =QUARTILE(D3:D12, 1) |
1,962.5 |
| Third quartile | =QUARTILE(D3:D12, 3) |
2,750 |
How to read it:
- Mean against median. The mean, 2,485, is above the median, 2,200. A few large values pull the mean up; here it is one sale of 4,500. When the two differ noticeably, the median is the better “typical” value.
- Spread. The standard deviation says how far values are from the mean on average. STDEV treats the data as a sample; STDEVP, for a whole population, returned 827.96 on the same numbers.
- The middle half. Half of the sales lie between the first and the third quartile, 1,962.5 and 2,750.
- MODE needs repeats. On a column where every value is different, MODE returned #N/A in our test.
More on each: AVERAGE functions, MAX, MIN and MEDIAN, QUARTILE, PERCENTILE.
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
Step 3. summarize by group
A total hides the differences between categories. Three ways to split it.
SUMIF and COUNTIF: one number per formula
=SUMIF(D3:D10, "Electronics", E3:E10)
=COUNTIF(D3:D10, "Electronics")
See COUNTIF and COUNTIFS and the SUM functions.
A pivot table: all groups at once
Select the data, choose Insert > Pivot table, put the category into Rows and the measure into Values.
Change Summarize by from SUM to COUNT, AVERAGE or MEDIAN to see the same groups from another side. See pivot tables.
QUERY: a summary as a formula
=QUERY(B2:D12, "SELECT C, COUNT(D), SUM(D), AVG(D) GROUP BY C ORDER BY SUM(D) DESC", 1)
On our test table this returned one row per sales rep with the number of sales, their total and their average: Sarah 4, 8,950 and 2,237.5; John 3, 8,750 and 2,916.67; Mike 3, 7,150 and 2,383.33. The largest total does not belong to the rep with the largest average, the kind of difference a single total would hide.
QUERY also extracts the rows behind a number:
=QUERY(B2:G10, "SELECT C, D, E WHERE D = 'Electronics' AND E > 500", 1)
See the QUERY function.
Step 4. filter, and total what is visible
Step 4. filter, and total what is visible
Data > Create a filter adds a menu to each header for showing only some of the rows.
SUM keeps adding the hidden rows. SUBTOTAL counts only the rows the filter shows:
=SUBTOTAL(9, E3:E10)
The first argument chooses the calculation: 9 is the sum, 1 the average, 2 the count of numbers, 4 the maximum, 5 the minimum. With a filter on, the result follows the visible rows. Slicers are a filter control for charts and pivot tables.
Step 5. chart the data
A chart shows in a second what a table of statistics only implies. Select the data and choose Insert > Chart; the chart type is changed in the editor on the right.
Which chart answers which question:
- Column or bar chart: compare categories.
- Line chart: follow a value over time.
- Histogram: see how the values of one column are distributed: where most of them lie, whether the distribution is lopsided.
- Scatter chart: see whether two numeric columns move together.
See charts for visualization, bar graphs and pivots and charts.
Step 6. outliers and relationships
Step 6. outliers and relationships
Highlight unusual values
Format > Conditional formatting colors the cells that meet a rule, for example sales below 500:
For a threshold that comes from the data instead of a guess, use the quartiles. A common rule calls a value an outlier when it lies more than 1.5 interquartile ranges above the third quartile or below the first:
=QUARTILE(D3:D12, 3) + 1.5 * (QUARTILE(D3:D12, 3) - QUARTILE(D3:D12, 1))
On our test amounts the upper limit is 3,931.25, and one sale, 4,500, lies above it. An outlier is a question, not an error: it may be a typo, a duplicate, or the most interesting row in the table.
Do two columns move together
CORREL returns the correlation of two numeric columns, from −1 to 1:
=CORREL(D3:D10, E3:E10)
Values near 1 mean the two rise together, near −1 that one falls as the other rises, near 0 that there is no linear relation. 0.48 on eight rows is a weak hint, not a finding. Always look at the scatter chart as well: one extreme point can create or hide a correlation.
Correlation shows that two columns move together, not that one causes the other.
What to write down at the end
- How many rows there are, and how many were removed as duplicates or errors.
- The typical value and the spread of each key number.
- The groups that differ most.
- The outliers, and what each of them turned out to be.
- The questions the data raised. They are the result of the exploration and the plan for the analysis that follows.
Related guides
- Pivot tables: Summaries by group.
- QUERY: Group, filter and sort with one formula.
- QUARTILE: Quartiles, the interquartile range and outliers.
- AVERAGE functions: The mean and its conditional versions.
- Charts for visualization: Choosing and building charts.
- Remove duplicates: Cleaning before counting.
Exploration is only as good as the extract
Exploration is only as good as the extract
Every number in an exploration, the mean, the quartiles, the outliers, describes the rows that happen to be in the sheet. An export limited to a date range or a row cap, or a file pasted together from several downloads, is explored just as confidently as the full data, and the findings are then about the extract.
The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.
What changes for the person who maintains the sheet:
- Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
- The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
- No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.
The extension does not write or change the analyst’s SQL. The exploration then runs on the complete, current rows the query returns, and can be repeated next month on fresh data with the same formulas. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
How can I automate my data analysis in Google Sheets?
Write the summary as formulas instead of one-off actions: QUERY for grouped summaries, ARRAYFORMULA for calculated columns, pivot tables for groupings. When the data changes, the formulas recalculate and the same exploration is ready on the new rows.
What is the purpose of Pivot Tables in EDA?
Pivot Tables summarize large datasets by grouping, sorting, and filtering data dynamically. They help identify trends and perform aggregations like SUM(), COUNT(), and AVERAGE(). This makes data exploration more efficient without modifying the original dataset.
How can I visualize my data in Google Sheets?
Google Sheets provides bar charts, line graphs, scatterplots, and pivot charts for data visualization. Select your data, click Insert > Chart, and choose a suitable graph. The Chart Editor allows customization for clearer insights.
What are the key functions for calculating descriptive statistics in Google Sheets?
AVERAGE (mean), MEDIAN, MODE, MIN, MAX, STDEV (standard deviation of a sample), QUARTILE and PERCENTILE, with COUNT for the number of values. Comparing the mean with the median shows whether a few large or small values skew the data.
How can I clean my data in Google Sheets for EDA?
Use Data > Data cleanup for Remove duplicates and Trim whitespace, TRIM and CLEAN for stray spaces and hidden characters, and VALUE or DATEVALUE for numbers and dates stored as text. Check for gaps with COUNTBLANK and for variants of the same category with COUNTUNIQUE.
What is Exploratory Data Analysis (EDA)?
Exploratory Data Analysis (EDA) examines and summarizes data to identify patterns, trends, and anomalies. It uses descriptive statistics, visualizations, and data cleaning to improve accuracy. EDA helps analysts make informed decisions and choose the right analytical methods.
