Posey's Tips & Tricks
Exploring OCR, a New Way To Get Data into Excel
Microsoft recently added a new optical character recognition feature to Excel that lets users import data from a photograph taken from a smartphone. Here's how to use it.
I first started using Microsoft Office way back in the 1990s. As you can imagine, Office applications such as Word, Excel and PowerPoint have changed a lot since then.
But even though old versions of Excel -- such as Excel 95 -- are barely recognizable compared to Excel 2019, there is one thing that hasn't changed: The basic way that you enter data into an Excel spreadsheet today is almost identical to the way it was done 25 years ago. However, that may be about to change.
While I don't expect Microsoft to discontinue its support for normal data entry into Excel, Microsoft recently introduced native optical character recognition (OCR) capabilities into Excel that can be used to import data from a photograph.
Right now, this capability only exists in mobile versions of Excel (iOS and Android). The basic idea is that you can use the device's camera to take a photograph of a document, such as a receipt, and then use OCR to extract data from the photograph. That data can then be read into an Excel spreadsheet.
The process used for performing this type of data import couldn't be easier. Begin by opening Excel on your mobile device and then tap on the Insert Data From Picture icon. It's the icon that looks like a camera on top of a spreadsheet. Tapping this icon causes Excel to open the device's camera. At this point, you can take a picture of the data that you want to import.
One of the nice things about this process is that Excel draws a red rectangle around the data. Excel will attempt to import any data located inside of this rectangle. You can adjust the rectangle's size to include additional data or to exclude data from being imported. Once the rectangle is in the correct position, snap a photograph to capture the data.
With this process, Microsoft is not only acknowledging the potential for errors to occur, it also helps you identify and correct those errors. Before the data is added to the spreadsheet, Excel will tell you which items might be problematic and therefore need to be reviewed. Those items are displayed in red cells. For each issue, you can either tap Edit if you need to correct the data, or you can tap Ignore if the data is correct. Once you have reviewed the data, just tap Insert and Excel will place the data into a spreadsheet. You can see how the process works here.
As previously mentioned, the desktop version of Excel lacks the ability to import data from a photograph. I don't know if Microsoft is planning to add this capability later on, or if it plans to only support photographic data import on mobile platforms. Regardless, there are some workarounds if you want to import data into the desktop version of Excel.
One workaround is to use your phone to capture the data and store it in a spreadsheet. After doing so, you can save the spreadsheet to OneDrive, or you can e-mail it to yourself. In either case, you can then open the spreadsheet on your desktop.
Another workaround is to use one of the many online services to perform OCR from outside of Excel. The nice thing about this approach is that it does not limit you to only capturing data using a camera. You could conceivably make a screen-capture of any data source, save the screen-capture as an image file, then extract the data and import it into Excel.
One of the tools that you can use to accomplish this is Easy PDF. This is a Web-based tool, which means that you don't have to install anything to use it. It can perform OCR on an image and then export the data to Word, Excel or to a text file. Best of all, the service is both free and anonymous.
Another potential option is Convertio, another free online tool for performing OCR on an image file. This tool can output data to Excel or to any of several other formats. It also supports languages other than English.
Regardless of whether you use one of these free solutions or a commercial product, there are numerous ways to get OCR data into Excel.
About the Author
Brien Posey is a 22-time Microsoft MVP with decades of IT experience. As a freelance writer, Posey has written thousands of articles and contributed to several dozen books on a wide variety of IT topics. Prior to going freelance, Posey was a CIO for a national chain of hospitals and health care facilities. He has also served as a network administrator for some of the country's largest insurance companies and for the Department of Defense at Fort Knox. In addition to his continued work in IT, Posey has spent the last several years actively training as a commercial scientist-astronaut candidate in preparation to fly on a mission to study polar mesospheric clouds from space. You can follow his spaceflight training on his Web site.