Apply Conditional Format for alternate row color based on groups…

Posted on June 8, 2019 by MF

…that works even when filter is applied.

Excel Tip - CF to make color bands

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, with the following formula:

In C2, input

=IF(A1=A2,C1,SUM(C1,1)) ‘copy down

and then apply conditional formatting based on the sequence.

Excel Tip - CF to make color bands1

Select the range A2:C29, then apply this formula to conditional formatting:

=ISEVEN($C2) ‘note the usage of $

Here’s the result!  It works great… until you apply a filter on subset.

Excel Tip - CF to make color bands

Why is that?

Because we are referring to a static (even) number for the color banding.  The number won’t response to the filter.  After the filter, only subsets of even number will be highlighted.  @_@

The desired result

We want the color banding to be responsive to filter, like the screen cast below:

Excel Tip - CF to make color bands0

With some twists to the formula for the helper column, this can be done magically.

You may download Sample File to follow along.  Continue reading →

Posted in Formula | Tagged , , , , | 3 Comments

Unstack uneven data across columns with #Excel #PowerQuery

Posted on June 5, 2019 by MF

Excel Tip - Unstack uneven data with Power Query

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 good for one-off as the result is static.  When you expect the data to be changed frequently; or will have new data added to it on regular basis, you would absolutely look for a more robust solution that can be updated easily.  With Power Query, a simple act of Refresh will bring you the result in a flash.  Continue reading →

Posted in Power Query | Tagged | Leave a comment

Unstack data across columns with #Excel Pivot Table

Posted on May 29, 2019 by MF

Excel Tip - Unstack data

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 this problem.

In the following video, I am going to demonstrate how to solve this problem using a Pivot Table as helper steps.   This approach is quick and dirty, which can be accomplished in a minute or two.  However, as I said, it’s quick and dirty so you will expect limitation for this approach – it’s for one-off only.  Whenever your data changes, you have to do it again.

Let’s watch it in action: Continue reading →

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

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

Posted on May 18, 2019 by MF

Excel Tips - WaterFall4

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. Continue reading →

Posted in Chart | Tagged , , | 1 Comment

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

Posted on May 13, 2019 by MF

Power BI Course.

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 here before you continue.

When we have two columns, like the screenshot below, it’s getting a bit complicating… but still very easy with Power Query.  It just requires a few more steps.

Split cells with linefeed (end)

Continue reading →

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

Free Excel Dashboard Webinars by Mynda Treacy

Posted on May 11, 2019 by MF

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 could just impress the boss I’d get that pay rise/promotion I deserve”

“I need to get my Excel skills up to date so I can stand out from the crowd of other job applicants”

“I’m not sure how to approach setting up my Excel workbook the right way, so it’s easy to build and maintain”

Then you should spend an hour, or even three hours, to watch the free Excel Dashboard Webinars by Microsoft Excel MVP, Mynda Treacy. Continue reading →

Posted in General | Tagged , | Leave a comment

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

Posted on May 4, 2019 by MF

After an in-house training, a colleague came to me and asked if there is a way to do the following:

Split cells with linefeed (Beginning)

The problem here is the contents in cells are separated by a line feed (line break).  A cell may contain two to five items and we want to split them into individual cells, into rows.

It reminded me of an old post How unorganized data could drive you crazy which I wrote about four years ago.  That’s the era before I know Power Query.

Although I used a trick of using Text to Columns to solve the problem before, this time the situation is different.  First, I want the cell contents to be split into rows, and unfortunately, we don’t have Text to Rows in Excel.  Second, we have more than one cells with this kind of unstructured data. 😦

This could be a nightmare for most Excel users.

But you know what, with Power Query, this can be solved at ease. 🙂

You may download a Sample File to follow along.
Continue reading →

Posted in Power Query | Tagged , | Leave a comment

Identify missing files in a folder with Power Query

Posted on May 1, 2019 by MF

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 folder?

Excel Tip - Power Query to identify missing files in a folder

Will you do it manually, by eyeballing? @_@

The first challenge is to get the list of files you have in the folder.  The second challenge is that fact that you will receive files and put them into the folder from time to time.  In other words, the folder content is changing.  You absolutely want a dynamic solution that helps you to monitor the status at ease, so that you know which files are missing.

Here comes Power Query to rescue. Continue reading →

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

Quick Tip – #Excel AutoSum

Posted on April 13, 2019 by MF

Excel Tip - 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 ALT+=.

Let’s watch it in action:

Excel Tip - AutoSum

Tip: When you make the selection, make sure you have selected an “extra” row and/or column to reside the total(s).

As simple as this! 🙂

Posted in Excel Tips | Tagged | Leave a comment

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

Posted on April 7, 2019 by MF

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 multiple criteria on a single column.  This is perfect timing to write this post to demonstrate a less-known feature of Auto-Filter.

First of all, have you ever noticed the “Add current selection to filter?

Example Tip - Add current selection to filter

Maybe yes maybe no.  Even though you have seen it before, you may have no idea what it does.  Let’s explore it now.

Here’s the situation

We have a list of account numbers (that are stored as text).  What we want to achieve is to filter all account number that starts with either 6, 7, or 8.

Excel Tip - Add current selection to filter 1 Continue reading →

Posted in Excel Tips | Tagged | 1 Comment

#Excel Flash Fill in action with 16 examples

Posted on March 31, 2019 by MF

Excel Tip - Flash Fill

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 capable of, they are all impressed and think that this is their “Greatest Discovery” moment.

So in this post, I am going to show you in a flash what Flash Fill can do with 16 examples.

Here’s the video on my YouTube channel.  Please turn on CC for English subtitles.

 

If you prefer reading to watching, you will see GIFs showing the examples below:

Note: CTRL+E is the keyboard shortcut for Flash Fill which had been used in all the GIFs in the coming demonstration.

Continue reading →

Posted in Excel Tips | Tagged | 2 Comments

Birthday Treat Calculator

Posted on March 16, 2019 by MF

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.   Similar to that old story, a friend asked me for an Excel solution for a different scenario, which I think is quite interesting and really need Excel to do the calculation. 

Long story short – when we have more than one birthday stars in a party, maybe a birthday star should treat other birthday star(s) as well.  Thus, s/he will need to pay a share.  WOW… it sounds like an Excel challenge that I really like to take.

Here’s my solution:

Excel Tips - Birthday Treat Calculator

Continue reading →

Posted in General | Tagged | Leave a comment

Extract FirstLast names in CamelCase with #Excel formula

Posted on March 10, 2019 by MF

Excel Tips - Split CamelCase.png

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 the pattern of CamalCase and return the desired result in a flash.  However, if you are still using Excel 2010 or before, it is NO EASY TASK and required advanced skills in formula writing.  The key challenge is to identify the position of the second CAPITAL letter in the text string.  Once we have identified the position of it, getting the First Name and Last Name is totally manageable.

Here’s the formula to identify the position of the second CAPITAL LETTER:

{=MATCH(TRUE,CODE(MID(A2,ROW(INDIRECT("2:"& FIND("@",A2)-1)),1))<=90,0)+1}
Note: This is an array formula, requiring Ctrl+Shift+Enter

How the formula works? Continue reading →

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

Turn CSV data into insight like a pro with Excel 2016

Posted on March 3, 2019 by MF

Without a single input in formula bar.  UI only.  No kidding!

Get insigth with FlashFill and QuickAnalysis

What’s even more unbelievable?  The above can be done in just a couple of minutes.

The technique discussed in this blog post is applicable to Excel 2016 or later.  And the techniques used are mainly Flash Fill and Quick Analysis, which are available since Excel 2013.  But why I said it’s applicable to Excel 2016 or later? Because the chart types Histogram and Pareto were introduced in Excel 2016.

I am not saying that we cannot perform the above transformation using Excel 2013/2010.  It’s just a matter of time + higher skill level is required.

Let’s see how it can be done in Excel 2016! Continue reading →

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

My second #Excel Video on YouTube

Posted on February 23, 2019 by MF

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 minutes…

I spent more or less the same amount of time (~12 hours in total, on and off) to produce it comparing to the first video.  However this video is 8-minute long, which is eight times longer than the first one.  In a way, I am doing it more efficiently. 🙂

Honestly it takes much more time and effort than writing a blogpost.  After making just two videos, I can imagine the GREAT efforts all other Excel geniuses like ExcelIsFun, MrExcel.com, Leila Gharani, Excel On Fire (just to name a few) they have put on their channels, where you will find a lot of great Excel videos.  Really appreciate their efforts! 👍  Indeed they have inspired me a lot through my Excel journey.

I am not dreaming about my channel will become as popular as theirs.  Though I do hope, my channel will get more popularity from time to time, especially in the Cantonese-speaking communities.  My goal is to produce one to two videos per month.  Should you have any comments/suggestions, please leave your comments.

Last but not least, if you like my video, please SUBSCRIBE my channel.

SUB

Posted in General | Tagged | 2 Comments

A compelling chart in three minutes…

Posted on February 17, 2019 by MF

…for Actual vs Budget, Target, Last Year

Excel Chart - ActvsBudvsTgtvsLY

If

  1. you need to plot chart to show actual sales vs budget, target, and LY sales;
  2. agree that the chart on the right is a better visualization such purpose;
  3. want to know how to create the chart on the right;

Then

  • please continue to read this post.

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 →

Posted in Chart | Tagged | Leave a comment

My first #Excel video on YouTube

Posted on February 9, 2019 by MF

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 the hard drive; got a new fantastic video production and editing tool; and more importantly produced my first Excel video and posted it on YouTube.

Indeed, I have been thinking about the topics for making my first video.  There are a few on my list but I could not decide.  Then all a sudden when I was having shower one day (yes… many ideas come from 🚿), an idea came to my mind: Why don’t make my first video for my top blogpost – When unhide row doesn’t work…?  That’s why we have this video: Continue reading →

Posted in General | 2 Comments

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

Posted on February 3, 2019 by MF

Chinese New Year is coming really soon.  Let’s talk about something about “New” in Excel in this post… but don’t make me wrong, I am not talking about any new features in Excel 2016/2019/365.  Indeed, what I am going to discuss about is a feature that exists for a long long time, but not many people are using it (at least not to the people I work with).

Interestingly, this “New” stuff is very common and I believe all Excel users have used it at the most fundamental way.

Still have no idea what I am talking about?  Watch the following:

Excel Tips - New Template1.gif

Note: All screenshots are prepared using Excel for Office 365

Yes… That’s the “New” stuff I am talking about.  🙂

You may be wondering… Excuse me?? What???…

Continue reading →

Posted in Excel Tips, Template | Leave a comment

Learn something amazing from Mynda’s #Excel Dashboard webinars

Posted on February 3, 2019 by MF

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 Excel could do for us.

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 could just impress the boss I’d get that pay rise/promotion I deserve”

“I need to get my Excel skills up to date so I can stand out from the crowd of other job applicants”

“I’m not sure how to approach setting up my Excel workbook the right way, so it’s easy to build and maintain”

Then you should spend a few hours in the coming days to watch the three webinars (Yes, I recommend you to watch all three webinars) by Mynda.  You will find the time worthy spent as you will learn many time-saver techniques in using Excel; and learn something about Power BI. Continue reading →

Posted in General | Tagged , | Leave a comment

Hard-coded range expands automatically with #Excel Table

Posted on January 26, 2019 by MF

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 Formatting, to fix the problem we face demonstrated below:

excel tips - using structure reference in conditional formatting5

David N gave a magical tip to handle that DIRECTLY.  Here’s his comment:

I believe you’ll also find that simply writing your conditional formula to refer to an entire column from a Table somehow enables that range reference to auto-magically grow as the Table grows without needing to use INDIRECT or other trickery…

p.s. I like the term “auto-magically” a lot! 🙂

You may download sample file to follow along.

Let’s watch it in action: Continue reading →

Posted in Excel Tips | Tagged , , | 1 Comment