Last updated: 12 November 2026
Short answer: Spreadsheet problems at work usually look small, a mismatched name, a missing row, a total that is a little off, and then become big when a report goes to a manager or a client. The six illustrations below are invented to show typical office situations, what usually goes wrong and the safest way to handle each. They also show where an AI assistant helps and where it needs supervision.
Practise on situations like these. Excel and AI for Office Work (Rs 299) is a self-paced certificate course on using Excel and AI tools for common office jobs.
Illustration 1: The attendance sheet that will not add up
Situation (illustration). An HR executive imports attendance from a biometric machine into Excel. The count of present days for one employee is far too low, even though the employee was in office all month.
What probably went wrong. The machine exports the status as text, and some cells carry a trailing space, so “P ” is not equal to “P” in a COUNTIF. Another possibility is that the dates arrive as text and do not sort properly.
What to do. Add a helper column with =TRIM(CLEAN(C2)) and count on that column. Compare LEN of the raw and cleaned cell to see where hidden characters exist. If a non-breaking space is the cause, TRIM alone will not remove it, because Microsoft’s documentation says TRIM does not remove character 160. Test the final count against a manual count for one employee before applying it to the whole sheet.
Illustration 2: The account name that does not match
Situation (illustration). An accounts assistant compares a vendor list with a payment list using a lookup. About one name in ten returns #N/A, though the names look identical.
What probably went wrong. Differences in spacing, spelling, or a stray non-printing character. It can also be a mismatch between numbers stored as text in one list and as numbers in the other, for vendor codes.
What to do. Clean both lists into helper columns, make sure both key columns have the same data type, and use an exact-match lookup. In XLOOKUP, give the fourth argument a text like “Check name” so that missing matches are visible; Microsoft says that without it the result is #N/A. Review the flagged rows by hand and correct the source, not just the report. An AI assistant can suggest fuzzy matching ideas, but it should not be left to decide that two different vendors are the same.
Illustration 3: A sales report that quietly double counts
Situation (illustration). A sales coordinator sends a monthly report showing revenue 6 per cent higher than the billing software’s total. The manager spots the difference in a meeting.
What probably went wrong. The export was run twice on one day and some invoices appear twice. Alternatively, the PivotTable was built over a range that included a subtotal row.
What to do. Compare the row count and grand total with the software’s summary before building anything. Flag duplicates with conditional formatting on Invoice No, check them, then use Remove Duplicates on a copy, ticking only the invoice column. Microsoft notes that the command removes data permanently and from all columns of the row. Keep a notes cell recording rows received, removed and used. For future months, keep the data as an Excel table, and refresh the PivotTable after pasting new rows.
Illustration 4: The file that breaks on an older computer
Situation (illustration). A manager builds a stock sheet with XLOOKUP on a Microsoft 365 laptop and mails it to a branch office. The branch staff see errors in the lookup column.
What probably went wrong. The branch uses Excel 2019. Microsoft states that XLOOKUP is not available in Excel 2016 and Excel 2019, and that a workbook created in a newer version can contain XLOOKUP formulas that older versions cannot calculate.
What to do. Ask which version each recipient uses before sharing. For mixed environments, rewrite the lookup with INDEX and MATCH, or VLOOKUP with FALSE as the last argument. Paste values over formulas for a final report that only needs reading, and keep the formula version for yourself.
Illustration 5: Payroll data pasted into a public chatbot
Situation (illustration). A junior accountant, short of time, copies a payroll sheet with names, bank account numbers and salaries into a public chatbot and asks it to find errors.
Position. Employee names, bank details and salaries are personal data. India’s Digital Personal Data Protection Act, 2023 and the DPDP Rules, 2025 were notified on 13 November 2025, and a law firm’s summary of the rollout says most substantive obligations, including those on notice, consent and security safeguards, take effect on 12 May 2027. Even before then, employment contracts and company confidentiality policies usually restrict sharing such data with outside parties.
What to do. Do not paste identifiable payroll data into any tool your employer has not approved. If you need help with a formula, replace names and account numbers with codes or dummy values, or describe the structure of the sheet without the data. Report the earlier paste to your manager or the person responsible for data protection rather than hiding it.
Illustration 6: A manager wants a summary in an hour
Situation (illustration). A department head asks for a one page summary of three months of expense data before a review meeting.
What to do. Convert each month into the same table layout, combine them into one table with a Month column, and build a PivotTable by head of expense and month. Where your licence includes Copilot in Excel, you can ask it to summarise the data, point out outliers, or draft a chart, and Microsoft says you can use chat mode to get insights without changing the workbook. Verify every figure that appears in the summary against the PivotTable. Copy only checked figures into your one page note, and mention any assumptions, for example that a missing invoice was excluded.
Habits that prevent most of these problems
- Keep the raw data untouched and do all cleaning on a copy.
- Reconcile a total to the source system before you format or present anything.
- Write down what you removed or changed, with counts, so that a manager’s question can be answered in a minute.
- Agree with colleagues on the Excel version and the approved tools before building shared files.
- Ask any assistant to explain its formula, then test it on a few rows you can verify yourself.
None of these habits needs advanced skills. They need discipline, which is why they are worth practising on small files before a high pressure deadline arrives.
Patterns across the illustrations
| Problem | Usual cause | First check |
|---|---|---|
| Counts or totals too low | Text with hidden spaces or numbers stored as text | LEN of raw and cleaned cell |
| #N/A in lookups | Different spellings, types or an approximate match | Exact match and a not-found message |
| Revenue too high | Duplicate rows or a subtotal inside the data range | Row count and grand total against the source |
| Errors only on one computer | Function not available in that Excel version | Ask for the version before sharing |
| Worry about confidentiality | Personal data in a tool not approved by the employer | Mask or remove personal columns |
Build confidence before the next deadline. Excel and AI for Office Work (Rs 299) is a self-paced certificate course on Excel and AI tools for office work, with a certificate on completion.
Frequently asked questions
Are the illustrations real cases?
No. The people and numbers are invented. They are built from common office situations to show method.
How do I find hidden characters in a cell?
Compare LEN of the original and of TRIM(CLEAN(cell)). A difference means something invisible is present. A non-breaking space needs separate handling because TRIM does not remove it.
Is it safe to ask an AI assistant to fix my formulas?
It is safe when you use an approved tool, remove personal data, and test the result on rows you can calculate by hand. It is unsafe to accept the output without checking.
What should I do if my report was wrong and already sent?
Tell the recipient promptly, send a corrected version, and note what changed. Then add a check to your process so the same error cannot recur.
Which Excel version do I need for PivotTables and SUMIFS?
Both have been in Excel for many versions, including Excel 2016 and Excel 2019. Only newer functions such as XLOOKUP need Excel 2021, Excel 2024 or Microsoft 365.
This article is general information, not legal or other professional advice for any specific case. Lexfolio is a learning resource. Laws, rates and procedures change, so check the current rule or consult a qualified professional before you act.