Converting And Importing PDF Data Into Excel
Did you know that you now have the option of converting and importing PDF data into Excel? If you are running Excel through an Office 365 or Microsoft 365 subscription, this feature is now available to you. In this tip, you will learn just how easy it is to import PDF data into your Excel workbooks.
The Role Of Excel's Power Query Feature
Microsoft added Power Query to Excel with the 2010 release of the application. Since introducing this tool, Microsoft has continually enhanced Power Query with new features, including the option of converting and importing PDF data into Excel.
You can access Power Query from the Data tab of the Ribbon in Excel. There, on the left side, you will notice a group of icons labeled Get and Transform Data. Collectively, these icons constitute Power Query. Using these tools, you can quickly and easily import data into your Excel workbooks. You can also add transformations that data to make it more useful to you. Examples of these include the following:
- Adding sorts and filters your data
- Splitting columns and joining columns
- Removing blank rows
- Pivoting and unpivoting data
- Adding calculations to your data
Importantly, each of these transformations – and so many more you can add – automatically execute each time you refresh your data query. Therefore, you do not have to perform these tasks manually each time your data changes. Obviously, Power Query can help save you tremendous amounts of time. Further, Power Query can improve accuracy by executing these transformations automatically every time you refresh your queries.
Converting And Importing PDF Data Into Excel
One of Power Query’s features is the ability to assist in converting and importing PDF data into Excel. To do this, execute the following steps.
- On the Data tab of the Ribbon, click Get Data followed by From File. Next, choose From PDF. Figure 1 below illustrates the initial steps of this process.
2. In the ensuing dialog box, select the PDF file you want to import into Excel.
3. Finally, Power Query will present you with a dialog box allowing you to choose the table or page from your PDF to import into Excel. Upon selecting the data you wish to import, click Load near the lower, right corner of the dialog box, as shown below in Figure 2. Note that if you need to add transformations to your data before loading it into Excel, you could click Transform Data instead of Load to initiate that process.
4. Upon clicking Load, Power Query completes the process of converting and importing PDF data into Excel, as shown below in Figure 3.
Summary
The amazing functionality of Power Query continues to expand. Microsoft routinely adds new features to this incredibly powerful tool. Now you can use Power Query for converting and importing PDF data into Excel. So, don’t keypunch the data, but rather, use Excel’s Power Query feature to do the heavy lifting for you!
You can learn more about Power Query in general from the following Microsoft page: https://docs.microsoft.com/en-us/power-query/power-query-what-is-power-query. You can also learn more about using Power Query in K2’s Advanced Excel Data Magic seminar; learn more by clicking here or visiting https://www.k2e.com/seminars/advanced-excel-data-magic/.
We encourage you to view the video presentation of this tip presented below.
