Important: 'Enable all macros' in ExcelPreferencesSecurity if you want to merge files that contain VBA code. There is a bug in Mac Excel that not trust if a.
![](/uploads/1/2/7/3/127364654/623466712.jpg)
Sometimes the Microsoft Excel data you need is split across multiple sheets or even multiple files. It can be significantly more convenient to have all of this information in the same document.
In a pinch, it’s possible to copy and paste the various cells you require, placing them all in the same sheet. However, depending on how much data you’re working with, this might end up taking a lot of time and effort.
Instead, consider some of the smarter ways to accomplish the same task. These methods might just allow you to skip some of the busywork when it comes to merging sheets or files in Excel.
How to Merge Sheets in Excel
Excel makes it easy to combine more than one sheet into a new workbook:
- Open the sheets you want to merge.
- Click Home > Format > Move or Copy Sheet.
- Use the dropdown menu to select (new book).
- Click OK.
Merge Excel Sheets Into One File
The easiest way to merge sheets in Excel is by using the Move or Copy Sheet command. This method has its limitations, but it’s quick and straightforward.
First, open up the sheets you want to merge into the same workbook. From there:
- Head to Home
- Click Format
- Select Move or Copy Sheet
You should see controls for where to move the selected sheets and the order for those sheets.
Use the dropdown to select (new book). This will serve as the master spreadsheet where you send all of your individual sheets. You can use the Before sheet box to specify the order that the sheets are in.
Repeat this process with the rest of the sheets you want to merge. Then save your new master document.
For splitting a large Excel spreadsheet into separate files How to Split a Huge CSV Excel Spreadsheet Into Separate FilesOne of Microsoft Excel's shortcomings is the limited size of a spreadsheet. If you need to make your Excel file smaller or split a large CSV file, read on! Read More , check out our tutorial.
Merge Excel Data Into One Sheet
Sometimes, you might want to take more than one dataset and present it as a single sheet. This is pretty easy to accomplish in Excel, so long as you take the time to ensure that your data is formatted properly ahead of time.
There are two important conditions for this process to work correctly. First, the sheets that you’re consolidating need to use exactly the same layout Use Custom Views in Excel to Save Specific Worksheet LayoutsExcel's Custom View setting makes it easy to view specific information on a crowded spreadsheet or to create different layouts for your data. Read More , with identical headers and types of data. Second, there can’t be any blank rows or columns.
When you’ve arranged your data to those specifications, create a new worksheet. It’s possible to run the consolidation procedure in an existing sheet where there’s already data, but it’s easier not to.
In this new sheet, head to the Data tab and click Consolidate. Select Sum from the dropdown and then use the button in the Reference field to access your spreadsheet so you can select the data you need.
To add the next dataset, click Add and then select the data in the same way. Do this for all the datasets that you want to merge. You can even draw from other workbooks by using the Browse button, which is Select in the Mac version of Excel.
Check the box titled Create links to source data if you’re going to continue to update the data in other sheets and want this sheet to reflect that. You can also select which labels are carried across with the Use labels in checkboxes, as shown above.
Finally, click OK.
Unfortunately, this process isn’t suitable if you want to merge cells with text in them—it only works with numerical data. In that situation, you’ll need to use VBA. Take a look at our Excel VBA programming tutorial for beginners The Excel VBA Programming Tutorial for BeginnersVBA is a Microsoft Office power tool. You can use it to automate tasks with macros, set triggers, and lots more. We'll introduce you to Excel visual basic programming with a simple project. Read More if you like.
Combine Excel Files With VBA
If you want to combine sheets from several workbooks in a snap, the best way is to write a simple VBA macro. This will come in especially handy if you perform this task on a regular basis.
First, make sure that all the files you want to combine are in the same folder on your computer. Then, create a new Excel spreadsheet that will bring them all together.
Head to the Developer tab and select Visual Basic. Click Insert > Module.
Copy and paste the following code, taken from a guide by ExtendOffice:
Make sure to change the path to wherever the files are stored on your computer.
Next, save your workbook as an XLSM file so that macros are enabled. Then run the macro and you should find that you have a single workbook that contains all the sheets from all the files in the folder.
Take Caution Before Merging Excel Data
![Sources Sources](http://brainplusiqs.com/uploads/106384/vba-excel-consolidator-merge-or-combine-multiple-excel-files.jpg)
Merging sheets and files in Excel can be rather complicated and messy. This illuminates one of the most important lessons about Microsoft Excel: it’s always good to plan ahead.
Merging different data sets after the fact is always going to cause a few headaches, especially if you’re working with large spreadsheets that have been in use for a long time. When you start working with a new workbook, it’s best to consider all possibilities of how the file will be used further down the line.
Excel is great at making documents that you can refer to and use for an extended period of time, but decisions made early on can cause or prevent problems later. For more help with Excel, take a look at how to freeze or lock Excel data How to Freeze, Unfreeze, and Lock Rows, Columns, and Cells in Microsoft ExcelOne small Excel feature that makes it easier to manage data is the ability to freeze rows and columns. Let's see how it works. Read More and our beginner’s guide to Microsoft Excel The Beginner's Guide to Microsoft ExcelUse this beginner’s guide to start your experience with Microsoft Excel. The basic spreadsheet tips here will help you start learning Excel on your own. Read More .
You expect Excel for Mac 2011 to open Excel files, of course, but the program can do more than that. You can actually open, work on, and save a file in several formats. Choose File→Save As and then click Format to open the pop-up menu. Excel can open and save in the formats listed in this Format pop-up menu.
You have several options for file formats besides Excel’s default (.xlsx) format:
-
Excel Template (.xltx): Saves the workbook as a template, which you can open in the My Templates section of the Excel Workbook Gallery. You can also open templates by choosing File→Open and selecting Excel Templates from the Enable pop-up menu. This format does not have macros and is another open XML format.
-
Excel Macro-Enabled Workbook (.xlsm): Workbooks in this XML format contain Visual Basic for Applications (VBA) programming language code, or Excel 4.0 macro code. When opening this format file, Excel displays a prompt asking whether you want to remove the macros contained in the file. The default is Disable Macros. You must instead click Enable Macros if you want macros to run.
-
Excel Macro-Enabled Template (.xltm): The same as .xlsm, except this is a template. The macro warning dialog displays when you open a workbook in this format, and you must click Enable Macros if you want macros to run.
-
Excel Add-In (.xlam): Saves a macro-enabled workbook as an Excel add-in.
-
Excel Binary Workbook (.xlsb): This is the most compact file format, and is recommended for large files. This format doesn’t conform to open source standards.
-
Comma Separated Values (.csv): Saves a worksheet data table as text separated by commas. This format is text only. All other content is discarded.
-
Web Page (.htm): Save the workbook in a format that Web browsers understand and can display. Excel creates a file in HTML format, along with a supporting folder. Upload both the file and folder to a Web server if you want to share your workbook via the Internet.
Excel can open and save Web pages in HTML (HyperText Markup Language) format and do great things with Web tables and data from the Web, but don’t uninstall your HTML (Web page) code editor. Excel can’t replace that.
![](/uploads/1/2/7/3/127364654/623466712.jpg)