October 16, 2015
This is not a technical question, but it is something I’m struggling with from time to time.
I create a lot of different Excel files for other persons. And those files can vary in complexity, but I always try to build these Excel files as user friendly as possible. For the end user most of the time it’s clear how to use the file to get the desired results.
But handing it over to the person who is responsible for the maintenance of the files it’s a different story, especially when you have incorporated all kinds of Power Queries, Pivot Tables etc. If that person is skilled enough then it won’t be a problem for him to find out what is happening on the “backend” of the file. But it’s very hard to understand why certain steps are taken, because there are many ways to achieve things. So when things break down of need to be updated, then it becomes very hard to find out how and where to do that. And if that persons have only limited experience with Excel and data it will become even harder.
My question to you is what will be the best way to hand over a file you made for a third party. During the handover there will be an extensive explanation, but sometimes it won’t be enough because the file contains a lot of steps. Do you have a standard approach for designing a file? Do you write an user maintenance manual?
Thanks for your response.
December 7, 2016
What I do is to create a copy of the file and in the copy I add comments or whatever suits best to give information of each data set, conditional formatting and formulas. This copy just contain some sample data, as it is the structure that is important to show in this file. For a complex file a user maintenance manual might be a better option, but that is perhaps more depending on how the third party wants to have it.
As I see it there is no single solution that will suite all scenarios, but one rule of thumb I try to stick with is to keep it simple. It will be easier to read and above all, easier to maintain whenever you need to update. In the copy file I also have a sheet where I or the third party user can keep track of updates/changes made.
April 21, 2015
This is a well known issue. Everyone has his/hers own 'style' of making workbooks and sheets.
I like the approach which is being taught here on this website: make different sheets/tabs for your data, your calculations and your presentation.
You can add an extra tab with explanation, maybe in the way Anders suggests.
But have to rely on some experience by the receiver and if there isn't enough on that site, that's their problem I think.
Some protection of calculations can help extra.