[Affiliate]
Blog Stats
- 3,266,387 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"
- Rename column names in a dynamic way with #Excel #PowerQuery
- Show number in thousand (k) or in million (M) by using custom format
- Perform VLOOKUP with 2 lookup values
- Highlight weekends and holidays using Conditional Formatting in #Excel
- #Excel Add current selection to filter. What it does?
- Repeat all item labels in Pivot Table (aka Fill in the blanks please)
- How to put the Date Modified info on #Excel worksheet using #PowerQuery
- Insert blank columns to table in #PowerBI Desktop
- How to sort bar chart in descending order?
My YouTube Channel
My Facebook page
- My Tweets
Category Archives: Formula
Send a cell content to Google search
This is a continuation of previous post, in which I talked about the potential use of Smart Lookup for an Excel dashboard. However there is limitation. It drove me to think of an alternative. Instead of asking user to trigger Smart … Continue reading
Apply Conditional Format for alternate row color based on groups…
…that works even when filter is applied. The situation: We have a table that we would like to apply color banding based on groups. We can achieve this by inserting a helper column to identify the sequence of each group, … Continue reading
Extract FirstLast names in CamelCase with #Excel formula
In the previous blog post, we see how Flash Fill extracts First Name and Last Name from an email address in a format shown above. I’ve also recorded a video for that post. Flash Fill is so smart to detect … Continue reading
Highlight weekends and holidays using Conditional Formatting in #Excel Part 2
Conditional Formatting with formula could be tricky (or difficult), especially when the data layout is bad because you have to very clear and careful on the “applied to” range and the absolute/relative references set in the formula. Otherwise, it won’t work and can be quite confusing, if not frustrating. 😛
In this post, I will talk about a case that you will see how empty rows in a data set would complicate the process in setting up conditional formatting. And then I will show three different approaches to tackle the issues. Hope you find it helpful. Continue reading
Learn something new from something that I think I knew well…
Today is a public holiday in Hong Kong. A perfect break in the middle of a busy week. What did I do on a public holiday? Excel, Excel and Excel. 🙂 I watched a few videos from my favourite YouTube … Continue reading
Change the first letter to upper case, first word only please #Excel
This is a short story of mine, and an imaginary conversation in my head… How to change the first letter (of first word only) to upper case in Excel? This was a question from a colleague sitting opposite to me. … Continue reading
How to remove leading space in #Excel
When TRIM and CLEAN do not work… Is it something bothering you? To fix this problem, we need to understand where is the “leading space” coming come. Before we jump to that, I want to show you two Excel functions … Continue reading
Has Space or NoSpace in worksheet name?
Have you received workbook from others that carries underscore _ as if a space in their worksheet names? Did you wonder why people use underscore when we can actually use space in worksheet name? Well… did you know… long long time ago, #Excel … Continue reading
The missing link of HYPERLINK function in #Excel help
I seldom use the HYPERLINK function in #Excel. Normally I insert hyperlink by CTRL+K, then setting the reference I want the link to go to. That is super easy (or quick and dirty)! Note: You may go to Insert tab on ribbon … Continue reading
How to lookup an image in #Excel
Lookup an image using “Linked Picture” in #Excel Perhaps you have used VLOOKUP to return a value from a table very often. However you cannot use VLOOKUP to return an image… So how the above can be done? You will … Continue reading
Insert a text string into a specific position of another text string in #Excel
Turn REPLACE function into “INSERT” function Continue reading
How to turn “1st January, 2017” into #Excel recognizable date?
Quite a long time ago, I wrote a post to discuss a trick to format date with “st”, “nd”, “rd”, etc… In this post, I am going to discuss the reversed way: Turning an English written date with “Dst MMMM, … Continue reading
Lookup a specific combination of characters exclusively, e.g. 5A not 5AA
Got the following question: hi, how do i search for a specific combination of characters exclusively i.e. if one cell contains 5A;3B&4C while another cell contains 5AA;3B&4C, and my search criteria is 5A, i want the results to return only … Continue reading
Using MIN/MAX to answer whichever lower/higher questions
What is “Whichever higher”? Situation: You call to your favourite restaurant to reserve a private room for family dinner on Saturday. The restaurant manager tells you that there will be a minimum charge of $2000 for a private room. That mean, … Continue reading
The amazing SUMPRODUCT
Advanced use of SUMPRODUCT for multi-conditional SUM In the previous posts, we talked about the basic of SUMPRODUCT, the behaviors of logical values (i.e. TRUE/FALSE) when they are put into basic mathematics operations. These set the foundation to understand the … Continue reading
The logic gate
Have you heard about Logic Gate? I learned logic gate in high school physics. Decades ago… so don’t expect I can remember what it is. 😛 Having said that, I do remember the fundamental concept about AND gate and OR gate, … Continue reading
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
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
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
Posted in Excel Tips, Formula
Tagged Array formula, Copy and Paste, Find and Replace, Paste Special, TRANSPOSE
2 Comments
Be cautious when using SUMIF(s)
SUMIF is a handy but helpful function. The syntax is simple: =SUMIF(range,criteria,[sum range] What it does is quite straight forward indeed. In the example above, it instructs Excel to look into the range (A2:A7), look for the matching criteria, which … Continue reading
Posted in Excel Tips, Formula
6 Comments
wmfexcel 