Guide To Flip Data In Excel Using SORT And Helper Column

The data in Excel may need to be flipped sometimes i.e. to reverse the order of the data in a vertical dataset or a horizontal data set. Excel does not have an inbuilt feature to do this. Although there are multiple ways to do this using simple sorting tricks, formulas, or VBA. Here is the… Read More »

Count Number of Words in a Cell

Number of Words This example describes how to count the number of words in a cell. 1a. The TRIM function returns a string with leading spaces, extra spaces and trailing spaces removed. 1b. To get the length of the string with normal spaces, we combine the LEN and TRIM function.   2a. The SUBSTITUTE function replaces existing text… Read More »

Count number of instances in a Text

Number of Instances This example describes how to count the number of instances of text (or a number) in a cell. 1. Use the LEN function to get the length of the string (25 characters, including spaces). 2. The SUBSTITUTE function replaces existing text with new text in a string. LEN(SUBSTITUTE(A1,B1,””)) equals 13 (the length of… Read More »

How to Separate Strings

Separate Strings This example teaches you how to separate strings in Excel. The problem we are dealing with is that we need to tell Excel where we want to separate the string. In case of Smith, Mike the comma is at position 6 while in case of Williams, Janet the comma is at position 9. 1. To get… Read More »

Text Functions in Excel

Text Functions in Excel Join Strings | Left | Right | Mid | Len | Find | Substitute Excel has many functions to offer when it comes to manipulating text strings. Join Strings To join strings, use the & operator. Note: to insert a space, use ” ” Left To extract the leftmost characters from a string,… Read More »

Formula Errors

Formula Errors ##### error | #NAME? error | #VALUE! error | #DIV/0! error | #REF! error | # IfError | # IsError | Aggregate | Circular reference | Floating Point Errors | This chapter teaches you how to deal with some common formula errors in Excel. ##### error When your cell contains this error code, the column isn’t wide… Read More »

Lookup & Reference (Index & Match) Functions

Lookup & Reference functions help you to work with arrays of data, and are particularly useful when you need to cross reference between different data sets. They perform tasks such as providing information about a range, returning the location of a given address or value, or looking up specific Lookup & Reference (Index & Match)… Read More »

IF combined with AND Function

In Excel, you can use IF with AND function to return a value based on two different numbers. Used to evaluate conditions with a test, then take one action if the result is true, else return the result based on the condition. Ah! why not Nested IF? Anyone who’s spent time in Data Analysis especially… Read More »

ABS Function in Excel, Return Absolute Value of a number

ABS Function in Excel will give absolute value of the number or formula. An Absolute number is a number without its sign. Eg:- Minus Five (-5) Minus is a sign, then the absolute number would be only 5 without minus sign. ABS Function in Excel Syntax of ABS function in Excel ABS(number) ABS function Example:… Read More »

How to Split a Single Cell diagonally in Excel

How can you Split a Single cell in Excel to give you data a perfect looks in your sheets This is required when you wanted to create table and wants to show data table with two different cells data into a cell which splits diagonally. For E.g:-   You have a data with table as… Read More »