Share this content

Tips on Constructing an Excel Worksheet

Mar 10th 2009
Share this content

Working as an accountant over the last umpteen years, I’ve developed my own manner of spread sheet construction and naming. These tips are from those years:

  1. Title it. Include a three line title centered over the first page. Top is your company’s legal name (larger font), second is what the worksheet about. I use verbiage like “Schedule of” or “Analysis of”. And the third and bottom is the time period the worksheet covers. E.g., “Twelve Months Ending: xx/xx/xx”. (This dresses up the worksheet and sets it apart. It also identifies it as one of yours to others right off the bat.) The final saving of the file should be over this page so when someone opens it up it is the first thing they see.
  2. Name and Date stamp it. Include the file name and date in the footing. Use Insert, then Header & Footer, File Name and Current Date. Then they come back with changes they want you to make, you’ll know for sure the name of the file. Putting the file path also is ok but it tends to fill up the bottom of the page.
  3. Remember it. In the early part of my career, Lotus123 was the only show in town. Since the mouse (and Excel) had not yet been invented, key strokes were use. Giving your file an alpha-numeral name, e.g., INC0010 made since because it was faster to bring up. In Lotus123, you could do File/Open and then type in the file name, and bam there it was! Any variant of an original file would be given the next alpha-numeric, i.e., INC0011. While I don’t use this method anymore I do name files in a logical manner (see below). Often I would add an x, y or a z to the ending of a file I’ve received or created to mark it as a variant.
  4. Control it. Include control totals and don’t lose them. Recently I was combining four quarters worth of shipments with each quarter its own file. I wanted to combine all four with the goal of getting a weight average cost for the year (2008) by part number. My first step was to save a blank worksheet with a name that told a story (E.g., WTAVGCSTFOURQTR08) but was different enough from the other four so, when I went back and forth between the worksheets, it was easier to identify it at the bottom of the screen. I then copied each quarter into a separate page in my just created worksheet. They would be part of my control and would not be modified. One by one I copied each the topmost worksheet named Summary. When I finished coping the quarters, I ran an adding machine tape of each quarter’s totals and then adding a minus of the total costs now shown on the Summary. If it gave me a zero I knew I didn’t make a mistake. Sometimes your eyes won’t pick up a transposition or a difference in cents but a zero is plain to see. One thing nice about putting all four quarters in the same worksheet in separate sheets is that if you have to send the results of your work you are also sending the backup. This can sometimes be more convincing then just send the answer.
  5. Review it. I rarely send a work sheet or even a memo the same day I created it. Let it stew overnight, if possible, and then see if it still makes sense and/or is errorless. I say 50% of the time I change something, or better yet, delete something that was redundant, confusing or too strong in tone.

If you have any tips about creating Excel worksheets, please feel free to let me know by replying to this blog.