10 Excel Habits

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

  1. 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
  2. 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!!
  3. 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

10 Excel Habits1

 

The correct way is to link the cell in the formula

10 Excel Habits2

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

10 Excel Habits3

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)

10 Excel Habits4

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

10 Excel Habits 5

 

Use a specific range of cells in the Table Array with absolute cell referencing. This way you can manage space in the spreadsheet better

10 Excel Habits 6

 

Habit #5 Understand Circularity in Excel – Circular references

I can best explain this with an example. Let’s say

  1. Our company contributes 10% of the Net Profit (assume Sales – Expenses) to Social Work
  2. Social work being an expense for the company, it will appear in the expenses and will eventually affect the Net Profit
  3. The equation looks like this..

10 Excel Habits 7

Here is how to do this in Excel10 Excel Habits 8

10 Excel Habits 9

  1. 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
  2. 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

10 Excel Habits 10

  1. Number the version as V 1.1, V 1.2 as you go along revising the work
  2. 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

10 Excel Habits 11

 

V/S

A Long Formula

10 Excel Habits 12

 

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  

10 Excel Habits 12

 

V/S 

Naming Cells

10 Excel Habits 14

 

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 ?

10 Excel Habits 15

V/S

Column Chart – Now read the same information presented in the column chart and answer the question

10 Excel Habits 16

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

  1. Hiding and Un-hiding options in Excel
  2. Unusual Shortcuts that I use in Excel
  3. 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?

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI