Excel Workbook Sharing (Part 1 of 5: Essential Excel Skills Every Controller Should Know)

As much as we’d all love to be able to perform all of our work in one system, like our ERP, everyone in the finance department knows that just isn’t the way life works. So we build these amazingly complex and intricate Excel workbooks and we’re super excited about all the fun things the workbook will do for us and tell us about our business.

Then, suddenly, we have a deadline that we just won’t be able to meet without someone helping us. We all know that we want to be a super hero and do the work all ourselves - because no one can do it the same as us. But, the reality of the matter is that we all need help. Sometimes that means we need to work on the same workbook at the same time.

A few years ago, this would have been problematic. We’d be searching for ways to make copies of the workbook and each person would fill in what they could, saving, copying, pasting, and hoping in the end that all the information was done correctly. Starting with Excel 2007, gone are those tedious days. But did you know about it? This is one of the most amazing Excel functionalities and I didn’t stumble across this until 2012 (I think).

So, how do you share a workbook? Microsoft has given us two options, depending on the version and network sharing options of your company. For on premise sharing, go to Review tab on the Ribbon. In the Changes section, you will see the option to Share Workbook.

A box will pop up with options for you to choose with two tabs, Editing and Advanced. By clicking the little checkbox on the Editing tab, you are giving others in the organization the capability to work in the workbook at the same time.

The Advanced tab will give you a few options like how long to keep the change history, when to save the changes being made, and what happens when there are conflicting changes.

If your company has already made the move to Office 2016, you have the added bonus of sharing your files online. In the upper right hand corner of your workbook, you will see the Share Icon.

Click the Share Icon and a box opens that allows you to Save to Cloud or Send as Attachment. (Note: Send as attachment is not a workbook sharing option. Both of these options are available in the File\Share and File\Save As Menu options, but this is a shortcut within the Ribbon.)

Clicking on the Save to Cloud option will bring up the File\Save As menu. Chose the correct OneDrive site, name the workbook and Save.

Next, you’ll have to invite the other members of your company that you’d like to have access to edit the document. Once you click Save, Excel will bring you back to the Share box to enter in the users. At this point, you can also identify if the users are able to edit or simply view the document.

Click Share and Excel will send emails and setup the permissions necessary for each user. Once it is complete, you will be able to see who has access and what their permissions are directly on the share box.

Removing or changing permissions is as simple as a right mouse click on the user you’d like to edit.

Once a workbook has been saved to OneDrive using the Share option, you now also have the option to get a sharing link (see in screenshot directly above). Click on this to choose what type of link to create.

Once you have the link you desire, click copy. Now you can send this in any format you’d like, whether it’s an email to a large group or through your company’s instant messenger.

Once your workbook is shared, your team can work collaboratively to get the job done. But, as we know, with all collaboration functions of any platform there are limitations. Some of the more advanced Excel functionality is not available when in the shared mode. Microsoft provides a pretty comprehensive list of what does and does not work in the shared mode here: MS Support.

Excel has so many hidden functions and features that it’s sometimes hard to know where to start. As Controllers, we’re dependent on our tools to help us provide accurate, efficient, and attractive reports and Excel can help us do that.

Read the next part of this series now: Conditional Formatting in Excel (Part 2 of 5: Essential Excel Skills Every Controller Should Know)