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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

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

Alluring Analytics

A Power BI Creator Blog


Work smarter by Mastering Functions in Excel

roads bel travelled

Exploring open roads without breaking the bank


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 Excelguru Blog

More geeky stuff from the author of

The Spreadsheet Page

Work smarter by Mastering Functions in Excel

Peltier Tech Blog

Peltier Tech Excel Charts and Programming Blog


Turn your data into opportunity

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

My Online Training Hub

Work smarter by Mastering Functions in Excel

Bacon Bits

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

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

%d bloggers like this: