People often ask about the best principles to follow to make the better excel spreadsheets. Hope you might also be interested to know them. Here we go…
The Excel Principles:
- Make your spreadsheets with at least three stage separated, it will enable your spreadsheets flexible, scalable and easy to go through.
i) Data Collection
ii) Data Analysis (I call it Calculation Engine of reports)
iii) Data Reporting (Presentations, Dashboards, MRs, etc)
- Do not mix the raw data with reference and analysis, which will make your spreadsheets clumsy!
- Do not hard code or enter numbers manually in a formula – its better you refer a cell as variable and key-in the numbers you need in it. This will help you to manage and update the variable numbers on need quickly.
- Always prefer to use Data Validations tools where ever possible. This will minimize the errors in Data Entry process and makes it easier to analyse and churn some reports out of it. Data Entry Tips and Tricks Here..
- Better not to directly manipulate the Raw Data especially when the data source is external, make your reports on it in separate sheets. This will help you reduce the errors and improve the efficiency. (This is in line with point 1)
- Keep your charts and graphs neat, clean with subtle color formatting (better avoid dark/hard colors). Do not forget to use common colors across, align your charts if you plan multiple charts in single sheet, label chart axes as needed. Bottom line it never-ever leave the charts in their default level. How to make neat and clean charts check here…
- Excel is great tool and with virtually unlimited possibilities, so there is always a better you could do your job. Ask yourself “How to do it in a better way which would save some time when you do it next time?”.
- Make the Data Structure and Layout which best suites with Excel than making it to please an eye. Here I am not underestimating the power of formatting which is quite essential to make the reports presentable. But every header, subtotal, empty columns/rows you place among the data makes it far less useable.
- Use proper cell reference (the dollar signs) when you refer cells. This will help you write a formula once and use it everywhere in the data range, which intern saves your time, easy to manage formulas and minimises the possibilities of errors
- Get used to using the Named Ranges. Named Ranges allow you to assign a meaningful name to a cell or a range of cells and then use that name instead of the actual cell reference in your formulas. A name is easier to remember when you are constructing your formulas. Naming cells can be helpful in creating a self-documenting formula.
- Avoid referring whole column or row in your formulas. Be careful, this will make your spreadsheets slow and time eaters especially with Excel 2007 and above versions which have more than a million rows!!. check here for top excel formulas cheats…
- Secure your spreadsheets and cell ranges with password, at times it becomes difficult to identity the incorrect or missing formula caused by typing in mistake.
- Practice data formatting diligently (instead of decorating) along with the descriptions, this will help the user understand where to enter the data and how to use it with out much a need of help. Do not forget to understand the your company/management/customer color codes and there representations, otherwise you may end up in convincing people what actually you are referring with those color formatting, than the reports speak themselves.
- Do not use merge cell option unless its quite necessary. Merged cells are hard to edit, non scalable and will eventually become the bottle necks when you want to improvise or automate your spreadsheets. Instead use Center Across Selection in cell alignment, which actually gives the same effect as merge cell but without actual cell merge!
- Make a habit of placing the hyperlinks in summary/report sheet to the detailed data sheet and back again. This will actually help the senior management/end user to quickly navigate to the source rather than decoding the formula to find the data source.
I am sure I would have missed a few key ones – these are very much my personal ones – and I would love to hear yours in the comments.
0 Comment to "Best 15 Key Principles for Excel Best Practice"
Post a Comment