[Affiliate]
Blog Stats
- 3,266,408 hits
-
Join 3,244 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"
- How to replace value with a value from another column in Power Query
- Show number in thousand (k) or in million (M) by using custom format
- How to sort bar chart in descending order?
- #Excel Add current selection to filter. What it does?
- Perform VLOOKUP with 2 lookup values
- How to put the Date Modified info on #Excel worksheet using #PowerQuery
- Repeat all item labels in Pivot Table (aka Fill in the blanks please)
- The INDIRECT trick of using Structured Reference in Conditional Formatting
- SUMIF vs. GETPIVOTDATA in terms of performance in #Excel
My YouTube Channel
My Facebook page
- My Tweets
Category Archives: Formula
Writing a long formula in steps
Writing a long formula is not easy, even for an advanced Excel user. What I mean long is a formula with many nested FUNCTIONS within a single formula. The difficulty I am talking about is not related to whether you understand the … Continue reading
Text manipulation with Excel functions
We talked about RIGHT, LEFT, MID, UPPER, LOWER, PROPER, FIND, SEARCH, REPLACE and SUBSTITUTE in the past few weeks. Now it’s time to put every thing together to solve a problem. This is actually the most amazing part of using … Continue reading
Case-insensitive SUBSTITUTE???…
We talked about REPLACE and SUBSTITUTE last week. At the final note, I said that SUBSTITUTE is case-sensitive. If we need to perform a case-insensitive SUBSTITUTE, it is indeed not an easy job unless we are talking a single letter … Continue reading
REPLACE vs. SUBSTITUTE in Excel
Another pair of functions that has very similar meaning literally. Although I am not able to tell you the differences between “replace” and “substitute” in English, I can show you the differences of the REPLACE and SUBSTITUTE in Excel. … Continue reading
FIND vs. SEARCH
In many situations, we use either FIND or SEARCH together with LEFT, RIGHT, MID to achieve what we need. A typical example is to extract the first name and the last name from a full name where a coma acts as … Continue reading
UPPER, lower, Proper
I am wondering why these functions are not available in Word, but Excel? Don’t make me wrong. I like these functions. Nevertheless I am not a big fan (nor a small fan) of using Excel as word processor. Whenever possible, … Continue reading
RIGHT. I LEFT. In the MID of…
Obviously this post is about the most popular text related functions in Excel. Meanwhile, it is also a message from me… Yes, you are RIGH! I LEFT my ex-company and have landed on a new job which is much more … Continue reading
Display single letter Day of week in Excel
Is it achievable? Not by formatting but formula.
Number Stored as Text…
It is something basic and commonly seen. Nonetheless, it is really important to understand the different behaviors between a number, and a number stored as text, especially if you want to move forward to write effective formula. This post is intended … Continue reading
Three different ways to do case-sensitive lookup
If you do VLOOKUP, you probably know that one of the limitations of VLOOKUP is case-insensitive. It means case is not a consideration in the lookup process, where PETER is essentially the same as peter. Hence VLOOKUP will consider that … Continue reading
Rank in subgroup… RANKIF?
To get the overall ranking is easy with the RANK function. The syntax of RANK =RANK(Number,Ref,[order]) Number is the number we want to rank; Ref is the list of numbers of comparison (could be an array, a reference or a list … Continue reading
Make impossible possible
Convert text of specific pattern like “1d 2h 3m 4s” into real time (26:03:04) in Excel To kick start the Year of Monkey, let’s challenge the apparently impossible… Do you think it is not possible in Excel? If you do, you … Continue reading
Calculate number of “*day” in a given period
This post is about how to twist the NETWORKDAYS.INTL function to solve the question. About two years ago, I wrote a post <Calculate number of a specific day between two dates> to explain how the following formula (Solution 1) could solve the question: … Continue reading
Display % of column and % of row in a dynamic way
This post is intended to give you an introduction of CHOOSE function and Option Button (Form Control). Here’s the situation: We want to display the % of total in a 2-D table. Nevertheless the % can be of column total … Continue reading
VLOOKUP the Nth matched item, with helper column
This should be the last post in 2015. So it’s better to answer unanswered question in the year. 🙂 Question: “VLOOKUP will return the first matched value found, in case there are multiple matched records what formula should we use?” This … Continue reading
How to limit time interval input in a single cell?
Got the following question from a reader: Under data validation, is it possible for me to restrict the time duration (in a cell) to be 30mins or less? Example: 9.00 – 9.30 (accepted) 10.15 – 10.50 (rejected) Obviously, the answer … Continue reading
IF, it is too complicated…
Writing an IF function is quite straight forward, if there is only one logical test involved. For example, we want to comment on a shop’s performance by looking at its actual sales. If Actual hits Budget, then Good job, else … Continue reading
A trick to SUM visible columns only (without VBA)
We know that SUBTOTAL allows us to perform some basics functions like SUM, COUNT, AVERAGE, etc. that apply to visible rows only. However, there is no similar function for visible columns only. If we need to SUM visible columns only, we will need … Continue reading
Put a dynamic hint for showing or hiding rows or columns hidden by Data Group
This is about using SUBTOTAL and CELL function to detect whether a row and a column is hidden respectively. When rows are hidden by Data–>Group, you will see a +/- button on the leftmost of spreadsheet to remind you that … Continue reading
HLOOKUP – The less-know sibling of VLOOKUP
Many people talk about VLOOKUP and agree that it is one of the most useful functions in their work life with Excel. However, if you ask them about HLOOKUP, maybe 30% of them have no idea of its existence. The Google search … Continue reading
wmfexcel 