Move a chart object with arrow keys

If you do chart in Excel, you may have encountered this: You clicked on a chart object, tried to move it just a little bit to the right by pressing Right Arrow key a few times… Instead of the chart being moved to right, different chart elements were selected one by one with each press of the arrow key.

Excel Tips - Move a chart with arrow key.png

OMG… How come it is so difficult to perform such a simple task?

There is a simple trick (although imperfect) to let you move a chart by using arrow keys…  Continue reading

Posted in Excel Tips | Tagged , | 2 Comments

The powerful Power Query and the interesting TechNet Virtual Labs

As you may be aware of, I haven’t written a real blogpost recently.  I was super busy at work in the past month.  No surprise.  There are moments Excel won’t help me much at work, especially when bosses like PowerPoint (and PDF) more than Excel.  You know what I mean!

Nevertheless, because of the tight schedule and heavy workloads, I did actually spend more time in exploring the Power Query, which is simply POWERFUL.

To be honest, I am just a beginner in using Power Query.  And what I used are mainly the most basic functions from the ribbon tabs (Home, Transform, Add column) of the Query Editor.   But you know what, it helps a lot already.  And it is indeed quite intuitive to an experienced Excel user.

What I really like about Power Query?

Continue reading

Posted in General | Tagged , | Leave a comment

Excel Humor #8 – iLOOKUP, the ultimate function in using Excel

Have you ever used “i”LOOKUP?  I do it almost every day…   🙂   Continue reading

Posted in General | Tagged | Leave a comment

Excel Humor #7 – Interactive Report

Excel Humor - Interactive Report.PNG

I hope you will never run into a situation like this… 🙂

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

What is a “Container Chart”?

First of all, I would like to thank Chandoo for suggesting this name for the chart I submitted to him for a charting contest a few months ago.  What’s more surprising to me is I was one of the winners of the contest. What a honour of mine! 🙂

Here’s a screenshot of part of the chart I prepared.  You may view the full chart and download a copy of the workbook from Chandoo’s site here.

Excel Tips - Column Chart1.png

Don’t miss the entries from other participants as you will learn a lot of Excel techniques from them.

What you see above is actually a Panel Chart… meaning more than one charts are putting together intentionally to visualise the data of interest.  The upper part (showing the yearly profit) of the chart is a simple clustered column chart; the bottom part (showing the yearly costs with four components) may be something of your interest to know about, right? Continue reading

Posted in Chart | Tagged , | Leave a comment

Insert “tick in a box” in Excel

Recently, I’ve been collecting Excel files from colleagues regarding implementation status of some activities in the region.  Part of the template looks like the screenshot below:

Excel Tips - Insert Tickmark.png

Quite simple and straight forward, right?

I expected I would get something like the following in return: Excel Tips - Insert Tickmark 1.png

But in reality, I got somethings like these:Excel Tips - Insert Tickmark 2.png

Franky, I don’t blame them.  On the contrary, I appreciate their efforts in communicating the results.  Especially the one who used “circle” as the indicator.  I can imagine how much time they spent on it.

Why couldn’t they simply click on the checkboxes?

Because the “checkbox” on the original template was not true checkbox.  It is just a plain “rectangle” in a cell, meaning that you cannot “check” or “uncheck” it…. not to even think about putting a tick on it.

So how can I put a tick on the box?

Excel Tips - Insert Tickmark 1.png Continue reading

Posted in Excel Tips | Tagged | 4 Comments

Three years of Excel Blogging – A quick review

Note: This is not an Excel post…

I totally forgot that today is the 3rd Anniversary of my Excel blog.  Time really flies.  Thanks WordPress for sending this 3rd Anniversary notification to me.  What’s a pleasant surprise! 🙂

Screen Shot 2016-09-10 at 4.17.54 PM.png

Did you know how many posts are here in

Screen Shot 2016-09-10 at 4.23.32 PM.png

I can’t believe it.  It’s 185 posts! Including this post, it turns to 186 posts.  That means I published more than 1 post per week since I started it.  I didn’t know I am so hard working.

So which posts got most views in 2016 so far?

Post Views
When unhide row doesn’t work… 50409
Dropdown calendar in Excel 47196
Perform VLOOKUP with 2 lookup values 43654
Advanced vlookup – wildcard characters “? and “*”” 19038
Advanced vlookup – Text vs. Number 16149

Obviously, VLOOKUP is still the most common function that people like to explore.  But you know what? The more I know Excel, the less I use VLOOKUP indeed.

Also tell you this…

I am actually a bit complacent when I Google some key words like “cannot unhide row in Excel“, “Dropdown calendar in Excel“, “vlookup 2 values“, “copy from protected excel sheet” and see my posts appear on the first result page, sometimes even on the top.  🙂

Where are my readers coming from?

Screen Shot 2016-09-10 at 4.44.02 PM.png

<Top 10> 

Not surprisingly, majority of readers come from United States.  Even less surprisingly is the number from Hong Kong, where I come from and live in.  I doubt if all the views from Hong Kong are coming from my friends or people who knows me.  🙂

The journey starts at low… but rewarding!

Screen Shot 2016-09-10 at 4.30.39 PM.png

Honestly, the first year was difficult.    What I mean is not the writing process but the emotional one.  I kept writing but no one was reading… not such a good feeling, right?

Nevertheless we have a proverb “萬事起頭難” in Chinese.  It means “The first step is the hardest. As long as you persevere, you are bound to succeed.

I wouldn’t say I have succeeded.  But frankly, this journey is quite rewarding.  I learn so much in the progress.

If you are (still) reading this, I sincerely thank you!  You are the motivation for me to keep on writing on this blog @, aka BeExcellence!

I hope you enjoy it as much as I do.  Let’s Excel together!

Posted in General | 4 Comments

Move cells with data to the top

Did you spend too much time to move cells so that there are no blank cells in each column?

Again, a picture tells thousand words.  Here’s the task…Excel Tips - Move cells with data to the top 0.JPG

Would you do it this way?Excel Tips - Move cells with data to the top 1.gif

Or this way?Excel Tips - Move cells with data to the top 1.1.gif

What did I do in the second animated screenshot?

Continue reading

Posted in Excel Tips | Tagged , | 1 Comment

Excel Humor #6 – Probably the shortest and the best Excel Proficiency Test

Definition of Advanced Excel user varies according to expectation.  Someone thinks advanced users should know all the VBA, Pivot Tables and be able to write super long formula.  Someone considers oneself “Advanced” for knowing the basic of VLOOKUP.   On the contrary, your boss may consider Power Pivot or Power Query as “Basic” Excel skill although h/she may have no idea what it is… 😦

Is this something bothering you?

No worries.  I am going to share with you probably the world shortest and the best Excel Proficiency Test that gives good indication of someone’s Excel skill in 3 seconds.

Want the test?  Here we go (Just for Fun): Continue reading

Posted in General | Tagged | 2 Comments

Insert or move a field into Pivot table without drag and drop

If you do Pivot Table, the following won’t be a stranger to you as you must do a lot drag and drop to pivot.

Excel Tips - Move field by direct input.gif

But did you know that you may insert a field name and / or  swap the position of field name in the layout of a Pivot table by directly input?  See below:   Continue reading

Posted in Excel Tips | Tagged | 2 Comments

Writing a long formula in steps

Writing a long formula is not easy, even for an advanced Excel user.  What I mean long is  a formula with many nested FUNCTIONS within a single formula.  The difficulty I am talking about is not related to whether you understand the functions.  It is more about the concentration and carefulness required for writing the formula.  Why is that?

Excel Tip - Writing long formula.png

If you have encountered the above message frequently, you know why! 🙂

You need to make sure you have corresponding pair of brackets () for each function; not to mention the comma (,) required for different arguments involved.  For instance, if you have 7 FUNCTIONS in your formula, you should have 7 pairs of brackets plus N commas spread around your formula.

The frustration usually comes from an attempt to write the long formula in one go.  When you are having the above error message popped up non-stop, maybe it’s time for you to break your long formula into bit-size formulas, with helper columns.  Let’s use the Text manipulation example discussed before to illustrate how a long formula is written through many small steps. Continue reading

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

Transform boring Excel reports into Business Intelligence 

Dashboard Course by MYNDA TREACY is now open

Last week, I shared the news about the free Excel Dashboard Webinars by Mynda.  I hope you did find a time to join the webinars.

If you haven’t watched the Webinars and are not sure what a dashboard is or how these skills might be relevant to you, then you can attend Mynda’s FREE Excel Dashboard Webinars, but hurry as they’re only on for a few more days.

If you’ve watched Mynda’s FREE Excel Dashboard Webinars, and you’re ready to take your dashboard skills to the next level then check out Mynda’s Excel Dashboard Course.

It will teach you how to build amazing interactive dashboards that impress your boss, get you noticed and help you stand out from the crowd.  And the best part, aside from the praise from your boss and colleagues ;-), is you’ll also learn tons of tips you can use in your everyday Excel work to get things done more efficiently.

What to Expect in the course:

  • The course is video based and available online 24/7
  • It comes with comprehensive Excel workbooks complete with notes
  • 5 example Excel dashboards to keep
  • There’s also an option to download the videos,
  • Plus Mynda personally provides support for the first 6 weeks of the 12 month membership.

By the way, the 6 weeks of personal support is the reason the course is only open for a limited time (registration closes Aug 18). Mynda limits the number of classes to allow time for other projects and a break every now and again. That’s not to say she won’t support you after the first 6 weeks, it’s just that it might take her a few days or a week to reply to you if she’s out of town or busy working on another course etc.

Click here for more information and a sample video from the course (it’s worth watching just to learn this tip).

Who is the course for?

Dashboards are an incredibly valuable tool in today’s market for consultants, analysts and managers. And when you know the tricks it can be straightforward to build highly professional and interactive Excel dashboards.

If your work requires preparation of reports in Excel (or you’d like it to) then you will find this type of training worthwhile.

Mynda teaches you some simple rules (that most people don’t know), which you can use in all of your Excel work that will get your job done more quickly and efficiently, and of course she’ll teach you how to make killer dashboards including animated charts and other cool interactive elements.  

I would 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.  However, this course is not for Excel beginner.

20% Early Bird Discount

You will enjoy a 20% early bird discount if you register Mynda’s  Excel Dashboard Course  by Thursday August 11 (at 8pm in Los Angeles to be exact).  Just to remind, the course will be closing on Thursday August 18.

So, do yourself a favour and check out the course.  The course is awesome and it’ll transform your Excel reports and possibly even your Excel career.

Disclosure: I make a small commission for students who join Mynda’s course via my site, 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, like you won’t become a Master Chef by reading cook book only.  You have to actually put it into practice, but then you know that.

Posted in General | Tagged , | Leave a comment

Excel Humor #5 – Are you relying on Excel too much?

Are you an Excel Dependency? Do you use Excel even for simple maths?  Sometimes I do. 🙂

Excel Humor (5) - Excel Dependency.png


Posted in General | Tagged | Leave a comment

Text manipulation with Excel functions

We talked about RIGHT, LEFT, MID, UPPER, LOWER, PROPER, FIND, SEARCH, REPLACE and SUBSTITUTE in the past few weeks.  Now it’s time to put every thing together to solve a problem.  This is actually the most amazing part of using Excel functions: A single function may not solve your problem.  A combination of functions could do a job that is out of your imagination.  🙂

Here’s the task:Excel Tips - Text Manipulation

Continue reading

Posted in Formula | Tagged , , , , , , , , , , | 6 Comments

Case-insensitive SUBSTITUTE???…

We talked about REPLACE and SUBSTITUTE last week.  At the final note, I said that SUBSTITUTE is case-sensitive.  If we need to perform a case-insensitive SUBSTITUTE, it is indeed not an easy job unless we are talking a single letter only.

Using the same example we used last time, if we want to substitute “E” with “_” regarding the case of “E”, i.e. all occurrences of “E” and “e”,  it is total doable without too much effort.  What we need to do is simply use SUBSTITUTE functions twice, in a nested formula.


Excel Tips - SUBSTITUTE case-insensitive1.png

The inner SUBSTITUTE changes “e” to “_”; the outer SUBSTITUTE changes “E” to “_”.  As a result, all “e” and “E” will be replaced by “_”.  As simple as that! 🙂

However, if we want to replace a “text string” that contains more than one letter, and hence could come in various case combinations, then it could be a real headache. Continue reading

Posted in Formula | Tagged , , | 6 Comments


Another pair of functions that has very similar meaning literally.   Although I am not able to tell you the differences between “replace” and “substitute” in English, I can show you the differences of the REPLACE and SUBSTITUTE in Excel.  Are you ready?

Let’s start with the function.  Both functions, as you may guess, look into a text string, then based on your instruction, replace part(s) of a string with any text(s)/character(s) specified.

The key difference between REPLACE and SUBSTITUTE

REPLACE requires you to specify a staring position and length of character you want to replace with something

SUBSTITUTE looks for specific texts/characters and then substitute it with something. Sound like FIND?  If you don’t know FIND, take a look at FIND vs. SEARCHContinue reading

Posted in Formula | Tagged , | Leave a comment


In many situations, we use either FIND or SEARCH together with LEFT, RIGHT, MID to achieve what we need.  A typical example is to extract the first name and the last name from a full name where a coma acts as the separator, like the example below:

Excel Tips - Find Search 1.png

To get the Last Name:

'Why 99 in the last argument? You may want to read the previous two post.

To get the First Name:


So I think it makes sense to talk about FIND and SEARCH. Continue reading

Posted in Formula | Tagged , | Leave a comment

UPPER, lower, Proper

I am wondering why these functions are not available in Word, but Excel?

Don’t make me wrong.  I like these functions.  Nevertheless I am not a big fan (nor a small fan) of using Excel as word processor.  Whenever possible, use Excel to handle data; Word to handle words.  That are what the softwares designed for!?

But the fact is, many people use Excel in their own ways and can somehow create forms and documents with Excel even better than they do the same in Word.  Agree?   I would say: Excel is so great that it handles not just numbers but texts equally effective.

Well, too much personal talks… let’s go back to the subject: UPPER, lower, Proper. Continue reading

Posted in Formula | Tagged , , | 8 Comments

RIGHT. I LEFT. In the MID of…

Obviously this post is about the most popular text related functions in Excel.  Meanwhile, it is also a message from me…

Yes, you are RIGH! I LEFT my ex-company and have landed on a new job which is much more busier… and I am in the MIDdle of probation.  Although I am trying hard to update this blog every weekend, I am not sure if that can be achieved.  Don’t be surprised if you don’t see new post every week as it used to be.  On the positive side, I’ve got more chances working with data and Excel in my new job.  That would absolutely give me more ideas on writing.  🙂

Let’s get back to the functions now.

Attention! LEFT! RIGHT! LEFT! RIGHT!… and MID!  Continue reading

Posted in Formula | Tagged , , | Leave a comment

Custom List is so good


Did you know… you can create a list of Month and Day of Week by drag and drop?  You probably knew it on the first week of your Excel journey… Excel Tips - Custom List.gif

But did you know that, Excel may also give you a list of your favorite fruits?Excel Tips - Custom List1.gif Continue reading

Posted in Excel Tips | Tagged | 3 Comments