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.

Identify missing files in a folder with Power Query

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

Quick Tip – #Excel AutoSum

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?

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

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

Birthday Treat Calculator

A powerful tool for a silly thing.  Why not?  🙂 Long time ago, I wrote a blog post How much do I need to pay? which was not really related to Excel but calculation of a random daily event.   … Continue reading

Rate this:

Posted in General | Tagged | Leave a comment

Extract FirstLast names in CamelCase with #Excel formula

In the previous blog post, we see how Flash Fill extracts First Name and Last Name from an email address in a format shown above.  I’ve also recorded a video for that post. Flash Fill is so smart to detect … Continue reading

Rate this:

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

Turn CSV data into insight like a pro with Excel 2016

Many people are not aware of how easy-to-use and powerful modern Excel is. I mean Excel 2016 or later. In this post, I will show you how to turn CSV data into insight in a just a couple of minutes like a pro. Techniques used: Flash Fill, Quick Analysis and two new chart types (Histogram and Pareto). Continue reading

Rate this:

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

My second #Excel Video on YouTube

Glad to share with you my second video here.   This video is about how to plot a compelling to compare Actual vs. Budget, Target and Last Year data.  The written instruction is available in previous post – A compelling chart in three … Continue reading

Rate this:

Posted in General | Tagged | 2 Comments

A compelling chart in three minutes…

In business world, we often compare actual sales to various benchmarks such as budget, target, and last year. In this post, I am going to show you step-by-step how to make a compelling chart for this purpose. This is basically a simplified version of bullet chart, and is super-easy to create. I hope you find it useful and relevant. Continue reading

Rate this:

Posted in Chart | Tagged | Leave a comment

My first #Excel video on YouTube

Kung Hey Fat Choi!  This week is Chinese New Year.  I wish you a fruitful, healthy, and rewarding Year of Pig! I have almost one week off for CNY holiday.  I have cleaned my PC and spared 50G+ space in … Continue reading

Rate this:

Posted in General | 2 Comments

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

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

Learn something amazing from Mynda’s #Excel Dashboard webinars

Free Excel Dashboard Webinars We don’t know what we don’t know.  The best way is to learn something new is to explore the web (or world), and/or learn from experts. Sometimes we just don’t know, or even don’t expect what … Continue reading

Rate this:

Posted in General | Tagged , | Leave a comment

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

Highlighting Top X values with Icon Set in #Excel

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

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

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