A spreadsheet (MS-Excel) is a grid of cells arranged in lettered columns (A, B, C…) and numbered rows (1, 2, 3…). A cell reference such as C5 identifies column C, row 5. A workbook contains one or more worksheets (tabs).
A1 — changes when copied (A1 → B1).$A$1 — fixed when copied.$A1 or A$1 — one part fixed.=, e.g. =A1+B1.Type 1 in A1 and 2 in A2, select both, then drag the fill handle down to A10 — Excel auto-fills 3, 4, …, 10 by detecting the step of 1.
To convert all "M"/"F" entries in a column to "Male"/"Female", use Ctrl + H: find "M", replace "Male" (with "Match entire cell contents" ticked to avoid changing words containing M).
Ordinary paste (Ctrl + V) copies everything. Paste Special (Ctrl + Alt + V) lets you paste selectively:
A column computed with =A1*1.18 (adding 18% GST) can be frozen: copy it, then
Paste Special → Values so the numbers stay even if column A is later deleted.
To convert a row of 12 monthly totals into a column, copy the row and Paste Special → Transpose.
A class list of 60 students sorted by Total marks (descending) instantly shows the ranks.
Filter a sales sheet to show only the rows where Region = "South" and Amount > 10,000 to inspect high-value southern sales.
AutoSum (the Σ button, or Alt + =) inserts =SUM(range) for the
cells above or to the left, and also offers Average, Count, Max and Min from its dropdown.
| Quantity | Excel function | Formula it computes |
|---|---|---|
| Sum | =SUM(A1:A10) | \(\sum x_i\) |
| Mean | =AVERAGE(A1:A10) | \(\bar x = \dfrac{\sum x_i}{n}\) |
| Sample SD | =STDEV.S(A1:A10) | \(s = \sqrt{\dfrac{\sum (x_i-\bar x)^2}{n-1}}\) |
| Population SD | =STDEV.P(A1:A10) | \(\sigma = \sqrt{\dfrac{\sum (x_i-\bar x)^2}{n}}\) |
| Variance (sample) | =VAR.S(A1:A10) | \(s^2\) |
| Count of numbers | =COUNT(A1:A10) | \(n\) |
| Maximum / Minimum | =MAX(…) / =MIN(…) | largest / smallest |
Marks 45, 50, 55, 60, 65, 70 are in A1:A6.
=AVERAGE(A1:A6) → 57.5.=STDEV.S(A1:A6) → 9.354.=AVERAGEIF(B2:B61,"South",C2:C61) gives the mean sale amount only for the South region;
=COUNTIF(D2:D61,">60") counts students scoring above 60.
Excel handles matrices through array functions. Select the output range first, type the formula, then confirm. (In modern Excel, dynamic arrays spill automatically; in older Excel press Ctrl + Shift + Enter.)
=TRANSPOSE(A1:C2) — turns an \(m\times n\) matrix into \(n\times m\).=MMULT(A1:C2, E1:F3) — matrix product (inner dimensions must match).=MINVERSE(A1:B2) — inverse of a square non-singular matrix.=MDETERM(A1:B2).For \(A = \begin{pmatrix}1&2\\3&4\end{pmatrix}\), =TRANSPOSE(A) gives
\(\begin{pmatrix}1&3\\2&4\end{pmatrix}\). =MMULT(A, A) gives
\(\begin{pmatrix}7&10\\15&22\end{pmatrix}\).
For \(A = \begin{pmatrix}4&7\\2&6\end{pmatrix}\), determinant \(= 4\cdot6 - 7\cdot2 = 10\);
=MINVERSE(A) gives \(\frac{1}{10}\begin{pmatrix}6&-7\\-2&4\end{pmatrix} =
\begin{pmatrix}0.6&-0.7\\-0.2&0.4\end{pmatrix}\). Useful for solving \(A\mathbf{x}=\mathbf{b}\) via
\(\mathbf{x} = A^{-1}\mathbf{b}\) with =MMULT(MINVERSE(A), b).
| Chart | Best for |
|---|---|
| Bar / Column | Comparing values across categories |
| Line | Trends over time (time series) |
| Pie | Parts of a whole (shares / percentages) |
Procedure: select the data range → Insert tab → choose the chart type → add a title, axis labels and data labels via Chart Elements.
Monthly sales (Jan–Jun: 20, 25, 22, 30, 28, 35) → select the range and insert a Line chart to reveal the upward trend.
Household budget (Food 50%, Rent 30%, Travel 20%) → insert a Pie chart; turn on data labels to show the percentages on each slice.
A results table pasted into a Word report as a linked object: when you fix a typo in the Excel source, right-click the Word table → Update Link and it refreshes automatically.
A chart copied into PowerPoint as a Picture stays pixel-perfect on any computer, even one without the original data file.
A1), absolute ($A$1) and mixed references.