I have just completed another very successful Financial Modelling course and as you know, at the end of such courses, I come here and offer something new: a new topic, a new file or some advice. In this case, it is advice: things that you really need to think about when you create and work on any Excel file.
- Tab/Sheet Names
- Links
- Dead Cells 125,433 rows created but only 831 needed/active ... files that balloon to many Mb for no real reason
Ever seen a tab name like this: FBU or OPT? I bet you have: short and sweet and probably mnemonic so easy to read and remember. How about L_P_Obasange_receivables_dont_forget_to PRINT_it_out? You think I am joking? I am serious! Just imagine you are working on your file with the large tab name and you want to link to a cell on that tab from another one: this is what will appear in your formula, by way of an example ... =IFERROR(AND(A15=45,D26="Jack",L_P_Obasange_receivables_dont_forget_to PRINT_it_out!BA154 ...
I am sure you see the point now. Keep tab names short and simple! More than that, if you do feel the need to use tabs to give instructions, colour code them to pass such messages: there are many colours to choose from so do that. Have a table of contents too. Give everyone a chance for a simple life!
Links
If you share a file with someone, make sure any links in your file are either live or delete them. If you receive a file with links that you cannot use or update, you know how frustrating it is. Think of the user before you send linked files.
Dead Cells
It is the easiest thing in the world to create a worksheet and as you work and improve what you are doing, to delete cells and ranges. We all do that. We create new ranges too, don't we! Check your work now and again though and if these happen, take a break and check your file:
- it takes 30 seconds 45 seconds or even longer for the file to open
- what seems like a small file in terms of content and complexity has ballooned in size to 20 or 30 or more Mb
- saving the file takes an age too
If these things happen, go to a worksheet and press Ctrl+End and see where that takes you. You work only in the range A1: CD831 but Ctrl+End has taken you to CG125433 ... what? How did that happen?
Even if there are not as many as an additional 1.8 million cells but just 500,000, look in those cells for formulas that are trying to find something from somewhere that is not there ... in some of these extra cells for example. Delete all of these extra cells. I did that this week: an extra 1.8 million cells in TWO separate worksheets complete with formulas. File size down from 28 Mb to 0.8 Mb, opening time just seconds, recalculation time hardy noticeable.
They were just some of things to report on from this week. Otherwise, this group of delegates really enjoyed the work and their end of course presentations were interesting and showed that significant learning had taken place!
Duncan Williamson
