Leading zeros, lookups and a total that misses a row
You are building a staff overtime sheet. In A2 you type employee ID 0012 and Excel displays 12. On the Employees sheet the same IDs are stored as text with leading zeros. Your lookup in C2, =VLOOKUP(A2,Employees!$A$2:$B$200,2,FALSE), returns #N/A. Column D holds overtime hours (some cells blank), column E holds hourly rate, and F2 contains =D2E21.5, copied down to F60. You then insert a title row at row 1 and a new employee row at row 60. The total in F61, =SUM(F2:F60), no longer includes every person. A colleague says the sheet is fine because column F is formatted to two decimal places.
1. Explain why 0012 became 12 and why the VLOOKUP returns #N/A, then give the most reliable fix.
Read the worked answer
Excel interpreted 0012 as the number 12. The leading zeros are not stored data in a number; they are only a display effect if a custom format such as 0000 is applied. The Employees sheet stores text "0012", so VLOOKUP compares the number 12 with the text "0012" and finds no match. The two values look similar but are different data types.
Fixes, in order of reliability:
- Make the ID columns consistent. The cleanest long-term fix is to decide that IDs are text everywhere. Format column A as Text before entering data, or type an apostrophe first ('0012). Then re-enter the affected IDs.
- If you must keep numbers in the lookup sheet, convert on the fly: =VLOOKUP(TEXT(A2,"0000"),Employees!$A$2:$B$200,2,FALSE). This is a patch, not a data fix.
- Diagnose with =ISTEXT(A2) and =ISTEXT(Employees!A2). If one is TRUE and the other FALSE, that is the cause.
A custom number format of 0000 on A2 alone will not fix the lookup: the cell still contains the number 12. Formatting changes appearance, not the stored value.
2. The 1.5 overtime multiplier should come from a single cell, H1, so payroll can change it. Rewrite F2 and explain what happens when it is copied down.
Read the worked answer
Use =D2E2$H$1.
The dollar signs make H1 an absolute reference: the column and the row are locked. When F2 is copied to F3, the relative parts shift as intended (D2 becomes D3, E2 becomes E3) while $H$1 stays $H$1. Without the dollar signs, =D2E2H1 copied down becomes =D3E3H2, then =D4E4H3, and the multiplier quickly points at empty cells, producing zeros.
Alternatives:
- Name H1 as OvertimeRate and write =D2E2OvertimeRate. Named ranges are absolute by nature and easier to audit.
- Put the multiplier on a separate Settings sheet and reference it as Settings!$B$1, so the timesheet stays clean.
One caution: if H1 ever contains text or is blank, every product returns #VALUE! or 0. Add data validation to H1 to restrict it to a decimal between 1 and 3.
3. Why did =SUM(F2:F60) stop including everyone, and what should replace it?
Read the worked answer
A SUM range expands automatically when rows are inserted inside the range, but not when data is added at its edge. Inserting a title row pushed the original rows down but kept the range at F2:F60. Adding the new employee at row 60 placed that value on the boundary, so it was not included in F2:F60.
Replace it with one of these:
- =SUM(F2:F61) if the data now ends at row 61. Simple, but it will break again the next time a row is added at the bottom.
- =SUM(F:F), which covers the whole column. SUM ignores text such as the header, so this is safe here, though whole-column references are poor practice in very large workbooks.
- Convert the range to an Excel Table (Insert > Table) and use =SUM(Table1[Overtime]). Structured references expand automatically as rows are added, and the totals row can be enabled from Table Design.
The table approach is the most robust because it removes the manual range maintenance that caused the problem. Also check the two-decimal formatting: F displays rounded values but SUM adds the unrounded stored values, so the visible column may not add up to the visible total. If payroll needs line-by-line accuracy, wrap the formula in ROUND: =ROUND(D2E2$H$1,2).