Excel is the most widely used application in most work places for record keeping and data analysis. It is also used for preparing data before the data is uploaded into any larger data management software used by medium or bigger organizations. In any case, Excel does have it’s role in the process of records or data management in an organization.
As part of the data or records management process, Excel 2016 and upward versions have built in features which allows us, the users to bring in data from several sources into a workbook file to process it. It even makes it easier to maintain the connection with the data sources so whenever there is any updates on the source files, the data can quickly update and reflect those changes in the master file. One source to bring in data is from related files in a folder. Here is how this is done.
Use of pre-designed Excel worksheets or templates.
First and foremost to prepare source data is to use a pre-designed worksheet template. It is a standard practice in many organizations to prepare the data in Excel using a worksheet template. The reason is to maintain consistency in the different fields (columns) of data in an Excel worksheet. Templates can be used by several staff and then the files can be collated together and final aggregate data analysis can be done. Here is how data sets in template Excel files in a folder can be combined together for analysis.
1. Use a Excel Template worksheet
It is important that Excel files that needs to be combined together exists in some form of templates worksheets. This means, having the same column/field names and number of columns. This maintains the consistency and easier for Excel to combine the data.
2. Put all the files in the same folder.
All the files that need to be combined has to be in one single folder. This will make it easier for Excel to process the data but also later in terms of connecting to the folder and updating the data.
3. Bring the data into the Master Workbook file.
- Start a new workbook or Excel file. This will be the combined data master file.
- Click Data Tab and Under Get & Transform command group, click New Query.
- Click From File and click from From Folder. This will open up the Browse dialog box.
4: Locate the folder that contains the files and click Open. Excel will locate the files and display the Load dialog box. From this dialog box, click Combine drop down menu at the bottom right corner of the window and select Combine & Load To option.
- From the Combine Files dialog box, select one worksheet only. Click OK.
- From the Load To dialog box, leave the Table option selected and click Load
- Excel will load the data onto a New Worksheet.
That’s how files in the folder can be loaded into a single workbook for analysis.
