How to Share Excel Data in the Cloud

Last week, I wrote about a material planning tool for the supply chain.

It is built in a spreadsheet and has a way to share Excel Data in the cloud.

There is a lot of hype about cloud computing.  Lots of vendors use the jargon to try and sell their stuff.

Forget that.  I believe that the best ideas are simple and free.  Here is a way to share Excel data across multiple users by using a simple cloud service.

Multi-User Excel Software

Think about a simple system with two users.   One is a product sales company (say, in America or Europe) and one in a factory unit (in a far eastern country like China).

It is quite common to find the factory and sales company unable to integrate data on or between ERP.  They may the lack the integration technology or resources.  The factory may not even have ERP at all.  Read this article last week for more details on this common scenario and download the tool.

This website is about building planning and scheduling systems.  We use spreadsheets to build Excel software for manufacturing and supply chain.

The trick to using Excel as a business system is in the method of sharing data.  The trick is to separate the data storage from the calculations and reporting.  This way, you know that all users are working from the same set of input data.

Share Excel Data, not Excel Workbooks

The easiest way to share data between Excel workbooks is not by using Excel.  This is going to sound counter-intuitive.  Nevertheless,  a direct link between two Excel spreadsheets is far from ideal.  It is slow, clunky and prone to error.

A better way is to do all Excel data integration away from formulas and worksheets.  Firstly, this forces the user away from storing Excel data in Excel.  Eliminate “spreadmarts“, those disconnected stores of data in spreadsheets, spread across the enterprise like warm butter on hot toast.

The easiest format to connect data with Excel is with text files.  You may also think about ODBC database connections or XML files.  We use text files because they are easy to integrate with any system, small in file size and virtually unbreakable.

Do not email or share Excel workbooks.  As soon as you start doing this, you end up with multiple versions and no clue which one (if any at all) is correct.  You would not let users re-write the code in the ERP software.  And you should not give users free rein to change business tools,  just because it is created as an Excel spreadsheet.

Sharing Excel Data in Text Files

Tab delimited text files are the choice for simple and robust Excel data integration.  So, how do you share them between multiple users?

The first step is to define the local file location.  Excel imports and exports data in .txt format very fast if it is read/writing to a local folder.  This one is good:

C:\Production_Scheduling\Input_Files

Not only is it easy to find, it is a common location for all users.  This one is bad:

E:\Users\Kien Leong\Documents\Production_Scheduling\Input_Files

It is specific to the user and their choice of drive partition.

The Fast Excel Development Template has a place where you can specify the input file path.  However, if you choose a local folder path that can be the same for each user, it is one less setting to remember.

So, any tool built with the Development Template is going to import and export Excel data as text file tables.  the files will get pulled from and sent to the file location specified on the menu sheet (or other parameters sheet).

It is possible to write a simple script that copies the contents of a network folder to the local folder.  And vice versa.  There are a couple of macros included in the Development Template that do exactly that.

Now we have a way to share input data with any other user on the network.

Usually this network folder is a shared drive on a local area network.  A small to medium sized facility might have all users on the same local network.

However, if you include multiple facilities or third party suppliers, the local network is not going to be enough.

Sharing Excel Data in the Cloud

Volumes have been written and spoken on cloud computing.  Some ideas are really transformational.  Others are just a new paint job on network computing– a concept that has been around since the mid 1990’s.

Let’s cut through the hype.  When I say “data in the cloud” I am talking about data available to the local user from non-local sources.  It needs to be simple, seamless and convenient.  If it is also cheap or free then so much the better.

The most widely used alternative for sharing files in the cloud is Dropbox.  They give you 2GB for free and is quite simple to set up.  When you install Dropbox then the contents of a Dropbox folder is shared in the cloud.  You can specify exactly which folders inside the Dropbox folder you share and with whom.

One of the simplest solutions for sharing files in the cloud is SugarSync.  You get more space, 5GB.  You can also share any folder on your local PC, rather than just the contents of one dedicated folder.

The free space given to either of these services is plenty for use with Fast Excel.   Our material planning example has 146 part numbers and 291 order lines.  The total unzipped text files for that tool add up to 64KB.  You could scale up this example 78,000 times before you would need to fork out any money to use the SugarSync service.

And simple it is.

1. Follow this link to Dropbox OR
   Follow this link to SugarSync.
2. Sign up for a free account.
3. Download and install the free, small application.
4. Place the folder that holds the Fast Excel input files inside the Dropbox folder OR
   Specify SugarSync to back up the folder that holds the Fast Excel input files.
5. Copy-paste this file location into your Fast Excel-based tool.
6. Log on to Dropbox or SugarSync and invite other users to share.
7. The users will repeat steps 1-5.
8. That’s it.

This is a really simple and effective way of sharing Excel data with other users.  Across organizations and continents.

Enterprise Software – When Do We Get Simplicity and Great User Experience?

Before you panic about data security, consider how many files are sent by business users as unencrypted email attachments.  Security is a human problem, not a just technology problem.

Nevertheless, those of you with large IT departments and onerous security policy will likely have trouble in sharing company data with a service like SugarSync.  Even if you are small, using it to share the BOM for a top secret product is never going to be a good idea.

At the very least, an idea like Excel+Dropbox can put pressure on IT to come up with an effective solution for sharing data with other facilities and suppliers.  You would be surprised to learn the number of SAP and Oracle users who email large attachments of Excel data around the internet.  Or maybe that would be no surprise at all.

All information systems need to be balanced for cost, simplicity, security and performance.  The unstable equilibrium that was set in the days of first-time MRP and ERP implementations was expensive and complicated.  Even today, many systems sit in silos because of security and complexity fears that may not be justified today.

Web 2.0 delivered a revolution in consumer internet content and services.  The new standard for websites and web apps is high on design, usability and simplicity.  These principles have yet to penetrate the broad market for enterprise software.  The typical ERP user experience of today remains ugly, clunky, complicated and inflexible.

This will not go on forever.  The new standard in consumer experience with technology is set to pervade into the Enterprise space.

This trick with sharing Excel Data in the cloud is a simple, low cost combination of old technology (text files) and new (synchronizing desktop data over the internet).  Whilst we wait for Enterprise Software to embrace simplicity and secure, yet ubiquitous data sharing this is one method we can use to manage data across the supply chain.