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

The power of Dynamic Arrays in #Excel 365

Posted on October 26, 2019 by

… in creating dynamic drop-down with conditions

Making a dynamic drop-down list is not difficult.  It may require multiple steps, helper formula and/or helper tables though.  And the number of helper tables depends on the complexity of the requirement.  I wrote a blogpost for a job-assignment task more than 5 years ago.  The requirement is simple: show the teacher with least hours of classes assigned to on top of a drop-down list… like the screen cast show below:

Excel tip - Dynamic dropdown with Excel 365_1

See!? The drop-down is changing according to the total hours assigned, as calculated on column F.

With dynamic arrays, this task is super easy.

You may download a Sample File to follow along.

Note: As of today, Dynamic Arrays is available to Excel for Office 365 Insider only

Continue reading →

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

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?

Excel Tip - Fill A to Z

If you tried (and without success), you may be wonder how I did it?

You may find the trick (using Custom List) in the following video: Continue reading →

Posted in Excel Tips | Tagged | Leave a comment

Add Data Labels for Total to Stacked Columns in #Excel

Posted on September 14, 2019 by

When we create stacked columns in Excel, we exclude Total from the data as it may give an extra stack that we don’t want.  However, we may want a data label for Total on the stacked columns, like the one below:

Excel Tips - Add data label of total to stacked columns

So how can we do that?  This is what I am going to show you in a short video.

You may download a Sample File to follow along.

Continue reading →

Posted in Chart | 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 you think you know Copy and Paste well?

In this post, I’d like to share 11 practical tips and tricks about Copy and Paste in forms of short videos.  Check them out!

Copy and Paste Visible cells only in Excel

 

Paste with column widths preserved in Excel

 

Multiply a value to a range of data in Excel

 

Convert text date to numeric date using Copy and Paste in Excel

 

And seven more… Continue reading →

Posted in Excel Tips | Tagged | Leave a comment

Suggestion: Adding a button for number formats in thousand/million/billion in #Excel

Posted on September 3, 2019 by

Did you know what you may submit your ideas to improve Excel through UserVoice?

The recent HOT news in the Excel community – the new XLOOKUP function is also resulted from an idea submitted in UserVoice.

While everyone is super exciting about the XLOOKUP function, I would like to suggest a minor thing that is very basic but super helpful to most all Excel users.

My humble suggestion:

Adding a button for number formats in thousand/million/billion

Number Units_v1

Why have I come up with this idea?

Long time ago, I wrote a post Show number in thousand (k) or in million (M) by using custom format, which to my surprise, have become one of the top 3 posts in my blog.  It reflects the truth that it is a very common demand from Excel users.  Although it is not really “difficult” to use the custom format to solve the problem, it is not obvious nor intuitive for sure.

Then I searched the UserVoice to see if anyone has the same idea.  Of course, there is.  I came across the idea submitted HERE.  I VOTED for that immediately and left my additional suggestions in comments.  However, the number of VOTE is surprisingly low (only 28 votes including mine).

So, if you like the idea, may I ask you to VOTE for it!  And more importantly, spread the word to invite more votes.  Every VOTE counts.  Sometimes, a tiny change would make a big difference.  🙂

 

 

 

Posted in General | Tagged | Leave a comment

CTRL A to Z – Learn 25 #Excel Shortcuts with demonstrations

Posted on August 31, 2019 by

Do you know how many keyboard shortcuts we have in Excel?  Honestly, I don’t know.  I just know the shortcuts that I need to use.  Believe it or not, from Ctrl+ A to Z, I use them all but M, because no action is assigned to Ctrl+M yet.  Having said that, I am using M codes more and more recently.  Maybe Microsoft could assign Ctrl+M as Open the Power Query Editor to complete the list.  😛

Let’s talk about the shortcuts.  Maybe you already know most of them, but do you know ALL of them?  Especially the secret use of CTRL+J? Check it out! (02:07)

You can learn all the 25 shortcuts in a 7-minute video embedded in the end of this post.

Here’s a list for quick reference.  

CTRL + A Select All (00:11)

CTRL + B Toggle BOLD (font) (00:34)

CTRL + C Copy (00:42)

CTRL + D Fill Down (00:49)

CTRL + E Flash Fill (Excel 2013 or later) (00:59)

CTRL + F Find… (01:11)

CTRL + G Go To… (01:28)   Continue reading →

Posted in Shortcut | Tagged | Leave a comment