What They Don't Teach You Anywhere - Excel

bEver so often I come across documents created by my team that make me cringe within. And it wasn't long ago when I had started my career in 2003 (ok it was long ago) that the documents I would create would make my team leaders cringe as well.

Back in 2005, I joined the budding team of 6 at ZS Associates in Pune. During the course of work, I came across a simple checklist for Excel created by [Eric Seelig](https://www.linkedin.com/in/eseelig/). Not only did I religiously follow those but also created some of my own. In 2009, I came to the states and Eric was my office mate. "Big Fan" were the two words that came out of my mouth the first time I met him.

Over the years, I developed a keen eye for what makes any document great and the simple yet effective practices that go into making one. These aren't taught anywhere but passed on from master to padawan.

So without any further adieu, here are the key practices that would make anybody's excel documents a gold standard.

• Create a summary sheet in your excel workbook. Add information in the summary sheet of what the underlying contents/ analyses are and usage or interpretation instructions, if any.
• Before saving your excel document, press CTRL+HOME on each sheet and ensure that the selected cell is A1. Save (CTRL+S) on the summary sheet.
• Update the excel document properties to ensure that the author and document title is accurate.
• In case, you are leveraging another document (from another client), search for client name and delete any instances which would give away your sign’s of leverage
• In worksheets, avoid both horizontal and vertical scrolling. If scrolling is inevitable, use freeze panes
• Sort values in tables based on column with meaningful value (sort descending preferred). If the values are summarized in a total line at the bottom, keep the summary line record as the first line of the table (instead of bottom). This avoids the viewer to scroll at the bottom to see the total summary record
• Keep work sheets with zoom size 80 / font size 10 (Eric doesn't agree with this but hey..it's my article)
• Remove headers and gridlines. Format table with gridlines (use gray color 40%). Format table header (dark blue/ black background with white font)
• Save filename with a meaningful name such as yyyy.mm.dd clientname-filename purpose vXX.xls
• Delete or hide unused/ background tabs. Keep a control tab where you have the list of values or if you are using a combo box/ checklist. Don’t embed formulas or control values in the same master sheet
• Use ALT+E+A+A for all unused rows/ columns to clear and unwanted formatting. Should reduce the file size as well
• For documents with analyses across multiple tabs, consider using hyperlinked objects for navigation (home, forward, back, jump to, jump back)
• Pre-apply filters
• Freeze panes at an appropriate place
• For “narrow” lists, just freeze the header rows
• For wider lists, freeze to ensure people can always see useful row headers
• Format numbers appropriately
• Use appropriate and consistent number of decimals
• Can use “$#,##0.0,,” for millions of dollars, etc.
• Align headers to data (numbers are right-aligned, so headers should be too)
• Make sure “numerical strings” like zip codes are formatted as text to avoid losing leading zeroes
• No formulas in the table! Paste as values.
• Replace “data” values with more intuitive values: TRUE/FALSE or 1/0 à Yes/No or Y/N
• Include a title at the top above the headers, as well as some explanation of what people are looking at
• Use meaningful tab name (not just “Sheet 1”)
• Pad around the edges (Keep column A blank, and set width to 1 or 2)
• Set similar types of columns to the same width
• Keep columns to a reasonable width—if most values are 30 chars, don’t set width to 60 just because of one odd value.
• Allow headers to wrap (bottom-align them) if it helps with column width
• Set up printing. If it will print across multiple pages, make sure the appropriate rows (and columns where applicable) will be repeated if necessary.
• Set it up so it can be displayed at 100% without zooming out to avoid the “#####” that sometimes happens