How to Use AI for Spreadsheets and Data Entry

Spreadsheets are where work gets stuck. The data exists, somewhere. It is buried in a PDF, split across forwarded emails, or pasted from a table that arrived as a wall of undifferentiated text. Getting it into the right shape has historically required either patience, power query experience, or a colleague you owe a favor. AI tools have closed most of that gap, provided you give them a clear instruction.

The basic workflow

The method is the same regardless of source format:

1. Name the columns you want
2. Describe any formatting rules (date format, currency symbol, blank vs. zero for missing values)
3. Provide the source (paste text, or upload a file)
4. Ask the AI to return a table
5. Paste the result into your sheet and verify

That last step is not optional. AI reads document structure well and makes arithmetic errors rarely, but “rarely” is not “never.” Spot-check every total and every date before the data feeds anything downstream.

Turning a PDF into a spreadsheet

The workflow turn a PDF’s data into a spreadsheet covers this end-to-end, but the core idea is simple: upload the PDF to a vision-capable AI and write a prompt like this one.

“Extract all line items from this invoice as a table. Columns: Date, Vendor, Description, Quantity, Unit Price, Total. Use ISO date format (YYYY-MM-DD). If a field is blank, leave the cell empty. Do not sum rows.”

The “do not sum rows” instruction earns its place. AI tools occasionally add a totals row without being asked, which then gets pasted into the sheet and silently double-counts.

For a native approach that does not involve copying from a chat window: Microsoft 365 includes a “Data from Picture” option in Excel (Insert > Data > Data from Picture) that works on screenshots or phone photos of tables. Google Sheets imports structured PDFs via File > Import. Both are faster for simple, well-formed tables; AI handles irregular layouts and PDFs with multiple tables on one page better than either.

Working with receipts and pasted text

Receipts are a good test case precisely because they are consistently inconsistent. Different vendors format them differently, amounts appear in different positions, and many arrive as email text rather than clean files.

The approach from how to automate everyday tasks with AI applies here: paste the receipt text and describe the rule. A prompt that works reliably:

“Here are several receipts pasted as plain text. For each one, extract: Vendor, Date (YYYY-MM-DD), Total Amount (number only, no currency symbol), Category (Travel / Meals / Software / Office Supplies / Other). Return one row per receipt as a markdown table.”

Paste the result into your sheet. Google Sheets parses a markdown table pasted directly into a cell. In Excel, paste into a text editor first, save as .csv, then import.

One pitfall worth knowing: AI sometimes splits a single receipt into two rows when the format breaks across a page. Scan for duplicate vendors on the same date before the sheet goes anywhere useful.

Excel and Google Sheets: where AI fits in

Excel

Microsoft 365 has Copilot built in (paid add-on) for natural-language operations: cleaning columns, suggesting formulas, generating pivot tables, all from inside the application with direct access to named tables.

Without Copilot, the standard path is: extract data via ChatGPT or Claude, paste into the sheet, use Excel’s own tools (flash fill, power query, data validation) to clean and structure it.

Excel’s formula engine is faster than AI for calculations. If you need to sum a column, apply a conditional format, or run a VLOOKUP, write the formula. AI is most useful at the extraction and reformatting layer, not the calculation layer. Knowing where one ends and the other begins saves a lot of prompting.

Google Sheets

Google Workspace includes Gemini in Sheets (Business and Enterprise plans) for natural-language column cleaning, value extraction, and formula suggestions in a side panel.

Free-tier users use the same paste-from-AI approach. Sheets is particularly forgiving about pasted formats: markdown tables, TSV, and CSV all parse reasonably well.

One Sheets advantage for AI workflows: the ImportHTML and ImportXML functions pull data from a public web page directly into a cell range with no AI required. If the source is a table on a website, that is usually faster than pasting through a chat window.

Common pitfalls

Numbers and currency

AI output sometimes includes currency symbols inside number cells, which breaks formulas downstream. Ask explicitly for numbers without symbols and format the column as currency in the sheet afterward.

Date formats

AI defaults to the date format implied by context or training data, which may not match your system locale. Specify the format in the prompt. Asking for YYYY-MM-DD gets consistent results that import correctly in both Excel and Sheets, regardless of where the source document originated.

Missing rows

When processing a long document, AI occasionally drops rows near page breaks or where a table continues across two pages. After pasting, check the row count against the source. This takes thirty seconds and has saved hours.

Merged cells

PDFs with merged header cells often produce garbled output. If a table has merged cells (a single “Q1 2025” spanning three month columns, for example), describe the intended structure explicitly in the prompt. The AI cannot see the merge; you have to explain it.

When a formula is faster than AI

AI adds friction when the task is purely computational. Clean data in a sheet, needing a sum, an average, a conditional, a VLOOKUP: write the formula. Excel and Sheets both have built-in formula help, and asking an AI to write or explain a formula (rather than to process data) is usually the most efficient use of the tool anyway.

The split: AI for getting data into the sheet; formulas for working with data once it is there.

A short comparison table

Task	Best tool
Extract rows from a PDF	AI (vision model) or Excel Data from Picture
Clean inconsistent text in a column	AI or Sheets Gemini / Excel Copilot
Sum, average, conditional logic	Formula
Pull data from a public web table	Sheets ImportHTML
Categorize a list of transactions	AI (prompt with a fixed category list)
Generate a pivot table	Excel Copilot / Gemini in Sheets or do it manually

Where to go next

For handling data at volume or setting up a repeatable extraction step, how to automate everyday tasks with AI covers scheduling and repeating these workflows without doing them by hand each time.

For a broader look at what AI tools are worth using day to day, see the best AI productivity tools.