A key feature of good spreadsheet design practice is the proper documentation of the spreadsheet but almost no help is given in Excel for this. Let's have some tools to help.
Three initial suggestions:
1/ a free-form notes page (a bit like the bottom part of a Powerpoint slide) where documentation notes can be added as the spreadsheet is created, possibly with automatic reminders when named ranges, new styles, user defined functions etc are created
2/ A record being made when a new workbook is linked in to take data from this workbook
3/ automated listing of ranges, names etc as in the Access database documentor
Thanks for the suggestion Malcom. We’ll keep an eye on this – we’ll be more deeply investigating things with more votes, so people should up vote this if they like it. I’d also encourage people to chime in on the comments for this one with any specific asks for particular cases/operations they care about more than others.
John [MS XL]
2/ and 3/ would be nice but 1/ by itself would still be awesome! The powerpoint type functionality is good. I also use great functionality like this in Scriviner - notes for a specific section/tab and notes for the file as a whole.
Better than comments because:
- Comments jump all around the pace when freeze panes, filtering, grouping or subtotal (which adds grouping) are involved.
- Comments are tied to a single cell.
- Comments can be hard to spot in a large/complex spreadsheet.
I often do as per comment from Ryan (Jul 24 9:41) - blank sheet with a large text box for documentation/comments. The problem with text boxes - Excels kerning issues make them bad for formatting, printing and large amounts of text. and text from multiple text boxes is hard to extract. Functionality like powerpoint where the notes can be printed/extracted by themselves would be awesome.
It would be a powerfull complement to cell comments.
I will often make a worksheet with nothing on it but a single huge text box, and I write lots of notes in it.
It would be super cool if I could embed a Word document as a shape in a worksheet, with all the power and flexibility of MS Word. Styles, headers, chaptering, bullet and numbered lists, tab control. etc.
It would also be cool if I could add a publisher slide as a shape in a worksheet.
A chart can exist as an independent sheet, or as a shape within a different worksheet. Why can't I add an embedded worksheet to a different worksheet as a shape. It could make multi sheet formulas way easier to write or audit. in Sheet 1, I have a formula that refers to Sheet1, Sheet2, and Sheet3. I could move Sheet2 and Sheet3 to Sheet1 as shapes. Then when I write the formula in Sheet1, I could see color boxes in each of the three sheets showing me what the formula is doing. If I press "trace precedents", Arrows would point to each of the sheets instead of showing that useless arrow to nowhere.
Excel already has a solution for this. It is called a text box.
Text boxes can hold hundreds of words. You can use them just like cell comments, but the text box gives you some different flexibility. The text box supports word wrapping and text formatting. Some people try to add documentation by merging several cells, or by writing documentation across several rows with manual word wrapping. Text boxes are far superior to both these solutions.
Here is how...
Insert -> Text Box
Alternatively, you can draw a shape and write on it.
Insert -> Shapes -> select a box, circle, oval, arrow, or any other shape.
Draw the shape.
select the shape.
The shape is now a memo embedded in your workbook.
Complex formulae can be difficult for some users to follow and understand so the ability to document and explain cells and structure would be useful.
I very much agree. The new spreadsheet analysis tools are helpful but if this could be tied to some form of documentation much as malcolm suggested. An option to turn on documentation which would create (and keep updated) a list of items on the page such as uses of names, function for sections. I do this currently in comments. They are clunky and don't aggregate. A simple formal methodology for documentation would be the basis which would then describe the documentation tools needed. This also means it would be 100 times easier to pick up someone else's work if there was a simple documentation protocol. With the new power of PP+PBI it is one of the things that's preventing adoption for many IT professionals. They have had long days and sleepless nights trying to unpick someone else's work and despite the obvious step change in excel's capability they will always associate "spreadsheets" with clunky unprofessional undocumented work. An excel supported documentation methodology and tools would go a long way to address this. Many of the "technical" parts exist in the new spreadsheet analysis tools which are great. It's just marrying it to a user interface and a suggested methodology.
Jan Wx, NZ commented
We don't need this. Either create a sheet with version control notes, or if you have Macros create a module called "aHistory" in which you log the history