Good habits of using Excel

In this post, I'm going to list five good habits that we should cultivate when using Excel.


1. Always use range names

Range names allow you to refer to a range of cells fast and accurately.
This is especially useful if you need to refer to the set of cells in formulas.
It will be even more useful if you need to refer to these sets of cells from another worksheet!

This is how you do it!

To create a Range Name in Excel:

Using Excel Range Name

 - Highlight the range of cells to be named (I like to just highlight the data that I want and name it using its title)
-Give it a name at the highlighted place
- Remember to press ENTER (Done)
-You can now refer to cells A2:A5 in this specific worksheet just by using "Sales" in your formulas in any other cells!


2. Always save your work

We do not want to lose our hard work at the end of the day for whatever reasons.
A good habit is to save your work even before you start doing anything in the worksheet using a suitable name. If you need to include Macro in your Excel file, remember to save as"Excel Macro-Enabled Workbook" or .xlsm if you just want to keep data and formulas then you can save as "Excel Workbook" or .xlsx.

For Macro users, actions done by Macro are not reversible using "back" or Ctrl+Z, so it is essential that you save your work before you run an Macro especially during development stage.

Excel Save

3. Always use formulas that applies to a range (preferably using a range name), rather than using formulas that applies to many specific cells.

Using the example in range name earlier,

For instance you want to sum cells A2:A5
Method 1: You can use A6=A2+A3+A4+A5
Method 2: You can also use A6=Sum(A2:A5) or even better if you use =sum(sales)

The difference is if you need to add in another entry between A2 to A5, method one will still sum cells A2+A3+A4+A5 even if now you have A6 to sum. Whereas for method 2, the sum will work even if you add cells in between the range A2 to A5.


4. Always copy raw data into a new sheet

This is to ensure that you have a backup data to fall back on in case you messed up the data.
This is how you do it:

- Right click sheet1 and select Move or Copy
- Select Ok and then rename the sheet

Back up Excel Raw Data

5. Always use a separate sheet for intermediate steps

This is to ensure that your worksheet looks neat and tidy.
Also, you will have less risk of messing up your "working ranges".

For example. if you want to do a lookup in sheet1, it will be better if you add a new worksheet and name it as "control". You can then populate the range in "control" and give it a name. It will be easier for you to just lookup for the range. Your sheet1 will be kept neat and tidy in this case and you are less likely to mess up your lookup range.


This is how it will look like:

Separate Excel worksheets for intermediate steps