LLexfolio

Excel and AI in Real Office Situations: 6 Illustrated Cases and How to Handle Them

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

ProblemUsual causeFirst check
Counts or totals too lowText with hidden spaces or numbers stored as textLEN of raw and cleaned cell
#N/A in lookupsDifferent spellings, types or an approximate matchExact match and a not-found message
Revenue too highDuplicate rows or a subtotal inside the data rangeRow count and grand total against the source
Errors only on one computerFunction not available in that Excel versionAsk for the version before sharing
Worry about confidentialityPersonal data in a tool not approved by the employerMask 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.

Useful books for this topic

As an Amazon Associate, Lexfolio earns from qualifying purchases. Prices and availability are shown on Amazon.

Free CLAT PG 2027 sample: exam guide + 2 judgment passages

Get our 16 page CLAT PG starter free: the 2027 pattern, how to read judgment passages fast, the 25 key Supreme Court judgments of 2026, and two practice passages with 10 explained questions. Plus new free guides and law updates by email. Unsubscribe any time.

Learn this properly, with a certificate. Police and FIRs, consumer and bank fraud complaints, property, wills and inheritance, work rights, family law, cyber fraud and RTI, in plain English. Certificate Course in Everyday Law for Every Indian, ₹349: self-paced lessons, module quizzes and a final test; pass at 60% for a verifiable Lexfolio certificate.

See all Lexfolio certificate courses

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *