[Affiliate]
Blog Stats
- 3,288,175 hits
-
Join 3,246 other subscribers
- Follow wmfexcel on WordPress.com
Search wmfexcel
Want Google to translate this page?
-
Recent Posts
Top Posts & Pages
- Perform VLOOKUP with 2 lookup values
- Show number in thousand (k) or in million (M) by using custom format
- Sequential number for visible rows only
- =SUM('???'!C3) Is it a valid formula?? No. It is magical indeed!
- Dropdown calendar in Excel
- Date Formats - A trick to format date with "st", "nd", "rd", "th"
- A trick to SUM visible columns only (without VBA)
- Copy data from strictly-protected sheet
- Calculate number of a specific day between two dates
- Advanced vlookup - wildcard characters "?" and "*"
My YouTube Channel
My Facebook page
- My Tweets
Category Archives: Excel Tips
Where are Filter drop-down buttons?
Have you ever encountered a filtered range without Filter drop-down buttons available on the top? No idea what I am talking about? Take a look at the following screenshot. Without the Filter buttons on the top, I tried to show hidden … Continue reading
Interesting bug – Filter with SUBTOTAL
In the process of writing my previous post, I found something wrong with Filter… when Column A contains formula starts with SUBTOTAL, Auto Filter refuses to take the last row.
Sequential number for visible rows only
How to display a column of sequential number to visible rows only? Got this question from a friend. Her task is simple as shown below: I guess many people wanted to achieve the same thing but have no idea at all. Are … Continue reading
Copy data from strictly-protected sheet
How to copy data from a range where you cannot select, due to worksheet protection? Here’s the situation: You receive a worksheet with data but not insight. Well, it happens and you get use to it already. You plan to … Continue reading
Do you know Menu Key??
This key is sitting on the keyboard forever. I wonder how many people have ever pressed it? Maybe you don’t even know where the key is on keyboard… do you? It is between “Windows” and “Ctrl”, just two keys below … Continue reading
Sum a range of number end with “k”
Does it sound familiar to you? If you are having this kind of issue all the time, you may want to continue reading this as I am going to show you three different approaches to fix the problem. Non-Formula approach Formula … Continue reading
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)
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
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 🙂
wmfexcel 