Monthly Archives: January 2019

Hard-coded range expands automatically with #Excel Table

The INDIRECT trick of using Structured Reference in Conditional Formatting   Is it a feature or a bug? This is an extension of the previous post, in which we discussed the use of INDIRECT trick to deal with Excel Table in Conditional … Continue reading

Rate this:

Posted in Excel Tips | Tagged , , | 1 Comment

The INDIRECT trick of using Structured Reference in Conditional Formatting

When setting up conditions for Conditional Formatting in Excel, have you ever tried to set a formula that refers to a Excel Table, but failed?

First of all, why we want to refer to Excel Table in Conditional Formatting? There is one simple answer: To accept new data automatically. 
To use Structure Reference in Conditional Formatting, we need an INDIRECT approach which is discussed here. Continue reading

Rate this:

Posted in Excel Tips | Tagged , , , | 2 Comments

Highlight weekends and holidays using Conditional Formatting in #Excel Part 2

Conditional Formatting with formula could be tricky (or difficult), especially when the data layout is bad because you have to very clear and careful on the “applied to” range and the absolute/relative references set in the formula. Otherwise, it won’t work and can be quite confusing, if not frustrating. 😛

In this post, I will talk about a case that you will see how empty rows in a data set would complicate the process in setting up conditional formatting. And then I will show three different approaches to tackle the issues. Hope you find it helpful. Continue reading

Rate this:

Posted in Formula | Tagged , , , | Leave a comment

Highlight weekends and holidays using Conditional Formatting in #Excel

In this post, I will show you step by step how to set up conditional formatting to highlight weekends and public holidays with Excel. The key is to set the formula right. Having a proper layout will make the whole thing more easier, which will be discussed in the next post. Continue reading

Rate this:

Posted in Format | Tagged , , , | 3 Comments

[Share] Introduction of #Power Query M code by ExcelIsfun

Happy New Year!  Wish you all an Excel-lent year of 2019! As an Excel nerd, I’d like to spend my holidays on learning Excel… haha… I know I am abnormal.  ;p  My recent love of Excel is Power Query (M) … Continue reading

Rate this:

Posted in Power Query | Tagged | Leave a comment