The many integration options of Microsoft Excel

The option to import web files with Power Query
Insert excel tables in Gmail
Integrate Excel with Word
Import data from PDF files
Integrations with Excel for advanced management
Do you want us to help you use DataScope?

Microsoft Excel is, along with the other Office applications, one of the most widely used in the world. An indisputable leadership that is based on its proven efficiency and multifunctionality, advantages that are reinforced today thanks to new tools that allow simple and effective interaction with other platforms of daily use in business management.

The competitiveness and success of any company is directly related to its long-term management capacity. The achievement of this objective depends both on the skills and abilities of its human capital, and on the use of appropriate tools. Especially those that digital technology provides today.

One of these IT solutions has proven to be, over the years, practically essential. It is Microsoft Excel , management software based on tables and spreadsheets, belonging to the Office suite, and considered the most popular in the world in its category.

This condition is based on its great multiplicity of versatile tools, which allow measuring and auditing all types of data, regardless of its origin or the purpose for which it is used.

Until very recently, this successful market positioning was not always reflected in the absolute ease of integrating the data processed in Ecxel spreadsheets with other platforms (such as word processors, checklist software or email).

However, as of the 2016 versions (and particularly with the advent of Office 365), these difficulties have been almost completely remedied. This allows today to export and import Excel spreadsheets without much difficulty, which guarantees maximum integration with some of the most popular digital platforms in the world.

The option to import web files with Power Query

One of the most characteristic examples of this greater “inter-platform dialog capacity” of Microsoft Excel is the introduction (since version 2016) of the powerful Power Query tool , which allows data to be imported directly from a website .

Power Query makes it significantly easier to import data into a spreadsheet, from a variety of sources and in the most convenient format. It also allows later to clean, transform and remodel this data according to the needs of the user. And since it is an integral part of the Office suite, it is a free add-on that is regularly updated.

Its main advantage lies in the fact that it fulfills the function of an ETL ( Extract, Transform and Load ) without inconveniences. That is, it extracts, transforms and loads data with great ease. In addition, its interface is intuitive and well designed, which makes learning easy. In fact, you don’t need to know or use codes, so its use is very simple, compared to other tools such as formulas or VBA ( Visual Basic for Applications ).

Among its main characteristics we can highlight that:

It allows connecting to different sources of information such as text files, websites and databases, among others.
Transforms the obtained data based on the prerequisites of the report.
It allows you to save the data in an Excel table or simply connect to it to load it later.
If the original data changes over time, update the results obtained and published in our spreadsheets with just a simple click. This functionality is very useful in case of working with dynamic data.

To successfully import data from a web to a spreadsheet, it is important to keep two considerations in mind:

A compatible Excel version is required: In this case, Microsoft 365, Excel 2021, Excel 2019, and Excel 2016.

Data format: The data imported from the web must be embedded in an HTML table. Otherwise, Power Query won’t identify them and they can’t be imported.

To use this function correctly, the following steps must be followed:

You must first open Microsoft Excel and select an existing or new sheet to import the data. Then, in the options menu, click on the “Data” tab. Next, go to the “Get and transform data” section, which is on the left. From there, click on the “Get data” section, an action that will display another menu, where you first select “From other sources” and then “From the web”.

This step will open a new popup called “From Web”. The “URL Address” text box will appear there, where you must paste the respective address of the web page you want to import.

When you click “OK”, Excel may ask you to re-confirm the data you want to load from the website. In these cases, you must press “Connect” to confirm the operation.

Once Excel finishes loading the data, a new window called “Browser” will appear, displaying the components of the web page in the left pane. Then you must select any of the numbered tables in the “Navigator”, to verify if the data has been imported correctly.

If at this point we want to make sure we’re on the right page, we can click the “Web View” tab to get a traditional HTML preview of the page.

When we agree with the way in which the data will be imported from the selected website, we can click on the “Load to” option, to send them directly to the Excel file.

Subsequently, a window called “Import data” will appear, which will offer us to select how we want to see the data in the book. This considers the options of “Table Format”, “PivotTable Report”, “PivotChart” or “Create only the connection”. In turn, we can also indicate where we want to place the data. For example, in an existing spreadsheet or in a new spreadsheet. Once the final destination has been selected, press “Accept”.

As a result, the table will be seen in our spreadsheet, already connected to the “Queries and connections” panel located on the right. It will list all the existing file queries, so if you hover over them an information window will appear, with a preview of the data, the number of imported columns, update date and time, how they were loaded the data and its original location.

When importing data from a web page to Excel, it is updated automatically. Similarly, Power Query allows us to edit our preferences to indicate how we want this process to be carried out.

To do this, from the web-connected worksheet, click on the table and then click on the “Table Design” tab. Then, in the “External table data” section, we click on the “Update” drop-down menu. Here, three options will appear: “Update”, “Update All” and “Connection Properties”. The latter is clicked to set our preferences.

This will open a new window called “Query Properties”, where we click on the “Usage” tab, to set our update preferences.

At this point we will find the “Enable update” box checked, which guarantees that the content is constantly updated. We can also check the “Update every” box so that Excel only updates the data during a certain period. Lastly, the “Refresh on file open” option allows the data to only be refreshed when the application is started.

Once this process is finished, click on “Accept” so that our update preferences are saved.

Insert excel tables in Gmail

Another of the most common actions that Excel users perform on a daily basis, especially to transmit information between company branches, or to interact with colleagues and other professionals, is to insert tables in the body of a Gmail message.

Performing this task may seem simple, but professional results are not always obtained. In fact, usually when Excel tables are copied and pasted into Gmail they don’t appear in their original appearance, and they can’t be easily or quickly edited. This forces us to resort to sending attachments, which today are both anachronistic and inefficient.

To solve this difficulty you can use the Cloudcodes Insertable extension , which is available in the Chrome Web Store. This tool allows you to insert and edit the tables directly in the body of the email, with the same appearance that they have in the original Excel document.

To activate this functionality, simply install the extension and wait for it to run when you open Gmail. Next, we copy the tables directly from Excel and go to the body of the new message. There you press an icon that appears at the bottom of the screen so that, instantly, the table appears within the Gmail dialog box.

Once this process is done, each of the cells, texts or numbers can be edited, in case we need to make last minute changes. All this without losing, at any time, the original appearance of the table (format, colors and design).

This gives your email a more professional look, allowing you to share your work in Excel with colleagues, partners, or bosses, without having to open other programs or upload or download attachments.

Integrate Excel with Word

Working with tables directly in Word is terrible and tedious. For this reason, Microsoft has enhanced the functionalities that allow tables to be included directly from Excel in a Word document, preserving the original format.

To carry out this step, you must select the cells that you want to copy from the Excel document, and then go to Edit>Copy, and return to the Word document.

In this last location you have to select, with the right mouse button, the options Edit> Paste special> Paste link. When the “As” menu is displayed, select “Microsoft Excel Spreadsheet Object”, and then click OK.

By selecting “Paste Link”, the copied and pasted cells will be updated whenever there are changes to the original Excel spreadsheet. Conversely, if only “Paste” is selected, the cells will not update when changes are made to the spreadsheet.

The same method can be used to copy and paste individual cells. In this case, instead of selecting “Microsoft Excel Worksheet Object”, “Plain Text” is selected. In these cases, the column will have the same format as the rest of the Word document, although it can still be updated.

Import data from PDF files

On some occasions, spreadsheets can be embedded within a PDF document, which requires resorting to other applications, external or online, to import and convert them.

However, the most recent versions of Excel today have a tool that allows you to skip these annoying extra steps, and obtain the data directly from this type of file.

To do this, the following steps must be followed:

Create a new blank sheet in Excel.
Click on the “data” tab.
Click on “get data”.
Choose “from a file”.
Choose the “from a PDF” option.
Choose the PDF document from wherever it is archived on our computer.
Double click on the file name.
Click on the “Table” field and double click on it, if the preview shown is correct.
Click “Close and Load”.

Integrations with Excel for advanced management

All of these capabilities and features position Excel as the most powerful and reliable spreadsheet software on the market. Advantages that add to the possibility of working directly online, importing data from files and web applications more easily and significantly expanding the options for collaboration in real time.

For example, today it is possible to integrate Excel with the Zapier tool, which allows its users to connect with more than 1,000 other applications. This helps, among other tasks, to automatically record sales transactions on platforms such as PayPal and Stripe ; or optimize contacts in Salesforce and Highrise .

Also, thanks to synchronization through OneDrive, it is possible to update and incorporate data online in our spreadsheets, from any computer or device to which we grant access.

This extensive Excel connectivity allows, for example, to take full advantage of apps that work with checklists, such as the modern security solutions of DataScope.io . These offer, among other advantages, the option of importing and exporting data in Excel spreadsheets, in order to apply more efficient control, supervision and management processes in all the critical variables for the success of a company.

Do you want us to help you use DataScope?

If you have any questions or queries, do not hesitate to contact our customer service from our DataScope.io page . Also, whenever you need help, you can schedule a phone call for one of our specialists to show you how to implement DataScope in your operations.