The custom format to show number in thousand or in million is simple:
The “k” or “M” is optional, depend on whether you want to show it in the header or in the figure itself.
The trick is to put 1 comma and 2 comma at the end of the format setting (#,##0) for “thousand” and “million” respectively.
Nevertheless, do you know how it works? Continue reading
The shortest post ever! 🙂
If you use Word, I believe there is high chance that you send out a document to someone for comment, and you expect the receiver would know how to use Track Changes in Word, which is a great function to highlight changes made to a document.
Unfortunately, not every one working in office really knows about MS OFFICE. You may get a revised file with no hints on what has been revised. So what do you do? Put the two document side by side and do eye-ball checking? LOL… 
Don’t laugh as that is what I heard and saw. It is doable if the document contains only few lines. What if we are talking a doc with few pages and even more?
The Compare… command in Word absolutely save your life. Continue reading
If you have read my post about Move Cells with Right Click, you should know it is possible. However for continuous improvement, we often look for faster way to achieve the same thing. With a simple trick, you may move cells and shift surrounding cells down or right easily:
Continue readingIn many case, we may want to combine texts from different cells. This can be done by either CONCATENATE function or simply an Ampersand &. E.g. A1 & B1 & C1 …… We may take it for granted as it looks so straight forward… Well. It actually depends on the reason for combining texts from different cells. If you are doing so because you want to Perform VLOOKUP with 2 lookup values, the robust way to combine texts should include a delimiter in between, e.g. A1 & “|” & B1 … Why? Because laziness has a price. Continue reading
As you may be aware of, Ctrl+Enter enables you to input the same thing (be it text, value or formula) in multiple cells simultaneously. See screenshot below for example:
It is quite handy and I use this trick a lot in daily work.
Nevertheless did you know that you may apply the same trick to stay on the active cell after you input a value? Continue reading
In many cases, we may want to create a list of month end dates. This can be done either by formula or Fill. Either way, we should input the first month end date (starting date) manually.
Syntax: EOMONTH(start_date, months) Continue reading
Have you applied SUMIF or COUNTIF to a range that contains numbers with more than 15 digits stored as text? If you did or if you do, you’d better continue to read this as you’ve got wrong answers that you may not be aware of. See example below: 
Continue reading
Did you know that there is Input Message box which look like a comment box?
The main difference is your don’t see any “indicator” on the cell. The Input Message box pops up when the cell is selected. To do this, first select the range of cells you wish to have the Input Message, then go to Continue reading
This is about a trick of using named range that includes a blank cell; which allows any input even the cell is with Data Validation that allows list.
When you attempt to input anything other than what is restricted by Data Validation, you will probably see the screen shot above which leaves you basically no choice but “Retry”. Actually there are two ways to get rid of this annoying message and input anything to a cell with data validation. Let’s talk about the conventional way first. Continue reading
Originally I have no plan to post until 2015. To my pleasant surprise, the WordPress.com stats helper monkeys prepared a 2014 annual report for this blog. So why not?
Here’s an excerpt:
The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 41,000 times in 2014. If it were a concert at Sydney Opera House, it would take about 15 sold-out performances for that many people to see it.
Click here to see the complete report.
Sincere thanks to all the visitors who read, shared, and commented this blog. Wish you all a Prosperous, Fruitful, and Happy New Year of 2015!
Christmas is all around. Here’s a Christmas Card (made with Excel of course) for you! 🙂
==> 
==> ==> Continue reading
Once upon a time, I was questioned with the above. First thing on my mind: “Is it a test?”… So I answered the question carefully, as shown in the screen shot below: Continue reading
IF you have no idea of what ActiveX Controls is or IF you don’t even have the Developer Tab on your ribbon, this post may not be relevant to your daily work with Excel; ELSE it is worth taking a look. 🙂
When the question “What is the average of…” is asked, the top of mind function could possibly be AVERAGE. But did you know that AVERAGE function ignores text (including number stored as text), logical values and empty cells??… OMG no? Then you have probably made a lot of mistakes in calculating average in your Excel spreadsheet.
Like majority of smartphone users, Excel users (know how to) use only a little portion of functionalities provided by the tool, even though we pay the full price for the tool…
Fill Justify (on the editing group of the Home Tab) is another mysterious tool in Excel that, when discovered, will make you not only fall in love with it; and also feel justified of your investment in Excel.
Just in case the above picture is not clear enough, here’s the content in A12:A16
Am I serious? Of course. Continue reading
Well, the first thing in your mind maybe using ampersand & or CONCATENATE to combine them.
There are many cases that we may just want the last word from a text string. Typical example would be something like the last row in the above screenshot: There are many codes at the end of a text string and we just want to focus on the codes… If you need to deal with this, the following formula may help you:
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",10)),10))
Let’s take the text string in A2 “Today is beautiful” for illustration:
A picture can tell a thousand words… Does the picture below give you the explanation? 🙂
Well, there is no such custom format for date in Excel… However it can be achieved indirectly, with helper cell 🙂
Today is Halloween. Let’s do some funny things today.
Have you ever thought about to turn your screen upside down? To do this, you just need to press CTRL + ALT +↓ Continue reading
New Generation Finance - Accounting - Controlling using Microsoft BI stack
A Power BI Creator Blog
Work smarter by Mastering Functions in Excel
Work smarter by Mastering Functions in Excel
Helping lawyers make the most of Microsoft Excel
Work smarter by Mastering Functions in Excel
Work smarter by Mastering Functions in Excel
Work smarter by Mastering Functions in Excel
Peltier Technical Services - Excel Charts and Programming
Turn your data into opportunities
Q&A about Excel
Doug Glancy's Excel Site
wmfexcel 