[Affiliate]
Blog Stats
- 3,289,886 hits
-
Join 3,246 other subscribers
- Follow wmfexcel on WordPress.com
Search wmfexcel
Want Google to translate this page?
-
Recent Posts
Top Posts & Pages
- Date Formats - A trick to format date with "st", "nd", "rd", "th"
- #Excel Add current selection to filter. What it does?
- Repeat all item labels in Pivot Table (aka Fill in the blanks please)
- Show number in thousand (k) or in million (M) by using custom format
- Contact
- Limitation (or bug?) with Filter by Color
- The INDIRECT trick of using Structured Reference in Conditional Formatting
- A trick to SUM visible columns only (without VBA)
- Close and Load... to table or to connection? That's the question
- Dropdown calendar in Excel
My YouTube Channel
My Facebook page
- My Tweets
Author Archives: MF
Show number in thousand (k) or in million (M) by using custom format
The custom format to show number in thousand or in million is simple: #,##0,“k” #,##0,,“M” 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 … Continue reading
Delete every thing behind the insertion point – Ctrl Delete
The shortest post ever! š
Compare two documents for changes – Word and maybe Excel
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 … Continue reading
Mouse Tip – Move cell with Shift Key
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 … Continue reading
Pay attention when you concatenate – A1 & B1 vs. A1 & “|” & B1
In 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 … Continue reading
Ctrl+Enter won’t let you down!
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 … Continue reading
Fill a series of month-end dates
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. Using EOMONTH Syntax:Ā EOMONTH(start_date, months)
15-significant-digit issue with SUMIF(S), COUNTIF(S), AVERAGEIF(S)
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 … Continue reading
Posted in Formula
Tagged 15 Significant digits, Array formula, averageif, COUNTIF, SUMIF
10 Comments
In-cell dropdown by Data Validation – Allow input NOT from a list
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 … Continue reading
2014 in review
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 … Continue reading
Posted in General
Leave a comment
Merry Christmas 2014
Christmas is all around. Ā Here’s a Christmas Card (made with Excel of course) for you! š Ā ==>Ā Ā ==>Ā Ā ==>
Posted in General
Leave a comment
Average Trap 2 – Average the average
Why we cannot take the average of averages? 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:
News sharing – ActiveX Controls Not Working?
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 … Continue reading
Average Trap
What do you mean by “Average”? 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 … Continue reading
Feel justified with Fill Justify
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 … Continue reading
Combine contents in a range of cells into a single cell, with no VBA nor formula.
Ā Well, the first thing in your mind maybe using ampersand & Ā or CONCATENATE to combine them. =A1&” “&A2&” “&A3&” “…… =CONCATENATE(A1,” “,A2,” “……) which is more or less time consuming. Two ways to achieve thisĀ quickly Using a combination of … Continue reading
Extract last word from a text string
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 … Continue reading
Date Formats – A trick to format date with “st”, “nd”, “rd”, “th”
Well, there is no such custom format for date in Excel… However it can be achieved indirectly, with helper cell š
Trick or Treat 2014
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 +ā Ā
wmfexcel 