16.1 How Excel Stores Percentages

This is the single most important concept for percentage calculations in Excel. When you apply a Percentage format to a cell, Excel does not change the stored value — it only changes the display. The number 0.15 displayed with Percentage format shows as 15%. The number 15 displayed with Percentage format shows as 1500%.

The Two Ways to Enter a Percentage

Checking the Stored Value

• Click a cell showing a percentage → look at the Formula Bar — it shows the stored decimal (0.15 for 15%)
• Press Ctrl+Shift+~ (General format) to see the raw stored value without formatting

16.2 Basic Percentage Calculations

Calculating a Percentage of a Value

Back-Calculating VAT (Extracting VAT from an Inclusive Price)

16.3 Percentage Change (Increase & Decrease)

Percentage change tells you by how much a value has changed relative to the original. It is used for growth rates, salary increases, price changes, and budget variances.

The Percentage Change Formula

Percentage Change Examples

Applying a Percentage Increase to a Value

Handling Division by Zero in Percentage Change

If the old value is zero, the percentage change formula returns #DIV/0!. Protect against this:

16.4 Budget Variances & Performance Percentages

Variance analysis compares actual values to budgeted or target values. It is a core skill for management accountants and financial analysts in SA organisations.

Variance Formulas

Percentage Contribution (Share of Total)

Running Percentage Totals (Cumulative %)

16.5 Compound Growth & Interest Rate Calculations

Compound Growth Formula

Using Excel's Financial Functions

16.6 The South African ID Number Format

South African ID numbers are 13 digits that encode significant personal information. Understanding the structure allows you to extract birth dates, determine gender, identify citizenship status, and validate the number using the Luhn algorithm — all automatically in Excel.

The 13-Digit Structure

16.7 Extracting Data from SA ID Numbers

Assume the SA ID number is stored as text in cell A2. All the following formulas use text functions (LEFT, MID, RIGHT) to extract specific digits, then convert them to usable values.

Extracting the Year of Birth

Extracting the Month and Day of Birth

Reconstructing the Full Date of Birth

Extracting Gender

Extracting Citizenship Status

Calculating Age from the ID Number

Complete ID Information Extraction Table

16.8 Validating SA ID Numbers (Luhn Algorithm)

The 13th digit of every valid SA ID number is a check digit calculated using the Luhn algorithm. You can verify whether a given ID number is structurally valid (correct check digit) using Excel formulas.

Step-by-Step: How the Luhn Check Digit Works

1. Take digits 1, 3, 5, 7, 9, 11 (odd positions) — sum them
2. Take digits 2, 4, 6, 8, 10, 12 (even positions) — concatenate them into a number, multiply by 2, sum the individual digits of the result
3. Add the two sums together
4. The check digit (digit 13) must make the total sum a multiple of 10

Luhn Validation Formula (Assumes ID in A2)

This formula calculates whether the SA ID number passes the Luhn check:

Practical Validation Formula

A simpler approach using helper cells (easier to understand and audit):

Quick Length and Format Checks

16.9 Building an HR ID Extraction Template

A practical template that extracts all information from a column of SA ID numbers automatically:

Template Structure

16.10 Quick Self-Check

Q1: You enter 15 into a cell and apply Percentage format. The cell shows 1500%. You wanted it to show 15%. What went wrong and how do you fix it?

✓ When you entered 15, Excel stored the number 15. Percentage format multiplies the stored value by 100 for display purposes only: 15 × 100 = 1500%. To display 15%, the stored value must be 0.15. Fix: delete the cell content → type 15% (Excel automatically stores 0.15 and displays 15%) OR type 0.15 and then apply Percentage format. Alternatively, keep the value as 15 and use a custom format code 0"%" which displays the literal number followed by a percent sign without multiplying by 100 — useful when your data is entered as whole percentages (like a rate column where 15 means 15%).

Q2: Last year's salary was R28,000 and this year's is R30,800. Write a formula to calculate the percentage increase, and write a second formula to calculate what the salary would be after a further 6% increase next year.

✓ Percentage increase (A2=28000, B2=30800): =(B2-A2)/A2 formatted as % → 10%. Equivalent: =B2/A2-1 → 0.10 = 10%. Next year salary with 6% increase: =B2*(1+6%) or =B2*1.06 → R32,648. You can also write =B2*(1+0.06) — all three are identical. The 6% in the formula is stored as 0.06, so =B2*1.06 adds 6% to the current salary of R30,800 to give R32,648.

Q3: An invoice total including 15% VAT is R9,890. Write formulas to: (a) extract the VAT amount, (b) calculate the price before VAT.

✓ Invoice inclusive price in A2 = R9,890. (a) VAT amount: =A2-(A2/1.15) → R1,290. Alternative: =A2*15/115 → R1,290. Both are mathematically identical: the inclusive price is 115% of the exclusive price, so the exclusive is A2/1.15 and the VAT portion is the difference. (b) Price before VAT: =A2/1.15 → R8,600. Verify: R8,600 + R1,290 = R9,890 ✓

Q4: An SA ID number is 8503205009083 (stored as text in A2). Write formulas to extract: (a) the date of birth as a proper Excel date, (b) the gender, (c) the citizenship status.

✓ (a) Date of birth: =DATE(IF(LEFT(A2,2)*1>=30,1900,2000)+LEFT(A2,2)*1, MID(A2,3,2)*1, MID(A2,5,2)*1). For 850320: YY=85 (≥30 so prefix 1900 → 1985), MM=03, DD=20 → result: 20/03/1985. (b) Gender: =IF(VALUE(MID(A2,7,4))>=5000,"Male","Female"). Digits 7–10 = 5009 ≥ 5000 → "Male". (c) Citizenship: =IF(MID(A2,11,1)="0","SA Citizen","Permanent Resident"). Digit 11 = 0 → "SA Citizen".

Q5: Why must SA ID numbers be stored as text in Excel, and how do you ensure this when building a spreadsheet that colleagues will use to enter IDs?

✓ SA IDs must be stored as text because: (1) IDs beginning with 0 will lose the leading zero if stored as a number, reducing the ID to 12 digits and making it invalid; (2) Excel may display long numbers in scientific notation (e.g., 8.5E+12) which corrupts the value; (3) text functions (LEFT, MID, RIGHT) work on text strings, not numbers. To ensure correct entry in a shared spreadsheet: (a) Select the entire ID column → Home → Number format dropdown → Text (before any data is entered — if formatted after entry, numbers must be re-entered). (b) Alternatively use Data Validation: Data → Data Validation → Allow: Text length → Equal to → 13 — this prevents non-13-digit entries and treats values as text. (c) Add a column F formula =IF(LEN(A2)=13,"✓","✗") as a visible length-check indicator.

Q6: Your budget for marketing this quarter is R150,000. Actual spend was R127,500. Calculate: (a) the Rand variance, (b) the percentage variance, (c) whether this is favourable or unfavourable, and (d) the percentage of budget actually spent.

✓ Budget in A2=150000, Actual in B2=127500. (a) Rand variance: =B2-A2 → −R22,500 (under budget). (b) Percentage variance: =(B2-A2)/A2 → −15% (spent 15% less than budget). (c) Favourable or unfavourable: For a cost budget, spending LESS than budget is favourable: =IF(B2<A2,"Favourable","Unfavourable") → "Favourable". (d) Percentage of budget spent: =B2/A2 formatted as % → 85% (R127,500 is 85% of R150,000). Reading it together: R22,500 under budget (15% variance) — a favourable outcome since the department spent only 85% of its allocated budget.

✓ Module 16 Complete — You Have Learned:

• How Excel stores percentages — as decimal fractions (15% = 0.15); the three entry methods; the golden rule (store decimal, format as %); checking stored value with Ctrl+Shift+~
• Basic percentage calculations — percentage of a value; using a rate cell; add/subtract a percentage (1+rate, 1−rate); share of total with absolute denominator (/SUM($B$7))
• Back-calculating VAT — extracting VAT from an inclusive price (Price ÷ 1.15 vs Price − Price/1.15); extracting the exclusive price
• Percentage change — formula (New−Old)/Old or New/Old−1; positive = increase, negative = decrease; handling #DIV/0! with IFERROR or IF
• Budget variances — Rand variance; percentage variance; % of target achieved; favourable/unfavourable flag (cost vs revenue conventions); percentage contribution with absolute reference; cumulative running % with mixed reference $B$2:B2
• Compound growth — PV*(1+rate)^n formula; salary projection; CAGR formula (B2/A2)^(1/n)−1; monthly compounding; FV() and PV() financial functions
• SA ID number structure — 13-digit format: YYMMDD+GGGGG+C+A+Z; gender sequence (0000–4999 = Female, 5000–9999 = Male); citizenship digit; check digit; must store as text (leading zeros, 13-digit length)
• Extracting data from SA IDs — LEFT/MID/RIGHT for year (with century: ≥30 = 1900s, <30 = 2000s), month, day; DATE() to reconstruct full date of birth; gender IF; citizenship IF; DATEDIF for age
• Luhn algorithm validation — how the check digit works; full SUMPRODUCT validation formula; simpler helper-cell approach; quick format checks (LEN=13, ISNUMBER, month/day range)
• HR ID extraction template — 7-column structure (ID, DOB, Age, Gender, Citizenship, Length Check, Luhn Valid); column A as Text; IFERROR wrappers; Table for auto-expansion; conditional formatting for invalid IDs