This popular software 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 its 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. We refer to Microsoft Excel, a management software based on tables and spreadsheets, belonging to the Office suite, and considered today as 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 Excel spreadsheets with other platforms (such as word processors, checklist software or even email).
However, as of the 2016 versions (and particularly with the advent of Office 365), these difficulties have been almost completely erased. This allows us today to export and import Excel spreadsheets without much difficulty, advantage 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 dialogue 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 performs the function of an ETL tool (Extract, Transform and Load) without inconveniences. In other words, it extracts, transforms and loads data with great ease. In addition, its interface is intuitive and well designed, which makes learning easy. In fact, we 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:
- Allows us to connect 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.
- Allows us to save the data in an Excel table or simply connect to it for later loading.
- If the original data changes over time, we can 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 in mind two key considerations:
We require a compatible Excel version: 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, we must follow these steps:
First of all, open Microsoft Excel and select an existing or new sheet to import the data. Then, in the options menu, click on “Data” tab. Next, go to “Get and transform data” section, which is on the left. From there, click on “Get data” section. This action will display another menu, where we 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, and we must paste the respective address of the web page we want to import.
When we click “OK”, Excel may ask us to re-confirm the data we want to load from the website. In these cases, we 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. At this point, we must select any of the numbered tables in the “Navigator”, to verify if the data has been imported correctly.
If 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 we 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.
Any data imported from a web page to Excel with this tool, it will be updated automatically. Similarly, Power Query allows us to edit our preferences to indicate how we want this process should 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 their daily business agenda, 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 very simple, but not always we can achieve professional results. 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 edited easily or quickly. This forces us to send attachments, which today are both anachronistic and inefficient.
To solve this difficulty, we can use the Cloudcodes Insertable extension, which is available in the Chrome Web Store. This tool allows us 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, we must simply install the extension and wait for it to run when we open Gmail. Next, we copy the tables directly from Excel and go to the body of the new message. There we 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 process can be achieved without losing, at any time, the original appearance of the table (including format, colors and design).
This gives our emails a real professional look, allowing us to share our work in Excel with colleagues, partners, or bosses, without open other programs or upload or download attachments.
Integrate Excel with Word
Working with tables directly in Word is a terrible and tedious task. For this reason, Microsoft has enhanced the necessary functionalities that allow us to include tables directly from Excel in a Word document, preserving the original format.
To carry out this step, we must select the cells that we want to copy from the Excel document, and then go to Edit>Copy. After this step we must return to the Word document and the with the right mouse button, select 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 in the original Excel spreadsheet. Conversely, if we only select “Paste”, the cells will not update when changes are made to the spreadsheet.
We can use the same method to copy and paste individual cells. In this case, instead of selecting “Microsoft Excel Worksheet Object”, we should select “Plain Text”. The column will have then 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 have a tool that allows us to skip these annoying extra steps, and obtain the data directly from this type of file.
To do this, we must follow these steps:
- Create a new blank sheet in Excel.
- Click on the “Data” tab.
- Click on “Get data”.
- Choose “From a file”.
- Choose the option “from a PDF”.
- 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.
- Press “Close and load”.
Integrations 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 operations 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 modern security solutions from 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.
Create an account DataScope and start now!
DataScope is the ideal tool to eliminate paper use, save time, and efficiently collect data from the field. It allows companies to streamline, organize and evaluate field work thanks to its online forms, which provide indicators in real time, 100% adaptable to any area.
With DataScope, your team can answer custom mobile forms from their phones or tablets, online or offline, through the app.
