Want some pies?

First of all, this blogpost is about Pie Chart, but not about how to make a Pie Chart.  Indeed, I am not going to show the steps of making Pie Chart here.   Also I am not intended to discuss whether Pie Chart is good or not to visualize data here. 🙂

One day my brother asked me how to show the data of “Jun” for the below Pie Chart:Excel Tips - Stacked Pie 1.png

As you can see from the above screenshot, the data contains 6 series.  My question to him  was:  “How are you going to plot that into Pie Chart by using Paper and Pencil?”


A flash in my mind is something like this:Excel Tips - Stacked Pie 2.png

Seriously??? How are we supposed to visualize the data? Continue reading

Posted in Chart | Tagged , , | Leave a comment

Copy and Paste can do so much…

Answer to the 5 little Tips (tests) – Part 5/5

The task: To sort the following dates in descending order.  Sound silly but…

Excel Tips - Trick #5.png

The first thing you would probably do is to go to Data tab and then click the Sort Z to A icon?

Excel Tips - Sorting Dates.PNG

And get the following result… without even looking at it (as you trust Excel as reliable tool), believing Excel gets the job done.

Excel Tips - Sorting Dates1.PNGYes. Excel gets it done…

BUT are you sure the dates are sorted in the way you want?  In the in-house training in my previous company, many participants really thought so…  

I always remind myself:  No matter how familiar I think I am with Excel, CHECK the result before sending out.

Get thing done ≠ Get thing right

The date is not sorted correctly because they are not input as numeric date.  Instead, they are input (actually downloaded from SAP) as text.


You may want to see my other post: Number Stored as Text…

So how to fix the problem?

Continue reading

Posted in Excel Tips | Tagged , , | 18 Comments

Go to Special + Copy and Paste do the magic

Answer to the 5 little Tips (tests) – Part 4/5

The task: To convert “1” to “Tick” in a table like the following one:

Excel Tips - Trick #4.png

Answer:  Copy and Paste with a simple trick …

What? Not Find and Replace?  Yes, you may do it by using Find and Replace.  As it is Excel, there are different ways to accomplish the same task.  Either way, the first thing we need to know is how to insert a “Tick” mark.  Sound silly? But are you sure every one knows how to do that??  I have a blogpost to describe how to perform a similar task.

The following screencast shows you how to accomplish the task: Continue reading

Posted in Excel Tips | Tagged , , | 2 Comments

Free webinar on Power BI by Mynda Treacy is coming soon

Have you heard about Power BI from Microsoft?

Probably yes.  However I guess most people are not yet aware of this amazing BI tool from Microsoft that enables you to build stunning interactive dashboards and share them on any device.screen-shot-2017-02-05-at-5-25-51-pm

If you want to know more about Power BI, you may want to join the free webinar by Mynda Treacy coming soon.  Before that, here’s a quick overview on Power BI. Continue reading

Posted in General | Tagged , | Leave a comment

The 5-minute Excel Dashboard Challenge @ Bacon Bits

Dashboard Challenge.gif

Have you ever thought of building an Excel Dashboard in 5 minutes?  Yes, only 5 minutes. That’s why it is a Challenge!   You may find the details of The 5-minute Excel Challenge at Bacon Bits.

Honestly, I haven’t thought of building a dashboard in such a short time.  In my experience, it takes hours (if not days) of work to create a dashboard.  Nevertheless, after reading the very last sentence of Mike’s post regarding the challenge:

“I’m very much looking forward to seeing all the cool things our Excel community can create!”

I’d decided to take the challenge.   And I managed to finish it in four and a half minutes! 🙂

As I am working in retail industry, I take the challenge with something I am familiar with: Store KPI Dashboard.   Continue reading

Posted in General | Tagged | 4 Comments

Paste special with simple mathematic operation + – * /

Answer to the 5 little Tips (tests) – Part 3/5

The task: Doing simple calculation (add, subtract, multiply, divide) without formula

Answer:  Copy and Paste Special…

excel-tips-copy-and-paste-with-operation Continue reading

Posted in Excel Tips | Tagged | Leave a comment

Happy New Year of Rooster

Hi everyone,

Today we celebrate the Chinese New Year🐔. No Excel tips but CNY greetings and blessings.  Wish you a prosperous and rewarding new year ahead! Continue to excel.

Cheers, MF

Posted in General | Leave a comment

Fill so good…

Answer to the 5 little Tips (tests) – Part 2/5

Fill a series (of sequential numbers)

The task: Fill a big series of sequential number, say from 1 to 24567.

Answer:  You may find the answer to the question in this post:  Fill Series.

That post was indeed inspired by one of the winning MEME posts found in Bacon Bits.

Check this out for some Excel FUN:  http://datapigtechnologies.com/blog/index.php/excel-meme-contest-winners/

Want more?

Continue reading

Posted in Excel Tips | Tagged | Leave a comment

Transpose Data (Static and Linked)

Answer to the 5 little Tricks posted in the beginning of the year – Part 1/5

Transpose Data – Result is static

This is actually a simple trick of using Copy and Paster (Special, Transpose).  See? It can be done in a second. Excel Tip - Transpose Data - opening.gif

Nevertheless, as I mentioned in previous post, many people are not aware of this simple trick even though they work with Excel every day (they work in accounting field).  What they did was to move cell one by one… @_@ Continue reading

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

5 Little Excel Tips (tests)

First of all, Happy New Year of 2017!  Wish you all an Excellent year ahead!

Let’s start the year with a simple “Game” which I developed for in-house Excel training in my previous company.  All participants were expected to have basic Excel knowledge.  Many of them worked with Excel on daily basis.  I had delivered the training to over 100 colleagues with this “Game” as warm up exercise.

The “Game” covers five Excel tricks.   To me, and probably to most of you, they are quite basic and simple.  However, to my big surprise, there was NO participant (out of the >100 participants) who could complete the “Game” successfully in 5 minutes.

You may see the short description of the five tasks below: Continue reading

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

Merry Christmas

Christmas Tree 2016.gif

Dear readers,

Merry Christmas!!!

Wishing you and your family a healthy, fruitful, rewarding and Happy New Year of 2017!

See you next year!

p.s.  Should you want to play around the Excel Card above,  here’s the christmas-tree!

Tip: Hold F9 for the snowing effect.  Hope you like it. 🙂

Posted in General | Tagged , | Leave a comment

A tip of using AutoCorret to input names quickly

This is a simple tip that most secretaries and executive assistants love. 🙂

Excel Tips - AutoCorrect0.gif

Did you ever look up someone’s full name in Outlook or email directory?  It happens especially if you are working in a multi-national company and you are working with people from around the world.  Some foreign names are really difficult to spell.  Right?

So why not we input them into AutoCorrect and let Excel/Word/PowerPoint to remember the spelling and type it lightening fast for us?  Isn’t it nice if we just need to input the initials?   Continue reading

Posted in Excel Tips | Tagged | Leave a comment

Are we aligned?

A simple trick to align objects neatly. 🙂


Did you move the objects one by one and then realized that it took you forever to have them perfectly aligned?


Why not using the “Align” under Drawing Tools?

Continue reading

Posted in Excel Tips | Leave a comment

Turn boring Excel reports into Business Intelligence – Excel Dashboard

Last week I told you about the awesome Excel Dashboard Webinar  was back.  I hope you had a chance to watch it and get something already.  If not, it’s not too late.  But hurry seats and sessions are limited with the last webinar on December 7. Click here to register for the free webinars.  Check it out, you will find something valuable to your daily Excel works.

If you want to have a deep dive into Excel Dashboard, the fantastic dashboard course that Mynda Treacy has put together over at My Online Training Hub should worth your time.


Mynda’s course will have you standing out from the crowd in no time, just like Fraser, with the skills to build interactive reports like this amazing Sales and Marketing dashboard:



Who is the course for?

If you’ve attended Mynda’s free Dashboard webinar then you’ll know how powerful Excel is and how having Excel Dashboard skills will skyrocket your productivity and career.

You really need to know Dashboards if:

  • Your current reports take forever to update or,
  • You feel like no one takes notice of your reports or they constantly come back with requests for a slightly different report or,
  • You want to be able to present informative and interactive reports or,
  • You need to take your Excel skills to the next level to impress your clients with more meaningful reporting or,
  • You want increase your chances of employment, or get that promotion you’ve been after.

What members are saying about the course:

Loads of people have already registered and are on their way to wowing people with some killer reporting tricks, not to mention getting their work done faster.

Mynda shared this email with me from Kerry (with her permission), a member from a previous class:


“I have to tell you that I just downloaded the Session 4 workbooks. They are fantastic. I thought I would have to take notes in order to recall your instruction. Not at all – it is all in the spreadsheet. Your online tutorials and download material are extraordinary. I am so impressed.

I will be recommending your online training to my colleagues. Your workbooks are easy on the eye and very easy to understand.

I am terrified by the thought of writing formulas. You explain them and make it look so easy.”

Early Bird Discount

I just wanted to remind you that the 20% early bird discount ends Thursday December 8, at 8pm in Los Angeles to be exact.

So, go ahead and enroll here before the discount ends on December 8.


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, you have to actually put it into practice, as if reading a cookbook won’t make you a chef.

Posted in General | Tagged , | Leave a comment

Free Excel Dashboard Webinars by MYNDA TREACY is back

If you have been following my blog for more than three months, you should have read this before.  If you wonder why I am reposting it again and again (Yes, this is the 3rd time I write about Mynda’s Excel Dashboard webinars and courses), the answer is simple: it is awesome.  If you are serious in taking your Excel skills to the next level, you should really take time to learn more about Excel Dashboard.

If you’re not sure what a dashboard is, or how these skills might be relevant to you, or you think Excel Dashboard is too advance for you, then you can attend one or both of Mynda’s FREE Excel Dashboard Webinars.  You will be surprised that it is less difficult than you may have thought about.  Nevertheless, you are expected to know Pivot Table.

Free Excel Dashboard Webinars

Webinar 1 – Excel Dashboard for Excel 2007/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.


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

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 the desktop version of Excel 2010, 2013 or 2016, or Office 365 ProPlus. Power Pivot is available with the desktop version of Excel 2010 or Excel 2013/2016 Office Professional Plus, Excel 2016 Professional, Office 365 ProPlus, or in the standalone editions of Excel 2013/2016.

By the way, did you know that Power Query is “renamed” to Get and Transform in Excel 2016?

I don’t know why there is such change, but my interpretation is “Get it and Transform your career”.  🙂

So, do yourself a favour and Click here to sign up for the Webinars.

What people are saying about the webinars

Over 25,000 people have attended her 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!”



“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.”



“I presented my first dashboard to the CEO yesterday and he was ecstatic. Since we are downsizing I was very worried about my job, but after seeing his reaction…I understand that my job is secure (for now). Thanks!”

Iris Stein




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, you have to actually put it into practice, as if reading a cookbook won’t make you a chef.

Posted in General | Tagged , | Leave a comment

Be cautious when using SUMIF(s)

SUMIF is a handy but helpful function.  The syntax is simple:

=SUMIF(range,criteria,[sum range]

Excel Tips - SUMIF.png

What it does is quite straight forward indeed.  In the example above, it instructs Excel to look into the range (A2:A7), look for the matching criteria, which is “A“, and then sum the “corresponding” value in the sum range (B2:B7).  That’s how we get 5 as a result.  Make sense?

However, if we point to incorrect reference for sum range, SUMIF may give you an unexpected (and also incorrect) result.  What do I mean?  Let’s say if I mistype (by human error of course) the formula to

=SUMIF(A2:A7,D2,B3:B8) 'Note the one cell shift in the sum range

Excel Tip - SUMIF 1.png

Do you expect a result of 4?  Continue reading

Posted in Excel Tips, Formula | 2 Comments

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