5 years of blogging

Posted on September 7, 2018 by MF

Totally forgot I put my first post here 5 years ago.  Time really flies! Thanks for flying with me. 🙂

Capture

Posted in General | Leave a comment

[Share] – Threaded Comments in Excel 365

Posted on September 1, 2018 by MF

Last week, I told you that my Surface Pro 4 is out of service, and I am waiting for a new laptop for writing my Excel blog… until late September or early October.   That was the plan.

Although I am having a “late” summer break for writing excel tips and tricks for now, nothing stops me from learning new Excel stuffs.  This morning, I watched MrExcel.com’s YouTube channel and heard MrExcel (Bill Jelen) said: “If you have a channel and you have viewers and you can reach people, please pass this note along“…

That was the moment.  Yeah… Why don’t I share something cool!  Even though I am not using Excel 365, it’s good to know what’s new and keep updated.

Here’s the MrExcel’s video talking about one of the cool features of Excel 365 – Threaded Comments:

 

Hope you enjoy it. 🙂

Posted in General | Tagged | 1 Comment

(: Late summer break :)

Posted on August 26, 2018 by MF

Today, I am not talking about Excel although I would like to.  Why?  Because my Surface Pro 4 is out of service… 😦

It has been out of order a few weeks ago… the moment when the warranty period was just expired.  I was struggling whether I should fix it, or I should buy a new one.  To fix a Surface Pro is not an easy task to my understanding.  Worst still, there is no official repairing service in Hong Kong.  When I contacted Microsoft, they offered to replace my Surface Pro 4 at a cost, which is actually not cheap given the fact that they are not going to replace me with a new machine….. @_@

On top of my mind is to buy a New Surface Pro.  Indeed, I like the usage experience of my Surface Pro 4 very much.  It’s an excellent replacement of traditional notebook: Light in weight with high performance (good enough for writing Excel blog).  Also, I like the touch screen and Surface Pen with that I can write on captured screens (to have sort of a personal touch to my illustration, although I know that my handwriting is poor. :P)  However, I am afraid that it will be broken in 13 months… even though I know it’s just a random event totally depends on luck.  A friend told me that I should buy the extended warranty.  For a machine that does not break down in three years, it probably can last for long.  He’s got his point, I believe.  But one of my considerations, after my Surface Pro 4 broke-down, is “Fixability”.  I prefer something that is fixable and can last for long as I don’t want to throw away a machine simply because one of the components went wrong.

Another consideration is not about the performance of the machine, but my commitment to this blog.  You know what… I felt a bit “emptiness” for not writing anything about Excel in the past few weeks.  I know that I am abnormal, sort of.  ;p  Therefore I am looking into other options such as traditional notebook, which is in most cases more repairing friendly.

After browsing and browsing, windows shopping and windows shopping, I have decided to buy a new notebook, which will be arriving in mid September.  Coincidently, I will be traveling for leisure for two weeks in mid September.  That means, I won’t be able to start writing about Excel until end of September, or early October.  Let’s consider it a belated summer break for myself.

Meanwhile, I will take the time to plan for the topics afterwards.  Stay tuned, Stay Excellent. 🙂

IMG_8451.jpgPhoto by me. 🙂

Did I tell you that besides Excel, I enjoy hiking and taking photos.

Posted in General | Leave a comment

#Excel Humor #15 – Performance Review

Posted on August 15, 2018 by MF

Excel Humor - MidYearReview.png

 

Feel free to share this formula to your friend / colleague whom you think applicable… 🙂

Screen Shot 2018-08-11 at 5.28.09 PM.png

He/She knows the answer.  😛

Posted in General | Tagged | Leave a comment

Same day last year…

Posted on August 8, 2018 by MF

Avoid Overthinking

Excel tip - Same day last year.png

Getting same day of last year using Excel formula

In retail, it’s very common to compare sales of same day, not same date, of last year.  If you are not in retail sector, you may wonder what is the difference between same day and same date of last year.

Let’s look at example:

Assume today is 2018/08/11, and same date of last year is 2017/08/11.  Very straight forward.

But retail people will never compare YoY sales performance in this way.  Why? Because it compares apple to orange.  Think about this, sales on Saturday (2018/08/11) should be better than Friday (2017/08/11).  Make sense?  So we want to compare 2018/08/11 to 2017/08/12 instead.  It’s a Saturday to Saturday comparison.

As such, there is constant demand for Excel formula to get the same day of last year.  In many cases, Excel user would use functions related to date.  Continue reading →

Posted in Excel Tips | Tagged | 2 Comments

Load multiple #Excel Tables as separate #Queries quickly…

Posted on July 26, 2018 by MF

Excel Tip - Load Excel Tables as mulitple queries0.PNG

To load an Excel Table into Power Query is easy.  Just click into any cell of the Excel Table, and then click the From Table/Range button (depends on which version you are using, this button resides in different location on the ribbon).   This action will take your active Excel Table to the Power Query Editor.  To load another Excel table, we need to close the Power Query Editor, go to the Table, then repeat the step.  Easy, Piece of cake.

However, when you have 20 Excel Tables and you want to load all of them into Power Query, you need to repeat the steps 20 times.  Not really a nice experience.  😦

To my limited knowledge to Power Query, there is no simple way to load multiple Excel Tables as separate queries in one step.  Please correct me if I am wrong. ;p  I tried to Google it, but no success.

Nevertheless, there could be a “quicker” way to do so, just a bit quicker…  Continue reading →

Posted in Power Query | 4 Comments

Change the first letter to upper case, first word only please #Excel

Posted on July 19, 2018 by MF

This is a short story of mine, and an imaginary conversation in my head… 

How to change the first letter (of first word only) to upper case in Excel?

This was a question from a colleague sitting opposite to me.  My quick response was: “Yes… but it is a bit, just a bit complicated…”As I was doing something else, I didn’t answer him right away.  Then after a while, he told me he “googled” the formula he needed.  That’s fine.When I finished my task, I asked him for the formula he’s got… then I provided him my formula (a shorter one). :PThat’s the end of the story, but the beginning of my thought:

Is it good to have an Excel “nerd” sitting around you?

Most people would say YES, I guess, because they can have quick answer to their Excel questions.However, I think the opposite.  It may indeed slow down your learning curve in Excel.Indeed what I meant “complicated” in the beginning is that it requires nested functions. The solution is not complicated.  We just need to know three functions:

  • UPPER
  • LEFT
  • MID (or REPLACE, that gives you a shorter formula)

Looking back, if I was not occupied by other tasks, I would continue our conversation like the following:   Continue reading →

Posted in Formula | Tagged , , , , | 7 Comments

Creating % of Total, Running Total in a few clicks with #Excel Quick Analysis

Posted on July 5, 2018 by MF

This post is about showing you how to perform a common task of adding a column of

  • % of Total
  • Running Total

with Quick Analysis in Excel 2013 or later.

Latest Excel makes things easier, just that you may not be aware of…

Well, what I meant “latest Excel” here are those versions since Excel 2013.  Of course, Excel 2016 and of course Excel 365 is getting even better.

You may think that Excel 2013 is a product of 5 years ago… there is no way it can be called “latest”…. uuuum… that’s true.  However I am living in a city where most people I know (across different companies) are still using Excel 2010 or before.  Believe it or not?  😛

What is even more surprising (or ironic):  when I met someone who uses Excel 2013 or later, I looked at them with my “hearty” smiles and told them how lucky they are with all those new features like Flash Fill, Quick Analysis, etc….. (not yet to mention about the new FUNCTIONS).  The response I got was mostly: “What are these?” and some of them unconsciously showed an attitude of “I don’t care…”

Needless to say, many people have never clicked the tiny icon that showed up automatically when a range of data is selected.

Excel Tips - Quick Analysis

So, I am going to show one quick tip of using Quick Analysis (provided that you are using Excel 2013 or later) to add a column of

  • % of Total
  • Running Total

A picture (especially an animated one) tells thousand words, so let’s look at the screencast below:   Continue reading →

Posted in Excel Tips | Tagged | 1 Comment

No more Copy and Paste to combine tables with #PowerQuery

Posted on June 28, 2018 by MF

Basic of Append Query in #PowerQuery

With no doubt, combining multiple tables (mostly on different worksheets, or even in different files) into a single “master” table for further analyses is one of the most tedious tasks we deal with Excel day to day.  Inevitably, manual Copy and Paste is the go-to option (unless you are VBA expert).   Sometimes, the tables we are trying to combine contain different columns.  You know that feeling of frustration, don’t you?

With Power Query, we can say NO MORE to copy and paste for this tedious task.  In this post, I am going to show you what Append Query does, and some interesting notes of it.

I will demonstrate how Power Query appends tables for four different cases:   Continue reading →

Posted in Power Query | Tagged | Leave a comment

Top 40 Excel Blogs by Feedspot

Posted on June 23, 2018 by MF

What an honour and pleasant surprise to be selected as one of the Top 40!

Recently I am quite busy (and moody) at work, I mean the real work at office.  You must be thinking my works involved using Excel a lot and I should be able to finish them in an efficient way.  However the fact is… Excel is the barely opened application in my PC at work.  When I use Excel at work, I probably use it for “non-analytic” purposes.  Can’t imagine, right?

Then one day, I received an email from Feedspot saying that wmfexcel.com has been selected as one of the Top 40 Excel Blogs.  That was the truly refreshing moment of my boring day.

Frankly speaking, I think I have not reached this level yet.  Nevertheless I am super happy that my blog is getting more popularity.  If you are reading this, I sincerely thank you! 🙂

Posted in General | Leave a comment

#PowerQuery makes advanced VLOOKUP so easy!

Posted on June 21, 2018 by MF

Do you use VLOOKUP?  If you do, you should know there are limitations to VLOOKUP.  Also, there are some cases VLOOKUP is not straightforward and efficient.

Consider the following cases:Excel Tip - Complicated vlookup with PQ

What we want to achieve is to map the corresponding staff information (in the lookup table, named tb_StaffInfo) to the orange tables (named as tb_Employee, tb_Name, tb_Dept) on the left.

The challenge

Case 1: Although there is a common key (EmployeeID vs. Staff ID) in both tables, the lookup direction for Name is to the left which an ordinary VLOOKUP fails to accomplish. Read here to learn more about this limitation and an alternative way to overcome this.

Case 2: There is no common key, we may need to combine First Name and Last Name in tb_Name first to get the common key, or to split the Full Name in the lookup table into First and Last Names, then do VLOOKUP 2 values.  Either way, it’s not direct.

Case 3: Duplicate record (Dept) in the lookup table.  We know that VLOOKUP will only return the first match in the lookup table.  VLOOKUP cannot return all the matching records in the lookup table.

Power Query, however, overcomes these challenges with ease.

You may download a Sample FIle – Doing complicated vlookup with PQ (Start) to follow along.

Continue reading →

Posted in Power Query | Tagged , | 1 Comment

Display descriptive words instead of numbers with Custom Format in #Excel

Posted on June 14, 2018 by MF

Sometimes we want to add a descriptive like “Good Job” for figures above target; “Work Harder” for those below… like the following screenshot:

Excel Tip - Turn Numbers into Descriptives

To many people, the top-of-mind solution should be using IF function.

=IF(A2>0, "Good Job", IF(A2=0, "Just Met","Work Harder"))

This formula does the job fairly easy! 🙂

One drawback is, we will need a helper column to achieve this.  In some cases, we want the descriptives only; showing the numbers is not necessary.  For this case, Custom Format should be the go-to approach.

Format Cells –> Number Tab –> Custom –> Input the following into Type:   Continue reading →

Posted in Format | Tagged , | 2 Comments

Maintain a price book in #Excel with #PowerQuery

Posted on May 31, 2018 by MF

Feel the Power (Query)

This is my first post devoting to my recent love for Excel – Power Query.  🙂

First, let me make myself clear.  I am still learning Power Query (indeed I am still learning Excel too).  The more I learn and use Power Query, the more Excel power I get.  Frankly, I am far away from being an expert in Power Query.  Having said that, I am “relatively” good by knowing how to use many Magical commands through the super-friendly User Interface of Power Query.  Together with a little knowledge in editing auto-generated codes in formula bar of Query Editor, I feel like I can fly with Excel.  🙂

In this post, I am trying to show you how to solve a workplace problem, that is quite complicated if using Excel alone.

Warning: This is a long post.

Continue reading →

Posted in Power Query | Tagged , , | 4 Comments

Create your own favorite keyboard shortcuts in #Excel without VBA

Posted on May 24, 2018 by MF

Make better use of the Quick Access Toolbar (QAT)

We all know that shortcuts could save us lots of time when working with Excel.  Excel has many built-in shortcuts.   Ctrl+C, Ctrl+V should be the most popular and commonly used ones as most of us are doing copy and paste daily, or even hourly.  ;p  Just to name a few more, Ctrl+1 to format cells, Ctrl+S to Save, Ctrl+F to Find are some other commonly used shortcuts using Ctrl+ Key combinations.

However, there are some actions you would not find the Ctrl+ shortcut for it, e.g. Format Painter, Sort A to Z, Align (selected objects) Top, etc…

But did you know that we can access to almost all buttons on Ribbon and QAT by using Alt key combinations?

In this post, I am going to show you how to find out the Alt keys combination for actions you find on Ribbon, and more importantly, how to put your favorite (most frequently used) actions onto QAT for enhanced productivity.

Continue reading →

Posted in Shortcut | Tagged , | 3 Comments

#Excel Humor #14 – Insert Table

Posted on May 17, 2018 by MF

I am having an incentive trip in Osaka… not sure if this is due to my Excel skills. 😛

So let’s have fun this week. 🙂

Excel Humor (14) - Excel Table

 

What would you say if you were the interviewer?  Leave it in comments.  🙂

Posted in General | Tagged | 2 Comments

How to remove leading space in #Excel

Posted on May 10, 2018 by MF

When TRIM and CLEAN do not work…

Excel tip - Invisible Characters1

Is it something bothering you?

To fix this problem, we need to understand where is the “leading space” coming come.  Before we jump to that, I want to show you two Excel functions first:

CHAR and CODE functions

CHAR returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters. (Source: Office Support )

CODE returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer. (Source: Office Support)

Operating environment Character set
Macintosh Macintosh character set
Windows ANSI

Continue reading →

Posted in Formula | Tagged , , , , | 2 Comments

VBA to make selected sheets “Very Hidden” in #Excel

Posted on April 26, 2018 by MF

VBA to make selected sheets “Very Hidden”;  all sheets visible

Excel Tip - VBA to veryhide sheets2

Weeks ago, I showed the way to unhide a very hidden sheet in #Excel via Visual Basic editor (VBE).  You may refer to that post HERE.  Technically we could follow the same steps to make a worksheet very hidden.  The problem is… we need to do it sheet by sheet. Think about if you want to make 10 selected worksheet very hidden, it takes quite a while (well… a minute or two is quite a while nowadays, isn’t it?).  So it’s better to have a “fast” way to do it in one click instead of repeating steps in minute(s). To achieve this, we need a few lines of VBA codes.

If you have been following my blog, you should know that I seldom write on VBA as I am not really good at it.  Having said that, I do use macros / VBA to enhance efficiency in daily work.

So in this post, I am trying to demonstrate how we can make use of the codes generated by recording a Macro and then modify it to achieve some simple codes that we need. Nevertheless, you are expected to know the very basic of macro and VBA.

Let’s see the codes first.  The codes for making selected sheets very hidden are short, as shown below:   Continue reading →

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

Save a Read Only copy

Posted on April 19, 2018 by MF

Excel Tip - Read Only 0

When we have created a wonderful #Excel template that is to be shared with others, we don’t want users to distort, if not destroy, the template someday.  Nevertheless we would like to leave as much “flexibility” as possible to users by not password-protecting anything on the template.  We know that some users are super creative to crack a password-protected sheet/book. 😛

Here comes Read Only to rescue.

It is simple to make a READ ONLY file.   Continue reading →

Posted in Excel Tips | Tagged , | Leave a comment

Hide and Seek – External Links in #Excel

Posted on April 12, 2018 by MF

The haunted External Links that won’t go away…

Excel Tip - Hide and Seek External Links

Have you ever bothered by this never-go-away warning message?

You’d searched all worksheets (visible or hidden and very hidden too) in order to locate the external links and remove them.  Somehow, you just could not find any cells containing formula linked to external workbooks.  You are totally lost as this warning message just popped out every time you opened the workbook.  So the question in your mind… Where are the External Links?????????

Before locating where external links reside in your workbook, let’s answer the basic question first.

What is External Link?

Continue reading →

Posted in Excel Tips | Tagged , | 1 Comment

Hide and Seek – #Excel Worksheet

Posted on April 5, 2018 by MF

How to unhide a very hidden worksheet?

Situation:

You see in the formula bar that the cell is referred to a worksheet called “VeryHidden”.  However you don’t see it on the sheet tab.

Excel Tip - Hide and Seek Worksheet

Naturally you right-click on a sheet tab in order to unhide the worksheet “VeryHidden”.

Excel Tip - Hide and Seek Worksheet1

The WHAT… moment

You don’t see any “VeryHidden” in the Unhide sheet dialog box! Excel Tip - Hide and Seek Worksheet2

How are we supposed to unhide a sheet that is not hidden??…. Wait, if it is not hidden, why couldn’t I see it on the worksheet tab?

Don’t doubt your eyesight.  The sheet “VeryHidden” is hidden, that’s why you do not see it on worksheet tabs.  Then why it is not found under “Unhide sheet”?  Simply because the sheet is not only hidden; but very hidden.

Told by its name, a very hidden sheet is very hidden.    It means it is very hidden.  😛

So what does “Very Hidden” mean?

We cannot unhide it by the “regular” routine of unhiding sheet.  We need to do it in VBE (Visual Basic Editor).   Wait… Don’t be scared by anything starts with VB…  It is super easy indeed.   Continue reading →

Posted in Excel Tips | Tagged , , | 3 Comments