Saving an Excel workbook for Multiple Users

Document sharing can be a hassle. A feature that’s already built into Excel can help you use Excel for simple collaboration for small groups. Of course, this shouldn’t take place of a more robust collaboration or document sharing tool, but can be set up in a pinch.

If multiple people need to work on an Excel spreadsheet stored on a network drive, access to the file can be made much easier by setting two options from the Save As dialog box.

These settings can be made at any time, just by using the Save As feature to save the workbook back over itself. You don’t even have to change the name.

The following instructions show you how to set the workbook to use a feature called
Read-only recommended as well as setting an automatic backup every time the file is saved.

Steps

  1. When the file is open on your computer, click File > Save As and choose the folder you want to save the file into. The Save As dialog will appear as shown here:
    excel-saveas-tools
  2. Next, click the Tools option and choose General Options.
  3. From the General Options dialog, place a check mark beside Always create backup, and Read-only recommended.

    Select Always Create backup and Read-only recommended.
    Note: You can also choose to set a password that would be required to open the file, and a different password that could be used to allow the file to be modified.
  4. With the options set the way you want, click OK and then click Save.

Now whenever a user tries to open a file, if it is available for writing to (which means someone else doesn’t have it open and is making changes to it) they will see a dialog asking them if they want to open it as Read-Only.

This allows the other user to finish their editing. The user who opens the file as Read-Only will not see the changes from the other user until they close the document and open it again.

If you choose NO, you will be able to write to the file as normal.

If you do not see that dialog box, the file has opened in normal mode allowing you to modify and save the changes.

I hope that helps you work with Excel files with multiple users. I’ve run into this situation where Sharepoint or other document sharing solutions are not in place.

Do you have any suggestions on how to work with Excel files and multiple users? Do you have good experience with Sharepoint? Add details in a comment below!

Excel Spreadsheet Training Provided by Black Ink Technical Training and E-learning Solutions

Hello everyone, and welcome to Black Ink Training. We are in the process of developing our online material for a number of applications, but we’ll be focusing on Excel and using Visual Basic for Applications (VBA) first.

As a starting point, below is an older video I created for my computer students what explains the basics of using Excel. In the near future I’ll be updating the material and providing detailed training for Excel 2016 on both Mac and Windows platforms. This video is for Excel 2007, but the basics apply to all current versions of Excel as well as other spreadsheet applications such as Google Spreadsheets and even Apple Numbers.

[youtube https://www.youtube.com/watch?v=j_bahcLeAjY?feature=oembed]

If you have any Excel questions you want answered, post a comment.

Follow us on Twitter@blackinktech or Instagram @blackinktraining.

Get over 1 million rows in Excel

A lot of people complain that you can’t work with more than 1 million rows of data in Microsoft Excel. Well, now you can!

Using the Power Pivot plug in in Excel 2013 or the fully integrated Power Pivot tab in Excel 2016, Excel now has a powerful database infrastructure that easily handles many millions of rows stores inside the Data Model portion of the file. Your only limits are really just the amount of RAM and speed of your processor.