Opening
About 1 minutesLet's look at a scenarioFictional example, numbers are illustrative
Chen Wei needs to merge expense details sent by various stores every month.
The formats vary widely: some dates are written as 'March 5', others as '2026-03-05', and the amount field sometimes includes the character 'yuan'.
Organizing the data takes over an hour, and she also needs to reconcile the payment table with the invoice table every month.
Methods and Templates
About 4 minutesStep 1: Provide a sample for it to write formulas
Suggested timeI use [your spreadsheet software]. Below is a header and a few rows of sample data for a fictional small table. Columns: A Store, B Date, C Expense Type, D Amount (number, in RMB) Store A 2026-03-05 Transportation 120 Store B 2026-03-06 Meals 86 Store A 2026-03-08 Transportation 45 I want to calculate the total transportation expenses for Store A in cell F2. Please provide the formula, explain it step by step, and tell me what issues arise with empty cells or text-formatted numbers.
Step 2: Format and Summarize
Suggested timeBelow is sample data with inconsistent date and amount formats. Please: 1. Provide steps to standardize dates to 'YYYY-MM-DD' and indicate which rows have special formatting that requires manual review. 2. Remove the 'RMB' character from amounts and convert them to numbers. 3. Create a summary table showing total amounts by store. 4. Specify your calculation logic: which rows were excluded and why. 5. Finally, list three row numbers for your spot check and provide the expected results for those rows. Use only the provided content; write 'TBD' for missing data, and do not fabricate. Data: [Paste fictional or anonymized small table]
Step 3: Spot Check, Manual Calculation of at Least Three RowsFictional example, numbers are illustrative
Its Summary
Store A Transportation Total (Sample)
Your Manual Calculation
Row 2 (120) plus Row 4 (45) matches the result
Step 4: Reconcile Two Tables, Output Three Categories of Results
Suggested timeBelow are two fictional tables: Payment Table and Invoice Table. Do not modify any data. Reconcile by 'Order ID' and output three categories: 1. Present in both with matching amounts: List order IDs. 2. Present in both with differing amounts: List order IDs and the amounts from both tables. 3. Present in only one table: List order IDs appearing only in the Payment Table and those appearing only in the Invoice Table. Finally, state the count of rows in each category and your reconciliation logic. Payment Table (Order ID / Store / Amount): P101 Store A 1200; P102 Store B 860; P103 Store A 450; P104 Store C 300; P105 Store B 90 Invoice Table (Order ID / Amount): P101 1200; P102 806; P103 450; P106 520; P107 75
Industry-specific Practice and Verification
About 3 minutesIndustry Practice Exercise: Categorize Comments by Topic
Suggested timeBelow are six fictional public comments. Categorize them by topic (Battery Life, Heat, Camera, Other), provide the count for each topic, and include representative quotes with their numbers. 1. Battery doesn't last a full day. 2. Photos are clear, and night shots are good too. 3. Gets hot after half an hour of gaming. 4. One charge lasts two days, satisfied. 5. Battery life is worse than the previous generation. 6. System occasionally lags.
Pre-submission Checklist
Quiz and Submission
About 2 minutesThree quick questions to check yourself
Question 1: Why check at least three rows instead of just one?
Next month, you'll only update the data.
Process, logic, spot-checking, and reconciliation.
- Document the spreadsheet workflow in no more than 300 words.
- Define the calculation logic clearly, specifying which rows are included and which are excluded.
- Spot-check records for three manually calculated rows
- Three types of results for two-table reconciliation, with two rows verified
Show the full transcript (video narration)
Spreadsheets and Data Organization
Welcome to Module P08 of the AI Implementation Course. This lesson covers spreadsheets and data organization. It can help you organize messy tables, write formulas, create initial summaries, and reconcile two tables. However, when it makes a calculation error, its tone is exactly the same as when it is correct. Therefore, the key focus of this lesson is learning how to spot-check.
What you will take away from this lesson
This lesson covers only organization and calculation, not interpretation. Analyzing trends and explaining why changes occurred are outside the scope of this lesson. After completing this lesson, you will take away four things. First, a spreadsheet organization workflow, specifying the software you use, the meaning of each column, rules for standardizing formats, and spot-checking methods. Second, a calculation scope for one summary, clearly stating which rows are included and which are excluded. Third, three types of results for two-table reconciliation, with two rows verified. Fourth, spot-check records for three manually calculated rows. When next month's data arrives, only replace the data; keep everything else the same.
Let's look at a scenario
Let's look at a fictional scenario, with illustrative numbers. Finance Specialist Chen Wei needs to merge expense details sent by various stores every month. The formats vary widely: some dates are written as March 5, others as another standard format, and the amount field sometimes includes the character yuan. Organizing the data takes over an hour, and she also needs to reconcile the payment table with the invoice table every month. Dragon Warrior excels at writing formulas, standardizing formats, and creating summaries. However, when it makes a calculation error, its tone is exactly the same as when it is correct. Therefore, this lesson is divided into two parts: the first half teaches how it can help you, and the second half teaches how you can spot-check.
Step 1: Provide a sample for it to write formulas
Step 1: Provide the header and three to five rows of sample data, not the entire table. Specify the spreadsheet software you are using and describe the meaning of each column. Formulas often fail because columns are not clearly defined. In your prompt, ask it to explain the formula step by step and highlight issues with empty cells and text-formatted numbers. A reminder here: software features and function support depend on your current version. This course was verified in November 2026; details may vary, so please verify again yourself. If the formula returns an error, paste the exact error message to it for explanation and correction.
Step 2: Format and Summarize
Step 2: Format and Summarize. Ask it to standardize dates, remove 'RMB' from amounts, sum by store, and specify the calculation logic (i.e., which rows were excluded and why). Finally, ask it to list three row numbers for your spot check and provide the expected results. Note the last point: use only the provided content, write 'TBD' for missing data, and do not fabricate. Whenever it modifies the format, ask it to list which rows were changed. Keep the original table unchanged; perform operations on a copy.
Step 3: Spot Check, Manual Calculation of at Least Three Rows
Step 3: Spot Check. This is the most critical step in this course. Randomly select at least three rows, calculate them manually using a calculator, and compare with its results. Using the sample data, it reports Store A's transportation total as 165. Your manual calculation of Row 2 (120) plus Row 4 (45) also equals 165, which matches. If it shows 175, it means some rows were duplicated or calculated incorrectly, so review its calculation logic. Only apply to the entire table if all three rows are correct; if one is incorrect, return to Step 2.
Step 4: Reconcile Two Tables, Output Three Categories of Results
Step 4: Reconcile Two Tables. Ask it to output three categories by Order ID: present in both with matching amounts; present in both with differing amounts; present in only one table. In the sample data, it should yield: 2 matching rows, 1 row with differing amounts, and 4 rows present in only one table. Verify the counts: 5 + 5, minus 3 rows present in both, equals 7. The sum of the three categories is also 7, which matches. For the row with differing amounts, the two numbers differ by only one digit, likely due to a data entry error (e.g., swapped digits). Verify against original vouchers; do not let it modify the data directly.
Industry Practice Exercise: Categorize Comments by Topic
Industry Practice Exercise. The data is fictional and can be pasted directly. R&D or product managers often need to categorize public comments by topic and output the count for each category. Note two things: 1. Categorize by topic, not by sentiment. Comment 4 is positive, but since it discusses battery life, it should be categorized under Battery Life. 2. Perform the counting yourself. Count them manually and compare with its count. Its count often has discrepancies, which is why human sampling is necessary for topic counting. If comments contain user personal information, remove it first.
Pre-submission Checklist
Before submission, review this checklist: Verify that the original table is unchanged and all operations are on a copy. Confirm that the total row count matches the original, with no missing or duplicate rows. Ensure text-formatted numbers, empty cells, and merged cells are handled. Verify that dates are recognized as dates, not text. Manually calculate at least three rows and confirm consistency. For reimbursement or payment amounts, have the finance manager and company policies review them, rather than relying on the AI's conclusion. Since its tone does not change when it makes errors, this checklist is designed to catch those mistakes.
Quiz
Let's take a quick quiz. Why should you spot-check at least three lines instead of just one? A: A single line might coincidentally be correct, but multiple lines can expose systemic errors in the logic or formulas. B: Three lines are the minimum number required by the software. C: The more you spot-check, the more time you save. The answer is A. A single line might coincidentally be correct, but only multiple lines can expose systemic errors in the logic or formulas.
Next month, you'll only update the data.
This lesson has four deliverables: a spreadsheet workflow document of no more than 300 words; a clear definition of the calculation logic, specifying included and excluded rows; a spot-check record of three manually calculated lines; and a reconciliation of the two statements covering three types of results, with two lines verified. Once complete, save the entire workflow. Next month, when the new data arrives, you will only update the data and keep everything else the same. In the next lesson, we will move on to reports, proposals, and presentations. Thank you for watching. The voiceover for this video is AI-generated.