Category Archives: Excel Tips

Excel Magic in One Minute

Posted on June 15, 2019 by

Last Friday was a public holiday in Hong Kong.  Yes, a long weekend!  Guess what I dd over the long weekend?  I’ve created a playlist – “Excel Magic in One Minute” on my YouTube Channel. The name says it all … Continue reading →

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

Unstack data across columns with #Excel Pivot Table

Posted on May 29, 2019 by

Another commonly seen problem in daily work.  Many hours are spent on reshaping data, we all know that.  Nevertheless, if you are good in Excel, doing this task is not difficult at all.   Indeed, there are many ways to solve … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , , , , | Leave a comment

Quick Tip – #Excel AutoSum

Posted on April 13, 2019 by

While SUMMING data is probably the most common task in Excel, I am surprised that many people are not aware of this Excel feature – AutoSum.  It is super handy especially when you know the shortcut of it, which is … Continue reading →

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

#Excel Add current selection to filter. What it does?

Posted on April 7, 2019 by

Get multiple filter criteria with Excel Auto-Filter This post is in response to the following Mr.Excel video where Mr. Excel Bill Jelen solved the problem with Advanced Filter. Indeed I got a similar question from a colleague recently for filtering … Continue reading →

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

#Excel Flash Fill in action with 16 examples

Posted on March 31, 2019 by

Flash Fill was introduced in Excel 2013.  After 6 years, it is still a hidden gem of modern Excel that most regular Excel users do not even know its existence.  Whenever I show to my colleagues what Flash Fill is … Continue reading →

Rate this:

Posted in Excel Tips | Tagged | 2 Comments

Did you know… there are many nice Excel Templates readily available?

Posted on February 3, 2019 by

Did you know that there are many nice Excel Templates readily for usage? Next time when you start a New workbook, browse or even search for a template that suits your need. Or you may start from a template. Why re-invent a wheel? Continue reading →

Rate this:

Posted in Excel Tips, Template | Leave a comment

Hard-coded range expands automatically with #Excel Table

Posted on January 26, 2019 by

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

Posted on January 19, 2019 by

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

Highlighting Top X values with Icon Set in #Excel

Posted on December 22, 2018 by

This post is a continuation of the previous one – Highlighting Top X values with Conditional Formatting in #Excel So I will go straight to the point.  For background information, please read the previous post. 🙂 To insert Icon Set Select … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

Highlighting Top X values with Conditional Formatting in #Excel

Posted on December 15, 2018 by

Well… think this topic is too simple?  How about doing this with a dropdown menu to make the Top X a dynamic one? with Icon Set? You may download a Sample File to follow along. Let’s start with the basics … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , , , , | Leave a comment

Customize Alt 1 to 4 to become the favorite #Excel shortcuts of your own

Posted on December 8, 2018 by

When talking about keyboard shortcuts, there is always discussion on what’s the most time-saving shortcuts, what are the top X shortcuts you should learn, what is the most frequently used shortcuts, etc… Just to name a few, Ctrl+S to save, … Continue reading →

Rate this:

Posted in Excel Tips, Shortcut | Tagged , | Leave a comment

Using Skip blanks in Paste Special

Posted on December 2, 2018 by

What the Skip blanks does? Have you ever had this question?  From my experience, 99.9% users (I work with) have no idea what it is or even are not aware of that option in Paste Special. Are you one of … Continue reading →

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

Dynamic Shrinking Dropdown with Dynamic Arrays in #Excel 365

Posted on November 14, 2018 by

The recent HOT topics about Excel should be the new Dynamic Arrays!   After watching all the amazing demonstrations about the new Dynamic Arrays, I’ve decided to sign up to the Office Insider (Fast) programme (for Office 365 subscriber only).  … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , , , , | Leave a comment

Pinned Folder for #Excel 2016 – A workaround

Posted on November 8, 2018 by

Dear Excel 2016, you have made so many enhancements that help me a lot in work… but why you took away my favorite Pinned Folder by giving me a half page of white space? My workplace just got upgrade to … Continue reading →

Rate this:

Posted in Excel Tips | 7 Comments

Calculating CAGR with Goal Seek in #Excel

Posted on October 10, 2018 by

when the starting point is a negative number… Calculating CAGR is not difficult, all we need is the starting value, ending value and the number of periods.  Then we use the formula: CAGR = (Ending Value / Beginning Value ) … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

Adding worksheet background in #Excel

Posted on October 3, 2018 by

How to add worksheet background in #Excel? If there is a secret recipe for an interesting Excel training, it would probably be starting with something interesting… 🙂 So in a recent in-house training, I’ve prepared a special Sheet1 in the … Continue reading →

Rate this:

Posted in Excel Tips | 4 Comments

Same day last year…

Posted on August 8, 2018 by

Avoid Overthinking Getting same day of last year using Excel formula In retail, it’s very common to compare sales of same day, not same date, of last year.  If you are not in retail sector, you may wonder what is … Continue reading →

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

Creating % of Total, Running Total in a few clicks with #Excel Quick Analysis

Posted on July 5, 2018 by

This post is about showing you how to perform a common task of adding a column of % of Total Running Total with Quick Analysis in Excel 2013 or later. Latest Excel makes things easier, just that you may not be … Continue reading →

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

VBA to make selected sheets “Very Hidden” in #Excel

Posted on April 26, 2018 by

VBA to make selected sheets “Very Hidden”;  all sheets visible Weeks ago, I showed the way to unhide a very hidden sheet in #Excel via Visual Basic editor (VBE).  You may refer to that post HERE.  Technically we could follow … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , , , | Leave a comment

Save a Read Only copy

Posted on April 19, 2018 by

When we have created a wonderful #Excel template that is to be shared with others, we don’t want users to distort, if not destroy, the template someday.  Nevertheless we would like to leave as much “flexibility” as possible to users … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment