You are here: Home / Archives for Excel

How to format a cell based on its value (conditional formatting)

April 10, 2008 By

  1. Select the cells for which you want to clear the conditional formatting.
  2. On the Format menu, click Conditional Formatting.
  3. The Conditional Formatting dialogue will open, and the applied conditions will be listed.

  4. Click Cell Value Is.
  5. In the combo box next to it select the comparison phrase e.g. less than.
  6. Next enter a constant value (0 in this example).
  7. Click the format button, the Format Cells dialogue will open.

  1. Select the formatting you want to apply when the cell value meets the condition e.g. you can set the font color to red.

Applies to: Excel 2003

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

How to validate a cell value

April 8, 2008 By

If you are designing an excel sheet for other users to enter data, or even for your own use, it is a good idea to use the data validation feature to insure that only correct data will be entered. hear is how you do it:

  1. Select the cell you want to validate.
  2. On the Data menu, click Validation, the Data Validation dialogue box will be shown.

  1. Click the Settings tab.
  2. Specify the type of validation you want. Suppose you have a numeric value and you want to allow only values between 1 and 99, then:
    1. In the Allow combo box select ‘Decimal’.
    2. In the Data combo box select ‘Between’.
    3. In the Minimum edit box enter ‘1’.
    4. In the Maximum edit box enter ‘99’.

To show an error message when an invalid data is entered:

  1. Click the Error Alert tab.
  2. Fill the Title and Error Message edit boxes with appropriate text. (See example).

To remove data validation, click the Clear All button in the Settings tab.

Applies to : Excel 2003

Filed Under: Data, Validation Tagged With: , , ,

How to link to values in another worksheet / workbook

April 5, 2008 By

    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.
    2. Right click and select Copy.
    3. In your main sheet (target), select the cell where the value is to be displayed.
    4. Right click and select Paste Special.
    5. When the dialog box appear click on the Paste Link button.
    6. The value in the source sheet will now be reflected in the target sheet.
    7. Every time the source sheet is updated, the target sheet will also be updated with the new values.
    8. The same procedure can be applied to link to data in another workbook.

    Applies to : Excel 2003

    Filed Under: Data, Links Tagged With: , , ,

    How to create a custom list to use in Autofill

    April 2, 2008 By
    1. In Excel menu click Tools.
    2. In the Tools menu click Options.
    3. Click the Custom Lists tab.
    4. Click the Add button. The cursor will move the List entries edit box.
    5. Enter the data for your list separated by commas. E.g. East, West, North, South.

     

    Now if you enter “East” in any cell and drag the fill handle, other cells will automatically be filled with West, North … etc.

    Applies to: Excel 2003

     

    Filed Under: Data, Lists Tagged With: , , , ,

    Automatically fill data in adjacent cells (Autofill)

    March 31, 2008 By

    You can save yourself allot of typing if you know how to make use of autofill feature in Excel.

    Suppose you want to enter names of days in cells A1 to A7 (Sun, Mon, …. Sat). Here is what you do:

    1. Enter “Sun” on cell “A1”.
    2. Drag the fill handle across the cells that you want to fill (A2 to A7).

    You can also use Autofill to enter your own series of numbers. For example you can fill cells with the numbers 5,10,15,20 … etc as follows:

    1. Select the first cell in the range that you want to fill.
    2. Type the starting value for the series (5 in our example).
    3. Type a value in the next cell to establish a pattern (10).
    4. Select the first two cells that contain the starting values.
    5. Drag the fill handle across the range that you want to fill.

    Applies to: Excel 2003

    Filed Under: Data, Lists Tagged With: , , , ,

    How to create a custom date format

    March 29, 2008 By

    Excel provides a vast number of common date formats. However if you do need a format that is not included in the built-in list then you can create your own custom format as follows:

    1. Right-click on the cell where the date is.
    2. Select Format Cells.
    3. When the dialogue box opens select the Number tab.
    4. In the Category list to the left select Date.
    5. In the Type list to the right select the format closest to what you want. A sample format will show above.
    6. In the Category list to the left select Custom. A list of custom formats will show to the right, and the format you selected will show in the Edit Box above.
    7. Modify the format in the Edit Box and watch the sample.
    8. When you are satisfied click OK.
    9. The new format will be applied to the cell and will also be added to the list of custom formats.

    Applies to: Excel 2003

    Filed Under: Date and Time, Formatting Tagged With: , , ,
    Newer Posts »