You are here: Home / Archives for April 2008

How to protect a workbook with a password

April 28, 2008 By admin

To allow only authorized users to view or modify your Excel workbook, you can secure your entire workbook file with a password. Follow the steps below to achieve this goal:

  1. On the File menu, click Save As.
  2. Click Tools.
  3. On the Tools menu, click General Options.

Save Options

  1. If you want users to enter a password before they can view the workbook, type a password in the Password to open box.
  2. If you want users to enter a password before they can save changes to the workbook, type a password in the Password to modify box.
  3. Click OK.
  4. You will be prompted to reenter your password(s) for confirmation.
  5. Confirm your password(s) then click Save.

Tip:

To remove a password from a password-protected workbook (If you have the right to modify of course), then follow the same steps to assign a password i.e.

File –> Save As –> Tools –> General Options

Clear the password(s) –> Click OK –> Click Save.

Notes:

  1. Passwords are case sensitive.
  2. If you are the only one who is allowed to open the workbook, then there is no need to enter the Password to modify.
  3. Make sure that you don’t lose your passwords because there is no easy way to recover them.
  4. This procedure applies to a new workbook as well as an existing workbook for which you want to add a password. In the second case you will be asked if you want to replace the existing file. Click Yes.

Related Links:

How to protect worksheet elements

Applies to: Excel 2003

Filed Under: Security and Protection Tagged With: Excel help, Excel Workbook Protection, Password, Workbook Protection

Autofill, formulas and cells references

April 24, 2008 By admin

In a previous post I explained How to use Autofill with formulas.Today I will cover the same subject but from a different perspective.

Suppose you have a list of values in a certain currency and you want to convert those values to another currency based on a given currency rate. If we follow the same scenario as in the previous post then it will go like this.

  1. Enter the formula in the first row of data as follows:

Formula Cell refrence 1

  1. Drag the fill handle across the rest of the rows to generate the formulas.

Formula Cell refrence 2

  1. Your data will now look like this.

Formula Cell refrence 3

What is wrong?

The cell reference style we are using here e.g. A3, B1 is called Relative Cell Reference. When you perform autofill downwards with relative cell reference, Excel will increase the row number by 1 successively e.g. A1, A2, A3 or B1, B2, B3 … etc.

In our case we want cell B1, which contains the rate, to be fixed. In this case we use the Absolute Cell Reference which means whatever cell reference you specify will remain fixed when you use autofill. The B1 cell will be coded like this: $B$1.

Now back to our example. This is how the formulas look after modification. $B$1 is the same in all rows.

Formula Cell refrence 4

And this is how our values will look like.

Formula Cell refrence 5

Related Links:

How to use Autofill with formulas


Applies to: Excel 2003

Filed Under: Data, Formulas Tagged With: Autofill, Data, Excel Formulas, Excel help, Formulas

How to use Autofill with formulas

April 20, 2008 By admin

The Autofill feature of Excel can be applied on formulas as well as predefined lists. Let us take a simple example:

Suppose you have two columns of Quantity and Price and you want to derive a third column of Value by multiplying the quantity by the Price, then you:

  1. Enter the formula in the first row of data as follows:

Autofill Formula 1

  1. Drag the fill handle Autofill Handle across the rest of the rows to generate the formulas.

Autofill Formula 2

  1. Your data will now look like this.

Autofill Formula 3

Filed Under: Data, Formulas Tagged With: Autofill, Excel Formulas, Excel help, Formulas

How to apply a cell format to other cells

April 18, 2008 By admin

1. Select the cell which you have already formatted.

2. Click Format Painter Format Painter in the standard toolbar.

3. Click the cell on which you want to apply the format. The format of the first cell will be applied to the new cell.

4. If you want to apply the format to more than one cell then select all the cells before releasing the mouse button (drag the mouse pointer across the cells).

Tip.

If you have none adjacent cells or ranges for which you want to apply the format, then:

  • Double click the format painter. This way it will remain active until you deactivate it again.
  • Apply the format as many times as you want (see 3 and 4 above).
  • When you are done double click the format painter to deactivate it.

Applies to: Excel 2003

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

How to Repeat Heading Row(s) on Every Page

April 16, 2008 By admin

If you have one or more heading rows in your worksheet and you want to print that worksheet then the heading row(s) will be printed on the first page only (default).

To print the heading row(s) on every page do the following:

  1. Click File then click Page Setup .The Page Setup window will open.
  2. Click the Sheet tab.

Heading to repeat

  1. In the Sheet tab click Rows to repeat at top
  2. Now go to your worksheet and select the row(s) which have the headings. The selected row(s) will be reflected in the Page Setup window.
  3. Click File –> Print Preview to verify that the heading row(s) will be repeated on every page, then print your worksheet.

Applies to: Excel 2003

Filed Under: Formatting, Printing Tagged With: Excel help, Formatting, Print headings, Printing

Function to decide whether a year is a leap year or not

April 13, 2008 By admin

The following Visual Basic code is for a function to determine whether a certain year is leap year or not. It will receive the year as an argument and return TRUE for a leap year and FALSE for others.Copy this code and paste it in a new visual basic module.


Function IsLeap(iYear)
If (iYear Mod 400) = 0 Then
IsLeap = True
ElseIf (iYear Mod 100) = 0 Then IsLeap = False
ElseIf (iYear Mod 4) = 0 Then IsLeap = True
Else: IsLeap = False
End If
End Function

Related Links:

Blog Post: How to create a user-defined function
External: Wikipedia article on Leap_year

Applies to: Excel 2003

Filed Under: Date and Time, Functions, Visual Basic Tagged With: Excel help, User Defined Functions, Visual Basic

How to create a user-defined function

April 12, 2008 By admin

If you know Visual Basic programming then you can create your own user-defined functions in Excel. Follow the steps below:

1. On the Tools menu select Macro.

2. On the Macro submenu select Visual Basic Editor.

User defined1

3. You will be switched to Visual Basic Editor Menu.

User defined 2

4. On the Insert menu (Visual Basic Editor) select Module.

5. This will open the code window.

User defined 3

6. Type the Visual Basic code for your function.

7. On the File menu click Close and return to Microsoft Excel.

8. Your new function should now be ready for use like any other Excel function.

Applies to: Excel 2003

 

Filed Under: Functions, Visual Basic Tagged With: Excel help, User Defined Functions, Visual Basic

How to create a custom toolbar

April 12, 2008 By admin
  1. Click Tools then click Customize .The Customize window will open.
  2. Click the Toolbars tab.

Toolbar Window1

  1. Click New. The New Toolbar window will open.

Toolbar Window2

  1. Enter a name for your toolbar then press OK. This will give you an empty toolbar.
  1. Click on the Commands tab of the Customize window.

Toolbar window 3

  1. As an example, and under Categories click on Insert.
  2. A list of relevant commands will show on the right
  3. Drag commands from the list and drop them on your new toolbar.
  4. Your toolbar will now look like this:

myToolbar

  1. Drag the new toolbar to the toolbars area at the top of the window.

Applies to: Excel 2003

Filed Under: Customization Tagged With: Customization, Excel, Excel help, Toolbars

How to print part of a worksheet

April 10, 2008 By admin

If you have a worksheet and you only want to print part of that worksheet then follow the steps below:

Method 1

  1. Click File then click Page Setup .The Page Setup window will open.
  2. Click the Sheet tab.

    3. Print Area0

  1. In the Sheet tab click Print Area
  2. Now go to your worksheet and highlight the cells you want to print. The Print Area in the Page Setup window will be updated automatically with the cells references.
  3. Now every time you print the worksheet only the specified area will be printed.
  4. To print the whole sheet, clear the Print Area before you start printing.

Method 2

  1. In your worksheet, highlight the cells you want to print.
  2. Click File –> Print Area –> Set Print Area.
  3. The print area is now set to your selection.
  4. To print the whole sheet, Click File –> Print Area –> Clear Print Area.

Print Area1

Applies to: Excel 2003

Filed Under: Data, Printing Tagged With: Data, Excel, Excel help, Printing

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

April 10, 2008 By admin

  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: Conditional Formating, Data, Excel, Excel help, Formatting
« Older Posts