Descriptive Statistics and Exploratory Data Analysis

Introduction

Business analysts rarely begin with prediction or optimization. They begin by asking what the data contains, how it is distributed, whether it is reliable, and what unusual observations may influence conclusions. Descriptive statistics and exploratory data analysis (EDA) provide the foundation for answering these questions.

Descriptive statistics summarize observed data using numerical measures, tables, and visualizations. EDA extends this work by examining patterns, variation, relationships, missing values, and anomalies before formal modelling. In Excel, these activities can be performed with formulas, PivotTables, charts, conditional formatting, and the Analysis ToolPak.

The purpose of EDA is not merely to produce attractive charts. It is to develop an accurate and decision-relevant understanding of a dataset. A manager deciding whether to expand a product line, for example, needs more than average sales. The manager should also understand sales variability, regional differences, skewness, extreme observations, sample size, and the quality of the underlying records.

Learning Objectives

By the end of this chapter, you should be able to:

1. Distinguish populations, samples, variables, observations, and measurement scales.
2. Calculate and interpret measures of central tendency, dispersion, position, and distribution.
3. Use Excel formulas, PivotTables, charts, and the Analysis ToolPak for descriptive analysis.
4. Identify potential outliers and explain why they require investigation rather than automatic deletion.
5. Construct and interpret frequency tables, histograms, box-and-whisker plots, scatterplots, and correlation measures.
6. Communicate statistical findings with appropriate attention to business meaning, assumptions, limitations, and ethics.

1. The Data-Analytic Starting Point

A dataset consists of observations recorded for one or more variables. An observation might be a customer, transaction, employee, store, or day. A variable is a characteristic measured for each observation, such as revenue, region, delivery time, or customer rating.

A population is the complete set of units relevant to a question. A sample is a subset used to learn about that population. Population parameters are commonly represented by Greek letters, whereas sample statistics are usually represented by Roman letters. For example, the population mean is μ and the sample mean is x̄.

Measurement scale affects which summaries are meaningful:

A spreadsheet should be structured so that each row represents one observation and each column represents one variable. The first row should contain unique, concise headers. Mixed data types, merged cells, repeated headers, totals embedded within data, and unexplained blanks create avoidable analytical errors.

2. Measures of Central Tendency

Central tendency describes a typical or central value.

Mean

For n observations x₁, x₂, …, xₙ, the arithmetic mean is:

The symbol x̄ is the sample mean, xᵢ is observation i, and n is the number of non-missing observations. The formula assumes numerical values for which addition and division are meaningful. The mean uses every observation and is therefore sensitive to extreme values.

In Excel, use =AVERAGE(range). Excel ignores empty cells but includes numeric zero values. Text stored in cells may be treated differently depending on whether it is inside a referenced range, so data types should be checked.

Median

The median is the middle ordered value. If n is odd, it is the value in position (n + 1) ÷ 2. If n is even, it is the average of the two central ordered values, in positions n ÷ 2 and n ÷ 2 + 1.

Use =MEDIAN(range). The median is less affected by extreme values than the mean and is often preferable for income, transaction value, delivery time, or house-price data when distributions are skewed.

Mode

The mode is the most frequently occurring value. Excel provides =MODE.SNGL(range) for one mode. A dataset can be bimodal or multimodal, and some datasets have no repeated value. The mode is especially useful for categorical variables, where a mean may have no meaningful interpretation.

Choosing a Centre

The mean is useful when data are reasonably symmetric and extreme values are legitimate. The median is often more representative for skewed data. Reporting both can reveal distributional asymmetry. For example, if average delivery time is 5.8 days but the median is 4.2 days, a relatively small number of very late deliveries may be raising the mean.

Worked Example 1: Central Tendency

Suppose the weekly order values, in dollars, are 80, 90, 90, 100, and 140.

Mean = (80 + 90 + 90 + 100 + 140) ÷ 5 = 500 ÷ 5 = $100.

The ordered middle observation is $90, so the median is $90. The mode is $90 because it occurs twice. The mean exceeds the median because the $140 order pulls the arithmetic average upward. A manager describing a typical order might report the median of $90 while also noting the mean of $100.

3. Measures of Dispersion

Two datasets may have the same mean but very different degrees of risk or inconsistency. Dispersion measures quantify spread.

Range

The range is:

It is simple to calculate but depends only on two observations and is highly sensitive to extremes. Excel functions are =MAX(range)-MIN(range), =MAX(range), and =MIN(range).

Variance and Standard Deviation

For a sample, sample variance is:

Sample standard deviation is:

The n − 1 denominator is used when estimating population variability from a sample; it provides the usual unbiased estimator of population variance under standard random-sampling assumptions. If the data constitute the entire population of interest, population variance uses n in the denominator:

Here, N is population size and μ is the population mean. Standard deviation has the same units as the original variable, whereas variance has squared units. In Excel, use =STDEV.S(range) for a sample and =STDEV.P(range) for a complete population.

Standard deviation is most interpretable when the distribution is reasonably symmetric. Under an approximately normal distribution, about 68% of observations lie within one standard deviation of the mean, about 95% within two, and about 99.7% within three. These percentages are not universal rules and should not be applied automatically to skewed or multimodal data.

Interquartile Range

The first quartile Q₁ is the 25th percentile and the third quartile Q₃ is the 75th percentile. The interquartile range is:

The IQR describes the spread of the middle 50% and is resistant to extreme values. Excel functions include =QUARTILE.INC(range,1), =QUARTILE.INC(range,3), or the equivalent percentile functions.

Coefficient of Variation

For positive measurements with a meaningful zero, the coefficient of variation is:

It is often reported as a percentage by multiplying by 100. CV compares relative variability across variables measured in different units or with different means. It is inappropriate when the mean is zero or close to zero, when values can be negative in a way that makes the ratio unclear, or when the variable lacks a meaningful ratio scale.

4. Measures of Position and Distribution

Percentiles and Quartiles

The pth percentile is a value below which approximately p percent of observations fall. Percentiles are useful for service-level analysis, compensation benchmarking, and customer segmentation. Excel’s =PERCENTILE.INC(range,k) returns the inclusive percentile for k between 0 and 1. Different software may use different percentile interpolation conventions, so the method should be documented for important reports.

A z-score expresses an observation’s distance from the mean in standard-deviation units:

For a sample-based descriptive calculation, x̄ and s are used. A z-score of 2 means that the observation is two sample standard deviations above the sample mean. A z-score is not automatically a probability and should not be interpreted as evidence of an error without contextual investigation.

Shape: Skewness and Kurtosis

Skewness describes asymmetry. Positive or right skew has a long right tail and often produces mean > median. Negative or left skew has a long left tail and often produces mean < median. Symmetry is not guaranteed merely because a mean and median are close.

Kurtosis concerns tail weight and concentration relative to a reference distribution. Excel’s =SKEW(range) and =KURT(range) provide sample statistics. Excel’s KURT function reports excess kurtosis, for which a normal distribution has a value near zero. Skewness and kurtosis are descriptive indicators, not definitive tests of normality.

A histogram, box plot, and comparison of mean and median usually provide more useful practical evidence than a single shape statistic.

5. Frequency Analysis

Frequency analysis counts how often values or categories occur. A frequency table may contain:

• absolute frequency: number of observations;
• relative frequency: count divided by total count;
• cumulative frequency: running total of counts;
• cumulative percentage: running percentage of observations.

For category counts, a PivotTable is often the most flexible approach. Place a categorical field in Rows and again in Values, configured as Count. To show percentages, select Show Values As and choose Percent of Grand Total.

For numerical variables, define mutually exclusive and collectively exhaustive bins. For example, delivery-time bins might be 0–2, 3–5, 6–10, and 11 or more days. Boundaries must be explicit so that an observation belongs to exactly one class.

Excel’s =COUNTIF(range, criteria) counts observations meeting one condition, while =COUNTIFS(range1, criteria1, range2, criteria2) applies multiple conditions. The Analysis ToolPak Data Analysis menu includes Histogram, which can generate a frequency table and chart when an input range and bin range are supplied.

6. Exploratory Visualisation in Excel

A numerical summary should normally be accompanied by a suitable visual method.

Histogram

A histogram displays the frequency of numerical values across contiguous intervals. It reveals concentration, gaps, skewness, and possible multiple peaks. Bin width affects the appearance: very narrow bins can exaggerate noise, while very wide bins can conceal structure. Excel’s Histogram chart or Analysis ToolPak Histogram can be used, but the analyst should inspect and justify bin choices.

Box-and-Whisker Plot

A box plot displays the median, Q₁, Q₃, IQR, and selected extreme observations. Excel’s Box and Whisker chart is useful for comparing regions, products, or departments. Under the common 1.5-IQR rule, lower and upper potential-outlier fences are:

Values beyond these fences are potential outliers, not proven errors. The 1.5 multiplier is a convention associated with box plots, not a universal law.

Scatterplot

A scatterplot displays paired numerical observations and helps reveal direction, form, clusters, and unusual points. Use an XY Scatter chart rather than a line chart when the horizontal variable is numerical. Add a trendline only when it supports the analytical purpose, and do not confuse visual association with causation.

Bar and Column Charts

Bar charts are suitable for categorical frequencies or aggregate comparisons. Sorting categories can improve readability, but analysts should avoid visual distortions such as truncated axes when the chart is intended to communicate absolute magnitude.

Time-Series Lines

A line chart is appropriate when observations have a meaningful ordered time dimension. It can show trend, seasonality, interruptions, and abrupt changes. However, descriptive time patterns do not by themselves establish that one event caused another.

7. Correlation and Association

The Pearson correlation coefficient measures the strength and direction of linear association between two numerical variables:

Here xᵢ and yᵢ are paired observations, x̄ and ȳ are sample means, and n is the number of complete pairs. The coefficient lies between −1 and +1 when both variables have nonzero variation. Positive values indicate that larger values of one variable tend to accompany larger values of the other; negative values indicate an opposite linear tendency. Values near zero indicate little linear association, not necessarily no relationship.

Excel uses =CORREL(range1,range2) or =PEARSON(range1,range2). Correlation is affected by outliers, restricted ranges, nonlinear patterns, and missing or mismatched pairs. A strong correlation does not establish causation; common causes, reverse causality, selection effects, or coincidence may explain the relationship.

For categorical variables, cross-tabulations, percentages, clustered bar charts, and measures such as chi-square association may be more suitable than Pearson correlation. For ordinal data or strongly non-normal variables, rank-based methods such as Spearman correlation may be considered, although Excel may require additional formulas or ranking columns.

8. Outlier Detection and Data Quality

An outlier is an observation unusually distant from the rest of the data. Outliers may represent data-entry errors, unusual but valid events, fraud, process failures, a new customer segment, or a change in measurement conditions.

A disciplined process is:

1. Detect the observation using a box plot, IQR fences, z-scores, or domain thresholds.
2. Verify the original record, units, date, and data-entry process.
3. Investigate business context and whether the observation is genuinely possible.
4. Decide whether to retain, correct, exclude, cap, transform, or separately analyse it.
5. Document the decision and conduct sensitivity analysis where the conclusion may change.

Deleting a data point solely because it is inconvenient is poor practice. Conversely, retaining an obvious duplicate or impossible negative quantity can distort the analysis. A corrected value should be traceable to evidence, not guessed.

Conditional formatting can highlight values above or below thresholds. Helper columns can calculate IQR fences or z-scores. Excel’s =IF(OR(value<lower_fence,value>upper_fence),"Review","Typical") can flag records, but automated flags are prompts for review rather than final classifications.

9. The Excel Analysis ToolPak

The Analysis ToolPak is an Excel add-in that provides ready-made procedures including Descriptive Statistics, Histogram, Correlation, Covariance, and Moving Average. To activate it, use Excel Options, select Add-ins, choose Excel Add-ins in the Manage box, and select Go; then tick Analysis ToolPak.

The Descriptive Statistics procedure can produce the mean, standard error, median, mode, standard deviation, sample variance, kurtosis, skewness, range, minimum, maximum, sum, and count. Select the input range, identify whether labels are included, choose an output range or new worksheet, and request summary statistics.

ToolPak output is convenient, but analysts must check:

• whether the input range includes the correct rows and columns;
• whether labels and missing values were handled appropriately;
• whether the procedure uses sample or population conventions;
• whether the output is reproducible and documented;
• whether the selected statistic is appropriate for the measurement scale and distribution.

Formula-based analysis is often preferable for transparent, refreshable workbooks. A professional workbook should separate raw data, calculations, visualisations, and conclusions. Tables, named ranges, data validation, and clear documentation improve auditability.

10. Worked Business Application: Delivery Performance

Background and Problem

The operations team of an illustrative online retailer has recorded delivery times for 12 completed orders, measured in days: 2, 3, 3, 4, 4, 5, 5, 5, 6, 7, 8, and 20. Management wants to know the typical delivery experience and whether a service problem requires investigation.

Analysis

The sum is 72 days, so:

Mean = 72 ÷ 12 = 6 days.

The median is the average of the sixth and seventh observations, both 5, so the median is 5 days. The mode is 5 days. The mean is higher because the 20-day delivery is unusually long.

Using the inclusive quartile convention, Q₁ is approximately 3.75 and Q₃ is approximately 6.25, giving:

IQR = 6.25 − 3.75 = 2.50 days.

The upper potential-outlier fence is 6.25 + 1.5 × 2.50 = 10 days. The 20-day delivery is therefore a potential outlier. It should be investigated: perhaps the order was held by customs, entered incorrectly, or genuinely experienced a severe delay.

A histogram would show concentration between 2 and 8 days with a long right tail. A box plot would identify the 20-day observation separately. A useful management statement is: Typical delivery performance was about five days, but the average rose to six days because one order took 20 days; the exceptional order should be investigated before deciding whether the process has deteriorated.

Excel Approach

Place the delivery times in an Excel Table column named DeliveryDays. Use:

• =AVERAGE(Table1[DeliveryDays])
• =MEDIAN(Table1[DeliveryDays])
• =MODE.SNGL(Table1[DeliveryDays])
• =STDEV.S(Table1[DeliveryDays])
• =QUARTILE.INC(Table1[DeliveryDays],1)
• =QUARTILE.INC(Table1[DeliveryDays],3)

Create a box-and-whisker chart and a histogram. Add a review flag based on the calculated upper fence, then inspect the corresponding order record. If the 20-day delivery resulted from a rare external event, management may report typical performance separately from exceptional incidents. If it arose from a recurring warehouse process, it may indicate a priority for corrective action.

Outcome and Learning Points

The analysis distinguishes typical performance from average performance, identifies a potentially important exception, and avoids prematurely removing data. It also illustrates why a single statistic is insufficient: the mean, median, quartiles, chart, and operational context each contribute different information.

11. Interpretation, Communication, and Limitations

Descriptive statistics summarize the observed data; they do not automatically generalize to all future cases. Generalization depends on how the sample was selected, whether observations are independent, whether measurement procedures were consistent, and whether important groups are missing.

Common limitations include:

• Sampling bias: a convenience sample may not represent the target population.
• Nonresponse: dissatisfied or busy customers may be less likely to respond.
• Measurement error: definitions, instruments, and data-entry practices may change.
• Dependence: repeated transactions from one customer may not be independent.
• Confounding: two correlated variables may both be influenced by a third factor.
• Aggregation: averages can conceal meaningful regional, demographic, or product-level differences.
• Missingness: missing records may be systematic rather than random.

A professional report should state the unit of analysis, time period, sample size, missing-data treatment, key definitions, and relevant limitations. It should translate statistical results into business language without overstating certainty. For example, saying that a campaign caused higher sales is not justified merely because sales and campaign exposure are positively correlated.

Ethically, analysts should protect confidential customer and employee information, limit access to sensitive workbooks, avoid discriminatory profiling, and explain how unusual observations or missing cases were treated. Charts should not be designed to mislead through selective periods, inappropriate scales, or hidden denominators. Reproducibility matters: another analyst should be able to trace a reported result to the source data and documented calculation.

12. Best Practices and Contemporary Tools

Effective Excel EDA combines sound statistical reasoning with spreadsheet discipline:

1. Begin with a clear business question and unit of analysis.
2. Inspect data types, duplicates, missing values, impossible values, and inconsistent labels.
3. Use both robust and non-robust summaries when skewness or outliers may matter.
4. Compare groups with consistent definitions and denominators.
5. Use charts to reveal structure, not to decorate a report.
6. Document formulas, assumptions, version dates, and outlier decisions.
7. Keep raw data unchanged and perform transformations in controlled columns or sheets.
8. Validate conclusions using sensitivity analysis, such as comparing results with and without a verified extreme case.

Modern Excel includes dynamic arrays, Tables, Power Query, PivotCharts, and automated insights. These tools can accelerate cleaning and exploration, but automation does not replace human judgment. AI-assisted suggestions may identify patterns or generate formulas, yet the analyst remains responsible for checking definitions, privacy implications, statistical validity, and the possibility of spurious relationships.

Chapter Summary

Descriptive statistics provide compact numerical summaries of business data, while exploratory data analysis investigates distribution, relationships, quality, and unusual observations. The mean, median, and mode describe central tendency; the range, variance, standard deviation, IQR, and coefficient of variation describe dispersion; percentiles and z-scores describe position. Skewness and kurtosis provide indicators of distributional shape.

Excel supports these tasks through formulas, PivotTables, frequency functions, charts, and the Analysis ToolPak. Histograms reveal frequency patterns, box plots support robust comparison and potential-outlier detection, scatterplots display paired relationships, and correlation measures linear association. None of these tools proves causation or guarantees representativeness.

Sound analysis requires appropriate measurement scales, explicit assumptions, careful treatment of missing values and outliers, transparent documentation, and communication in business terms. The analyst’s goal is not simply to calculate statistics but to support responsible decisions based on an accurate understanding of the evidence.

Key Terms

Population: The complete set of units relevant to a study.

Sample: A subset of a population used for analysis.

Observation: One recorded unit or case in a dataset.

Variable: A characteristic measured or recorded for observations.

Mean: The arithmetic average of numerical observations.

Median: The middle ordered value, or average of two middle values.

Mode: The most frequently occurring value or category.

Variance: The average squared deviation, using an appropriate population or sample denominator.

Standard deviation: The square root of variance, expressed in the original units.

Quartile: A value dividing ordered data into four sections.

Percentile: A value below which approximately a specified percentage of observations fall.

Interquartile range: Q₃ − Q₁, representing the middle 50% spread.

Skewness: A descriptive measure of distributional asymmetry.

Kurtosis: A descriptive indicator of tail weight and concentration.

Histogram: A chart showing numerical frequencies across intervals.

Box plot: A chart summarizing median, quartiles, spread, and potential extremes.

Correlation: A measure of association, commonly focused on linear association for Pearson’s r.

Outlier: An unusually distant observation requiring investigation.

Exploratory data analysis: Systematic examination of data using summaries, visualisations, and contextual investigation.

Analysis ToolPak: An Excel add-in containing procedures for statistical analysis.

Review Questions

Short Answer Questions

1. Why might the median be more appropriate than the mean for household income?
2. Distinguish sample standard deviation from population standard deviation.
3. What does the interquartile range measure?
4. Why does correlation not establish causation?
5. Name two possible explanations for an outlier.

Long Answer Questions

1. Explain how mean, median, mode, range, standard deviation, and IQR provide complementary information about a dataset.
2. Describe a complete Excel workflow for conducting descriptive statistics and EDA on a sales dataset.
3. Explain how histograms, box plots, and scatterplots answer different analytical questions.
4. Discuss the assumptions and limitations associated with using Pearson correlation.
5. Explain how sampling bias, missing data, and aggregation can affect business interpretation.

Analytical / Application Questions

1. A store’s transaction values are strongly right-skewed. Recommend a set of numerical summaries and visualisations, and justify your choices.
2. An analyst deletes all observations beyond 1.5 IQR without checking the source records. Critique this procedure and propose a better approach.
3. Two regions have the same average delivery time, but one has a much larger standard deviation. Explain the managerial implications and recommend additional analysis.

Multiple Choice Questions with Answers

Question 1

Which measure is generally least affected by an extreme value?

A. Mean

B. Median

C. Range

D. Variance

Correct Answer: B. Median

Question 2

Which Excel function calculates sample standard deviation?

A. STDEV.P

B. VAR.P

C. STDEV.S

D. AVERAGE

Correct Answer: C. STDEV.S

Question 3

If Q₁ = 20 and Q₃ = 35, what is the interquartile range?

A. 15

B. 20

C. 35

D. 55

Correct Answer: A. 15

Question 4

A Pearson correlation of −0.85 indicates:

A. Strong positive linear association

B. Strong negative linear association

C. No relationship of any kind

D. Proof that one variable causes the other

Correct Answer: B. Strong negative linear association

Question 5

Which chart is most appropriate for displaying the distribution of a numerical variable across intervals?

A. Histogram

B. Pie chart

C. Radar chart

D. Organizational chart

Correct Answer: A. Histogram

Question 6

Using the 1.5-IQR rule, with Q₁ = 10 and Q₃ = 18, which value is a potential upper outlier?

A. 25

B. 28

C. 30

D. 35

Correct Answer: D. 35

Question 7

What does a z-score of 2 indicate?

A. The value is two units above zero

B. The value is two standard deviations above the mean

C. The value is twice the median

D. The value has a probability of 2

Correct Answer: B. The value is two standard deviations above the mean

Question 8

Which Excel feature is especially suitable for counting categories and displaying their percentages?

A. PivotTable

B. Goal Seek

C. Solver

D. Watch Window

Correct Answer: A. PivotTable

Question 9

Why should an identified outlier not be automatically deleted?

A. All outliers are always errors

B. It may be a valid and important business event

C. Outliers cannot affect the mean

D. Deleting it always increases sample size

Correct Answer: B. It may be a valid and important business event

Question 10

Which statement is correct about a correlation near zero?

A. There is no possible relationship between the variables

B. The variables must have equal means

C. There is little linear association, although a nonlinear relationship may exist

D. One variable causes the other only weakly

Correct Answer: C. There is little linear association, although a nonlinear relationship may exist