“Good habits are worth being fanatical about“
– John Irving
And that is so true. I must recommend you these 10 habits that have worked for me. With these I have saved countless hours of manual work, have been extremely productive and have always attained more refined and accurate output. Here we go!
Habit #1 Use Keyboard Shortcuts over Mouse
I have always blown trumpets for using keyboard shortcuts over mouse. Two awesome things happen when you use keyboard over mouse
- There is an immense surge in your productivity for a reason that your hands do not shuffle between keyboard and mouse to perform tasks on Excel. Keyboard becomes your One Stop Shop
- You look like a stud geek working at lightening speed with options being selected automatically, filters being applied, colors changing, screen effects taking place. You are doing it all with keyboard strokes. WOW!!
- Even if you don’t want to fall for the second point, the first point is good enough to get you started!
I wrote 2 post on Excel Shortcuts. They will help you get started
Habit #2 Do not hard code in Formulas
There is a saying that “Today’s convenience might become tomorrow’s inconvenience”. This so much applies to hard coding values in formulas. Let’s take this example
We are calculating bonus, 1.2 times the salary. So we directly multiply the salary by 1.2, which is good for a rough calculation but certainly not good for an MIS / Model because the bonus might change to 1.5 x
The correct way is to link the cell in the formula
Although it is very tempting to hard code the values in the formula but it might turn out to be a nightmare when you have to manually change the value in a dozen cells
PS for Habit #2 I don’t know if that is a saying or not (now it is). I just made that up to support my point 😆
Habit #3 Do Not Read the formula in the formula tab
This is the most weird thing I have seen but it is rampant. Consider this simple formula and decide for yourself where do you think it is better to read and interpret the formula
In the Formula Tab
Since you trace the location of the cells manually. It takes forever to read and understand the formula
In the Cell Edit Mode (by pressing F2)
Always press F2 to read your formula because excel uses different colors to indicate which cells are linked in your formula, which becomes very simple to read and understand
Habit #4 Do Not apply V-Lookup to the Entire Column
If you want to get started on Vlookup you can click here and here
While applying VLookup do not select the entire column as a Table Array. The reason is that, this approach prevents you (or take caution) to enter any other data below your actual range, since the entire range is fed in the VLookup Formula
Use a specific range of cells in the Table Array with absolute cell referencing. This way you can manage space in the spreadsheet better
Habit #5 Understand Circularity in Excel – Circular references
I can best explain this with an example. Let’s say
- Our company contributes 10% of the Net Profit (assume Sales – Expenses) to Social Work
- Social work being an expense for the company, it will appear in the expenses and will eventually affect the Net Profit
- The equation looks like this..
Here is how to do this in Excel
- Enable Circular Calculations in Excel
- Go to Excel Options (Shortcut is Alt f i for Excel 2007 and Alt f t for Excel 2010)
- In the Formulas Tab tick the Enable Iterative Calculation
- Develop the spreadsheet
- Calculate Profit (Sales – Total Expenses)
- Calculate Social Expense (10% of Profit)
If you would have done this without enabling circular calculation, excel would have given a 0 (zero) for Social Expenses and Profit because by default iterative calculations are turned off
Habit #6 Creating versions of your work
I have developed this habit by losing a lot of work and then eventually redoing it all over again. When you are working on a large spreadsheet (like MIS, Financial Model, Dashboard etc) it is becomes imperative to make versions of your work as you go because you never know when you would be needing the last version
Here is structure that I follow to keep versions for my work
- Number the version as V 1.1, V 1.2 as you go along revising the work
- Also add a brief commentary about what changes have you made in this version
Habit #7 Breaking down long formulas into smaller logics
It is far easier to build complexity that to understand it. When you are trying to build the most complex formula / VBA Code of your life, break down long formula in smaller parts
It will not only help you to easily revisit the formula when needed but also develop a structured problem solving approach. Here is an example on How to find Indian Quarters
Formula Broken Down into Parts
V/S
A Long Formula
Habit #8 Use Cell Naming
Cell naming in Excel is somewhat synonymous to naming people. Peter sounds and reads much better than Birth No 4129876008 😆 you can replicate a similar effect in your excel spreadsheet by Using Cell Naming
Non Naming
V/S
Naming Cells
Habit #9 Do not Use Pie Charts
Pie charts have more respect than they deserve! I can prove this! Let’s play a pie chart fallacy game
Pie Chart – Read the pie chart and answer the question ?
V/S
Column Chart – Now read the same information presented in the column chart and answer the question
Our eyes are more receptive to interpreting the height (in the column chart) than the area (in the pie chart). Although we can argue about applying labels to the pie chart but if you had to answer by just seeing the labels then the pie chart is of no use
Habit #10 Spell Check your Spreadsheets
Just as they way it is important to check spellings in word documents, I don’t find a reason to omit checking spelling in an Excel Spreadsheets 🙂
All you got to do is to Press F7 😎
More Excel Resources
- Hiding and Un-hiding options in Excel
- Unusual Shortcuts that I use in Excel
- Sheet Protection Options
Share your Good Habits
Please share if you have a habit while working on Excel and that has paid you off. Also share how many of the above do you follow?
