Author Archives: MF

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 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.

Four Text to Columns tricks you cannot miss in Excel

Posted on August 18, 2019

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

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

Free Excel Dashboard Webinars you should not miss

Posted on August 7, 2019

Have you ever had an Excel moment like this? “My Excel reports take way too long to update each month/week” “I spend hours collating and cleaning data, updating formulas and charts and then no one reads my reports anyway, what’s … Continue reading →

Rate this:

Posted in General | Tagged , | Leave a comment

[Guest Post] DRAW – The New Tab in Office 2019/365 for INKING

Posted on August 4, 2019

This is a guest post by Aprajita of ExcelChamps. Let me ask you something. Suppose you are presenting data to the management and suddenly an important point strikes your mind. Will you look around for a piece of paper and … Continue reading →

Rate this:

Posted in General | Tagged , , | 1 Comment

Interesting behavior of Copy and Paste

Posted on August 3, 2019

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

Updates on Excel Magic in One Minute

Posted on July 27, 2019

Six weeks ago, I created my YouTube playlist – Excel Magic in One Minute. As of today, I’ve already published 17 videos to the playlist.  Meaning 2-3 videos per week.  Not too bad as a weekend YouTuber (newbie), I guess. … Continue reading →

Rate this:

Posted in General | Tagged | Leave a comment

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

Posted on July 20, 2019

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 , | 8 Comments

Freeze a random result with mouse trick

Posted on July 13, 2019

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

How to sort bar chart in descending order?

Posted on July 6, 2019

Sound like a silly question, but it is a bit confusing indeed. One day, a friend asked me how to sort a bar chart in descending order.  I told her to sort the data. She replied: “I did.  The data … Continue reading →

Rate this:

Posted in Chart | Tagged , | Leave a comment

Send a cell content to Google search

Posted on June 29, 2019

This is a continuation of previous post, in which I talked about the potential use of Smart Lookup for an Excel  dashboard.  However there is limitation.  It drove me to think of an alternative. Instead of asking user to trigger Smart … Continue reading →

Rate this:

Posted in Formula | Tagged | Leave a comment

Smart Lookup in #Excel 2016 or later

Posted on June 22, 2019

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

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

Apply Conditional Format for alternate row color based on groups…

Posted on June 8, 2019

…that works even when filter is applied. The situation: We have a table that we would like to apply color banding based on groups.  We can achieve this by inserting a helper column to identify the sequence of each group, … Continue reading →

Rate this:

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

Unstack uneven data across columns with #Excel #PowerQuery

Posted on June 5, 2019

This is a continuation of the previous post, in which I showed you how to unstack two columns of data by using Pivot Table as a helper, and together with a couple of excel tricks.  As mentioned, that approach is only … Continue reading →

Rate this:

Posted in Power Query | Tagged | Leave a comment

Unstack data across columns with #Excel Pivot Table

Posted on May 29, 2019

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

Waterfall chart is just a few clicks away with #Excel 2016

Posted on May 18, 2019

Did you know, you can plot a Waterfall chart in #Excel in less than a minute…Provided that you are using Excel 2016 or later! 🙂 No Kidding! You may download a Sample File to follow along.

Rate this:

Posted in Chart | Tagged , , | Leave a comment

Split cell contents separated by line feed into rows with #Excel #PowerQuery – Part 2

Posted on May 13, 2019

Last week, I showed you how to split cell contents separated by line feed into rows with Power Query.  That was easy when we are dealing with only one column.  If you don’t know how, you may read the post … Continue reading →

Rate this:

Posted in Excel in Action, Power Query | Tagged , | 4 Comments

Free Excel Dashboard Webinars by Mynda Treacy

Posted on May 11, 2019

If you’ve ever felt like: “My Excel reports take way too long to update each month/week” “I spend hours collating and cleaning data, updating formulas and charts and then no one reads my reports anyway, what’s the point?” “If I … Continue reading →

Rate this:

Posted in General | Tagged , | Leave a comment

Split cell contents separated by line feed into rows with #Excel #PowerQuery

Posted on May 4, 2019

After an in-house training, a colleague came to me and asked if there is a way to do the following: The problem here is the contents in cells are separated by a line feed (line break).  A cell may contain … Continue reading →

Rate this:

Posted in Power Query | Tagged , | Leave a comment

Identify missing files in a folder with Power Query

Posted on May 1, 2019

It is a common task to compare two lists in Excel.  This can be achieved with MATCH function.  But what if you want to compare a list of filenames stored in your spreadsheet, with the files you’ve got in a … Continue reading →

Rate this:

Posted in Excel in Action, Power Query | Tagged | Leave a comment