No Pain No Gain

No Pain No Gain

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.

No Pain No Gain 1

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.

No Pain No Gain 2

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.

No Pain No Gain 3

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)


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in General and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

The BIccountant

New Generation Finance - Accounting - Controlling using Microsoft BI stack

Alluring Analytics

A Power BI Creator Blog


Work smarter by Mastering Functions in Excel


Your next stop in mastering Power Query and Power BI

ExcelUser Blog

Work smarter by Mastering Functions in Excel

P3 Adaptive

Leading Consultants on Power BI and the Microsoft Data Platform

Excel Esquire

Helping lawyers make the most of Microsoft Excel

ASAP Utilities Blog

Work smarter by Mastering Functions in Excel

Analyst Cave

Work smarter by Mastering Functions in Excel

The Spreadsheet Page

Work smarter by Mastering Functions in Excel

Peltier Tech

Peltier Technical Services - Excel Charts and Programming


Turn your data into opportunity

Let's Talk Excel

Q&A about Excel


Doug Glancy's Excel Site

%d bloggers like this: