Advanced Excel – Linking, Moving / Copying and Protecting Spreadsheets

If you have a workbook with multiple spreadsheets; linking, moving or copying spreadsheets is very useful.

Linking spreadsheets helps you to access and reference data contained elsewhere in a workbook. For example, there is a spreadsheet with the data of the quarterly expenditures of your department and another spreadsheet with the total expenditures of your department. If these two spreadsheets are linked, updating the data on any one would automatically update the other one.

* Open the two spreadsheets. Select the cells in the source location (one with the quarterly expenditures) that has to be linked with the target spreadsheet (one with the total expenditure).

* Right click on the selected cells. Click Copy on the menu that appears.

* Go to the target location and select the cells where the source data has to be pasted.

* Right click on the selected cells. Click on the Paste Link icon.

Now, whenever the source data is updated the target location will automatically get updated.

Moving or Copying spreadsheets in a workbook is possible.

* On the bottom of the spreadsheet, right click the spreadsheet tab you want to move.

* On the menu that appears, click on Move or Copy.

* Go to Before Sheet on the Move or Copy window that appears. Select where you want to move the currently selected spreadsheet. Then click OK. The spreadsheet will now be placed before the selected position.

* If you want to copy the selected spreadsheet to a different location, click on the Create a Copy checkbox under Before Sheet in the Move or Copy window. Click OK. A copy of the spreadsheet is now available in the desired location.

Protecting spreadsheets is done by locking them.

* Go to Review and click on Protect Sheet. On the Protect Sheet window select Protect worksheet and contents of locked cell.

* Enter a chosen password. Click OK.

Spreadsheets can be partially protected as well, i.e. it allows others to make certain edits to certain cells.

* Select the cells you want other users to edit and Right click on them.

* Click on Format Cells … on the menu that appears.

* Click on Protection on the window that appears.

* Uncheck the Locked checkbox. Click OK.

* Now lock the spreadsheet following the Protecting spreadsheets guide.

* Check Select unlocked cells. Click OK. Only the unlocked cells can now be edited.

You can learn this and a lot more if you enroll yourself in a Microsoft Excel training course.

Post a Comment

Your email is kept private. Required fields are marked *