There are many different methods to import data into Excel! However; it is still commonplace to cut and paste data from the data source (normally another spreadsheet) into Excel.
There are several different methods to import data into Excel. Use of these methods depends on where the data is coming from and how data is used.
These methods will be compared based on business scenarios. This post will not go into depth on how to get data from the systems.
VBA is not being considered in this post!
Alternative automated methods for importing data are being suggested.
VBA solutions are more challenging to use and maintain. VBA should only be used if all other solutions fail!
The Scenarios for the Import into Excel
- A financial analyst has been asked to prepare a quick ad-hoc variance analysis based on a small number of transactions taken from another spreadsheet.
- An accounting manager is looking to aggregate daily transactions for a month that are stored in spreadsheets with a common format.
- An operations analyst is looking for transaction trends against the company database, which contains millions of transactions.
- A business analyst is getting user acceptance testing feedback from a large number of users on a new piece of software.
- A project manager has created a “suggestion box” for new ideas that can be implemented. Team members need to be notified when a suggestion is made.
Scenario 1 – The Quick Analysis
Recommendation: Cut and Paste
When a quick analysis is required, the fastest method can be the best method. In this case, it is just cutting and pasting the data.
To accomplish this, select all the data (using Ctrl-A) and paste from the data spreadsheet into the new spreadsheet.
Some best practices when cutting and pasting include:
- Make sure that the data is in a table. In order to do this, select any cell in the pasted data and click Ctrl-T to turn the data into a table.
- Where possible, use different tabs for data and analysis.
- If the import process starts to become a recurring process, consider using Power Query instead.
Scenario 2 – Multiple Spreadsheets
Recommendation: Power Query / Get and Transform
When there are multiple spreadsheets that need to be copied as a single data source, the best method is Power Query.
By using Power Query, as long as the data is in the same format for each file, a single data model can be created.
This significantly reduces the amount of time required to import the files (vs. the cut and paste method!)
Once the data is imported into Excel, it can be either copied to a table in the workbook if visibility is required for the data, or it can be kept in the data model.
If it is kept in the data model, it will require cube functions or pivot tables in order to be used.
Scenario 3 – Large Data Sets
Recommendation: Power Query / Get and Transform (or, Power BI).
When working with large data sets, cutting and pasting is not recommended or impossible (if the data set has over 1,048,576 rows).
All data from the import must be stored in the data model, and can only be accessed using pivot tables or cube formulas.
Scenario 4 – User Acceptance Testing
In many cases, this sort of testing is done through email. This is the wrong approach. An email like the one below is sent to all users.
Results from these emails are aggregated on a testing spreadsheet like the one below:
By using a form instead of an email, it provides users with a significantly cleaner experience for the users as opposed to sending emails to a distribution list.
Results are automatically tabulated into an Excel table, so there is no need to re-key information from emails!
Scenario 5 – The Suggestion Box
Recommendation: Power Apps / Automate
When there is a case that steps need to be taken other than just logging data into the spreadsheet, a Power Automate workflow can be an effective way to meet all requirements.
In the case of the suggestion box, the results are added to a list in SharePoint.
Instead of copying all results (like with Forms), some conditional logic can be applied before the results are logged into an Excel spreadsheet.
Additional steps, such as emailing key team members can be taken as part of the workflow.
There are many ways to import data into Excel. The examples that were provided in this post show some of the cases that I have seen for using specific tools.
However, my recommendations may not make sense in some cases.
- If a data set is large, but high interactivity is required, the data model may not meet requirements.
- If users are still using Office 2010, then they may not have Power Query.
- Power Automate is limited to accessing files on OneDrive / SharePoint.