[Affiliate]
Blog Stats
- 3,028,946 hits
-
Join 3,888 other subscribers
- Follow wmfexcel on WordPress.com
Search wmfexcel
Want Google to translate this page?
-
Recent Posts
Top Posts & Pages
- How to sort bar chart in descending order?
- Show number in thousand (k) or in million (M) by using custom format
- How to replace value with a value from another column in Power Query
- Date Formats - A trick to format date with "st", "nd", "rd", "th"
- Insert a text string into a specific position of another text string in #Excel
- Repeat all item labels in Pivot Table (aka Fill in the blanks please)
- Rename column names in a dynamic way with #Excel #PowerQuery
- Open an embedded Excel file during a slide show in PowerPoint?
- Send a cell content to Google search
- Perform VLOOKUP with 2 lookup values
My YouTube Channel
My Facebook page
- My Tweets
Category Archives: Formula
Distinct count using functions in #Excel 365 is so easy
Have your ever wanted to count how many distinct items in a range? Though a common task, there was not build-in functional for it. Perhaps we can make a distinct list using advanced filter first then we count the outcome. … Continue reading
Basic Date Calculations – Power Query vs Excel Formula
It is quite common to perform date-related calculations when we work with Excel. Before Power Query, we must do it with built-in functions. However, we may not get the results we need with a single function most of the time. … Continue reading
Posted in Formula, Power Query
Tagged CEILING, CHOOSE, Date, DAY, EOMONTH, LET, MONTH, Power Query, SWITCH, TEXT, TODAY, YEAR
Leave a comment
How to sum a range with #ERRORS in #Excel?
SUM is probably the most frequently used function in Excel. And probably the first function we learned. Though powerful, it cannot handle errors… 😰 This is the fact we need to deal with. SUM cannot handle errors. AGGREGATE comes to … Continue reading
The strange behavior of SUMIF and the quick fix
Have you ever got an incorrect result from SUMIF and wonder why? You can find the answer in this post. 🙂 Continue reading
Fixing inconsistent data type in lookup table in #Excel
A common VLOOKUP problem with an easy fix The situation – VLOOKUP fails… Have you ever encountered something like this? This is quite common. We are sure that the VLOOKUP formula is correct. We are sure that the lookup values … Continue reading
Convert numbers of different digits into text of fixed digits in #Excel
The situation We have an extensive list of numbers in different digits, say from 10 to 13 digits. The problem is, they are supposed to be numbers in thirteen digits stored as text. (Of course, in our example, we work … Continue reading
#Excel INDIRECT function explained with a use case
There are many #Excel functions that you may not find them useful when you first learn them. Sometimes you may even doubt why there are such functions. INDIRECT should be one of them. 🤔 What INDIRECT does? It returns the … Continue reading
Have you encountered any weird formula in #Excel?
Happy Halloween! 👻 Let’s do something fun. You are invited to leave your comments below. For what? For sharing with us any “weird” formula that you have ever seen in Excel. Real cases please. 😁 Wait… what do I mean … Continue reading
SUM the last X non-blank values in a column in #Excel
Got this question from my brother. He is tracking scores for game he plays with friends. The game can be played with up to four players. He wants a formula to calculate the last 10 scores of a player. He … Continue reading
Determine leap year with #Excel
Today is a special day. Today is February 29th, which happens only once every four years. And today is Saturday, when I usually post a new blog. So let’s talk about how to determine if a year is leap year using … Continue reading
Be cautious when using XLOOKUP
Can you spot the error in the above formula? It’s not about the new function of XLOOKUP. It’s about inevitable human error… If you are an Excel fan, you should be aware of the exciting XLOOKUP function in Office 365. … Continue reading
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
wmfexcel 