How to add a line break to a cell in Excel

Today we are going to show you a little trick but that can be very useful. How to insert a line break in a cell.

There are 2 ways to insert it, depending on the purpose.

What are they? Let's see:

Another way to count unique values in a range in Excel

Not long ago I wrote an article about how to count the number of unique values in a range. That trick was basically using the SUMPRODUCT and COUNTIF functions. Today we are going to see another way to calculate it.

To understand what we are trying to do it is always best to use an example. We have a list of names in the range A1: A14, which we have called Name_list.

How many of these names are unique? The answer is obviously 11. But how we calculate it? We saw earlier that can be calculated using the SUMPRODUCT and COUNTIF functions, but today I want to show you another way to perform the same calculation, but this time using the FREQUENCY and MATCH functions.

And what formula is that?

15 functions in Excel for any user - Part 1

Today I will talk about Excel functions, one of the most important tools in Excel. We will create a list of 15 functions not very used but that can be very, very useful at some point. This entry is the 1st of 2 entries. Here it goes!

1. Remove the spaces from a text except single spaces between words

Sometimes the data are cluttered, dirty, with spaces where you do not want to have, right? As one of the most useful ways to help you clean your data is the TRIM function.

This function removes all spaces from a string except for single spaces between words. Let us examine an example.

=TRIM("The     wizard     of     Excel      ") = The wizard of Excel


The function has removed those spaces before, after and in between to leave the text clean and ready for analysis.

Selecting a range from many using the CHOOSE function

A while ago I wrote about the how to convert long NESTED IF formulas to a simple formula with the CHOOSE function. Today we are going to see what else we can achieve by using the CHOOSE function.

We can fetch a range from a selection of ranges

How do we do that?

Let's see by looking at one example:

Say that we have 4 ranges, {A1:A10}, {B1:B10}, {C1:C10} and {D1:D10}, and that depending on a condition or a selection by the user we want to use one of them, for example, we want to sum up the range.

=SUM(CHOOSE(2, A1:A10, B1:B10,C1:C10,D1:D10))

How to count the number of unique values in a range in Excel

Hello, on many occasions we have a list or range with lots of data, and we wondered how many of those values or data are unique, and we have had to develop a complex solution by adding helper columns and doing multiple operations.

That is no longer necessary !!

Because we can calculate the number of unique values in a list with a simple formula.
And what formula is that ?? Let's see ...

Conditional Formatting - Formula to search values

Today we are looking at another example of using formulas in conditional formatting.

It is recommended to first read and understand the entrance Conditional Formatting - formulas for selecting cells.

Imagine you have the list of European football teams, located in the range B5:B31.

What we want to achieve is to enter some text in a cell, for example D2, and if that text is in any value from the list then highlight the cell(s) in the way you want.

To do that the formula to be used is:

Conditional Formatting - Formula to highlight duplicates, errors and omissions

Today we are looking at another example of using formulas in conditional formatting.

It is recommended to first read and understand the entrance Conditional Formatting - formulas for selecting cells

1. Highlight duplicates in a list

For this example we will use a standard list of data like this. This list is in the range B7:B23.

So the formula to highlight duplicates is:

=COUNTIF($B$8:$B8,$B8)>1

Conditional Formatting - Formula to highlight any day(s) of the week

Today we are looking at another example of using formulas in conditional formatting.

It is recommended to first read and understand the entrance Conditional Formatting - formulas for selecting cells

For this example we will use a normal list of days. In my case the list is in the range A1: A15.

Conditional Formatting - Formula to highlight alternate rows or columns

Here's an example of using conditional formatting formulas.

It is recommended to first read and understand the post Conditional Formatting - formulas for selecting cells

For this example we will use a normal table like this.

As can be clearly seen the table is for Moe's Tavern, and it shows a record of the daily sales.

1. Highlighting alternate rows

Convert long NESTED IF formulas to a simple formula with the CHOOSE function

Anyone who has used Excel for a while will have found on more than one occasion long formulas that use the IF function inside another IF that in turn is inside another IF. This is normally called NESTED IF.

An easy example of NESTED IF would be:

= IF (B1 = 1, "Apple", IF (B1 = 2, "Orange", IF (B1 = 3 "Pera", "Grape")))


Obviously instead of the name of the fruits we could write a formula, or a cell.

= IF (B1 = 1, A1, IF (B1 = 2, A1-C1, IF (B1 = 3, A1 * D1, A1 ^ 2)))

MIN function

Category: Statistical functions

What does this function do?

Returns the minimum value in a list of values. Ignores logical values and text.

Syntax:

MIN(number1,number2,...)

• number1 (required): The first of the values or ranges to evaluate.
• number2 (optional): From the second number or range parameters are optional.

Examples:

=MIN(1, 2, 3) = 1
=MIN(A1:A2,B1:B2) = 1, if A1:A2= {1,2} and B1:B2= {2,3}

MAX function

Category: Statistical functions

What does this function do?

Returns the maximum value in a list of values. Ignores logical values and text.

Syntax:

MAX(number1,number2,...)

• number1 (required): The first of the values or ranges to evaluate.
• number2 (optional): From the second number or range parameters are optional.

Examples:

=MAX(1, 2, 3) = 3
=MAX(A1:A2,B1:B2) = 3, if A1:A2= {1,2} and B1:B2= {2,3}

Statistical Functions

The Statistical Functions are used in Excel to perform statistical operations. Take a look at some of the most common functions.

AVERAGE - Returns the average (arithmetic mean) of the arguments, which can be numbers, names, arrays, or references that contain numbers.

COUNT - Count the number of cells in a range that contain numbers.

COUNTA - Count the number of cells in a range that are not empty.

COUNTBLANK - Count the number of cells in a range that are empty.

COUNTIF - Count the cells in a range that meet a specified condition.

COUNTIFS- Count the cells in a range that meet one or more specified conditions.

FREQUENCY - Calculate the frequency of occurrence of a value within a range of values and returns a vertical array of numbers.

LARGE - Returns the largest k-th value of a data set.

MAX - Returns the maximum value in a list of values. Ignores logical values and text.

MEDIAN - Returns the median of a data set.

MIN - Returns the minimum value in a list of values. Ignores logical values and text.

MODE.SNGL - Returns the most frequent or repetitive value of an array or range of data.

RANK.EQ - Returns the rank of a number in a list of numbers: its size relative to other values in the list; if more than one value has the same hierarchy, the top hierarchy of the set of values is returned.

SMALL - Returns the smallest k-th value of a data set.

FREQUENCY function

Category: Statistical functions

What does this function do?

Count the cells in a range that meet one or more specified conditions.

Syntax:

FREQUENCY(data_array, bins_array)

• data_array (required):An array of or reference to a set of values for which you want to count frequencies. If data_array contains no values, FREQUENCY returns an array of zeros.

• bins_array (required): An array of or reference to intervals into which you want to group the values in data_array. If bins_array contains no values, FREQUENCY returns the number of elements in data_array.

Examples:

=FREQUENCY(A1:A6,B1:B3) = {0, 1, 2, 3}, if A1:A6 = {11; 20; 21; 30; 29; 29} and B1:B3 = {10; 17; 23}. This means that the number of values equal to 10 or less is 0, the number of values in the interval 11 to 17 is 1, the number of values in the interval 18 to 23 is 2 and the number of values greater than 23 are 3.

COUNTIFS function

Category: Statistical functions

What does this function do?

Count the cells in a range that meet one or more specified conditions.

Syntax:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

• criteria_range1 (required): The first range in which to evaluate the associated criteria.

• criteria1 (required): The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32".

• criteria_range2, criteria2, ...(optional): Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

Examples:

=COUNTIFS(A1:A3, “>1″) = 2 if A1:A3={1,2,3}
=COUNTIFS(B1:B5,1,A1:A5,”>1″) = 1 if B1:B5 ={1;0;0;1;1} and A1:A5 ={0;2;3;4;4}

COUNTA function

Category: Statistical functions

What does this function do?

Count the number of cells in a range that are not empty.

Syntax:

COUNTA(value1, [value2], ...)

• value1 (required): The first argument representing the values that you want to count.

• value2 (optional): Additional arguments representing the values that you want to count, up to a maximum of 255 arguments.

Examples:

=COUNTA(A1:A3,B1) = 3 if A1:A3 = {2,””,4} and B1=3

COUNTBLANK function

Category: Statistical functions

What does this function do?

Count the number of cells in a range that are empty.

Syntax:

COUNTBLANK(value1, [value2], ...)

• value1 (required): The first argument representing the values that you want to count.

• value2 (optional): Additional arguments representing the values that you want to count, up to a maximum of 255 arguments.

Examples:

=COUNTBLANK(A1:A3,B1) = 3 if A1:A3 = {2,””,4} and B1=1

COUNT function

Category: Statistical functions

What does this function do?

Count the number of cells in a range that contain numbers.

Syntax:

COUNT(value1, [value2], ...)

• value1 (required): The first item, cell reference, or range within which you want to count numbers.

• value2 (optional): Up to 255 additional items, cell references, or ranges within which you want to count numbers.

Examples:

=COUNT(A1:A3) = 2 if A1:A3 = {2, A, 4}
=COUNT({1,"E",3}) = 2

COUNTIF function

Category: Statistical functions

What does this function do?

Count the cells in a range that meet a specified condition.

Syntax:

COUNTIF(range, criteria)

• range (required): One or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

• criteria (required): A number, expression, cell reference, or text string that defines which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32".

Examples:

=COUNTIF(A1:A3,"> 1") = 2 if A1:A3 = {1,2,3}
=COUNTIF(B1:B3,"count") = 1 if B1:B3 = {"add", "count", "add"}

LARGE function

Category: Statistical functions

 

What does this function do?

Returns the largest k-th value of a data set.

Syntax:

LARGE (array,k)

• Array (required): the array or range of data for which you want to determine the k-th largest value.
• K (required): the position (from the largest) in the array or cell range of data to return.

Examples:

=LARGE({1,2,5,3,6}, 4)= 2

If A1:A5={1,2,5,3,6}

=LARGE({A1:A5, 4)= 2