Unit 4: Basic Introduction to Excel, tabulation and graphical presentation

Microsoft Excel is a spreadsheet application in which data are stored in a grid of cells and manipulated with formulas, functions and charts. For research methodology it serves as the working surface where raw observations are entered, organised into tables, condensed into frequency distributions and turned into diagrams that reveal patterns the raw numbers hide.

• Core purpose: stores numeric and text data, applies calculations automatically, and re-computes results whenever inputs change.
• Grid model: every value sits in an addressable cell defined by a column letter and row number (e.g. B3).
• Live linkage: formulas reference cell addresses, so editing one input updates all dependent results and charts.
• Statistical role: supports counting, sorting, summarising and plotting — the mechanical steps of tabulation and graphical presentation.

II. The Excel Workspace

Structure of the file and the grid.

The workspace is a hierarchy: a workbook contains sheets, each sheet is a grid of columns and rows, and the intersection of a column and row is a cell.

A. Sheets / workbook

The container level of every Excel file.

• Workbook: the whole file, saved with extension .xlsx; holds one or more sheets.
• Worksheet (sheet): a single tabbed grid; tabs appear at the bottom and can be renamed, coloured, added or deleted.
• Multi-sheet use: related datasets kept on separate sheets can be linked, e.g. =Sheet2!A1 pulls a value from another sheet.

B. Cell

The smallest unit of storage.

• Address: column letter + row number, e.g. C5; the address shows in the Name Box.
• Content types: numbers, text (labels), dates, or formulas beginning with =.
• Active cell: the highlighted cell that receives typed input; a rectangular selection of cells is a range, written A1:A10.

C. Row

The horizontal division of the grid.

• Identity: numbered 1, 2, 3 … down the left margin.
• Convention: in a data table one row usually holds one record or observation.

D. Columns

The vertical division of the grid.

• Identity: lettered A, B, C … Z, AA … across the top.
• Convention: one column holds one variable, so column headers name the variables measured.

III. Working in Excel

Operations and features used to build and analyse tables.

A. Basic operations

The everyday actions of entering and computing data.

• Data entry: type into a cell and press Enter; use the fill handle to copy or extend a series (1, 2, 3 …).
• Arithmetic: operators + - * / inside a formula, e.g. =A1+B1.
• Functions: built-in calculations, defined by symbols below.
  
  TEXT

    =SUM(A1:A10)      total of a range
    =AVERAGE(A1:A10)  arithmetic mean
    =COUNT(A1:A10)    count of numeric cells
    =MAX / =MIN       largest / smallest value

• Sorting and filtering: arrange rows by a column's values or display only rows meeting a condition.
• Formatting: number, currency, percentage or date formats change display without changing the stored value.

B. Use of all excel options and add-ins

The menu features and optional modules that extend core functions.

• Ribbon tabs: Home (formatting), Insert (charts, tables), Formulas (function library), Data (sort, filter, analysis).
• Data Analysis ToolPak: an add-in enabled via File → Options → Add-ins; provides Descriptive Statistics, Histogram, Regression and Correlation without manual formulas.
• PivotTable: an Insert feature that cross-tabulates and summarises large datasets by dragging fields into rows, columns and values.
• Analysis add-in output: e.g. the Histogram tool takes a data range and a bin range and returns a frequency table plus an optional chart.

IV. Tabulation and Frequency Distributions

Condensing raw data into ordered tables.

Tabulation arranges collected data into rows and columns so that totals, categories and patterns become readable; the form of the table depends on whether the variable is discrete or continuous.

A. Discrete data

Data taking only separate, countable values.

• Nature: whole-number counts with gaps between values — number of children, defects per batch.
• Tabulation: list each distinct value against its frequency (tally count).
• Excel: =COUNTIF(range,value) counts how many times each value occurs.

B. Continuous data

Data that can take any value within a range.

• Nature: measurements on a continuous scale — height, weight, time — limited only by instrument precision.
• Tabulation: grouped into class intervals because individual values rarely repeat.
• Class terms: class limits (stated boundaries), class width (upper − lower limit), class mark / midpoint = (lower + upper) / 2.

C. Frequency distributions

A table pairing each value or class with its number of occurrences.

• Frequency (f): count of observations in a class.
• Cumulative frequency: running total of frequencies, used later for the ogive.
• Relative frequency: f ÷ N, the proportion in each class, where N is total observations.
• Excel: the FREQUENCY(data, bins) array function returns counts falling into each bin.

Worked example — 30 test scores grouped:

Class      f    Cumulative f
0–20       4        4
20–40      9       13
40–60     11       24
60–80      6       30

V. Diagrammatic and Graphical Representation of Data

Visual display of tabulated data.

Once tabulated, data are displayed as pictures so relative sizes and trends are grasped at a glance; a diagram compares categories while a graph plots a variable against a scale.

A. Graphs and their presentation

Principles common to all visual displays.

• Axes: horizontal x-axis (independent variable), vertical y-axis (frequency or value); both labelled with units.
• Scale: chosen so the data fill the plot without distortion; start counts at zero to avoid misleading heights.
• Titles and legends: a clear title, axis titles and a legend identifying each series.
• Excel route: select the table, then Insert → Charts, and choose the chart type matching the data.

B. Diagrammatic vs graphical representation

The distinction between the two families of display.

1. Diagrams: compare discrete categories using bars, rectangles or sectors — bar diagram, pie chart; no continuous scale needed.
2. Graphs: plot data against a numeric scale to show distribution or trend — line chart, histogram, frequency polygon, ogive.

VI. Bar Diagram

Rectangular bars whose heights (or lengths) are proportional to the values they represent.

• Use: comparing discrete categories or a variable across groups; bars are separated by gaps.
• Types: simple (one series), multiple/clustered (several series side by side), stacked (segments summed within a bar).
• Construction: categories on the x-axis, frequency on the y-axis; equal bar width, height ∝ value.
• Excel: Insert → Column/Bar Chart after selecting category labels and their values.

VII. Pie-Chart

A circle divided into sectors, each sector's angle proportional to the share of the whole.

• Use: showing how a total splits into parts (percentage composition).
• Angle formula:
  
  TEXT

    sector angle = (component value ÷ total) × 360°

• Example: a component of 25 out of 100 → (25/100) × 360° = 90°.
• Limitation: hard to read with many small slices; suits a few categories only.

VIII. Line Chart

Points joined by straight lines to show change in a value across an ordered sequence.

• Use: displaying trends over time or another continuous index — sales per month, temperature per day.
• Construction: independent variable (time) on x-axis, value on y-axis; plot points and connect them.
• Reading: slope shows rate of change; multiple lines compare several series on one scale.

IX. Histogram

Adjacent rectangles representing the frequencies of continuous class intervals.

• Use: showing the shape of a continuous frequency distribution.
• Distinction from bar diagram: bars touch (no gaps) because classes are continuous; the x-axis is a numeric scale, not categories.
• Construction: class intervals on the x-axis, frequency on the y-axis; area of each bar ∝ frequency.
• Excel: the Data Analysis ToolPak Histogram tool, or Insert → Histogram chart with a bin range.

X. Frequency Polygon

A line graph of frequencies obtained by joining the midpoints of the tops of the histogram bars.

• Use: showing distribution shape and comparing two or more distributions on one plot.
• Construction: plot each class midpoint against its frequency; join the points with straight lines.
• Closure: add a midpoint with zero frequency at each end so the polygon touches the x-axis.
• Relation to histogram: it is the histogram's outline reduced to a single continuous line.

XI. Ogive Curves

A cumulative frequency curve plotting the running total of frequencies against class boundaries.

• Use: reading off medians, quartiles and the number of observations below or above a value.
• Less-than ogive: plot cumulative frequency against the upper class boundary; rises left to right.
• More-than ogive: plot cumulative frequency (from the top) against the lower class boundary; falls left to right.
• Intersection: where the less-than and more-than ogives cross gives the median value.
• Excel: build a cumulative-frequency column, then plot it as a line chart with markers.