LLexfolio

Tag: Excel for office work

  • 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.

  • Common Excel Mistakes at Work and AI Myths: What Goes Wrong and the Correct Fix

    Last updated: 12 November 2026

    Short answer: Most spreadsheet disasters in an office come from a handful of habits: a lookup that quietly returns the wrong row, cleanup commands that delete data permanently, numbers that are really text, and a file that behaves differently on a colleague’s older version of Excel. Adding an AI assistant helps with drafting formulas and summaries, but it does not remove these risks and it adds new ones about accuracy, licences and confidential data. This guide goes through the common mistakes and myths, and gives the correct position for each, based on Microsoft’s own documentation.

    Build the habits behind clean spreadsheets. Excel and AI for Office Work (Rs 299) is a self-paced certificate course on using Excel, and AI tools alongside it, for everyday office tasks.

    Mistake 1: Leaving VLOOKUP on its default setting

    Microsoft’s documentation for VLOOKUP says that if you leave out the last argument, the function uses an approximate match (TRUE), and that this is the default method. With approximate matching the first column must be sorted, or the formula can return a wrong value without any error. A clerk who types =VLOOKUP(A2,Rates!A:C,3) may get a plausible but incorrect rate for an employee code that is not in the table.

    Correct position: add FALSE (or 0) as the fourth argument when you need an exact match, or use XLOOKUP, which returns exact matches by default, can look to the left of the key column, and lets you set a message to show when nothing is found. Test with a code that you know is missing and check that you see your message and not a nearby value.

    Mistake 2: Assuming XLOOKUP works on every computer

    Microsoft states that XLOOKUP is available in Excel for Microsoft 365, Excel 2024, Excel 2021 and the Excel apps for tablets and phones, and that it is not available in Excel 2016 and Excel 2019. A workbook built in a newer version may contain XLOOKUP formulas that an older version cannot calculate. In an office where some desks still run Excel 2019, that means errors on the colleague’s screen but not on yours.

    Correct position: before sharing a file with a department, ask what version people use. If anyone is on 2016 or 2019, use INDEX with MATCH, or VLOOKUP with FALSE, in the shared file.

    Mistake 3: Running Remove Duplicates on the only copy

    Microsoft warns that removing duplicates permanently deletes the duplicate values, unlike filtering, which only hides them. It also notes that matching is based on what is displayed, so the same date shown in two formats counts as two different values; that data is removed from all columns of a row, not only the column you checked; and that the first occurrence is kept. The advice is to copy the original range to another sheet or workbook first and to test with a filter for unique values.

    Correct position: keep an untouched raw sheet. Do your cleaning on a copy. If the result is wrong, Ctrl+Z may work immediately, but it will not help after you have saved, closed and mailed the file.

    Mistake 4: Trusting TRIM to clean all spaces

    TRIM removes extra spaces and leaves single spaces between words, but Microsoft’s page says it does not remove the non-breaking space (character 32 is the ordinary space; the non-breaking space is character 160) that often arrives when you copy from a web page or some software exports. A name that looks identical in two sheets will then fail to match in a lookup. The page shows TRIM combined with CLEAN, as in =TRIM(CLEAN(B5)), for general cleaning, though a column copied from a web page may still need the non-breaking space replaced separately.

    Correct position: when two values look equal and still do not match, compare their lengths with LEN. A difference tells you there is an invisible character.

    Mistake 5: Storing long identifiers as numbers

    Excel keeps 15 digits of precision for numbers, according to Microsoft’s specifications page. A 16 digit card or account number typed into a cell formatted as a number will have its last digit changed to zero. Codes with leading zeros, such as 00123, also lose the zeros when they are treated as numbers.

    Correct position: format the column as Text before typing or pasting identifiers, or import them as text. Never use such a column in calculations.

    Mistake 6: Building a PivotTable on messy source data

    Microsoft’s guidance says PivotTable source data should be clean, tabular and organised in columns, with a single header row of unique, non-blank labels, no double header rows and no merged cells, preferably formatted as an Excel table. It also says that after adding data to the source you must refresh the PivotTable, and that value fields default to Sum for numbers but to Count for text, so a column of numbers stored as text will be counted and not added.

    Correct position: fix the source first. If a total looks suspiciously like the number of rows, check whether the column holds text.

    Mistake 7: Mixing up SUMIF and SUMIFS arguments

    In SUMIFS the sum range comes first, then pairs of criteria ranges and criteria. In SUMIF the sum range comes third. Microsoft says every criteria range must have the same number of rows and columns as the sum range, and that a result of 0 often means text criteria were not enclosed in quotation marks.

    Correct position: check one total by hand. Filter the table to one region and one month and compare the filtered total with the formula result.

    Myth: “The AI assistant is built into every copy of Excel”

    Microsoft’s Copilot in Excel page says Copilot may not be included in your Microsoft 365 subscription and that your organisation’s settings may restrict it, so you should check your licence or ask your IT administrator. It works on Windows, Mac and the web in edit, chat and plan modes, while phones support chat mode only. If your office has no licence, the Copilot icon will not be there, however confidently a colleague describes it.

    Myth: “AI output in a spreadsheet is always right”

    Microsoft describes edit mode as the default mode in which Copilot can change your workbook directly, and says you can stop it at any time and that plan mode proposes an approach for you to confirm first. That is a design for supervision. A generated formula can be syntactically valid and still point at the wrong column. Treat anything an assistant writes as a draft from a trainee: ask it to explain the formula, test it on three rows you can calculate by hand, and only then fill it down.

    Myth: “Pasting client or employee data into any chatbot is harmless”

    It is not. Two points apply. First, tools differ in what they do with your data, and Microsoft’s own PivotTable page notes that its PivotTable recommendation feature uses AI services as part of its connected experiences and that, if you opt out of those experiences, the feature is unavailable. Read the privacy terms of every tool you use, and use only tools that your employer has approved. Second, India’s Digital Personal Data Protection Act, 2023 and the DPDP Rules, 2025 were notified on 13 November 2025, with the main obligations on data fiduciaries phasing in until 12 May 2027, according to a law firm’s summary of the rollout. A payroll or customer sheet containing names, phone numbers and bank details is personal data. Remove or mask the personal columns before you use an outside tool, and follow your employer’s confidentiality rules, which apply today.

    Myth: “If I use AI, I do not need to learn formulas”

    An assistant is quickest for people who can already tell whether the answer is plausible. Knowing what a lookup, a SUMIFS and a PivotTable do lets you ask a precise question, spot an error and fix it. People who only paste results forward cannot defend their own report when a manager asks where a number came from.

    A short pre-send checklist

    1. Is the raw data saved on its own sheet?
    2. Do lookups use exact match, and did you test a missing key?
    3. Do totals agree with one figure calculated by hand?
    4. Are identifiers stored as text?
    5. Will everyone who opens the file have the functions you used?
    6. Did you remove personal data before using any outside tool?

    Practise these skills properly. 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

    Is XLOOKUP better than VLOOKUP?

    For most new work, yes: it matches exactly by default, can return values from columns to the left of the key and has a built in not-found message. VLOOKUP is still needed when you must share files with people on Excel 2016 or 2019, where XLOOKUP is not available.

    How many rows can an Excel sheet hold?

    Microsoft’s specifications give 1,048,576 rows and 16,384 columns per worksheet.

    Does Remove Duplicates have an undo after saving?

    Microsoft says deletion is permanent and advises copying the original data first. Ctrl+Z works only in the session where you ran it, so keep a backup copy.

    Can I use an AI assistant for confidential office data?

    Only with tools and settings approved by your employer, and with personal and client data removed or masked wherever possible. Check the tool’s privacy terms.

    Why does my lookup fail when the values look the same?

    Common causes are extra spaces, a non-breaking space that TRIM does not remove, numbers stored as text in one sheet and as numbers in the other, or an approximate match setting.

    This article is general information, not 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.