ICAEW.com works better with JavaScript enabled.
Exclusive

Excel updates

Excel updates – Data From Picture

Author: Simon Hurst

Published: 07 Jul 2022

Exclusive content
Access to our exclusive resources is for specific groups of students and subscribers.
Simon Hurst describes a new feature of Excel, which uses a picture as a data source.

Although Excel updates often add new data sources to the Excel Data Ribbon tab, Get & Transform Data group, a recent update for the Beta Channel added a different type of data source – pictures. When the update arrives for your edition of Excel, a new ‘From Picture’ dropdown will appear in the ribbon. This includes the options to use a ‘Picture From File…’ or a ‘Picture From Clipboard’. In either case, Excel will read the picture in and analyse it in order to separate it out into rows and columns. It then presents the results to the user to review it before inserting it into an Excel worksheet:

In this initial example, we are looking at a training video in a web browser and have decided it would be useful to use the on-screen example to practice on:

Excel screenshot

The new Excel feature works best if the picture is cropped to only show the content that we need. This can be done directly using a dedicated screen capture utility, or by editing a more general screenshot using a graphics editing program such as Microsoft Paint. For our example, we have used a screen capture program to copy just the area we need to the clipboard. We can then use From Picture, Picture From Clipboard to import our data into Excel:

Excel screenshot

The Data from Picture pane appears, and, after the analysis process, our picture appears in the top half of the pane, with the translation of our picture into rows and columns beneath. Where Excel is unsure about the content, the cell is highlighted. Pressing the Review button will go through each review cell in turn to be corrected if necessary. It is also possible to click on any cell, even if it is not highlighted, in order to make a correction. The Insert Data button will insert the data into the worksheet at any point, but will display a warning if the Review process is incomplete:

Excel screenshot

Even if all the marked cells have been reviewed, Excel will still remind you that ‘You are responsible for validating the accuracy of all data.’ before the insertion:

Excel screenshot

As you can see below, the feature worked well with our very simple table of data:

Excel screenshot

The Picture From File… option works in the same way, but allows you to browse to find a saved picture file before launching the process described above.

Whichever of the two options you use, for the import to work, Excel does need a recognisable table of data to work with, and any stray characters or marks are likely to cause inaccuracies so, as the warning message suggests, it will be very important to check the imported data very carefully.

There will often be better alternatives to acquiring the data than using From Picture. Many scanners will include software utilities that can use Optical Character Recognition (OCR) and many OCR apps are available for mobile devices. Ideally the data could be linked to directly or copied directly, so for a web page for example, a straightforward copy and paste might work better than capturing a screen shot. Although the new option is perhaps most likely to be a last resort, there may well be some circumstances for which it is a quick and easy way to get formatted data into Excel as in our training video example.