Before Sharing an Excel Spreadsheet, Make Sure You Do These Things First

Source: https://www.makeuseof.com/tag/before-sha…e-sure-things-first/
Capture Date: 11.03.2018 15:11:23

Advertisement

Sharing Excel workbooks makes it easy to collaborate on data collection. But before you put your Excel files in the hands of other people, here are a few tips to get your worksheets ready for sharing.

Allow Multiple Simultaneous Edits

If you want multiple users to be able to edit a shared Excel sheet at the same time, do the following:

1. Go to the Review tab and under Changes, click Share Workbook.
2. In the dialog that opens up, make sure that Allow changes by more than one user at the same time is checked.
3. Save your Excel file in a shared location where other users can access it.

One thing to note about allowing this: you could get conflicting changes if two users make edits to the same cells. The owner of the file will be alerted and can choose which changes to keep or discard.

Protect Worksheets or Cells

If there’s specific data you don’t want anyone to modify or delete, you can protect an entire worksheet, protect a workbook, or protect specific cells.

If you want to protect an entire worksheet or workbook:

1. Go to the Review tab and click Protect Worksheet. (If you would prefer to protect the entire Workbook, click Protect Workbook.)
2. Enter a password. This will allow anyone who has the password to unprotect it, but people without the password will still be able to view it.
3. Under ‘Allow all users of this worksheet to’ you can select what other users can do on the worksheet including: format cells, add/delete rows or columns, and add hyperlinks.

If you want to protect a selection of cells:

1. Select the cells you would like others to edit, right-click then, and click on Format Cells in the menu that pops up.
2. Navigate to the Protection tab and make sure that Locked is unchecked.
3. Go through the same three steps above.

Now all cells, aside from those that set you as unlocked, should be protected.

Add Drop-Down Menus

If you want other users only to add from a selection of data to specific cells, you can create a drop-down menu for a specific range of cells. You can give users the choice of selecting only from that list or allowing them to enter other information.

To add a drop-down menu to a range of cells, do the following:

1. In a separate sheet in your workbook, create a list of the items you wish to include in your drop-down menu. These items should be in one row or column.
2. Select the entire list, right-click, and select Define name.
3. A dialog box will pop up where you can enter a name for your list. It can be anything that works for you — just don’t include any spaces.
4. In the sheet where you will be entering data, select the cell or cells where you want the drop-down to appear. Navigate to the Data tab and click Data Validation.
5. A dialog box will open up to a Settings tab. In the Allow field, select List.
6. In the Source field, type =ListName.
7. Make sure that In-cell dropdown box is checked. If you don’t want users to be able to leave the cell blank, make sure that Ignore blank box is unchecked.

The following steps are optional:

1. If you want a message to appear when a cell is clicked, navigate to the Input Message tab. Here you can enter a message up to 225 characters that will pop up.
2. If you want an Error Alert to appear, navigate to the Error Alert tab and make sure that Show error alert after invalid data is entered is checked. You can enter a specific message for your error alert.

What tips do you have for Excel users who want to share their workbooks? Let us know in the comments.

Enjoyed this article? Stay informed by joining our newsletter!