If you want to break up Excel spreadsheet workbooks so that the sheets are within different workbooks you can achieve this by cutting and pasting the sheets out into a new workbook. You may want to do this to protect sensitive information (helps with POPIA and GDPR) or to allow different people to work on different parts of a spreadsheet.
YouTube Split Excel workbook into multiple workbooks
Separate the data from the sheet you want to send
In the example below in one workbook we have a costing and a price list. The formula looks at the Price List sheet to get the prices. However, we want to split the workbook so that the price list is in a separate workbook but still linked to the costing sheet.
To achieve this you simply need to move one of the sheets into another workbook or a new workbook. You can right click on the sheet name and choose Move or Copy as shown below
In this case we want it to create a new book with the sheet so we click on the dropdown and choose ‘new book’. Note that we leave the ‘Create a copy’ tick box blank as we do not want a copy (the links will be lost if we copy). We want the sheet to be moved.
When you click OK, you will see that a new spreadsheet is created called Book2. Note as well that the formula’s immediately update themselves so that they now look at the new workbook. At the moment it is called Book2 but we recommend that you immediately do a ‘save as’ while all the workbooks are open to a more meaningful name.
The end result is a split spreadsheet which maintain its links. You can now break up Excel spreadsheets in the safest way.