Category Archives: Excel Tips

Get a list of A to Z in #Excel

Posted on September 28, 2019 by

Have you ever tried to input “A” and then drag it down with a hope of getting a list of A to Z? If you tried (and without success), you may be wonder how I did it? You may find … Continue reading →

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

You think you know Copy and Paste in #Excel?

Posted on September 7, 2019 by

11 Excel Copy and Paste Tips and Tricks you should know Perhaps Ctrl+C and Ctrl+V are the pair of shortcuts you use on a daily basis.  And probably you learned that on the first day your learned Excel.  Nonetheless, do … Continue reading →

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

The proper way of copying worksheet in #Excel

Posted on August 27, 2019 by

It is not uncommon for people to copy entire worksheet to another sheet (be it on the same workbook, or another workbook, even a new workbook). Nevertheless, are we doing it the right way? Many people would do so by … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

Copy chart formats to other charts in Excel

Posted on August 21, 2019 by

If you know Format Painter, the on top of your mind solution should be using Format Painter to copy Chart Formats to other charts.  However, it doesn’t work… 😦 No worries.  Copy and Paste comes to recuse!! Really?!

Rate this:

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

Four Text to Columns tricks you cannot miss in Excel

Posted on August 18, 2019 by

Have your heard about “Text to Columns”?  A big icon grouped into Data Tools of Data Tab of Excel?  Be your answer Yes or No, you do not want to miss the following tricks that may help you transform data … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

Multiple short videos about using Fill in #Excel

Posted on August 11, 2019 by

If you have followed my blog for a while, you should know that I have created my YouTube channel recently.   And a playlist – Excel Magic in One Minute was created in early June this year.  So far, I’ve created 25 videos in … Continue reading →

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

Interesting behavior of Copy and Paste

Posted on August 3, 2019 by

This sounds silly, I know.  To achieve what is stated in the above scenario, what we need to do is simply Select “January to March” from the slicer Copy the range C4:C9 Paste to C13 Then repeat the above steps … Continue reading →

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

Do your random numbers stay the same when you copy and paste in Excel?

Posted on July 20, 2019 by

This is an extension of my previous post – Freeze a random result with mouse trick. In my experience using Excel, from Excel 2000 to 2010, and recently 365 (I’d never used Excel 2013 though), I have experienced the same strange … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , | 10 Comments

Freeze a random result with mouse trick

Posted on July 13, 2019 by

Perhaps you have tried to use Excel to generate a random number for a lucky draw. That is easy.  For example, if you want to generate a random number, you can use the function RANDBETWEEN. =RANDBETWEEN(1,1000) This will give you … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , , | 6 Comments

Smart Lookup in #Excel 2016 or later

Posted on June 22, 2019 by

Have your heard of Smart Lookup?  Don’t confuse it with Excel Functions.  It is nothing related to formula writing in Excel.  Simply consider it is a web search engine (Bing) embedded in Excel. Watch it in action, and you will … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

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