How do I merge data from multiple sheets in Excel without losing anything?
Merging data from multiple Excel sheets can sound and seem complicated, but it doesn’t have to be.
If you are looking for an approach that is simple and involves no complicated formulas or heavy duty Excel tools, then I’m here to help.
This simplistic approach offers a clear process that minimises stress and prioritises accuracy.
Subscribe to my productivity eNewsletter for more tips and insights to make using Microsoft 365 programs easier. Click here.
Step 1: Create a Main Worksheet
- Open a new Excel file (or a new worksheet in your current Excel file).
- Save the file giving it a clear name such as Merged Sales Data for example. This will be the central place for the combined data.
Step 2: Consistency is Key
Before merging there are a few things we need to check:
- Do all sheets have the same column headers and order. This means in each of the worksheets to be merged is the column heading for column A, B, C, D, E etc the same? If not, you may need to move some columns around so they are all in the same sequence. No point combining them all only to find your data is missed in the wrong column.
- Alter any inconsistent formatting such as all caps, or all lower case and make sure the data is consistent across all sheets.
- Remove any blank rows from within any of the worksheet data.
- Check to make sure each sheet contains just the data to be merged.
Once the data is standardised, you are ready to move onto Step 3.
Step 3: Use Copy and Paste
To keep things simple, we are going to use Excel’s Copy and Paste tools to merge data:
- Copy the data from the first sheet (removing any headers) and paste it into row 2 of the main worksheet sheet under the column headings, or if you already have initial data there, at the bottom of the existing data.
- For each additional sheet, copy the relevant data and paste it directly below the last line in the main worksheet.
- Use Paste Values to avoid bringing over formatting or formulas.
Special Note: If you want the data to “stand out” visually as being from another worksheet, you could colour each set of cells BEFORE you paste them to the main worksheet.
Additionally, you could colour code the relevant worksheet tabs the same colour to make it easier to “see” which items have been added to the list and help make sure no data is missed and there are no overlaps.
Step 4: Clean and Check
Once all data is in the main sheet:
- Cross check the main sheet for duplicate headers or missing rows with each of the other worksheets.
- Organise the data by key fields using Excel’s Sort & Filter options from the Home Tab.
- If desired, consider adding a further column called Source Sheet which specifies which worksheet or file the data came from. Use the fill cross in the bottom right corner of the cell to drag the entry down to the end of the relevant source sheet data. See the video for specifics!
Step 5: Protecting Your Data
Although we can use the undo key in Excel… an often-overused feature 😊 It is important to protect your data in Excel so make sure you do the following:
- Minimise the risk of overwriting cells by pasting data into a new combined or main sheet rather than adding them to an existing one.
- Be sure to check for any filters that might be on the worksheets or hidden rows or columns that might mean data is missed.
- If you are trying to highlight duplicates or cells with missing values, use Excel’s conditional formatting.
Smart Tip: Merge Moments
If this is something you do regularly, for example combine sales results on different products or regions into one worksheet. Put this in a routine or process that simplifies combining or merging data from multiple sheets without losing anything.
Some ideas:
- Use clear names to identify worksheets – for example, Region 1 Sales, Region 2 Sales etc.
- Colour code the worksheet tabs so you can visually see what data has been merged and what hasn’t.
- Create a “Merge Log” worksheet to detail when and how you combined data and where it came from, particularly useful if the data is from other files.
Whilst you COULD utilise some complex formulas and tools in Excel to achieve the same thing, I’m all for keeping it simple and at the same time keep your data safe, streamline your workflow with no formulas or tools required. Why make things any harder than they need to be… less complex and more simple… got to love it!!
Liked this? Subscribe to my productivity eNewsletter for more tips and insights to make using Microsoft 365 programs easier, just click here.
