Excel Examples

Filter Data by Column and Sort by Row

This post will guide you how to use the FILTER function to filter a value by column and then sort data by row in Microsoft Excel. You can use the following formula based on the SORT function in combination with the FILTER function to filter data by column and the sort data by row in… read more »

Filter And Transpose Data From Horizontal To Vertical

This post will show you how to use Filter function and in combination with Transpose function to filter data from horizontal and transpose data as vertical in Microsoft Excel. You can refer to the below general formula based on TRANSPOSE and FILTER function: =TRANSPOSE(FILTER(range,logic)) You can use the FILTER with TRANSPOSE function to filter data… read more »

VLOOKUP Formula | Faster Trick with 2 VLOOKUPS

This post will guide you how to use 2 VLOOKUPS function to looking up data entries from a given range of cells in Microsoft Excel. VLOOKUP with 2 lookups can be faster than a single VLOOKUP in certain scenarios. The lookup times will, of course, depend on the data and the indexes. The generic formula… read more »

Extract Unique Items From A List

This post will guide you how to extract unique itmes from a given list in Microsoft Excel. How to create a newly formula to get unique values from a range cells in Excel. The unique list of items is the first one found, which does not have a value equal to any other in the… read more »

Extract substring In Excel

This post will guide you how to use Excel’s MID function is a quick and easy way to extract pieces from your text. Use the Excel formula to extract a substring with MID. Note: If you want to extract just a single character at any given time, use the “start” and end positions as your… read more »

How to Use 3D SUM Multiple Worksheets

To sum a range of numbers is straightforward for most Excel users, but do you know how to establish a 3D reference to total the same range of numerous sheets. In this post, I will present the steps for this work. Let’s explore formulae to construct a 3D sum multiple worksheets in Excel. 3D SUM… read more »

Extract all the matches with helper Column

With Excel’s powerful functions IF, INDEX, and MATCH, we can find exactly what you’re looking for with a few clicks of the mouse. This step-by-step tutorial will show how easy it is to extract data using these tools and more! Generic Formula =IF($F5<=$F$2,INDEX($A$2:$D$12,MATCH($F5,$D$2:$D$12,0),1),””) Summary Excel’s INDEX and MATCH functions can be used to extract multiple… read more »

If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of them is true, true value is returned and if none of them is true, false… read more »

If Value is Greater Than A Certain Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, you can use IF function to build a formula with logical test argument is “value>certain… read more »

If Cell is Not Blank

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some comments for not blank cells, you can build a formula with IF function, IF function… read more »

VBA Macro For VLOOKUP From Another Sheet

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data from another worksheet in Excel 2013/2016/2019. VLOOKUP Function in VBA Macro You should know that… read more »

If Cell is Blank

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some comments for blank cells, you can build a formula with IF function, IF function can… read more »

If Cell Equals Certain Text String

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you can build a formula with the help of IF function to return either true result… read more »

If Cell Contains Either Text1 or Text2

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true of false based on test result, you can build a formula based on IF function… read more »

If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like “abc”, you can build formula with IF function to return result. FORMULA To test if… read more »

VLOOKUP From Another Sheet Not Working

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working in Excel 2003/2010/2013/2016 or Excel 365. You should be want to know some of the… read more »

If Cell Begins with One of Three Supplied Characters

If you want to test values to see if they begin with some given specific characters like “x”, ”y”, or “z”, you can create a formula with COUNTIF and SUM functions to return results. EXAMPLE You can see “TRUE” or “FALSE” is returned properly based on the first letter in each cell. In this example,… read more »

Fix #N/A Error For VLOOKUP From Another Sheet

This post will show you how to fix the #N/A error why it occurs when you extract values from another sheet using VLOOKUP function in Excel 2016,2013,2010 or other Excel versions. How can you correct a #N/A error in VLOOKUP function in Excel. This post will also show you several reasons why VLOOKUP formula is… read more »

How To Remove Special Characters in Excel

This post will show you how to remove special characters from text strings in Excel. And I am going to introduce two ways to remove special characters (@&)#%$) from a string in Excel. Removing Special Characters using Power Query If you are using Excel 2010 or Excel 2013, and you need to install power query… read more »

How To Insert Comments in Protected Worksheet in Excel

This post will show you how to allow comments in a protected worksheet in Excel. You can easily to insert comments into cells in a normal worksheet in Excel, but if want to insert a comment in a worksheet that is protected by setting password, how can I do it in Excel 2013 or Excel… read more »

Sidebar