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)

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

ExcelUser Blog

Work smarter by Mastering Functions in Excel


Transforming your Business with Power Pivot and Power BI

Excel Esquire

Helping lawyers make the most of Microsoft Excel

ASAP Utilities for Excel – Blog

Work smarter by Mastering Functions in Excel

The Analyst Cave | Excel, VBA, programming and more

Work smarter by Mastering Functions in Excel


zum Excel Profi werden

Ann's Blog

Equipping you to collect, analyze, and visualize data

The Ken Puls (Excelguru) Blog

More geeky stuff from the author of

Spreadsheet Page Blog

Work smarter by Mastering Functions in Excel

Oz du Soleil

Excel Training, Education & Entertainment

Peltier Tech Blog

Peltier Tech Excel Charts and Programming Blog


Microsoft Excel Solutions and Training

Let's Talk Excel

Q&A about Excel


Doug Glancy's Excel Site

Option Explicit VBA

A blog of Excel, Dashboards, Visual Basic for Applications, Data Analysis, Operations Research, and Visualizations.


Advanced Excel Techniques with XOR LX

Excel Unplugged

Collection of Excel Know How and little Excel Tips and Tricks

Excel and UDF Performance Stuff

Charles Williams on 'Making Excel go Faster'

Contextures Blog

Save Time and Money in Business


Work smarter by Mastering Functions in Excel

Bacon Bits

Work smarter by Mastering Functions in Excel - Learn Excel & Charting Online

Fresh Excel Tips, Tricks, Charts, Tutorials, Downloads, Dashboards and Visualization Showcase for your Inspiration and Productivity

%d bloggers like this: