Chart Image to Excel: Rebuild the Numbers Behind a Graph

A chart image to Excel conversion cannot be done by OCR alone, because a bar or a line is a shape, not text. You rebuild the numbers instead: read the two axis ticks, measure where each bar or point sits, and convert those positions to values with a simple formula. Printed data labels, when present, are the shortcut.

Why can't OCR read a chart image?

OCR, optical character recognition, finds letters and digits in a picture and turns them into text. A chart image holds little text beyond the title, axis labels and tick numbers. The values themselves live in the height of a bar or the position of a dot, and OCR has no way to turn a height into a number.

That is why a converter given a graph usually returns a handful of stray words. You might get "Monthly spend", "Jan Feb Mar Apr" and "0 500 1000 1500" in a few cells, with nothing that lines up into rows. The tool did its job. It read every character on the page. There simply were no characters for the data.

Some tools advertise that they convert a graph image to data in Excel online. Look closely at what they do. Most are digitizers: they ask you to click the axis ends and then click each point, and they do the arithmetic for you. That is useful, but it is the same method as the one below, with a mouse instead of a ruler. A few try to detect bars automatically, which works on clean, flat bar charts and struggles with gridlines, shadows and 3D effects.

A chart that sits inside a statement or report is different again from a picture of a spreadsheet. If the image shows cells and numbers, the job is ordinary OCR, and the guide to turning a screenshot of an Excel sheet back into cells covers it. If the image shows a printed table with ruled lines, see how to convert a scanned table to Excel with its columns intact. Only the bars and lines need the measuring approach.

Reading bar heights and line points against the chart axes

Reading a chart by hand means comparing each bar top or line point with the value axis, the vertical scale on most charts. You find the nearest tick marks above and below the point and estimate where between them it falls. For quick work with a few bars, that estimate is often good enough.

Start by writing down what the chart tells you outright. Copy the category labels along the bottom into column A of a new sheet. Note the unit of the value axis: dollars, thousands of dollars, percent or a count. A chart titled "Spend (in $000)" with a bar reaching 12 means 12,000, and losing that unit is the most common way a rebuilt chart ends up wrong by a factor of a thousand.

Then work bar by bar:

• Lay a straight edge, or the edge of a sheet of paper on screen, across the bar top to the value axis.
• Read the ticks either side. If the bar top sits about two thirds of the way from 400 to 600, write about 533.
• For a line chart, do the same for each marker. If the line has no markers, read it at each category label, directly above the label.

Your accuracy depends on the gridlines. A chart with a gridline every 100 lets you estimate to within about 10 or 20. A chart with ticks only every 500 gives you a rough shape and little more. Write down the precision you expect next to the values, so nobody later treats a hand reading as an exact figure.

Hand reading is quick for five bars and tedious for fifty. For anything larger, measuring pixel positions, covered in the next section, is faster and more consistent.

Calibrating chart image pixels to axis values

Calibration turns a pixel position in the chart image into a value. You pick two ticks on the value axis whose values you know, note the pixel row of each, and then every other pixel row maps to a value by straight-line proportion. Excel does the arithmetic once you have the pixel readings.

Any image viewer or editor that shows the cursor position will give you pixel coordinates. Many free editors display them in the status bar as you move the mouse. On most screens the vertical coordinate grows downward, so a taller bar has a smaller pixel number. The formula handles that for you as long as you use the same convention for every reading.

A worked example

The numbers below are an example, not readings from a real chart. A bar chart shows monthly card spending. The value axis has a tick at 0 and a tick at 2,000. Hovering over them gives these pixel rows:

Now read the pixel row at the top of each bar:

In Excel, put the two calibration rows in cells B2:C3, with pixel rows in column B and values in column C. Put the bar readings from B6 downward. The value for each bar is:

=$C$2 + (B6 - $B$2) * ($C$3 - $C$2) / ($B$3 - $B$2)

For January that is 0 + (250 − 410) × (2,000 − 0) / (90 − 410), which gives 1,000. Fill the formula down and every bar converts the same way.

Three habits keep the results honest. Pick calibration ticks far apart, because two ticks close together magnify any error in your readings. Read the bar top at its centre, not a corner, since anti-aliased edges can blur by a pixel or two. And check one bar whose value you can confirm some other way, such as a total printed in the caption, before you trust the rest.

Data labels printed on the chart image

Data labels are the numbers some charts print on or above each bar, and they change the job completely. When a chart image has labels, the values are text, so OCR can read them directly. You then only need to pair each number with its category, which is a small cleanup step rather than a measurement.

Labels make the result exact, at least to the rounding the chart used. A label reading "1.2k" carries less precision than "1,214", and you should keep whatever the chart printed rather than inventing digits. Note the rounding beside the column so a reader knows the values came from a chart.

OCR on labels has its own pitfalls:

• Labels placed inside dark bars may be white text on colour, which some engines read poorly. Inverting the image colours before OCR can help.
• Labels on a line chart can overlap where two lines cross, and the engine may merge two numbers into one.
• Small labels on a scanned page can suffer the same digit confusions as any other small print, such as 1 read as 7 or 8 read as 3.

Check the label count against the bar count. If there are twelve bars and the OCR returned eleven numbers, one label was missed or two were joined. If the chart arrived as a photo or a scan, the same scanning care that helps any page helps here, and a sheet feeder scanner for statements is only worth it if you scan many pages, not one chart.

Stacked bars, pie slices and log scales in a chart image

Some chart types break the straight-line calibration above. Stacked bars need two readings per segment, pie charts need angles instead of heights, and a logarithmic axis needs a different formula. Recognize these before you start measuring, or the rebuilt numbers will look plausible and be wrong.

Stacked bars

In a stacked bar, each coloured segment starts where the one below it ends. Read the top and bottom pixel row of every segment and convert both. The segment's value is the difference. Reading only the tops gives you running totals, not segment values, which is an easy mistake when the chart has no labels.

Pie charts

A pie slice's value is its share of 360 degrees. Measuring angles by hand is slow and imprecise, so a pie chart without percentage labels rarely rebuilds to more than a rough split. If the slices are labelled, take the labels and check that they sum to 100 percent, allowing for rounding.

Log scales

On a logarithmic axis, the ticks go 1, 10, 100, 1,000 at equal spacing. The straight-line formula will give nonsense here. Convert each calibration value with LOG10 first, apply the same proportion to the logs, and raise 10 to the result. If the tick labels jump by powers of ten, assume a log scale and check before measuring.

Dual axes and truncated axes

Two warnings apply to every chart type. A chart with a second value axis on the right needs its own calibration for the series that uses it. And a value axis that starts at a number above zero exaggerates differences between bars, which does not change your arithmetic but does change how a reader might interpret the rebuilt table.

When should you ask for the chart's source table instead?

You should ask for the source table whenever someone who made the chart is reachable. A chart is a picture drawn from a table of numbers, and that table almost always still exists in a spreadsheet, a report system or an export. One email usually beats an hour of measuring, and the result is exact instead of estimated.

Ask first in these cases:

• The numbers will feed a decision or a filing. A measured value carries error, and that error should not reach a tax figure, a loan file or a reconciliation.
• The chart has many points. A daily line over a year has 365 values. Measuring all of them by hand is error-prone even with pixel readings.
• The chart came from a bank or card portal. Spending charts in online banking are built from the same transactions you can download as a statement. Converting the statement gives you every transaction, with dates and amounts, rather than monthly bars.

That last point is common with personal finance. A spending-by-month graph in a banking app tells you the shape, but the statement behind it tells you the detail. FastBankConvert is built to turn the statement PDF into a sheet with dates, descriptions, amounts and balances in their own columns, and it is getting ready to open; the homepage explains how to join the list.

Rebuilding from the image still makes sense when the source is gone: an old printed report, a chart in a scanned archive or a slide whose author has left. In that case, label the rebuilt sheet clearly as estimated from a chart, keep the original image beside it, and record your calibration points so anyone can check the work. If the chart also carries handwritten notes or hand-drawn bars, the limits described in what OCR can realistically do with handwritten PDF pages apply, and reading those by eye is usually the only option.