Last updated: 12 November 2026
Short answer: To turn a messy monthly export into a reliable report in Excel, work in this order: keep a raw copy, convert the data to a table, clean text and duplicates on a copy, validate what goes in, bring in rates or names with a lookup, check totals with SUMIFS, then summarise with a PivotTable and a chart. Use an AI assistant for drafting formulas and wording, but check each result against figures you can verify by hand. The steps below use an invented sales sheet as the example.
Turn this routine into a skill you can show. Excel and AI for Office Work (Rs 299) is a self-paced certificate course on Excel and AI tools for everyday office tasks such as cleaning data and reporting.
The example we will use
Imagine a small distributor in Lucknow whose billing software exports a sheet every month with these columns: Invoice No, Date, Customer, City, Zone, Product Code, Quantity, Amount. The Customer column has stray spaces, a few invoices appear twice because the export was run twice on the same day, and the rate list sits in a separate file. This is a typical office situation, and the same method suits attendance sheets, fee registers and stock lists.
Step 1: Protect the original
Save the export under a name such as Sales_Oct2026_RAW and never edit it. Copy the sheet into a working file. Microsoft’s own advice before removing duplicates is to copy the original range to another sheet or workbook first, because the command deletes data permanently. Cleaning mistakes cost nothing when you can start again from the raw copy.
Step 2: Convert the range to a table
Click inside the data and press Ctrl+T, or use Insert, then Table. Tick the box saying the table has headers. Microsoft recommends giving PivotTable source data one header row of unique, non-blank labels, no merged cells and, ideally, an Excel table format. A table also grows automatically when you paste next month’s rows beneath it, so later formulas and PivotTables stay in step. Give the table a clear name such as Sales on the Table Design tab.
Step 3: Clean the text
Add a helper column called Customer_Clean and enter:
=TRIM(CLEAN([@Customer]))
TRIM removes extra spaces and CLEAN removes non-printing characters, as shown in Microsoft’s TRIM documentation. If a value still refuses to match, it may contain a non-breaking space (character 160), which TRIM does not remove. Compare the results of LEN on the original and the cleaned cell. If an AI assistant is available, you can describe the problem in words and ask for a formula, for example to replace character 160 with a normal space, but run it on a few rows first.
If you need to split or recombine text, such as separating a first name from a last name, Flash Fill can do it. Type the first result by hand, start the second, and press Enter to accept the preview, or press Ctrl+E. Microsoft lists Flash Fill as available in Excel for Microsoft 365 and in Excel 2016 and later versions. Always scan the filled column for rows where the pattern broke.
Step 4: Find and remove duplicates carefully
First look, then delete. Use conditional formatting to highlight duplicate Invoice Numbers, or filter for unique values, to confirm what Excel considers a duplicate. Remember Microsoft’s note that matching depends on displayed values, so two dates in different formats count as different. Once satisfied, choose Data, Remove Duplicates, and tick only Invoice No. Ticking every column would keep two rows for the same invoice if one of them has a different spelling in the Customer column.
Write the number of rows before and after in a notes cell. A report that says “212 rows received, 9 duplicate invoices removed, 203 rows used” is far easier to defend.
Step 5: Stop bad data entering again
Select the City column, then use Data, Data Validation. On the Settings tab choose List, point to your list of cities, and set an error alert message on the Error Alert tab. Microsoft’s documentation describes this sequence, including the drop-down list option. Data validation is most useful where several people type into the same sheet.
Step 6: Bring in rates with a lookup
Suppose the rate list is a table named Rates with columns Code and Rate. To fetch the rate for each row, enter:
=XLOOKUP([@[Product Code]],Rates[Code],Rates[Rate],"Rate missing")
XLOOKUP matches exactly by default, and the fourth argument is the text shown when no match is found. Because it is not available in Excel 2016 or Excel 2019, use VLOOKUP with FALSE as the last argument, or INDEX with MATCH, if anyone who will open the file runs those versions. Then filter the column for “Rate missing”. Every such row is a data problem to resolve with the billing team, not a number to guess.
Step 7: Check totals with SUMIFS
On a separate sheet, build a check table. For example, for the total quantity of one product code in the North zone:
=SUMIFS(Sales[Quantity],Sales[Product Code],"P101",Sales[Zone],"North")
The sum range comes first in SUMIFS, and every criteria range must be the same size as the sum range. A result of zero often means a text criterion was written without quotation marks. Compare the grand total of Amount with the figure on the billing software’s own summary screen. If the two do not agree, find out why before you go further.
Step 8: Summarise with a PivotTable
Click inside the table and choose Insert, then PivotTable. Drag City to Rows and Amount to Values; Microsoft notes that numeric fields in Values default to Sum, and text fields to Count. Use the Show Values As tab to display each city as a percentage of the grand total. When you add next month’s rows to the table, right-click the PivotTable and choose Refresh. Forgetting to refresh is the most common reason a PivotTable shows old numbers.
Add a chart from the PivotTable and give it a title that states the finding, for example “Lucknow contributes the largest share of October sales”, only if the numbers support it.
Step 9: Use an AI assistant as a reviewer, not as the author
Depending on your licence and your employer’s policy, Copilot in Excel can generate formulas, summarise data, build charts and PivotTables, and highlight outliers. Microsoft says that it may not be included in your subscription and may be limited by organisation settings. Where it is available:
- use plan mode or chat mode when you want to see a proposal before anything in the workbook changes;
- ask it to explain every formula in plain words;
- test the output on three rows that you can compute by hand;
- never include columns with phone numbers, bank details or similar personal data unless your employer permits it;
- record in your notes which parts were drafted with assistance and checked by you.
Step 10: Hand over a file that explains itself
- A Notes sheet listing the source, the date, the rows received and used, and what was cleaned.
- Clear sheet names: RAW, CLEAN, CHECKS, REPORT.
- A last-updated date on the report sheet.
- A version number in the file name, not words such as final2.
Want guided practice? Excel and AI for Office Work (Rs 299) is a self-paced certificate course on Excel and AI tools for office work, finishing with a certificate.
Frequently asked questions
What is the quickest way to turn a range into a table?
Click any cell in the range and press Ctrl+T, then confirm that the table has headers. Excel then treats the data as one named table that grows when you add rows.
Why does my PivotTable show last month’s numbers?
The PivotTable has not been refreshed. Right-click it and choose Refresh, or use Refresh All on the PivotTable Analyze tab when you have several.
Should I use Remove Duplicates or a filter?
Use a filter or conditional formatting to look first. Remove Duplicates deletes rows permanently, so use it only on a working copy.
Can I use these steps on Excel 2019?
Mostly yes, but not XLOOKUP. Use VLOOKUP with exact match or INDEX with MATCH. Flash Fill, PivotTables, SUMIFS and data validation are all available.
Do I need Copilot to follow this guide?
No. Every step works with ordinary Excel features. An assistant can speed up drafting, but the checks in steps 4 and 7 are what make the report reliable.
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
- Bare acts of the new criminal laws (BNS, BNSS, BSA)
- Code of Civil Procedure bare act
- Legal drafting and conveyancing books
- Constitution of India (coat pocket edition)
As an Amazon Associate, Lexfolio earns from qualifying purchases. Prices and availability are shown on Amazon.
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.
Leave a Reply