How to change the range of a defined name in Excel 2010

In a previous post I explained how to define named ranges in Excel 2010.

OK, you have defined a name for a specified range. Later on you decided to change the size of that range either by expanding it or reducing it. Here is how you do it:

1. In the Formulas tab, Defined Names group click Name Manager. The Name Manager window will open. It will list all your defined names.

2. Select the name you want to change its range then click Edit. The Edit Name window will open.

3. Click in the Refers To edit box. Now you can enter the new range through the keyboard, or you can highlight your range using the mouse and EXCEL will update the range for you automatically.

4. Click OK.

 

How to define named ranges in Excel 2010

If you have a range of cells that hold a specific type of data e.g. Sales, No. of students etc. then you can give that range a meaningful name. Later if you want to refer to the range you can do that by specifying its name instead of using its cell reference.

In the example below we have two columns, Month (column A) and Sales Value (column B).

If we want to sum the total sales for the 12 months then we will use this formula:

=sum(B2:B13)

Alternatively we can give the range B2:B13 a name like this “SalesValue” and then use the sum formula as follows:

=sum(SalesValue)

The names will make it easier for you to refer to a range and to make your formulas more meaningful.

How do you define a named range?

1. Select the range that you want to name.

2. In the Formulas tab, Defined Names group click Define Name. The New Name window will open.

3. In the Name field enter the name you want to give to your range.

4. In the Scope combo box, if you want this name to be known to other sheets in the workbook, select Workbook. If the name will only be used in the current worksheet, then select the name of the worksheet.

5. In the Refers To field make sure that the required range is specified.

6. Click OK when you are done. Your defined name is now ready to be used in any formula.

 

 

 

 

How to show or hide formulas in Excel 2010

You can show or hide formulas in Excel 2010 in tow ways:

A. The quick (shortcut) way:

  • Press the control key and the ~ key at the same time (CTRL ~) to show formulas in the cells.
  • Press the same keys again to hide formulas in the cells.

B. Using Excel Options:

  1. Click File.
  2. Click Options. Excel Options window will open.
  3. Click on the Advanced tab.
  4. Go to the section titled “Display options for this worksheet”.
  5. Select the check box that says “Show formulas in cells instead of their calculated results”.

How to join text from multiple cells into one string

If you have multiple cells that contain text and you want to join or merge them in one cell then you can use the concatenate function.

Syntax:

CONCATENATE(text1, text2, text3 ……)

You can concatenate up to 255 items into one text string.

Example:

Cell A1 = “MBA”

Cell B1 = “Master of Business Administration

Cell C1 contains the formula: CONCATENATE(A1; B1)

The value of C1 will be “MBAMaster of Business Administration”

You can also use literals (fixed text) in the CONCATENATE function. E.g. if C1 has the formula:

CONCATENATE(A1; “ – “; B1

Then the value of C1 will be “MBA – Master of Business Administration”

You can also use the ampersand (&) calculation operator to achieve the same result as the CONCATENATE function. The last example will look like this:

=A1 & ” – ” & B1

 

How to calculate a running total in a column

Suppose you have a journal in which you register daily sales, and you want to know the accumulated sales each day.

We can do this in Excel as follows:

Column A:  Date entry.

Column B: Sales entry.

Column C: A formula to calculate the running total

  1. In C2 enter the same value as B2. This is the first row of sales.
  2. In C3 enter the formula : =C2+B3.
  3. Select cell C3 and drag the fill handle down until the end of your data.
  4. Now C4 will have the formula C3+B4, C5 will be C4+B5 and so on. The running total will be updated automatically.

How to sum non-adjacent cells

Suppose you have a rang of cells, say A1:A20, and you want to sum only cells A1, A4 , A6, A7and A8 which are non-adjacent. You can write a formula like this:

=A1+A4+A6+A7+A8

This is OK for a few cells but for large range, it is better to write the formula as follows:

=SUM(A1,A4,A6:A8)

Which means some A1, A4 and the range A6 to A8.

EXCEL logical functions : the NOT function

The NOT function, one of many EXCEL logical functions, is used to evaluate a single logical condition, passed as an argument to the function.  It will return the opposite of that argument. I.e.:

  1. If the evaluated logical condition is TRUE, the function will return FALSE.
  2. If the evaluated logical condition is FALSE the function will return TRUE.

Syntax

NOT(logical)

Logical is a condition that evaluates to TRUE or FALSE.

Examples:

A1 = 6

B1 = 10

=NOT(A1>5) : returns FALSE.

= NOT(A1>10): returns TRUE.

=NOT(A1=B1): returns TRUE.

You can nest the NOT function within another logical function as follows:

=IF(NOT(A1=B1) ;”Valid numbers”;”Invalid numbers”)

If you put this formula in C1 for example, and A1 and B1 have the values shown above, then C1 will have the value: “Valid numbers”, because the NOT function will evaluate to TRUE.

EXCEL logical functions : the OR function

The OR function , one of many EXCEL logical functions, is used to evaluate a number of logical conditions, passed as arguments to  the function.  The function will return a single value of TRUE or FALSE.

Syntax

OR(logical1, logical2 ………)

logical1 and logical2 are conditions that evaluate to TRUE or FALSE. The first argument is required the second one is optional.

You can have up to 30 arguments in EXCEL 2003 and 255 in EXCEL 2007.

Examples:

A1 = 6

B1 = 10

=OR(A1>5;B1>10) : returns TRUE.

= OR(A1>10;B1>10):returns FALSE.

=OR(A1>5;B1>5): returns TRUE.

You can nest the OR function within another logical function as follows:

=IF(OR(A1>5;B1>10) ;”Valid numbers”;”Invalid numbers”)

If you put this formula in C1 for example, and A1 and B1 have the values shown above, then C1 will have the value : “Valid numbers”, because the OR function will evaluate to TRUE.

EXCEL logical functions : the AND function

The AND function , one of many EXCEL logical functions, is used to evaluate a number of logical conditions, passed as arguments to  the function.  The function will return a single value of TRUE or FALSE.

Syntax

AND(logical1, logical2 ………)

logical1 and logical2 are conditions that evaluate to TRUE or FALSE. The first argument is required the second one is optional.

You can have up to 30 arguments in EXCEL 2003 and 255 in EXCEL 2007.

Examples:

Cell A1 = 6

Cell B1 = 10

Cell C1 :

=AND(A1>5;B1>10) : returns FALSE.

=AND(A1>5;B1=10): returns TRUE.

=AND(A1>5;B1>5): returns TRUE.

You can nest the AND function within another logical function as follows:

=IF(AND(A1>5;B1>10) ;”Valid Range”;”Invalid Range”)

If you put this formula in C1 for example, and A1 and B1 have the values shown above, then C1 will have the value : “Invalid Range”, because the AND function will evaluate to FALSE.

EXCEL logical functions : IF function

The IF function, one of many EXCEL logical functions, is used to evaluate a logical condition, passed as an argument to the function.  The function will then return one of two values specified as the second argument or the third argument in the function. It will return the second argument if the test evaluates to TRUE or the third argument if the test evaluates to FALSE.

Syntax

IF(logical test , value if TRUE, value if FALSE)

Logical test:  is a condition that evaluates to TRUE or FALSE.

Value if TRUE : is the value to be returned by the function if  logical test evaluates to TRUE.

Value if FALSE : is the value to be returned by the function if  logical test evaluates to FALSE.

Examples:

A1 = 6

B1 = 10

=IF(A1>5; “Valid value”; “Invalid Value”): returns “Valid value”

=IF(A1>B1;A1-B1;B1-A1) : returns 4. This function will always return a positive number.