Excel Formula Best Practices to Boost Efficiency
5 Do not rely on hardcoding values while constructing formula in Excel.
If you are a seasoned user of Microsoft Office Excel 2016, for sure you have created or wrote tons of formula to aggregate and analyzing data in Excel. In this article we’re going to talk about what is the sins you should not be repeating again while writing one. But if you’re new, you have definitely come to the right place because this is something you should not be doing, do not hard code value while constructing an Excel formula.
What is Cell Referencing?
My best advice when it comes to creating a report or anything in Excel, list and enter every single data and store into range and cells that you will be needing later on. Plan ahead! It is very much important to label everything in the cells, so it makes the client (could be you yourself) understand the report within a glance. Labelling and listing down everything in the cell allow others to view your report quickly.
Let’s do a quick comparison between the report A and B. The report is to show the total sum of the payable salary amount to the sales representative in XXX company.
After viewing those 2 reports, do you realize that the person who created the Report A did not list down the commission rate while in the second report which is Report B it is shown above the Total Payable amount? Even that small little data it does make a huge difference. Reason being for the person who created the report A did not list down the 4.5% commission rate in the spreadsheet is because the 4.5% is being hard coded into the formula of Payable Salary. You will be able to see the 4.5% only while the cell is in edit mode as shown below.
*To turn the cell into an edit mode, you can achieve it by double clicking or hitting F2 keystroke.
In the middle of the process of turning the cell into editing mode just to check out the commission is definitely not a very productive way to view and deliver report. When the client is viewing the Report B, it is more pleasant to view because all the information is as displayed. Easier to view and quicker to analyze the report without wasting anytime.
The conclusion is to make sure label and list down every single data you will be needing, whether to use it in the formula or for display purposes in the cell. Well that makes a great report.
Cell Reference made easy
While all the data are being properly listed and display in the spreadsheet, it is not only makes the client review the report in a quicker manner, it is also makes you the person who constructing those crazy formula easier.
I always emphasize a lot while writing any formula, it can be the simplest kind of formula like 1+1, refer the data from cell reference instead of hard coding the value into the formula. The figure below shows 2 different kind of formula, one is simply hard coded value and another one is reference towards to the cell that holds the value.
The value is hard coded inside the formula.When the cell is in editing mode, this is what you will be seeing.
The example below shows the value is embedded within the formula, without turning the cell into editing mode, the user will not realize the cell is actually containing formula which is misleading and difficult to review.
On the other hand, the sample below has all the value being display and listed in the cell A2 and B2. At the meantime, the data are proper labelled with header Col A, Col B, A+B. This allow the client (person who review the report) understand what the report is trying to express.
The value is the amount of sum from A2 and B2.When the cell is in editing mode, this is what you will be seeing.
The another reason of why hardcoding value data within the formula is not being encourage(difficult to review) is because it is very hard to edit and modify when there are changes in the value.
Let’s say the number now has to be update and change into 2+2. To modify the value of a formula that has the number being hardcoded into the formula, the user has to purposely turn the formula into and editing mode and then carefully change the number into 2+2.
Instead if we’re looking at the formula which is reference towards the cell, the editing makes so much difference and easier to edit.
Are you interested to know more about maintaining list in Excel Sheet? Check out the previous posts (Part 1 – Part 4) and stay tuned for more.
