My first video with English Voice Over

Posted on March 28, 2020 by

Power Query – Full Outer Join Revisit Part 2

If you have been following my blog for a while, you should probably know that I’ve started my YouTube channel about one year ago.  Up to this moment, I’ve uploaded 54 videos.  Almost one video per week.  Not bad as a part-time YouTuber, I guess.  😅

And you know, most of the videos (in the Excel Magic in One Minute) are silence, without any voice over.  For the rest, I recorded them in Cantonese, which is my mother tongue.

I know Cantonese is a very niche segment for Excel learning.  And I know majority of YouTube viewers do not understand Cantonese.  Therefore I put English subtitles on all my videos with Cantonese voice over.  I planned to continue in this way until one day I read the following comment from bebuger bebuger:

Comments

Thank you very much for your kind words, bebuger!  It’s very encouraging! 😉

With your encouragement, here comes my first video in English:

I hope you like it.

 

Here’s the Cantonese version:

 

It turned out that recording English Voice Over is much more efficient.  For a 10-minute video, it took me 5+ hours in preparing the English subtitles. I had to write the script, put it onto the video, and sync it with the video.  Indeed it’s a quite tedious and time consuming.

This time, I recorded the video in Cantonese first.  After editing the video, I recorded English voice over on it.  Although it took me many times for the recording… although my fluency and intonation was not good, I finished it in just more than one hour… Cool.  😎  I should have done this earlier.

If you like my videos, please give a thumb up 👍, share it and SUBSCRIBE.  😉

Thanks again, bebuger.  I hope I will have more subscribers as a result.

Posted in General | Tagged , | Leave a comment

Power Query Challenge – Joining two tables fully (Part 2)

Posted on March 21, 2020 by

Append Queries + Group By = Another alternative for a better Full Join

In the previous post, we explored an alternative way to perform FULL OUTER JOIN with Left Outer Join + Right Anti Join, followed by Append Queries in order to get a result with better layout.  Well, as it’s Excel, that means there are different ways to achieve the same.  In this post, let’s see how we can do the same using Append Queries + Group By.

Again a picture tells thousand words.  The following diagram illustrates the concept:

Power Query Full Outer JOIN Revisit

In the end of this post, I will post a video (work in progress 😅) riding on the example in previous post to demonstrate it.  From which you will see the two different approaches yield the exactly the same result.

For illustration purpose, two simple tables are used for this post.  Let’s see how to do it step by step in Power Query!

You may download a Sample File to follow along.

Continue reading →

Posted in Power Query | Tagged , | Leave a comment

Power Query Challenge – Joining two tables fully

Posted on March 8, 2020 by

Left Outer + Right Anti = A better Full Join

Excel Tip - Full Outer Join revisit

A sample file is available below.

If you prefer reading to watching, please continue to read this post.

The situation:

I have two tables.  One is Sales Table; One is Traffic Table.  What we need to do is to combine them into a single fact table.

Excel Tip - Full Outer Join revisit1

With Power Query, it is an easy job.  We can merge the two tables with Left Outer Join!

BUT… If we do that with Left Outer Join, we will miss some data points.  The following screenshot depicts the situation:

Excel Tip - Full Outer Join revisit2

Those traffic records with “?” do not have a matching record in the Sales table.  As a result, those traffic records are missed in the output, and most of the time without any notice/warning… 😑  Continue reading →

Posted in Power Query | Tagged , | 2 Comments

Idea: Ctrl+M to launch #PowerQuery Editor

Posted on March 3, 2020 by

To all Power Query Users:  I need your vote!

Idea - CTRL M to launch Power Query Editor

Well… we know that in Excel

  • the language behind Power Query is called M;
  • it takes at least three clicks to launch Power Query Editor;
  • the shortcut CTRL+M is currently unassigned…
Then why not using CTRL+M to launch Power Query Editor?
If you like this idea, please vote and make it happen:

 

Posted in General, Shortcut | Leave a comment

Determine leap year with #Excel

Posted on February 29, 2020 by

Leap Year

Today is a special day.  Today is February 29th, which happens only once every four years.  And today is Saturday, when I usually post a new blog.  So let’s talk about how to determine if a year is leap year using Excel.

First of all, what is leap year?

We may find a detailed explanation in Wikipedia.  Below is part of the article describing how to determine a leap year without needing an Excel formula.

…in the Gregorian calendar, each leap year has 366 days instead of 365, by extending February to 29 days rather than the common 28. These extra days occur in each year which is an integer multiple of 4 (except for years evenly divisible by 100, which are not leap years unless evenly divisible by 400)…

https://en.wikipedia.org/wiki/Leap_year

But you know, this is an Excel blog and I am an Excel nerd.  That’s why I am going to use Excel to solve this problem.  A simple formula indeed.

=DAY(EOMONTH(DATE(A2,2,1),0))=29
'where A2 is the year input; works for year 1901 onward
Note: 
It's an known issue that Excel mistreats the year 1900 as leap year. 😐

How the formula works?

  1. The most inner portion DATE(A2,2,1) returns the first date of February of the year;
  2. Wrapping it with EOMONTH, Excel returns the last date of February of the year;
  3. The outermost DAY() function returns the day portion of the date;
  4. As a result, if it is 29, it’s a leap year. 

As simple as that.  🙂

Indeed, we may use a much shorter formula to achieve the same.

=DAY(DATE(A2,2,29))=29

When we hard-code the day portion as 29, and when there is no 29th in that February, Excel is smart enough to return March 1st instead.  Therefore the outside DAY() function would return 1, which is not equal to 29.

Make sense!?

Want some challenge?

Well, you may think it is too easy to determine if a year is leap year of not.  Really no need to use Excel to do that.  How about if I twist the question a bit…

When is the next February 29th which is a Saturday?

Think a bit before you continue to read this…

Tip: We can do it in a single formula by using Dynamic Array 

Continue reading →

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

So many different ways. That’s #Excel!

Posted on February 28, 2020 by

Last week, I posted a video and blogpost in response to a challenge from Mr. Excel .  And this week, Mr. Excel has collected viewers’ solutions and post a video to summarize different techniques used for solving the same question.  Very informative!  Check it out:

 

Btw, you have no idea how happy I was when I heard Mr. Excel mentioned my name in his videos many times.  Feeling so honored. 😊  Thank you Bill!

Posted in Power Query | Leave a comment

Power Query Challenge from Mr Excel – Reshaping data

Posted on February 22, 2020 by

I really like Power Query (in both Excel and Power BI).  Honestly I do not know much about the M behind the scene (still learning it piece by piece).  Nevertheless, I’ve got the POWER simply through the User Interface of Power Query Editor.  Seriously,  everyone can become a data wizard by using Power Query!  Let’s watch the following two videos to feel the POWER of Power Query.

Here’s is a challenge (with solution) from Mr Excel on how to reshape data.

You may download a sample workbook from Mr.Excel YouTube channel to follow along.

 

And here’s my approach to solve the challenge:

 

If you prefer reading to watching, please continue to read this post.

Note: All screenshots used in this post are captured using Excel for Office 365.  If you are using other versions of Excel, you may find the locations and look&feel of buttons a bit different. The concept is the same though.

 

Continue reading →

Posted in Power Query | 1 Comment

Compare two #Excel workbooks for changes

Posted on February 15, 2020 by

Excel Tips - Inquiry0

In about 5 years ago when I was still using Excel 2010, I wrote a post Compare two documents for changes – Word and maybe Excel.  Lukas left a comment:

In the Inquire Add-In you have the option to compare Workbooks the same way that you describe for Word…

At that time, I didn’t have the Inquire Add-In… and neither had I ran in a situation that I really needed it.

Not until recently, I encountered a situation at work that I really needed to compare two workbooks for changes.

Here’s the situation:

This is a file with translation that I need local team to review…  As the translation agency would need to know what’s commented, they asked us to write our comments on Column C…

Before

I did expect the reviewer would return the file with comments on column C… Nevertheless, this is what I’d got:

After

OMG…  Instead of writing on column C, it’s (over)written on the original translation on column B.

(I guess not following instruction when using Excel is a common practice at workplaces! Isn’t it? 😑)

I was so happy that I knew about the Compare Files feature under Inquire Add-in.

I am going to show you what it is now! 🙂

You may download the following two files to follow along:

Continue reading →

Posted in Excel Tips | Tagged , | Leave a comment

What-If analysis in #Excel – Part 2

Posted on February 8, 2020 by

Basic of Two way Data Table

This is an extension of the post What-If analysis in #Excel.

Excel Tips - TwoWay Table 1

If you do not know about Goal Seek or One-Way Data Table, you may read Start the new decade with Goal Seek in #Excel and What-If analysis in #Excel before moving on.

We talked about a situation that we’d to have a list of outcomes with different initiate savings (Variable), by using One way Data Table.  Since we have two variables (the other one is the Weekly increment%), we may actually see the outcomes of different scenarios in a cross table, like this:

Excel Tips - TwoWay Table 4

With Two way Data Table under What-If analysis, this can be achieved easily… as long as you know the setup required. 😉

You may download a Sample File to follow along.

Continue reading →

Posted in Excel Tips | Tagged , | Leave a comment

Wanna boost your #Excel Dashboard skill?

Posted on February 5, 2020 by

Is learning Excel Dashboard one of your 2020 resolutions?

If so, then you’re in for a treat because today I’m excited to tell you about Microsoft Excel MVP, Mynda Treacy’s highly acclaimed <Excel Dashboard Webinars> are open for limited time!

Free Excel Dashboard Webinars

If you’re not sure what a dashboard is, or how these skills might be relevant to you, and help you overcome the challenges above, then you can attend one or both of Mynda’s FREE Excel Dashboard Webinars.

Webinar 1 – Excel Dashboard for Excel 2007/2010/2013/2016/2019/Office 365

The first webinar will teach you how to use Excel to build this interactive dashboard below, no additional software or add-ins required, just plain old Excel and some data.

Click here to register for webinar 1.

Continue reading →

Posted in General | Tagged , | Leave a comment

What-If analysis in #Excel

Posted on February 1, 2020 by

Basic of One way Data Table

This is an extension of the post Start the new decade with Goal Seek in #Excel.  

In that post, we talked about using Goal Seek to find the initiate savings required to obtain a total savings of $1000 at period end, given the other variable remains constant.

How about if we want to have a list of outcomes with different initiate savings?

Do we need to change the value one by one manually and then do the great copy and paste?  Like the screen-cast below… 🤦‍♂️

Excel Tip - OneWay Data Table

Of course we don’t!  We can do it with (One-Way) Data Table under What-If Analysis.

You may download a Sample File to follow along.

Note: You may want to read this post to understand the context before moving on.

Continue reading →

Posted in Excel Tips | Tagged , | Leave a comment

#Excel Power Query solves a real life problem – Who eats what?

Posted on January 18, 2020 by

Interesting application of Power Query for real life problem – See how to transform table into different layout using Power Query

I came across this Power Query Challenge from Computergaga and I found it really interesting as it seems that it happened to some of my ex-colleagues for event planning before.

I took the challenge.  In this post, I will give a possible way of solving it with Power Query.  Yes only one of the possible ways.

The Challenge

You’ve got the following table. The headers of columns are different types of dishes that your guests can select from.  What underneaths are the guests who pick it.  The first there columns are Appetizers, while the middle three and the last three columns are for Main course, and Desserts respectively.

Power Query Challenge by Computergaga

Is the above a bad layout?  Well it depends.

If you serve the dinner, it works fine as you know exactly which dish goes to which guest.

However, if you are the event planner and you want to confirm the dishes selected by your guests, this above table is a nightmare to you.   Isn’t it? 😂

What you would like to see should be a table like this:

Power Query Challenge by Computergaga1

It clearly states who eat what (as appetizer, as main and as dessert).  It will certainly save your day! 🙂

And this is exactly the expected result #1 of the challenge.

Expected result #2 is more for the kitchen member, I guess.

They are more concern on the quantity of each dish.  They don’t (usually) care who eats what?  They just want to know the quantity required.  The following summary table will do.

Power Query Challenge by Computergaga2

If you want to watch the original video for the challenge and the solution from Computergaga YouTube channel, please click HERE. You can get the sample file to follow along from there too.

Continue reading →

Posted in Power Query | Tagged , , , , , | 7 Comments

Start the new decade with Goal Seek in #Excel

Posted on January 11, 2020 by

Happy New Year of 2020!  Wish you all an Excellent year decade ahead! 😉

Have you made your new year resolutions?

Or you are relying on Excel to set your goals of the year?

Did you know that Excel has a feature called Goal Seek?

Let’s start the year with a simple example to demonstrate how to use Goal Seek in Excel!

The situation:

Let’s say I want to set a saving plan for the year.  My saving plan is simple: On week 1, I started my saving with $10.  Following each week, I will save just 1% more than the previous week’s saving.

With Excel, I can calculate the total savings at the end of week 52 easily.

You may download a Sample File to follow along.

Continue reading →

Posted in Excel Tips | Tagged , | Leave a comment

Free Template: Party Expense Calculator

Posted on December 28, 2019 by

Christmas has just passed while New Year is just around the corner.  Party must go on!

As a token of appreciation for you visiting my blog, I am going to present you a free gift – Party Expense Calculator,  in form of Excel of course.

Party Expense Calculator

What it does?  I guess the name says it all.

If it doesn’t, maybe a GIF tell a better story:

Excel Template - Party Expense Calculator

Isn’t it clear now?  Nevertheless, it uses functions of dynamic arrays, which means it’s applicable to Excel for Office 365 (Insider) users only, as of today.

I hope you will find it useful, especially when you have parties to go!

If you are still reading this, I would like to take this opportunity to sincerely thank you for visiting my blog.  I hope you found something valuable to you on my blog.

Last but not least, I wish you and your beloved ones a Fruitful, Healthy, and Happy New Year of 2020.   BeExcellence! 😉

Posted in Template | Tagged , | Leave a comment

Insert Current Time or Today using #Excel functions or shortcuts

Posted on December 22, 2019 by

Excel Tip - Insert Date Time using shortcuts.PNG

It’s not uncommon to insert a date stamp or time stamp on a spreadsheet.  We may use the following functions to get the results easily:

=TODAY() 'to get the date of today

=NOW() 'to get both the date of today and current time

=TEXT(NOW(),"HH:MM") 'to get just the current time* 
*Tip: If we want to display down to second, use "HH:MM:SS" in the 2nd argument


Example of outputs:

Formula Results
=TODAY() 22/12/2019
=NOW() 22/12/2019 16:59
=TEXT(NOW(),”HH:MM”) 16:59
=TEXT(NOW(),”HH:MM:SS”) 16:59:07

Nevertheless, these functions always update whenever there is change applied to the spreadsheet.  Most of the time, we would like to convert it to a static value by using Copy and Paste value afterward.  We just want to record a moment, not to have “ever-changing” values.  In this case, it’s better to use shortcut keys to insert Today, and Current Time.

And the shortcuts are: Continue reading →

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

Create in-cell chart with Sparklines in #Excel

Posted on December 15, 2019 by

🎵🎤Do you wanna build the Sparklines?

Excel tip - Incell chart with Sparklines

Sparklines is not a new feature of Excel.  It was introduced in Excel 2010.  Yes, almost a decade ago.  But I am quite sure it’s still something new to a lot of regular Excel users. Sparklines is a great tool to enrich your report and it is super easy to implement.

You may watch this short video to learn it in one minute:

If you prefer reading to watching, please continue with this post.

You may also download a Sample file to follow along.

First thing first: Where is Sparklines?  Continue reading →

Posted in Excel Tips | Tagged | Leave a comment

Be cautious when using XLOOKUP

Posted on November 30, 2019 by

Excel Tip - Be cautious when using XLOOKUP

Can you spot the error in the above formula?

It’s not about the new function of XLOOKUP.  It’s about inevitable human error…

If you are an Excel fan, you should be aware of the exciting XLOOKUP function in Office 365.  It is, no doubt,  an awesome function that will beat VLOOKUP, HLOOKUP, LOOKUP, INDEX/MATCH in the future when it is generally available to all Excel users.  You may find more details about XLOOKUP from Microsoft Tech Community.

There are many very well presented videos about XLOOKUP on YouTube already.  Just to name a few (my favorite YouTube channels),

So in this post, I am not going to talk about all the cool features of XLOOKUP.  Instead, I would like to draw your attention to a potential mistake when writing XLOOKUP function.

You may download a Sample File to follow along.

Continue reading →

Posted in Formula | Tagged , , , | 4 Comments

Create in-cell chart with Conditional Formatting in #Excel

Posted on November 24, 2019 by

Have you ever seen an Excel sheet with in-cell bars to visualize the data like the ones below, and wonder how they are made?

Believe it or not, you can do it in one less than a minute with Conditional Formatting.

Let’s watch it in action:

 

Please continue to read this post if you prefer reading to watching.

You may download a Sample File to follow along.

Continue reading →

Posted in Excel Tips | Tagged | Leave a comment

Last Chance for Dashboard Course and Free Webinars

Posted on November 19, 2019 by

Just a quick reminder that registration for the awesome Excel Dashboard and Power BI courses by Microsoft Excel MVP, Mynda Treacy, closes on Thursday Nov 21, 8pm in Los Angeles, to be exact.

With demand for Data Visualization skills and Excel data analysis jobs set to explode by 44% and 34% respectively, now is the time to get these skills so you can take advantage of these exciting opportunities. Demand for workers with these skills is also predicted to outstrip supply, and that means these jobs will command a premium salary.

Mynda’s courses will have you up to speed quickly so you can start benefiting ASAP. Continue reading →

Posted in General | Tagged , | Leave a comment

Are you tired with data dump Excel Report?

Posted on November 9, 2019 by

that takes you long to prepare; and is hard for report consumers to get insight…

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

“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’re in for a treat because today I’m excited to tell you about Microsoft Excel MVP, Mynda Treacy’s Continue reading →

Posted in General | Tagged , | Leave a comment