Category Archives: Formula

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 , , , , | Leave a comment

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

Rate this:

Posted in Excel Tips, Formula | Tagged , | Leave a comment

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

Rate this:

Posted in Formula | Tagged | 2 Comments

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

Rate this:

Posted in Excel Tips, Formula | Tagged , , | 8 Comments

Insert a text string into a specific position of another text string in #Excel

Turn REPLACE function into “INSERT” function Continue reading

Rate this:

Posted in Formula | Tagged , | Leave a comment

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

Rate this:

Posted in Formula | Tagged , , , , , , | 8 Comments

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

Rate this:

Posted in Formula | Tagged , , , , | 3 Comments

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

Rate this:

Posted in Formula | Tagged , , | Leave a comment

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

Rate this:

Posted in Formula | Tagged , | 15 Comments

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

Rate this:

Posted in Formula | Tagged , | 4 Comments

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

Rate this:

Posted in Formula | Tagged | 2 Comments

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

Rate this:

Posted in Formula | Tagged | 3 Comments

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

Rate this:

Posted in Excel Tips, Formula | Tagged , , , , | 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

Rate this:

Posted in Excel Tips, Formula | 2 Comments

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

Rate this:

Posted in Formula | Tagged , , , , , | Leave a comment

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

Rate this:

Posted in Formula | Tagged , , , , , , , , , , | 6 Comments

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

Rate this:

Posted in Formula | Tagged , , | 6 Comments

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

Rate this:

Posted in Formula | Tagged , | 2 Comments

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

Rate this:

Posted in Formula | Tagged , | Leave a comment

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

Rate this:

Posted in Formula | Tagged , , | 10 Comments