Select the data range → Insert tab → choose chart type → click. Customise with Chart Design and Format tabs.
Common Chart Types and When to Use Each
Chart
Best for
Column
Comparing values across categories (vertical bars)
Bar
Same as column but horizontal — useful for long category names
Line
Trends over time (continuous data)
Area
Cumulative trend (filled area beneath line)
Pie / Doughnut
Parts of a whole, ≤ 6 categories
Scatter (XY)
Relationship between two numeric variables
Bubble
Three-variable relationship (X, Y, size)
Histogram
Distribution of one continuous variable
Box & Whisker
Five-number summary, outliers
Combo
Mix of bar + line, e.g., sales + growth rate
Chart Elements and Formatting
Chart Title — describe what is shown.
Axis titles — label both X and Y axes with units.
Legend — names of series.
Data labels — show numeric values on bars/points.
Gridlines — keep light and minimal.
Trendline — for scatter/line: linear, polynomial, exponential, etc.
Pie Chart Example
For category totals (Food, Rent, Education, Savings):
Enter categories in A2:A5 and values in B2:B5.
Select A2:B5 → Insert → Pie chart.
Right-click slice → Add Data Labels → choose Percentage.
Apply a meaningful title.
Histogram (built-in chart, Excel 2016+)
Select the data column.
Insert → Statistic Chart → Histogram.
Right-click the X-axis → Format Axis → choose Bin width or Number of bins.
Alternative: Data → Data Analysis → Histogram (ToolPak). Provide Input Range and Bin Range; tick "Chart Output".
EXAMPLE 1 (Column chart)
Sales in 5 years 200, 250, 300, 280, 350 (B2:B6 with years in A2:A6). Select A2:B6 → Insert → Column. Result: 5 vertical bars showing growth.
EXAMPLE 2 (Scatter with trend line)
Hours of study (A2:A10) vs marks (B2:B10). Select → Insert → Scatter → "Scatter with only markers". Right-click any point → Add Trendline → Linear → tick "Display Equation on chart" to see the regression equation.
Visual Examples — What Each Chart Looks Like
The pictures below are drawn from the same small datasets used above, so you can match each chart to
the numbers behind it.
Bar height = value. Use a column/bar chart to compare a quantity across
categories or years.Same five numbers, joined by a line. Use a line chart to show a trend or movement
over time (continuous data).Values 4000, 3000, 2000, 1000 shown as slices. Use a pie chart for parts of a
whole (≤ 6 categories that add to 100%).Each dot is one student (hours, marks); the red line is the fitted trend. Use a scatter
plot to show the relationship between two numeric variables.Bars touch (the variable is continuous) and their heights are the class
frequencies 4, 8, 9, 6, 2, 1. Use a histogram to show the shape of one continuous
variable's distribution.
2. Frequency Tables with COUNT Functions
Function
Purpose
Example
=COUNT(range)
Count numeric values
=COUNT(A2:A100)
=COUNTA(range)
Count non-empty cells (text too)
=COUNTA(B2:B100)
=COUNTBLANK(range)
Count empty cells
=COUNTBLANK(C2:C100)
=COUNTIF(range, criteria)
Count cells meeting one condition
=COUNTIF(A2:A100, ">50")
=COUNTIFS(r1,c1, r2,c2,…)
Multiple conditions
=COUNTIFS(A2:A100,"M", B2:B100,">35")
EXAMPLE 1
Marks in A2:A50. To count students who passed (marks ≥ 35): =COUNTIF(A2:A50, ">=35").
EXAMPLE 2 (Two conditions)
Count male students who scored ≥ 75: =COUNTIFS(GenderRange, "M", MarksRange, ">=75").
3. FREQUENCY Function (for Grouped Data)
=FREQUENCY(data_array, bins_array)
Returns counts of values falling in each bin (≤ bin boundary). Must be entered as an array formula (Excel 365 spills automatically; legacy Excel needs Ctrl+Shift+Enter).
Procedure
Enter raw data in column A (say A2:A51).
Enter upper bin limits in column C (say C2:C8): 30, 40, 50, 60, 70, 80, 90.
Select cells D2:D9 (one extra for the "above last bin" overflow).
Type =FREQUENCY(A2:A51, C2:C8) and press Ctrl+Shift+Enter (legacy) — or just Enter in Excel 365.
Number of children per family: data in A2:A21; bins 0, 1, 2, 3 in C2:C5. FREQUENCY gives counts of 0, 1, 2, 3, 4+.
4. PivotTables — Powerful Summarisation
DEFINITION
A PivotTable dynamically summarises large amounts of data by grouping rows, columns, and aggregating values (sum, count, average, etc.) — without writing formulas.
Creating a PivotTable
Ensure the source has headers in row 1 and no blank columns.
Click anywhere in the data → Insert → PivotTable.
Choose location (New Worksheet recommended). Click OK.
Drag fields into the four areas:
Rows — categorical variables to break down rows.
Columns — categorical variables to break down columns.
Values — numeric variable to aggregate (Sum, Count, Average, …).
Filters — top-level filter dropdown.
Right-click a value cell → Value Field Settings → choose aggregation function and number format.
Common Operations
Group dates by Month/Quarter/Year: right-click a date row → Group.
Refresh: when source data changes, click Refresh (Alt+F5) or Refresh All.
EXAMPLE 1
Sales data with columns: Date, Region, Product, Salesperson, Amount.
Rows: Region; Columns: Product; Values: Sum of Amount.
Result: a matrix of total sales per region per product — instantly.
EXAMPLE 2 (Average grade per section)
Student data with Section, Gender, Marks. PivotTable: Rows = Section, Columns = Gender, Values = Average of Marks. Drag and the cross-tab of mean marks appears.
5. PivotCharts
A PivotChart is a chart linked to a PivotTable — when you slice or filter the PivotTable, the chart updates automatically.