Scan the table OCR and do data pivot: from picture to analysis table

Key points of this article

Before recognition, straighten the image, crop it and organize it into PDF, which can reduce misaligned rows and columns later.
OCR only completes the first step. The recognition results must be checked against the original image. The content recognized by the machine cannot be directly used for analysis.
Before pivoting, delete total rows, convert textual numbers, and complete merged cells, otherwise the summary results will be biased.
Comparing the sum of the amount column with the subtotal of the original table is a quick way to find identification errors.

Many people have a paper sales form, a scanned reimbursement form, or an inventory list taken with their mobile phone. They want to make summary statistics, but find that they are just pictures and cannot be sorted or summed. Converting pictures into text Excel tables is a common way to solve this problem: first identify the text and numbers in the picture, turn it into a table, and then use a pivot table to make statistics by region, month or category.

This article breaks down the entire link: from taking photos, scanning, to OCR recognition, to Excel cleaning, and finally doing data perspective analysis. After reading, you will get a set of procedures that can be followed, and you will know what mistakes are likely to occur and how to check each step.

OCR is only responsible for the first step. The subsequent cleaning and analysis determine whether the results can be used, so I will spend more time talking about these two paragraphs below.

How to turn a picture table into an analysis table? One sentence conclusion

The table in the picture is first converted into editable tabular data using OCR, the numbers are checked column by column, the format is unified into a format that Excel can recognize, and finally the pivot table is inserted. If you skip the verification and go straight to fluoroscopy, the results often look normal, but are actually skewed.

your situation	Recommended practices	reason
Table taken with mobile phone, with perspective distortion or shadows	First take the photo, crop it, then organize it into PDF and then recognize it.	Skewing and shading can cause text to run or appear missing.
The scanned document is clear and the number of table rows is small.	Directly export to Excel after OCR recognition, and check the entire table	The process is short, eliminating the need for manual entry
Table spans multiple pages	Identify page by page, merge into the same table and unify column names	Pivot tables require data areas to be continuous
There are merged cells in the header	Cancel the merge and complete the header to each column	Merging cells will cause misalignment of field names
Amount with comma in units or thousandths	First remove the symbols, convert them to numerical values and then analyze	Textual numbers cannot participate in summation

Scenario: A scanned monthly sales table to be summarized by region

Suppose you are an administrative or sales assistant in a small company. You have a 12-page monthly sales breakdown in your hand, which is a PDF scanned from a paper form; the other is a handwritten photo taken at the store, a total of 3 photos. The boss requested to calculate the amount by region and product category and provide monthly trends.

There are three stuck points: first, the pictures cannot be filtered and summed directly; second, the column order and header writing of the two sources are different; third, the original table has merged cells and subtotal rows, and direct pivoting will cause repeated calculations. These issues need to be resolved after OCR and before perspective.

Complete process

Make sure the form is complete when taking photos or scanning. When taking photos, try to face the form directly, avoid reflections and shadows, and have all four corners in the mirror; the text on the scanned copy must be clear and legible.
Organize the pictures into PDF. Multiple photos are combined into one PDF in page number order, with the month or source included in the file name for easy checking back.
Use OmniDoc to recognize text in scanned documents. Open the PDF in the OmniDoc desktop client (download.omnidoc.top), use the OCR function to recognize text, and convert the table content into selectable and editable text.
Export to Excel. After the recognition is completed, export the PDF to Excel format and check page by page to confirm that each row of data falls on the same row.
Clean up table headers and formats. Merge the column names from the two sources, delete duplicate headers, blank rows and subtotal rows, convert the amount columns into numerical values, and unify the date columns into the same writing method.
Complete merged cells. If the region or category of the original table is only written once, it needs to be filled downwards so that each row contains complete classification information.
Insert Pivot Table. Select the entire data area including the table header, insert the pivot table, put "Region" in the row, "Month" in the column, and "Amount" in the value, and confirm that the summary method of the value is summation.
Check and save the results. Extract a few rows and compare them with the original picture to confirm the numbers; then check whether the pivot table total is consistent with the original table total, and save or deliver after confirmation.

Where things can easily go wrong

Number recognition error. Handwriting, small font sizes, and blurry 3’s and 8’s, and 0’s and 6’s can easily be confused. Discovery method: Sum the amount column and compare it with the subtotal of the original table. If the difference is obvious, go back to the original picture and find the corresponding row. Solution: Only modify the wrong cells, do not replace the entire column.
Text numbers cannot be summed. Cells with a green triangle in the upper left corner, aligned to the left, or with unit and thousandth commas may be regarded as text. Processing method: Remove the units and commas, convert to numerical values, and then perform perspective.
Subtotal rows are counted twice. If the "Total" and "Subtotal" rows of the original table are left in the data, the total number of the pivot table will be doubled. How to find it: Filter the region column to see if there is the word "Total". Solution: Delete these rows, or put them in another table separately.
Cross-page table line breaks. When one line is divided into two pages, it will become two lines after recognition, and the amount will be misaligned. Processing method: Check the number of rows by page and merge the disconnected rows back into one row.
Pivot table fields are misplaced. When there are merged cells or empty columns in the header, the field name will become a default name such as "Column 1". Solution: Cancel the merge, complete the table header, and then re-insert the pivot table.

The results of machine recognition need to be checked manually, and all important figures must be based on the original image.

🎯 Which method is suitable for you?

If you only have one or two clear scanned pages

Directly use OCR to identify and then export to Excel. Just check the entire table once, and the process is short.

If you want to summarize multiple tables from different sources

First unify the column names and formats, then merge them into a general table, and finally perform pivoting. Don't rush to analyze before merging.

If the form is handwritten or photographed

Focus on steps 1 and 6: spend more time correcting the photos, completing the classification information, and comparing the original pictures one by one.

If it is just a one-time small statistics

When the number of rows is small, manual calculation after checking can also be done; when the number of rows is large and needs to be updated repeatedly, it is more cost-effective to create a pivot table template.

❓ Frequently Asked Questions (FAQ)

1. What should I do if the data is wrong after converting pictures to text Excel tables?

First find the column with the error, sum the column and compare it with the subtotal of the original table, and then return to the original picture to check row by row. Only correct the wrong cells, and then re-sum and confirm after making the corrections.

2. The pivot table is "counted" instead of "summed". How to change it?

Select the value field in the pivot table and change the summary method to sum. When this happens, the amount column is usually still text, so convert it to a numerical value first.

3. Can the scanned PDF be directly made into a pivot table?

It cannot be done directly. The scanned document must be OCR recognized and exported to Excel first, and then it can be fluoroscopyed after cleaning. If the text in the file can be selected, it is not a pure scan and can be exported directly.

4. Looking for a free image to Excel tool, what should I look for?

Check the file size and page limit, whether it will be watermarked after exporting, whether the file will be saved for a long time, and whether table lines and number formats will be retained after exporting. These items can better determine whether the results can be used directly than simply looking at "free".

📝 Summary

Converting images into text Excel tables is only the first step. What really determines the quality of analysis is the verification and cleaning after identification. The statistical results will be reliable only after sorting out the data first and then inserting it into the pivot table. Please refer to the original image for machine-recognized content, and important numbers must be reviewed manually.

Open OmniDoc and work with your spreadsheet file