How to Merge Multiple Spreadsheets in Excel

By Chris Hoke

i Jupiterimages/Brand X Pictures/Getty Images

Microsoft Excel is a spreadsheet program that allows users to easily manipulate and analyze data using formulas and tables. A spreadsheet is a grid of cells which are arranged in numbered or lettered rows and columns that organize data. Data from multiple spreadsheets can be merged into a single spreadsheet by using the "Consolidate" function. This allows you to distribute copies of a spreadsheet to multiple users, and then later the data can be collected and combined into a master spreadsheet.

Step 1

Open the Microsoft Excel program by double-clicking the "Microsoft Excel" icon on the desktop or clicking the "Start" button, choosing "All Programs", then "Microsoft Office", then clicking on "Microsoft Excel".

Step 2

Open the spreadsheets that contain the data that you want to merge together by clicking the "File" menu at the top of the program window, then choosing "Open". Choose the first spreadsheet you want to open by clicking on it, then click the "Open" button. Repeat this process to open multiple spreadsheets. Each spreadsheet should be set up with column and row headers (values in the top row and left row) that create a table of data. The column and row headers can be different values in each spreadsheet but must be present in order for each workbook to merge the data properly. Data in a cell with the same column and row headers will be added together in this example.

Step 3

Open a new spreadsheet by clicking on "New" icon at the top of the program window.

Step 4

Click the "Data" menu at the top of the program window and choose the "Consolidate" option. The Consolidate dialog window will open.

Step 5

Click on the name of the first spreadsheet in the taskbar at the bottom of the screen. Use your mouse to select the upper left cell and drag down to the lower right cell, making sure that your selection includes the headers and all data that you want to merge. It is alright if the selection is larger than just your data, as empty cells will not be merged. Click the "Add" button in the Consolidate dialog box.

Step 6

Repeat step 5 for each spreadsheet that contains data that you want to merge. Select "Sum" from the Function drop-down menu. Click to put a check in the boxes for "Use Labels" in "Top Row" and "Left Column".

Step 7

Click the "OK" button to consolidate the data, which will then appear in the new spreadsheet you opened in step 3. Click the "File" menu and choose "Save" to save the document.