Tag Archives: absolute cell references

Worksheet Design Tips

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.

  • Your website was easy to navigate and informative.” “This was my first use of your resources but I’m sure it won’t be my last. Thank you for your excellent course material and succinctly stated instructions which expedited order and payment of course selection, completion of exam, and immediate availability of Certificate of Completion. A great experience. Thanks.

    - Draper

  • Overall I was very pleased with the course. Previously I knew just enough about pivot tables to be dangerous. Now I see the power that they have and I feel much more confident using pivot tables. The course materials were very clear and well prepared. I’ll be looking at your website for future CPE hours.

    - Scott

  • I loved the course as it had current cases which I am familiar with. Best ethics course I have taken in recent memory. Thanks!”

    - Kathy