excel Best Practices


Example

Here are some basic best practices for Excel:

  • Flat File Database - Excel IS a Flat File application and should be treated as such

  • Less Worksheet/Workbooks is more. Analyzing will be much faster with fewer worksheets/workbooks to go through. Try to ensure that all raw data is on one worksheet in one workbook

  • The layout should consist of 1 sheet of raw data, your final data will come from this.

  • Make headers BOLD this helps Excel recognize them as headers for things like sort

  • When putting data into the data area of your spreadsheet try to avoid blank rows and columns. Excel might consider a blank row or column to be the end of your data. It would be a good idea to leave the top 4 rows blank above your headers to use for totals instead of having them at the bottom

  • Sort your data whenever possible. This will help in speeding up some of the formulas and calculations that you have in the spreadsheet

  • Use real dates for headings and then format them appropriately. By this I mean if you need month names for your headers use 1/1/2017, 1/2/2017, 1/3/2017 and then format them as "Mmmm". This is very simple nad will make life easier down the road when used in formulas.

  • Don't put into 1 cell what can go into more than one. Meaning if you are making a list of users full names you should have the first name in one column and the last name in the next column (and possibly more columns for middle names and suffixes). It is easier to do this from the beginning than to try and do this with formulas later.

  • Place your headings across the columns and your data in rows directly beneath. Excel has far more rows than it has columns. To make you spreadsheet future proof your raw data should have the data going down the rows so that it can continue well past the number of available columns.