How to Use Zhipu Qingyan's Advanced Data Analysis for Spreadsheets, Data Cleaning, and Charts

Business analysts and knowledge workers frequently find themselves trapped in spreadsheet friction: broken VLOOKUP formulas, irregular date strings, merged header rows, mixed numerical units, and repetitive requests for executive charts. When standard large language models (LLMs) attempt to process these spreadsheets through pure conversational text generation, they inevitably run into token limits, hallucinated arithmetic, and fabricated summaries.

Zhipu Qingyan (智谱清言), powered by the GLM architecture, bypasses these structural flaws through its Advanced Data Analysis capability (Code Interpreter). Instead of attempting mental math over tabular tokens, Zhipu Qingyan translates natural language instructions into real, executable Python scripts running in a secured cloud sandbox. Equipped with pre-installed analytical libraries such as pandas, openpyxl, matplotlib, and seaborn, it executes programmatic transformations against uploaded Excel (.xlsx, .xls) and CSV files, catches runtime errors through an automated agent loop, and produces verified numerical outputs, clean downloadable files, and presentation-ready visual charts.

Whether working through the web interface at chatglm.cn or directly inside Microsoft 365 spreadsheets via the GLM in Excel (Beta) add-in, knowledge workers can transform hours of manual data wrangling into a structured, four-phase analytical workflow.

1. The Execution Engine Behind the Curtain

To extract reliable results from Zhipu Qingyan, an analyst must understand how it operates under the hood. When you upload a spreadsheet, Zhipu Qingyan does not treat rows and columns as mere reading material.

• Sandboxed Python Runtime: Files are mounted into an isolated Linux runtime environment running Python 3.11+. The model issues tool calls using a dedicated code interpreter tool.
• Algorithmic Accuracy Over Probabilistic Guessing: In traditional chat models, asking "What is the standard deviation and 90th percentile of Column D?" forces the neural network to approximate calculations based on language probability. In Zhipu Qingyan, the model writes df['Column_D'].std() and df['Column_D'].quantile(0.90), executes the code, and reports the exact numerical return.
• Self-Correction & Agent Loop: If an initial Python script fails due to an unexpected encoding error (e.g., encountering a GBK or UTF-8-SIG byte order mark) or a data type mismatch, the interpreter captures stderr. GLM reads the traceback, modifies the script (such as adding encoding='gb18030' or coercing errors with pd.to_numeric(..., errors='coerce')), and re-executes automatically until it obtains clean results.
• Code Transparency: Knowledge workers can expand the code drawer behind every conversational response. This allows analysts to audit the exact Pandas logic, verify formula criteria, and copy the code to internal pipelines or Jupyter notebooks.

2. Phase 1: Spreadsheet Inspection and Structural Reconnaissance

The single biggest mistake analysts make with AI spreadsheet tools is issuing vague, broad instructions immediately after uploading a file (e.g., "Analyze this file and tell me what is interesting"). Complex corporate workbooks contain multi-line headers, blank separator rows, totals rows, and multi-sheet arrangements that confuse one-shot prompts.

A professional workflow always starts with structural reconnaissance.

Step-by-Step Inspection Practice

1. Upload the File: Click the attachment icon in Zhipu Qingyan and upload your .xlsx or .csv dataset.
2. Issue a Reconnaissance Prompt:

   "Read the uploaded workbook. First, list all sheet names. For the active/main sheet, display the first 5 rows (head()), total row and column dimensions (shape), column data types (dtypes), and the percentage of missing values per column. Do not make any changes yet."

3. Audit the Output: Zhipu Qingyan will write and execute a Python script inspecting the workbook schema. It will present:
   • Sheet names (e.g., ['Q3_Sales', 'Lookup_Master', 'Pivot_Summary']).
   • Detected headers versus actual data rows.
   • Data anomalies: columns intended to be numerical currency that were ingested as object (string) due to dollar or yuan symbols, commas, or notes like "N/A" and "pending".

Identifying Structural Flaws

During this inspection stage, Zhipu Qingyan typically surfaces hidden spreadsheet defects:

• Trailing Whitespace: Column names like 'Region ' that cause formula lookups to fail silently.
• Merged Cells in Excel: Multi-level headers where values in merged cells become NaN in subsequent columns when parsed into a DataFrame.
• Footer Contamination: Summary rows at the bottom (e.g., "Total Average", "Prepared by Finance") that would distort statistical calculations if not pruned.

3. Phase 2: Data Cleaning and Schema Normalization

Once the data structure is documented, analysts use Zhipu Qingyan to clean and reshape messy rows into a normalized tabular format.

Common Cleaning Scenarios and Prompt Patterns

A. Handling Mixed Formats and Non-Numeric Characters

Corporate reports frequently store amounts with symbols or shorthand abbreviations (e.g., "$1,250.00", "¥3,400", "50w", "1.2M").

• Prompt:

  "In the 'Revenue' and 'Cost' columns, remove all currency symbols ('$', '¥'), commas, and spaces. For shorthand entries ending in 'w' or 'W', multiply the preceding number by 10,000. Convert these columns to float64 and fill remaining missing values with 0. Show before-and-after sample rows."

B. Resolving Merged Headers and Blank Fillers

In many operational reports, department names are merged across multiple rows, leaving lower cells blank.

• Prompt:

  "The 'Business Unit' column contains blank cells resulting from unmerged rows. Apply forward-fill (ffill) so every record inherits its respective parent unit. Then, remove any rows where both 'Transaction_ID' and 'Amount' are empty."

C. Standardizing Inconsistent Dates

Spreadsheets aggregated across global teams often mix 2026/03/15, 15-03-2026, and Excel serial numbers (like 45366).

• Prompt:

  "Convert the 'Order_Date' column into a standardized ISO datetime format (YYYY-MM-DD). Flag any dates that fail to parse or fall outside the calendar year 2026 into a separate diagnostic list."

D. Deduplication and Entity Resolution

• Prompt:

  "Check for duplicate records based on the composite key of ['Customer_ID', 'Transaction_Date', 'Product_SKU']. Keep only the latest entry based on 'Timestamp', and tell me how many duplicate rows were removed."

Exporting Clean Data

Zhipu Qingyan saves transformed datasets to the session output storage (/mnt/session/outputs/). You can conclude cleaning by instructing:

"Save the fully cleaned dataset as cleaned_sales_q3.xlsx with clean column headers and proper number formatting, and provide a direct download link."

The model returns a clickable link allowing you to download the purified file for immediate operational use.

4. Phase 3: Generating Analytical Charts and Visualizations

After data cleaning, Zhipu Qingyan leverages Python's visualization ecosystem—primarily matplotlib and seaborn—to generate visual charts.

Unlike basic web-based chart tools that struggle with typography or multi-byte text, Zhipu Qingyan's cloud sandbox comes configured with Chinese and multilingual font support (such as Noto CJK and WenQuanYi). This prevents the dreaded "tofu box" (□□) rendering bug when generating charts with mixed English, Chinese, or specialized business terminology.

Best Practices for Designing Charts with Zhipu Qingyan

1. Specify the Dimension and Metric: Avoid asking for "a nice chart". Specify the independent variable (X-axis), dependent variable (Y-axis), aggregation method (sum, mean, median), and sort order.
2. Define Chart Hierarchy: Request annotations, target reference lines, data labels, and clean color schemes suitable for executive presentations.
3. Instruct Formatting Guidelines: Ask for high DPI rendering (dpi=300) and appropriate figure sizing so charts can be pasted directly into PowerPoint slides or memo decks.

High-Impact Visualization Prompts

Scenario 1: Pareto Distribution & Cumulative Share (Bar + Line Combo)

• Prompt:

  "Group sales by 'Product_Category'. Calculate the total revenue per category and sort descending. Create a dual-axis chart: a clean bar chart showing total revenue per category on the left Y-axis, and an overlayed cumulative percentage line on the right Y-axis with an 80% Pareto threshold line in dashed red. Add direct data labels to the bars."

Scenario 2: Monthly Run-Rate and Margin Variance (Trend Analysis)

• Prompt:

  "Resample transactions by month. Plot a line chart showing 'Gross Revenue' vs. 'Net Margin' across the past 12 months. Use shaded area fill under the Net Margin curve, format the Y-axis ticks with currency markers, and highlight the month with the lowest margin using an arrow annotation."

Scenario 3: Correlation Matrix Heatmap (Multivariate Diagnostic)

• Prompt:

  "Calculate the Pearson correlation matrix between 'Marketing_Spend', 'Impressions', 'CTR', 'Customer_Acquisition_Cost', and 'Customer_Lifetime_Value'. Generate an annotated heatmap using Seaborn with a coolwarm color palette, rounded to 2 decimal places, with angled labels on the axes."

Downloading and Refining Visuals

Once Zhipu Qingyan displays the chart image in the chat stream:

• Interactive Adjustments: You can give iterative feedback: "Rotate the X-axis labels 45 degrees, change the color palette to navy blue and muted gray, and move the legend outside the top-right corner."
• Download Formats: You can ask Zhipu Qingyan to save the chart as high-resolution PNG or vector PDF: "Export this figure as a 300 DPI PNG file named q3_revenue_pareto.png for download."

5. Integrating with Excel: The GLM in Excel (Beta) Plugin

Beyond browser-based file uploading, knowledge workers who live inside Microsoft Excel can leverage the GLM in Excel (Beta) add-in.

• In-Situ Sidebar Workflow: The AI operates in a task pane docked directly inside Microsoft 365 or Office 2021. Instead of exporting data out of Excel, uploading to a browser, downloading the cleaned file, and re-importing, the add-in performs tasks in place.
• Live Formula Generation & Repair: The add-in inspects active sheets, diagnoses formulas throwing #REF!, #VALUE!, or #N/A errors, explains the root cause (e.g., misaligned range offsets or deleted column references), and rewrites the formula while preserving downstream cell dependencies.
• Cross-Sheet Navigation and Lookups: For complex financial workbooks spanning balance sheets, income statements, and cash flows, the model recognizes cross-table keys (such as Entity_Code or SKU) and writes appropriate XLOOKUP, INDEX/MATCH, or SUMIFS formulas across multiple sheets.
• Audit Trails and Cell Highlighting: Modifications made by the plugin highlight affected ranges in the sheet, providing financial analysts and auditors with verifiable verification trails.

6. Enterprise Data Hygiene and Workflow Guardrails

To use Zhipu Qingyan safely and effectively in commercial environments, business analysts should maintain key operational guardrails:

• Pre-Upload Data Sanitization: Never upload unmasked PII (personally identifiable information), proprietary customer identification numbers, unencrypted passwords, or non-public financial credentials. Replace real names and account IDs with synthetic surrogates (User_001, Account_A) before uploading.
• Deconstruct Large Requests into Steps: Rather than requesting inspection, cleaning, pivot calculation, and five charts in a single prompt, follow the four distinct phases:
  1. Inspect structure and data types.
  2. Clean and normalize values.
  3. Compute aggregations or metrics.
  4. Render targeted visualizations. This structured approach eliminates timeout bottlenecks and keeps token context focused on the current task.
• Inspect the Generated Python Scripts: Always click to expand the underlying code block. Check for critical assumptions—such as how null values were handled (dropna() vs fillna(0)) or whether outer joins inadvertently duplicated rows.
• Respect File and Memory Limits: When dealing with multi-gigabyte transactional tables, pre-aggregate data in a local database or data warehouse before uploading representative CSV samples (typically under 20MB to 50MB) to the interactive code interpreter.

By treating Zhipu Qingyan's Advanced Data Analysis as a sandboxed, programmatic partner rather than an intuitive text generator, business analysts and knowledge workers can reliably automate repetitive spreadsheet janitorial work, conduct verifiable data diagnostics, and deliver executive-grade visual reporting in minutes.

References

• 智谱清言
• GLM in Excel（Beta）
• 预装沙箱环境
• 工具
• 工具调用