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

Rate this:

Posted in Formula, Power Query | Tagged , , , , , , , , , , , | 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

Rate this:

Posted in Formula | Tagged , , , , | 2 Comments

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

Rate this:

Posted in Formula | Tagged | 1 Comment

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

Rate this:

Posted in Excel in Action, Formula | Tagged , , , | 2 Comments

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

Rate this:

Posted in Formula | Tagged , , , , , , , | 2 Comments

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

Rate this:

Posted in Formula | Tagged , , , | 10 Comments

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

Rate this:

Posted in Formula | Tagged | 10 Comments

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

Rate this:

Posted in Formula | | 2 Comments

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

Rate this:

Posted in Formula | Tagged , , , , | 7 Comments

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

Rate this:

Posted in Formula | Tagged , , , , | 2 Comments

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