Top 40 Excel Blogs by Feedspot

Posted on June 23, 2018 by

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! 🙂

Advertisements
Posted in General | Leave a comment

#PowerQuery makes advanced VLOOKUP so easy!

Posted on June 21, 2018 by

Do you use VLOOKUP?  If you do, you should know there are limitations of 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 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 accompanish. Read here to learn more 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 , | Leave a comment

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

Posted on June 14, 2018 by

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 , | Leave a comment

Maintain a price book in #Excel with #PowerQuery

Posted on May 31, 2018 by

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 , , | 2 Comments

Create your own favorite keyboard shortcuts in #Excel without VBA

Posted on May 24, 2018 by

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

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

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 , , , , | Leave a comment

Learn something amazing from Mynda’s #Excel Dashboard webinars

Posted on May 5, 2018 by

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.

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 2010/2013/2016

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.

Webinar 2 – Excel Dashboard for Excel 2010/2013/2016 using Power Query and Power Pivot

Excel is evolving; in the last couple of years Microsoft have added new tools like Power Query and Power Pivot, to name a couple.

Embracing these new tools will not only enable you to get your work done more quickly, but also since less than 1% of Excel users know these tools you’re going to have a huge competitive edge in the workplace.

It’s easy to ignore these developments and continue doing things the way you always have, but that won’t get you ahead in your career, but you know that right?

If you have Excel 2010, 2013 or 2016* then this webinar will showcase how you can use Excel’s Power Query tool to get data from multiple sources, mash it up with Power Pivot, analyse it in PivotTables to create this interactive Excel Dashboard.

 

*Power Query is available with all versions of Excel 2010, 2013 and 2016. Power Pivot is available with the desktop version of Excel 2010 and Excel 2013/2016 Office Professional, Office 365 ProPlus, or in the standalone versions of Excel 2013/2016. Power Query and Power Pivot are not available for Mac.

Click here to register for webinar 2.

What people are saying about the webinars

Over 30,000 people have attended Mynda’s Excel Dashboard webinars and this is what some of them had to say:

“Thank you! This webinar has already saved tremendous time and effort. I tried some of the techniques you illustrated for a cumbersome report update that I can see will cut our preparation time by 2/3’s or more. ….awesome! Many thanks!!!!”

Cynthia Tashjian

 

“WOW, just WOW
Can’t type more now – am trying to create a dashboard. Thank you Mynda!”

Susan

 

“The webinar was fantastic! I really enjoyed every moment. As you said, it was fast paced, but I am so excited to go back and review the presentation. We have been trying to get a dashboard started for my department and this really motivated me to take the lead. Thank you so much!”

Kathryn Puskar

 

“Oh. My. God. I know nothing! My jaw dropped a couple of times as you casually mentioned a way to manipulate PivotTable data. That’s the problem with being largely self-taught – there are huge gaps in my knowledge.”

Ann

 

If you’ve seen Mynda’s free Dashboard Webinars and you’re ready to take your dashboard skills to the next level then check out Mynda’s Online Excel Dashboard course.

Bonus 20% Off

If you register by Thursday, May 10 it’s 20% off.

So, do yourself a favour and check out the course. I highly recommend the course, but don’t take my word for it. You can read further comments from past students and find out more here.

 

Disclosure: I make a small commission for students who join Mynda’s course via my site (at no additional cost to you), but as you know I don’t just recommend anything and everything. It has to be of outstanding quality and value, and something I can genuinely recommend. After all, if doesn’t live up to what I’ve promised you’ll think poorly of me too and I don’t want that. Oh, and just watching the course videos won’t transform your career, as if you won’t become a Master Chef by just reading cookbooks. You have to actually put it into practice, but then you know that.  

Posted in General | Tagged , | Leave a comment

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

Posted on April 26, 2018 by

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

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

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

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 , | 2 Comments

April #Excel Fool

Posted on April 1, 2018 by

Wanna have fun with your colleagues who get too serious in #Excel on April Fool?  How about playing Hide and Seek?

You may refer to my blogposts below for some ideas:

Hide and Seek – #Excel Workbook

Hide and Seek – Cell Content

Hide and Seek – Column A

When unhide row doesn’t work…

[April fool] – Invisible Gridlines?

Want something other than Hide and Seek?

Trick or Treat 2015

Trick or Treat 2014

Trick or Treat?

IMPORTANT: Play at your own risk! Especially when you plan to play it with your boss(es).  😛

Posted in General | Tagged | Leave a comment

Hide and Seek – #Excel Workbook

Posted on March 29, 2018 by

Excel Tip - Unhide workbook

Have you ever had this experience with #Excel?  Someone sent you a workbook that is empty!  What I mean empty here is not that there is no content in the workbook.  You know that there are lots of data in the workbook but somehow you just could not see a single worksheet, row, or columns in the workbook.  The following GIF demonstrates the situation:

Excel Tip - Unhide workbook1

The workbook is opened, with no worksheet.  NO, not a single one.  Almost all the icons on the ribbon were greyed out, meaning you cannot select them.  Even though there a few, yes just a few, icons that you can click on… the next level icons are also dimmed.

What happened to the workbook?

Continue reading →

Posted in Excel Tips | Tagged , | 1 Comment

Hide and Seek – Cell Content

Posted on March 22, 2018 by

Don’t be shy!  Let silly things continue!  Let’s Hide and Seek with #Excel again! 🙂

Case 1

Excel Tips - Hide and Seek Cell Content

Case 2

Excel Tips - Hide and Seek Cell Content1

Case 3

Excel Tips - Hide and Seek Cell Content3

You may download a Sample File – Hide and Seek Cell Content for the above cases.

If you know the answers for all the three cases above, I believe you are good in using Excel.  If you don’t know all of the above, you may want to continue. Continue reading →

Posted in Format | Tagged , , , , | 1 Comment

Hide and Seek – Column A

Posted on March 15, 2018 by

Cannot unhide column A?

I know… it sounds another silly thing in #Excel.  But I do believe many novice users have had this experience before.  If you were one of them, you would know how frustrating that would be.  I know, because I was one of them…  ;p

Perhaps all we know, we need to select at least two columns (with the hidden column(s) in between) in order to unhide columns.  Since there is no column on the left to column A, we may find it difficult to do such simple thing.

Indeed, there are many ways we can do to unhide Column A.

Continue reading →

Posted in Excel Tips | Tagged , , | 4 Comments

Hide and Seek – Cell border

Posted on March 8, 2018 by

Making cell border in #Excel should be something real basic.  However some people may have found it frustrating / confusing to play Hide and Seek with borders just hoping that they will be displayed in the way they want it.

For example, a simple table as below, we have Bottom Border on row 5 (or A5:B5 in our example):

Excel Tip - Hide and Seek Cell Border

When we hide row 5, the border line is gone….

Excel Tip - Hide and Seek Cell Border1

It is hidden indeed.  Did we just hide row 5?  🙂

Continue reading →

Posted in Format | Tagged | Leave a comment

Has Space or NoSpace in worksheet name?

Posted on March 1, 2018 by

Excel Tips - Space in worksheet name

Have you received workbook from others that carries underscore _ as if a space in their worksheet names?  Did you wonder why people use underscore when we can actually use space in worksheet name?

Well… did you know… long long time ago, #Excel did not allow space in a worksheet name.   If you knew it, you may think it maybe a habit for those who having been using Excel for years.   Yes it could be… but I believe the people who stick to no_space_rule in naming worksheet indeed understand the advantage of avoiding space in worksheet name.

What’s the difference?

Many people don’t know what is the (subtle) difference, except the fact that one has space and one has no space on it.

You will see the difference when you need to write a formula to refer to a different worksheet, only if you pay close attention enough.

Now, let’s watch the following GIF to see if you can spot the difference? (Hint: Play attention to the formula bar)   Continue reading →

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

The missing link of HYPERLINK function in #Excel help

Posted on February 17, 2018 by

I seldom use the HYPERLINK function in #Excel.   Normally I insert hyperlink by CTRL+K, then setting the reference I want the link to go to.   That is super easy (or quick and dirty)!

Excel Tips - Hyperlink0

Note: You may go to Insert tab on ribbon –> Link

However, laziness comes with a price, i.e. limitation ==> Static link.  We cannot insert different hyperlinks based on the contents in a range of cell.  And that’s the reason I need the HYPERLINK function.    Here’s my story:

Excel Tips - Hyperlink

Recently, I created a workbook with more than 50 sheets.  For easy navigation for users, I created a summary page, with a table of content.  Well, 50 sheets!  And I am not going to insert 50 different links by doing the CTRL+K 50 times.  NO WAY.

Continue reading →

Posted in Formula | Tagged | 2 Comments

#Excel Dashboard Course and Free Webinars by Mynda Treacy is closing soon

Posted on February 13, 2018 by

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

With demand for Data Visualization skills and Excel data analysis jobs set to explode, 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.

Excel or Power BI?

Continue reading →

Posted in General | Tagged | Leave a comment