You are here: Home / Archives for June 2008

How to nest and join multiple functions in a cell

June 28, 2008 By admin

Some Excel users, especially new comers, may think that you can only use one Excel function at a time in a cell. But the fact is that you can join as many functions as necessary in a cell. You can even nest multiple functions within each other to achieve a certain formula. Let us take some examples.

Example 1.

If you have a date in cell “A1″, you can use the Weekday function to return the day of the week like this:

=WEEKDAY(A1),

But this will only return a number. If you want the name of the day then you can nest the Weekday function within the Text() function to get the name of the day as follows:

=TEXT(WEEKDAY(A1);”ddddd”)

Example 2.

Suppose you have a list of email addresses like this, and you want to extract only website addresses from them:

User1@hotmail.com
User256@yahoo.com
User3@exceldigest.com

Two options that may come to mind is the Right() function and the Mid() function, but the Right() function will require the length of the string from the “@” sign to the end, and the Mid() function will require both the length of the string and its start position and both are not available. So here is one possible solution:

  1. Use the find() function to get the position of the “@” sign :

    2. =FIND(“@”;A1)

  2. Use the Len() function to get the length of the whole string :

    3. =LEN(A1)

  3. Nest these two functions within the Mid() function to get the final formula:

=MID(A1;(FIND(“@”;A1))+1;(LEN(A1)))

Please note that this may not be the optimum solution but it is used here to demonstrate our subject. For example instead of using the Len() function you could only use 255 (maximum string length).

Applies to: Excel 2003

Filed Under: Formulas, Functions Tagged With: Formulas, Functions

A primer on chart terminology

June 25, 2008 By admin

If a picture worth a thousand words as the saying goes then a chart could be a good proof of this.

Excel has a chart wizard that provides the user with a variety of chart types and many options to customize those charts. So in this post I will not get into the details of how to create a chart but will try to clarify some of the jargons related to charts, and I will take the Column Chart as an example.

Before plotting a chart you have to arrange your data in a way suitable for the chart type that you are going to use.

Chart data consist of one or more series (a group of data). The series could be in a row or in a column. In case of the column chart, each series appear in the chart as a column, and also appear as a legend or caption to identify the relevant group of data.

The chart itself has two axis; The X-axis (horizontal line) which is called the Category and the Y-axis (vertical line) which is called the Value. Along the X-axis you will find the category name and category labels. Along the Y-axis you will find the value name. In our example if you put headings for the columns then they will be reflected in the legends of the chart.

There are many other chart options that can be customized like gridlines, data labels and data table which can be accessed by right-clicking on the chart.

Chart Jargon

Applies to: Excel 2003

Filed Under: Charts Tagged With: Charts

How to create and use a workspace file

June 22, 2008 By admin

If you have a number of workbooks which you usually open together at the same time, then you can save those in a workspace file. This will allow you to later open all workbooks by opening the workspace file. You will also be able to resume work with the same window sizes, print areas, screen magnification, and display settings.

Follow these steps to create a workspace file:

  1. Open all the workbooks that you want to save in a workspace.
  2. Size and arrange the workbook windows as you want them to appear when you open the workspace.
  3. On the File menu, click Save Workspace. The “Save Workspace” window will open.
  4. Enter the name you want for the workspace then click Save.
  5. A workspace file will be created with the extension of xlw.
  6. To open the workspace click File, Open and select the workspace file.

Notes:

  1. The work space file does not contain the workbooks themselves but only references and display information about the workbooks. If you want to make a backup copy for example then you have to copy the workbooks individually and not the workspace file.
  2. To close all the workbooks, press shift then click File, Close All.

Applies to: Excel 2003

Filed Under: General Tagged With: Files, workspace

How to use the HLOOKUP Function

June 19, 2008 By admin

The HLOOKUP function (short for Horizontal Lookup), searches for a value in the first row of a table array and returns the corresponding value in the same column from another row in the table array.

Syntax

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

  • Lookup_value:The value to search for in the first row of the table array.
  • Table_array:Two or more rows of data. The values in the first row of table_array are the values searched for the lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase texts are equivalent.
  • Row_index_num:The row number in table_array from which the corresponding value must be returned. A row_index_num of 2 returns the value in the second column in table_array; a row_index_num of 3 returns the value in the third column in table_array, and so on.
  • Range_lookup:A logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If set to “FALSE”, a corresponding value will be returned only if an exact match is found. If set to “TRUE”, the nearest match will be considered if an exact one is not found.

Note:

The values in the first row of table array must be placed in ascending sort order; otherwise, HLOOKUP may not give the correct value.

Let us take a simple example to clarify the HLOOKUP usage:

In the image below, we have the worksheet: Budget with two rows of input data. Row 1 holds the quarters and row 2 the assigned budget. In the second worksheet: Spending we have the quarters listed vertically. We want to pick the budget for each quarter from the worksheet: Budget and put it in column B. In cell B2 we will enter the HLOOKUP function as follows:

=HLOOKUP(A2;Budget!$B$1:$E$2;2;FALSE)

A2 lookup value, quarter
Budget!$B$1:$E$2 Table array, found in worksheet: Budget.
2 Row_index_num, row 2 in worksheet named Budget
FALSE We want to find an exact match

Cells B3 to B5 can be updated with the HLOOKUP function by dragging the fill handle across the cells.

hlookup1

Budget

hlookup2

Spending

Applies to: Excel 2003

Filed Under: Data, Functions Tagged With: Excel Functions, HLOOKUP

How to validate a cell based on the value of another cell

June 16, 2008 By admin

In a previous post I explained how to validate a cell using a fixed range of data. In this post we will see how to validate a cell based on the value of another cell.

Suppose we have a list of documents each with issue date and expiry date. We want to validate the expiry date so that it is always greater than the issue date.

Validation

 

To validate the expiry date in cell C2 follow these steps:

  1. Select the cell.
  2. On the Data menu select Validation.
  3. Select the Settings tab.
  4. In the Allow list box select Date.
  5. In the Data list box select Greater Than.
  6. In the Start Date: box type “=B2″. This refers to cell “B2″ which have the corresponding issue date.

Note:

You can put an error message in the Error Alert tab.

Applies to: Excel 2003

 

Filed Under: Data, Validation Tagged With: Validation

How to convert columns to rows, or rows to columns

June 14, 2008 By admin

If you have a column of text like this:

Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

And you want to put this data in a row like this:

Monday Tuesday Wednesday Thursday Friday Saturday Sunday

Instead of moving the data manually, you can use an option of the Paste Special command as follows:

  1. Select the data in the column.
  2. Right click and then select copy.
  3. Select the first cell in the destination row.
  4. Right click and then select paste special.
  5. The paste special dialog box will open.
  6. In the lower right corner select the check box labeled “transpose” and click OK.
  7. Your data will be transposed into a row.

Notes

  1. You can follow the same steps to transpose rows into columns.
  2. Transpose is not limited to one column or one row. Select any area in your worksheet, copy it and do a transpose as mentioned earlier. see the example in the image below.

Transpose

Filed Under: Data, Formatting Tagged With: Data, Formatting, Transpose

How to remove personal information from a workbook

June 12, 2008 By admin

When you save a workbook (or any other Microsoft Office document), some of your personal information like Author, Manager, Company and Last saved by are saved as File properties. If you want to share your workbook with others, then you may want to remove this information before you distribute it.

There are two things you can do:

Method 1

  1. On the File menu click Properties.
  2. Click the Summary tab.
  3. Clear any information that you don’t want to share with others.
  4. Save your workbook.

However this method is incomplete because if you click on the statistics tab you will see your name in the Last saved by field.

Method 2

  1. On the Tools menu click Options.
  2. Click the Security tab.
  3. Under Privacy options you will find a checkbox labeled: Remove personal information from file properties on save. Select this checkbox.
  4. Save your workbook.

Note

Apparently this method will only hide the information in Excel, but if you save the document in other format like a web page (*.mht) then some of this information can still be viewed. Please check this link for more details:

http://office.microsoft.com/en-us/excel/HP010503321033.aspx

Applies to: Excel 2003

Filed Under: Security and Protection Tagged With: Exel help, File properties, Security

How to preview a workbook without opening it

June 10, 2008 By admin

If you want to preview a workbook in Windows Explorer without opening it, then you have to save the workbook with a preview picture.

This is how you do it:

  1. In the File menu click Properties. The Properties window will open.
  2. Click on Summary tab.
  3. Select the checkbox labeled “Save preview picture” at the bottom of the window.
  4. Click OK.
  5. Save the workbook.

Applies to: Excel 2003

Filed Under: General Tagged With: Excel, preview picture, Workbook preview

Change the number of the default worksheets

June 8, 2008 By admin

When you open an Excel workbook, by default Excel provides three worksheets named sheet1, sheet2 and sheet3 respectively.

You can change this number to your needs as follows:

  1. On the Tools menu click Options.
  2. On the Options window click the General tab.
  3. Next to ‘Sheets in new workbook’ enter the number of sheets you want.
  4. Click OK.

Applies to: Excel 2003

Filed Under: Customization Tagged With: Customization, default worksheets, Excel help

A macro to generate a list of random numbers

June 5, 2008 By admin

Excel has a function to generate random numbers: RAND (), and if you have the Analysis ToolPak add-in installed you will have another function available which is RANDBETWEEN().

However both functions will generate one random number. What if you want to generate a list of random numbers? The immediate answer will be: use Autofill.

This is OK if you want a small list, but what if you have to generate say a list of 1000 random numbers?

The macro shown below will loop through to generate any number of entries you specify. It will also give you an option to specify the number of digits you want in the generated number.


Sub GenNumbers()
Dim i As Integer, Row As Integer, Col As Integer
Dim NoRows As Integer, NoDigits As Integer
Dim MinNum As Double, MaxNum As Double
Dim NumFormula As String
Col = 1 ‘ Write random numbers on column A
‘Get the number of rows and number of digits entered by the user
NoRows = ActiveSheet.Range(“B1″).Value
NoDigits = ActiveSheet.Range(“B2″).Value
‘Calculate minimum and maximum numbers to be generated
MinNum = WorksheetFunction.Power(10, (NoDigits – 1))
MaxNum = WorksheetFunction.Power(10, NoDigits) – 1
‘Construct the formula to be entered in the cells.
NumFormula = “=randbetween(” & MinNum & “,” & MaxNum & “)”
‘ The loop to generate the numbers
i = 0
For Row = 3 To NoRows + 2
i = i + 1
ActiveSheet.Cells(Row, Col).Value = i
ActiveSheet.Cells(Row, Col + 1).Formula = NumFormula
Next Row
End Sub

How to use the macro

1. Switch to Visual Basic Editor and Insert a new module.
2. Copy the code into the new module.
3. Close Visual Basic Editor and return to your worksheet.
4. In cell B1 of your worksheet enter the number entries you want.
5. In cell B2 of your worksheet enter the number digits you want.
6. Go to Tools -> Macro -> Macros, you will see the list of available macros.
7. Select GenRandomNum and run it.
8. You will get an output like this:

genrandnum.gif

Applies to: Excel 2003

Filed Under: Visual Basic Tagged With: Excel, Macros, Visual Basic
« Older Posts