How to link to values in another Excel 2010 worksheet or workbook

In Excel 2010 or 2007, if you want data from one worksheet to be linked to, or shown in another worksheet then follow these steps:

1. In the sheet you want to link to (source) select the cell where the value is stored.

2. Right click and select Copy.

XL10Link1

3. In your main sheet (target), select the cell where the value is to be displayed and right click on it.

4. In the displayed context menu, under Paste Options, click on Paste Link (the last icon to the right).

XL10Link2

5. The value in the source sheet will now be reflected in the target sheet. Notice the link to sheet1 in the formula bar.

XL10Link3

6. Every time the source sheet is updated, the target sheet will also be updated with the new values.

7. The same procedure can be applied to link to data in another workbook.

 

How to convert numbers between decimal and hexadecimal

To convert numbers between decimal and hexadecimal you use the equivalent Excel built-in function.

To convert from decimal to hexadecimal use the function DEC2HEX.

Syntax

DEC2HEX(number;places)

Number: The decimal number you want to convert into hexadecimal.

Places: Optional, the number of characters or positions to use. You can use this option to pad the return value with leading zeros. If number is negative, then the places parameter is ignored.

Examples:

1. A1 = 35; B1 = DEC2HEX(A1;4).

The returned vale in B1 will be 0023. Notice the leading zeros.

2. A1 = 1234; B1 = DEC2HEX(A1).

The returned vale in B1 will be 4D2.

To convert from hexadecimal to decimal use the function HEX2DEC.

Syntax

HEX2DEC (number)

Number: The hexadecimal number you want to convert into decimal. It cannot contain more than 10 characters.

 

Examples:

1. A1 = 100; B1 = HEX2DEC(A1).

The returned value in B1 will be 256.

2. A1 = FF25; B1 = HEX2DEC(A1).

The returned value in B1 will be 65317.

3. A1 = FFFFFFF6CC; B1 = HEX2DEC(A1).

The returned value in B1 will be -2356 (negative number).

 

Use INDEX and MATCH functions for table lookup

 

The table above lists some internet Top Level Domains (TLD) and their equivalent country codes.

How can we look up the country name from the table if we know its TLD? E.g. we have the code “cn” and we want to know the corresponding country name.

We can achieve this by using the INDEX and MATCH functions to search for a specific TLD code and pick up the corresponding country name from the adjacent column,  as follows.

The INDEX function, syntax:   INDEX(array;row_no, column_no). Find details here:

http://www.exceldigest.com/myblog/2008/07/16/how-to-use-the-index-function/

It takes three arguments:  array or table, row no. and column no. and returns the value at the intersection of the row and column.

In this example if we put: =INDEX(A2:B9;4;2). I.e. Table A2:B9, row 4 (of the table, not the row of the worksheet) and column 2, then we will get the country “China”. However we don’t have row no. but we have the TLD code. This is where we need the MATCH function.

The MATCH function, syntax:  MATCH(lookup_value;array;match_type). Find details here:

http://www.exceldigest.com/myblog/2008/07/03/how-to-use-the-match-function/

It takes three arguments: lookup value, lookup array and match type and returns the position where the value is found (row no. within the table). So we can use the MATCH function to get the row no. and then feed it into the INDEX function:

=MATCH(“cn”;A2:A9;0). This function will return a row no. of 4.

We can join the two functions together as follows:

=INDEX(A2:B9;MATCH(“cn”;A2:A9;0);2). This will return the country “China” as we have seen before.

Note: you can replace “cn” with the address of the cell which has the TLD code.

How to get the day of the week as text in Excel 2010

Excel WEEKDAY function will return the day of the week as a number. See details here:

How-to-get-the-day-of-the-week-in-excel-2010

To return the day of the week as text (name of the day), we combine the WEEKDAY function with the TEXT function as follows:

TEXT(WEEKDAY(A1;2);”ddd”) will give the abbreviated name of the day : Sun , Mon, Tue … etc.

TEXT(WEEKDAY(A1;2);”dddd”) will give the full name of the day : Sunday , Monday … etc.

 

Examples:

 

Cell A1 have the date: 25/11/2012, which is Sunday.

 

1. Cell C1 have the formula : =TEXT(WEEKDAY(A1;1);”ddd”). The value in C1 will be “Sun”

2. Cell C1 have the formula : =TEXT(WEEKDAY(A1;17);”ddd”). The value in C1 will be “Sun”

3. Cell C1 have the formula : =TEXT(WEEKDAY(A1;2);”ddd”). The value in C1 will be “Sat”

Why the returned day of the week is “Sat” instead of “Sun” in example No. 3 above? This is because the TEXT function will always assume 1 as Sunday, 2 as Monday and so on. This is only compatible with WEEKDAY return type of 1 and 17. So you have to be careful if you are using a return type other than these two. You have to use some arithmetic to get the correct name of the day in this case.

 

How to get the day of the week in Excel 2010

To get the day of the week as a number you use Excel WEEKDAY function.

Syntax

WEEKDAY(serial_number,[return_type])

Serial number: a serial number that represent the date.

Return type (optional): a number that determines how the weekday is calculated. In other words, it specifies what is the first day of the week and whether numbering will start from 0 or will start from 1.

Here is a list of the valid return types:

1 or omitted       Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Excel.

2             Numbers 1 (Monday) through 7 (Sunday).

3             Numbers 0 (Monday) through 6 (Sunday).

11           Numbers 1 (Monday) through 7 (Sunday).

12           Numbers 1 (Tuesday) through 7 (Monday).

13           Numbers 1 (Wednesday) through 7 (Tuesday).

14           Numbers 1 (Thursday) through 7 (Wednesday).

15           Numbers 1 (Friday) through 7 (Thursday).

16           Numbers 1 (Saturday) through 7 (Friday).

17           Numbers 1 (Sunday) through 7 (Saturday).

 

Examples:

 

Cell A1 have the date: 25/11/2012, which is Sunday.

 

  1. Cell B1 have the formula: =WEEKDAY(A1;1).  The value in B1 will be 1.
  2. Cell B1 have the formula: =WEEKDAY(A1;2).  The value in B1 will be 7.
  3. Cell B1 have the formula: =WEEKDAY(A1;16).  The value in B1 will be 2.
  4. Cell B1 have the formula: =WEEKDAY(A1;17).  The value in B1 will be 1.

 

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 calculate age in Excel 2010

To calculate a person (or anything else) age you basically subtract his date of birth from today’s date. However which date functions to use depends on the amount of precision and the output format you want.

Suppose that we want to calculate the age of someone who is born on 15/06/1973 and we have his birth date in cell A1.

1. If we want his age in whole number years only, e.g. 40 years, then we can use this formula:

=YEAR(TODAY())-YEAR(A1)

2. If we want his age in whole numbers and a fraction of a year, e.g. 40.5 years, then we can use this formula:

=(TODAY()-A1)/365.25

3. If we want his age in years and months, then we can use a formula like this one:

=INT((TODAY()-A1)/365.25) & ” years and ” & INT(MOD((TODAY()-A1)/365.25;1)*12) & ” months”

The output of this formula will be as follows:

40 years and 2 months.

4. If we want to extend the previous formula to include days, then we can use a complex formula like this one:

=INT((TODAY()-A1)/365.25) & ” years , ” & INT(MOD((TODAY()-A1)/365.25;1)*12) & ” months and ” & INT(MOD((TODAY()-A1)/30.4375;1)*30.4375) & ” days”

The output of this formula will be as follows:

40 years, 2 months and 18 days.

This formula could yield a one day difference due to approxmation.

What if you want to calculate the age in a target date other than today’s date?

Well, simply put the target date in another cell and replace the function TODAY() with the reference of the target cell. In the previous example if we want to calculate the age in 31 July 1995, then we can put 31/7/1995 in cell B1 and modify the first formula to be like this:

=YEAR(B1)-YEAR(A1)

Note:

Excel does not recognize dates before year 1900 (windows) or 1904(Macintosh). So formulas given above are also subject to Excel limitations.

 

 

 

New dynamic yearly calendar

I have added to the downloads page a new dynamic yearly calendar template.  You access the calendar through a user form which has two combo boxes: the first one to specify the year and the second  to select the start date of the week. You can generate a calendar by selecting any year between 2010 and 2030. You can also enter the year through the keyboard if it is not in the list. Each selection will trigger a macro that will generate the calendar. Here are a few notes to consider:

  1. The calendar is compatible with Excel 2007 and 2010.
  2. You may receive a warning message that macros are disabled. You have to enable macros for this template to work properly.
  3. The “Calendar”  sheet is protected to avoid accidental change of data and formulas. If you want to make some changes, like formatting for example, then you can unprotect the sheet. Make sure however that you don’t change any formulas.
  4. You can print the calendar just like any other Excel worksheet. To avoid having the sheet split in two pages for any reason , make sure that in page layout settings you have the Scaling set to “Fit sheet on one page”.

Your feedback is appreciated.

 

How to protect VBA code in Excel 2010

If you have an EXCEL workbook that includes some VBA code, and you want to hide that code, either to protect your intellectual property or simply to prevent others from messing up your code accidently , then follow these steps:

1. In the Developer tab, Code group, click Visual Basic. This will switch you to VBA development environment.

2. Create your project with the required code.

3. In the VBA development environment, click on the Tools menu and select VBAProject Properties.

The Project Properties window will open.

4. Select the Protection tab.

5. Select the checkbox Lock project for viewing.

6. Enter  Password and Confirm password.

7. Click OK.

8. Save the workbook and then close it so that the password protection takes effect. Next time you open the workbook and try to view the code you will be prompted for the password.