more-arw search

Q&A Forum

Is Excel reliable in keeping more than one spreadsheet in sync? Or does the reliability decrease if some of the worksheets aren’t in the same spreadsheet?

Laurie Gray's Profile

Answers

John Denney
Title: CFO
Company: Consultant
(CFO, Consultant) |

The reliability of excel, which while I use it extensively is inherently error prone, is just as good if the data is in just one workbook or synced with several others PROVIDED that they all reside on the same accessible network. If you email the main sheet to someone or take work home and do not have complete access to all the related workbooks, then you have no guarantee that you are working with the same data as someone else.

There may be a solution to this problem that I do not know but version control becomes problematic with synced worksheets since you have to update the links each time you change the referenced worksheets if you change their names to keep track of different versions.

Hope that is helpful.

Joan Nevins
Title: CFO
Company: Kalido Inc
(CFO, Kalido Inc) |

Go look at the posts on Budgeting and Forecasting for mid-sized companies. Most people painfully come to the conclusion that Excel will bite you by getting out of sync and make the move to one of these systems

Topic Expert
Keith Perry
Title: Consulting CFO and Business Operations A..
Company: Growth Accelerator
(Consulting CFO and Business Operations Advisor, Growth Accelerator) |

Laurie,

I've found that Excel fails pretty predictably. One model I work with is based on as series of sheets that are large enough to bring my machine (32bit, so memory issues) to its knees. It works...but you have the simple effect of math creating the likelihood that you will fail Excel, and it will come apart.
-Some formulas (like indirect references) require the target to be open, or they will fail.
-If you are doing version-saves, you have to carefully go back and forth saving each spreadsheet in sequence. A slip-up means either a dead link or an old reference.
-Multiple-person access is another place where the errors rise exponentially in relation to the number of people accessing.
.....and so on. I've found Excel to be reliable up to the point that it takes my computer down, and then it does not fail gracefully at all. The big issues I've run into are error-between-keyboard-and-chair, along with some functions that simply don't work well in a multi-workbook setup.

Echoing John Nevins; if you are using Excel for fun and games (sensitivity analysis, etc), it might not be so bad. If you are using it as a system of record, the software packages designed for this will save you enough time and grief that they are worth the money.

Jolene Patterson
Title: Financial Analyst
Company: Medtronic Inc
(Financial Analyst, Medtronic Inc) |

I agree with the multiple comments about spreadsheet failure and syncing issues. The best solution I have found when trying to do this is to keep the drives on a SharePoint site using version control and removing the ability for users to delete the files. This still is error prone but seems to work with remote and home users better than vpns to network drives and resyncing files from your hard drive when you return to work. I backup all files simultaneously weekly so that matching versions could be restored if absolutely necessary - I do this separately from the IT Departments backup of the SharePoint site because that usually requires the site to be restored.

Topic Expert
Regis Quirin
Title: Director of Finance
Company: Gibney Anthony & Flaherty LLP
LinkedIn Profile
(Director of Finance, Gibney Anthony & Flaherty LLP) |

Excel is an excellent program. Failure can be avoided through proper planning. Failure often happens not initially, but after multiple uses and a decision to make a change, i.e. user error. Whenever possible --

1) Place links and inputs on one tab and only one tab. Any modification should be done to this input tab, whether it be links or variable adjustments. The subsequent tabs should be calculations only.
2) Document all changes to formulas.
3) Make use of the audit functions that help you trace where you came from and where you are going.

Caution - Do not use Excel as a database. Even though the program may show multiple lines and columns, the system will fail if overloaded.

Good luck.

2272 views
Products and Companies

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.