Author Archives: MF

SUM the PRODUCTs or SUMPRODUCT? That’s the question.

The basic of SUMPRODUCT Suppose we have a column showing retail price of different items; another column showing the units sold (illustrated above).  To get the total sales, most people will deploy a helper column to get the sales of each … Continue reading

Rate this:

Posted in Formula | Tagged | 2 Comments

CrossTab SUM with 3 variables

This post is trying to answer a question by Laura in the post of 2D SUMIF with two variables – one on column and one on row. Here’s part of the question: ……I want to look up the department in the vertical … Continue reading

Rate this:

Posted in Formula | Tagged | 3 Comments

Sort Warning?!

Sorting is one of the basic features in Excel.  I believe most Excel users, if not all, know how to sort in Excel.  Without any scientific research, I think most users would click the “Sort A to Z” (ascending order) … Continue reading

Rate this:

Posted in Excel Tips | Tagged | 1 Comment

[Guest Post] 5 things you should start doing with Power Query

This is a guest post by Puneet Gogia. Power Query is a revolution. It’s a (part of a) complete self-service BI tool which can help you in importing, connecting, shaping, cleaning and sharing your data with little efforts. It has … Continue reading

Rate this:

Posted in Excel Tips | Tagged | 9 Comments

Adding bullet to each line in cell

Does it sound familiar to you? Are you using Excel as word processing tool? If you have experienced the task shown above, you should know how tedious and time-consuming it could be as you cannot convert each line into bullet … Continue reading

Rate this:

Posted in Excel Tips | Tagged , , | 4 Comments

Quick Tip: Undo the “Undo”

Undo the “Undo”?  This sounds silly, I know.  But silly things happen (frequently). 🙂 If you undo a step, you cannot undo the undo by applying another undo.   (Because Excel allows 16 undos by default, although you can modify this … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

Remove hidden sheets with “Inspect Document” or VBA

This post is an extension of the previous one – we talked about hiding worksheets is not a good way to prevent others from seeing the data on hidden sheets.  Since you have read that post, you decided to remove all … Continue reading

Rate this:

Posted in Excel Tips | Tagged | 4 Comments

Simple trick to look (hack) into hidden sheet

This post is inspired by the previous post Excel Humor #9 – You do not see me Situation Suppose you are going to share an Excel workbook to your colleagues.  However there is a worksheet with sensitive data that you don’t want them to … Continue reading

Rate this:

Posted in General | Tagged | 11 Comments

Excel Humor #9 – You do not see me

This is a true story with modification… I can’t believe it happened when I first heard it, but then I realize some changes made on Excel 2016 (not sure if also in Excel 2013). Here’s today humor: If you are working … Continue reading

Rate this:

Posted in General | Tagged | Leave a comment

Interactive Chart with navigation panel [NO VBA]

Isn’t it nice?  Don’t think that this is difficult.  No VBA is required.  Indeed you only need to know a few Excel skills in order to create an interactive chart  with navigation panel like this. The skills required: Conditional formatting … Continue reading

Rate this:

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

Last Chance for Power BI and Excel Dashboard courses by Mynda Treacy

  As an Excel user you’ve probably created the occasional report. You may even regularly create Excel Dashboard reports. After all, Dashboards are the most value-adding element for Excel users. However, if you’ve used Excel to build dashboards then you probably … Continue reading

Rate this:

Posted in General | Tagged | Leave a comment

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 … Continue reading

Rate this:

Posted in Chart | Tagged , , | 2 Comments

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… The first thing you would probably do is to go to Data tab and then click the Sort Z to A icon? … Continue reading

Rate this:

Posted in Excel Tips | Tagged , , | 22 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: Answer:  Copy and Paste with a simple trick … What? Not Find and Replace?  Yes, you may do it by using … Continue reading

Rate this:

Posted in Excel Tips | Tagged , , | 4 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. If you want … Continue reading

Rate this:

Posted in General | Tagged , | Leave a comment

The 5-minute Excel Dashboard Challenge @ Bacon Bits

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 … Continue reading

Rate this:

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…

Rate this:

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

Rate this:

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 … Continue reading

Rate this:

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 … Continue reading

Rate this:

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