Merge Microsoft Excel Spreadsheets

Table of Contents

Merge Spreadsheets
Collating Data
Live Sharing to OneDrive
Link Data
Conclusions

Merge Excel Workbooks

Excel Template concepts can be created in a few ways. One is to create an Excel Workbook with pre-laid out sheets. For example, sheets labelled – Expense, Income, Profit, Bank Statements. Every year you need a new Workbook with the same structure of Sheets.

Save the Excel file as a Template. Importantly ensure it is saved in the template folder not just in your network or cloud drive.

To access your template Workbook open Excel, select File then New look for Custom and find your template. If this is an Excel Template that you use a lot, best to press the pin icon to pin to lists.

If you have the Excel Icon displayed on your Taskbar. Right Mouse Click it and pin the template you have just opened. This is another way to access your Excel template quickly.

The workbook may contain information where you need a new expense sheet for each month. In this case it is not the whole workbook that needs to be a template style but just the Sheet.

I would create a sheet lay it out with the framework you like. Label it Mthly Temp.

When I need a new sheet for the following month. Hold Ctrl and Drag the sheet name. Otherwise you can Right Mouse Click and select Copy/Paste.

Hiding the template sheet could be a good idea for this simply Right Mouse Click and select Hide.

Collating Data

As a manager you collate data from other colleagues. Let us say it is time sheets.

Each staff member adds new sheets as they need. At the end of the month the manager consolidates the sheets from each staff member.

To create this scenario, start with the master document which is the one that the manager will merge into each month. Turn on Share Workbook (Legacy). This is different from the Share button on the top right-hand corner that will share to OneDrive.

When you Share a workbook like this you will not be able to perform commands such as delete sheets. Turn on Protection, Apply Tab colour, Format as Table, Conditional Formatting, Merge and Centre, Create Pivot Tables, Text to Column or Insert Comments.

Consider Carefully if this tool is best for you.

Add the Share Workbook (Legacy) command button to the Review Ribbon. Go to the Quick Access Toolbar then more commands. Ensure you have selected all commands to search for Share Workbook (Legacy).Also add the Compare and Merge Workbooks command.

In the Master Workbook turn on Share Workbook (Legacy). Using File Save As ( F12) create a Workbook for eg; Employee1 and Employee2 this step is important otherwise it won’t work.

After Each employee has entered their data. Open the Master Workbook and Press Compare and Merge Workbooks, navigate to Employee1 workbook repeat steps for Employee2.

Live Sharing to OneDrive

Sharing to OneDrive is true live sharing. Multiple users can be making changes at the same time. Not like in the old day when you had to log off so that others could make the changes.

Press the Share command on the top right-hand corner or go to File and select Share. Enter the email address it is best to include a message.

Comments from Excel Files stored on OneDrive have ability to make comments and have others reply to my comments. You can Edit, Delete and Resolve thread. As comments are being made you will see the users name appear on the top right-hand corner.

At times we may want to share but with limitations. If you save your Microsoft Excel Workbook as Adobe PDF and have Acrobat PDFMaker you can choose to PDF the whole Workbook, Selection or Sheet(s).

Track Changes Made In Excel

When there are multiple users needing to access the Excel spreadsheet it is best to keep the file on a Network drive preferably Microsoft OneDrive.

Excel can produce reports of who and when and What has been changed. Previously this was readily and easily available under the Review tab. This is something you must know about and add yourself.

Let us look at how we can get this done.

Start by Right Mouse Clicking in the Review Tab and select Customize the Ribbon. From the right-hand side Press New Group, then rename eg Sharing.

In this new group you can add any commands you like. We will start with the Track Changes command found under – NOT Popular Commands but “All Commands”. Find Track Changes Legacy Press Add making sure you have your new group name “Sharing” selected on the right-hand side.

Link Data

Sometimes you might find yourself needing to repeat information in another location for convenience. Copying and Pasting from one location to the other. However, that is fraught with the danger of updating one area and not another. This is where linking data comes in.

For example, I have a list of expenses with a total at the end. Now repeat for each department. I want a summary of each department and the total expense in one location for ease of use.

Press the equals sign = where you want the result, proceed to now click on the sheet then the cell reference Press your operator such as Plus (+). Repeat the step for as many sheets as you like BUT when you finish no more (+). That is to say ensure you don’t have an extra (+) on your last cell reference and press enter to finish up.

Your result should look something like this:

=Marketing!A12+Sales!A12+Manufacturing!A12

In this example I have used the same cell reference this may not necessarily be the case for you. That is ok select which ever cell contains the result you are after.

With this technique you can make changes in cell A12 in Marketing and it will change in your summary page where you have the formula.

The process is the same if you were to select workbooks then sheets then cells.

The only danger is that people delete workbooks or move them to other locations, and this could break your link.

Making Changes To Linked Excel Workbooks

To make changes to a link you will need to go to the Data Tab and select Edit Links. From here you will be able to break links.

Be careful if you break a link there is no undo. Simply reinstate the link again. That means do the link steps again.

From this window you will see all the links that pertain to this Excel Workbook.

Conclusions

Creating Excel Templates is a great way to keep your spreadsheets consistent.  Sharing data using Share Workbook (Legacy) along with the Compare and Merge Workbook comes with its limitations.

The trick to using this feature is to remember to create the subsequent Workbooks from the original Shared Workbook.

Save Workbooks to OneDrive for great sharing capabilities including the comment feature.

You may not have thought about this, but you can save a Microsoft Excel Workbook as a PDF. This way the document is protected. Even choosing to PDF only a single or specific selected Sheets.