Like To Customize Your Sortings?

While working with large data there are some functions that can be effectively used to sort based on specific features within cells. LEN is such a function used to find out the number of characters within a cell.

Below given is the Syntax for LEN Function:

=LEN(Text)

Here Text is the cell reference whose length has to find out. Please go through below examples to know the various possible results.

When a cell reference M24 has value OK

=LEN(M24)

LEN function returns 2 (counted characters O and K)

When a cell reference C2 has value OK!

=LEN(C2)

LEN function returns 3 (counted text including special characters)

When a cell reference P18 has value TYPE A

=LEN(P18)

LEN function returns 6 (counted text including blank spaces)

When a cell reference Y11 has value 120, which is a result of arithmetic operations.

=LEN(Y11)

LEN function returns 3 (counting numbers 1, 2 and 0)

Try any sample from above, you will find it simple.

Want To Perform COUNTIF Function?

COUNTIF is the function, which is used to count number of cells in a list that matches a particular condition.

The Syntax should be the following:

=COUNTIF(list,”condition”)

Here list is the range of cells selected and condition can be in the form of arithmetic, text or logical expression.

Find below some of the examples:

In the above picture, the range of cells between Row 2 and Row 6 are counted by different expressions.

Arithmetic expressions:

=COUNTIF(B2:B6,”>500”)

Here condition is to count cells that are greater than 500. The range of cells is between B2 and B6. There are two cells (B2 is 780 and B4 is 650) that meet the condition, so COUNTIF function returns 2

Text expression:

=COUNTIF(E2:E6, “Lara”)

Here condition is to count cells that have name Lara. The range of cells is between E2 and E6. Three cells B2, B3 and B5 matches the given condition, so COUNTIF function returns 3

Logical expression:

=COUNTIF(H2:H6, “false”)

Here condition is to count cells that have logical value FALSE. The range of cells is between H2 and H6. Two cells have the matching logical value, so COUNTIF function returns 2

Look at the above example and give a try. It is simple!

Do You Want To Print Same Title On Every Page?

Whenever we need to take print out for a long report we observe that Title in the top or left of the report appears only in the first page. We copy them and insert properly to make them appear in all pages. This normal method takes lot of time and leads rework while adjusting margins.

There is an easy way to overcome this problem. Select ‘page setup’ from File menu to open page setup dialogue box. You can find four tabs Page, Margins, Header/Footer and Sheet. Click on ‘Sheet’ tab.

There are two options in the print titles area, which are meant for rows and columns. If your Title is arranged in rows then locate it by using ‘Rows to repeat at top’ option. Excel hides the dialogue box temporarily and prompts your to select the rows. Even if you select one cell, the whole row will be considered for selection. You can even select more than one row to repeat at top every page.

Once you finished selecting rows press enter to return into dialogue box. The range of your selection will be displayed in the range locator. If you want to edit it either you can do directly or select again the title. Ensure that title is being repeated in all sheets before you take print out.

In case if your title is arranged in vertical order then use ‘Columns to repeat at left’ option to locate it.

Tips: If you need to print row and column header of excel sheet, use ‘Row and Column Headings’ option in the same print tab.

I hope that you will find this method very easy to apply in your day to day office work.