How To Create Access Database From Multiple Excel Spreadsheets

Try This, It Works

How to Link Excel Data across Multiple Sheets

Excel has an excellent, underutilized way to take data from a master sheet and capture subsets of that data on other sheets. In this way, Excel can act as a functional database by using a component called Microsoft Query.

Let's say that I have a master sheet of all technical support questions posed in 2013, with a column called Assigned To.

Now I want three separate sheets, one for each support rep in the Assigned To column, that will only contain that person's support tickets. But in addition to that, I need the following to be possible:

New data added to the master sheet will automatically populate on the corresponding subset sheets.
The master sheet can be sorted and filtered itself without affecting the subset sheets.
Functions and charts analyzing data on any sheet should automatically adjust for new data.

Sound difficult? It's not!

The steps below explain how to set this up using Excel 2007 or later.

(This feature is also available in prior versions of Excel.)

Create all the desired sheets in the workbook. Here I created a sheet for each support rep.
Create a named range for your master table. Highlight the data in your table (including headings), type a name for the range in the Name Box to the left of the formula bar, then hit Enter. The named range is required for Microsoft Query to pick the data to analyze.
Create a Table out of your master table. Highlight your data according to the steps above. ClickInsert tab. Click Table. This step is a little redundant, but making your data into a Table has tons of benefits, the primary one here being that when you add new data to it, it automatically expands to capture the new data. In turn, this will also automatically expand your named range.
Navigate to the sheet where you want the query results to go. This could be another worksheet or even another workbook.
Click Data tab. Click From Other Sources. Choose From Microsoft Query. Choose Data Source window displays.
Select Excel Files. Click OK. Select Workbook window displays.
Find and choose the workbook to query. Note that it can be the same workbook as the one you have currently open. Click OK. Query Wizard displays.
Your named ranges should appear on the left-hand side of the window as a table. I will choose all columns from the left to show on my subset sheets. Click Next.
On the Filter Data step of the wizard, choose to filter for the data you want to appear on that sheet. Here I am making Jerry's sheet so I only want data where Assigned To equals Jerry. Click Next.
Follow the remaining steps in the wizard until you are ready to choose the destination for your data. In the Import Data dialog make sure Table in chosen as how your data will be pasted. I choose to paste the data in A1 on the Jerry sheet.
Since you pasted the data as a table, you have the special ability to Refresh if you make any changes to the master table. You can find Refresh on the Data tab or under the Table Tools Design tab.