Excel Tips on Entering Data and Formatting
Brian Cane’s Excel Tips show how to effectively enter information and to format more effectively.
His source – a public domain Ibbotson (the famous Nobel Laureate) workbook. Please click on the attached sheet to view.
A go-with Tip1 is when a formula references the cell above.
So the cell above is the header and causes the formula to #error! and that is why users have a different formula for the first cell in the column. In my example tip1 in the prior attachment I show how to deal with the situation when the general formula requires a 1. When the formula needs a zero (most often the case) then use the N function.
So if for example you have a common BoY to EoY (beginning of year in say column B to end of year in column F) progression where you code B2=0 and B3=F2 and fill down because coding B2=F1 will give a #Value!. But if you code B2=N(F1) you can fill that down because N(“EoY”) will return 0 and N(any number) will return the number. Thus avoiding different row1 formulas.
Thanks Brian for the tips.
I would like to add to Brian’s tips and emphasize that you should always check your formulas and make sure they are consistent and correct.
If you make the first entry a different formula, it is easy to forget that and you may try to copy that first formula down at a later point in time which could result in an incorrect answer. People sharing your worksheet may copy or use the original formula not realizing that it changes after the first entry. Another problem is that people frequently create a formula, copy it down and never check any of the other entries to make sure they are correct. Often- due to a lack of absolute references the formula in the third or fourth row may be incorrect even though the original formula in the first row is correct.- This happens to a lot of people using Vlookup tables. Also, sometimes people find an error and fix that cell but forget to go back to the original cell and fix the problem there and recopy it. Finally, hard coding numbers in can be problematic as those numbers might change. It is good worksheet design to make sure that formulas are correct and consistent and that cell references are used instead of hard coding numbers into formulas.