Imagine you’re holding a printed report teeming with crucial data that you need in an Excel spreadsheet. Maybe you’ve found an old journal with a historical data table, or weather records you want to digitise and make use of. You could have a screenshot of some data online, your social media analytics, or the financials of an annual report you want to reanalyse and present it awesomely in Excel.
Manually typing out each entry is a daunting task; it’s time-consuming and error prone. Let’s face it, that’s not a humane way to spend a Wednesday, whatever your job. But what if there was a way to skip the drudgery and let AI technology do the heavy lifting? And as a bonus, a way involving zero coding or do anything that feels technical.
Enter Excel’s game-changing feature: importing data from a picture. In this blog I show you why this is the tool you never knew you needed, how to easily convert your image into data in Excel in simple steps, and if you’re interested, the inner mechanics of how exactly Excel makes this work. And remember, this comes after we’ve already solved how to get data from text files…
How to Convert Picture into Excel Data
Now you can comprehend the potential efficiency and accuracy benefits of this tool, let’s get straight into how to convert your picture file into useable Excel data. Below I embed my recent quick explainer video you can follow along, then I’ll write out the steps for you. We’ll be using the “Insert Data from Picture” feature available in Excel for Microsoft 365…
STEP 1: Data Tab > ‘From Picture’
When in your Excel worksheet, make sure you have a cell selected (not an image or object). Go to the ‘Data’ tab on the Ribbon. Choose the ‘From Picture’ drop-down option, as shown in the image below.
STEP 2: Locate the Image to Use
For the source of your picture, either…
- Locate it from a file you’ve saved (e.g. JPEG or PNG image file) and ‘Open’ using your file and folder navigator, or
- Simply copy the image you want to Clipboard (e.g. Ctrl + C the image from a website) and use that.
STEP 3: Follow ‘Data from Picture’ Wizard
Excel will automatically process the image and pop up the ‘Data from Picture’ wizard / review area. You will now need to check over its attempt to extract the data for you, using the preview provided (see image below).
Review the numbers to make sure all is in order. Excel highlights in the review area anything it’s particularly uncertain about, but you must check the other areas for yourself too! Clicking in a cell of the preview area will zoom in closer, making the comparison between the image and previewed data table easier.
When you’re happy, click ‘Insert Data’, and then again on the (unnecessary) pop-up warning. This will place the data into your worksheet, saving heaps of time on data entry.
How It All Works: The Power of AI
This remarkable tool leverages recent artificial intelligence (AI) developments to detect text, numbers, and structures/spaces within an image. Using well-established Optical Character Recognition (OCR), it then converts all this visual data into digital form, ready for editing and manipulation in an Excel spreadsheet.
If you’re interested in the steps going on in the background, here’s the clever stuff behind how Excel is sifting out your numbers and letters from the image file. By combining these advanced techniques, the AI in Excel can accurately convert complex images into organised spreadsheet data, making the process fast and reliable:
1. Image Processing: The AI first enhances the image to improve clarity, adjusting contrast and brightness if necessary.
2. Segmentation: The image is divided into sections, isolating different parts like text blocks, tables, and individual cells.
3. Character Recognition: Scanning for recognisable characters and numbers then begins. Each character is identified and converted into digital text. The AI then compares the shapes of the characters to a large database of known characters to ensure accuracy.
4. Structural Analysis: For table detection, the AI looks for patterns that suggest the presence of a table, such as rows and columns formed by lines or consistent spacing. Within detected tables, individual cells are identified again by borders or spaces separating the data.
5. Context Understanding: Next, the AI analyses the context to differentiate between different types of data, such as text, numbers, dates, and currencies.
6. Pattern Recognition: Pattern recognition is then employed to understand the structure of the data. For example, recognising that a sequence of numbers in rows might be a list of figures, while text in a single column might be headers or labels.
7. Cell Mapping: The AI maps the detected data into corresponding cells in Excel. It places text and numbers into rows and columns just as they appear in the image.
8. Error Checking: The AI performs checks to ensure that the data placement makes sense, such as aligning headers with their respective data and maintaining the correct format.
9. Correction Interface: Allowing for the ‘human touch’, it presents a review screen where users can see the converted data and make any necessary corrections before finalising the import.
Now, isn’t that rather clever when it’s broken down like that?!
Beware: While this is very clever, be sure to review and edit; the AI is highly accurate but not infallible. So make sure you check things over before finalising the import and checking again once it’s there in the spreadsheet!
A Picture is Worth 1000 Cells…
You can see why Excel created this powerful capability in Excel in 2022, harnessing the growing capabilities of AI. It saves tons of admin time, gives opportunities to digitise data recorded on paper, reduces transfer errors, and increases productivity. By cutting the time spent entering data, you can spend more time analysing and interpreting the results.
Before we part ways, here’s some final tips to ensure you give Excel the best chances to gather data efficiently and accurately from your images:
- Use high quality, clear images. Avoid blurry or pixelated pictures, as these can confuse the AI as it tries to sort the wheat from the chaff.
- Consider lighting. If taking a picture of data in print to import, ensure the area is well-lit. Natural light is best, but if indoors, use sufficient artificial lighting to avoid shadows and glare.
- Use straight angles. Take the picture straight on, not at an angle. This helps the AI accurately detect lines and spacing.
- Ensure contrast. Ensure there is a good contrast between the text and the background.
Excel’s ability to import data from a picture is not just a novelty; it’s a transformative tool to enhance your efficiency, accuracy, and productivity. By leveraging AI, Excel bridges the gap between the physical and digital worlds, making data management seamless. Follow these tips to unlock the full potential of this powerful feature, turning a tedious data entry task into a swift import. At the very least, I hope it saves you some time!
Kind Regards, Adrian
I hope this guidance helps you ‘excel at the office’! Subscribe to my blog and EATO YouTube channel for more useful info. Liking the content? Please like and share others who may also find it useful. If so inclined and able, buying me a coffee helps keep free content flowing and I greatly appreciate it. Want fantastic, time-saving templates or dashboards tailored to you and your business? Then please get in touch to arrange a bespoke quote.