Learning is a process, in which I believe the easier we take, the less we get.
I am glad that I’ve learnt Excel starting with Excel 2000. Yes it is more than a decade ago.
Throughout the decade, I have been impressed by the new features being added version by version. Just to cite a few examples:
- Table (previous known as List in Excel 2003) offers you great flexibility of dynamic range but it demotivates you to learn how to define a dynamic Named Range using OFFSET or INDEX.
- Table structured reference is great but a bit scary if you have never used a Named range in formula.
- Flash Fill in Excel 2013 is awesome, but it gives you great excuse of not learning TEXT functions like LEFT, RIGHT, MID, etc.
- Starting from Excel 2007/2010, to understand a Function is much easier than before, but to master a function is not.
Dated back in Excel 2003, we need to go into the function dialog box to understand the syntax of a function and how it works. Now we will see a list of available functions when we start typing = and a character. For example, when you type =D, you will see all functions start with D. Then once you select the function you need (by using the up and down arrow key), you will see the syntax of it right away on the tip box. That is cool actually.
However, because of this, we seldom go into the function dialogue box and study the details of the function. In this way, we restrict ourselves to learn just the basic, and overlook the potential power of a function. To give you an example, look at the NETWORKDAYS.INTL function below.
Once we select the function, we know what it does.
When we move on, the tooltip guides us through each argument required; and shows options if any, which is quite informative. We don’t need to remember which number represents Sat & Sun as weekend.
However did you know that there is an added control, for deciding where weekend falls onto, offered to you with this function? i.e. you may actually customize weekend on Thu and Fri by putting a text string “0001100” in the third argument.
This is from Excel Help for NETWORKDAYS.INTL function:
Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. Using 1111111 will always return 0. For example, 0000011 would result in a weekend that is Saturday and Sunday.
Credit of this example goes to Gašper Kamenšek @ ExcelUnplugged, where his post on the tip of using NETWORKDAYS.INTL drove me to study more about it.
I am often asked by participants in my in-house Excel training class: “Why you know this and that in Excel?”. My answer to this question would be something like: “Reading books”, “Google”, “Reading blogs of experts”, “Practicing”, “Experiment”, etc.. What do you expect?
No Pain No Gain. (No Offence)
wmfexcel 