Excel Tips

Back to Hints and Tips Index                                             Back to Main Index

Leading zeros in Excel  (97 - 200&)

There are times when you want to display a series of numbers with leading zeros, for example, 001, 002 etc.  When you type them in Excel drops the leading zeros off.

  1. Select the cells.

  2. From the Format menu select Cells4Number tab.

    (2007 - Select the Home tab and click on the Format button and select Format Cells.)

  3. From the Categories list select Custom.

  4. In the box marked Type:  type the following 00#


     

  5. Click on OK.

    This tells Excel that there must always be 3 digits showing which forces leading zeros to be displayed.

Format alternate rows in a worksheet (97-2007)

Conditional formatting is a Microsoft Excel feature that sets a cell's format according to conditions that you specify. Conditional formatting allows you to present numeric data in different colors; for example, you can shade data depending on whether the value is greater, equal to, or less than zero. You can also present list data against a background pattern of alternating shades, as in the following example.

To apply alternating shades to the rows of your worksheet, follow these steps:

  1. Select the range of cells.

  2. From the Format menu select Conditional Formatting.

    (2007 - Select the Home tab and click on the Conditional Formatting button, and select Manage Rules.  Click on the New Rule button.  From the Rules Type select Use a formula to determine which cells to format.)

  3. Under Condition 1, select Formula Is.
    (2007 - Format Values where this formula is true)

  4. In the data entry box, type =MOD(ROW(),2)=1.

  5. Click on the Format button.

  6. In the Format Cells dialog box, click the Patterns (2007 - Fills) tab.

  7. Select a colour, and then click OK.

  8. In the Conditional Formatting dialog box, click OK.

For alternate columns, change the text ROW to COLUMN.

Take a snapshot of an Excel range (97-2007)

If you want to bits of a number of worksheets, Excel does not allow you to do this. Using the Camera tool you can take a snapshot of the data an place it on one worksheet.

The snapshots are live, so if the change on the worksheets so does the photo.

Place the Camera on the toolbar

  1. Right click on the toolbar and select Customise and select Command tab.

  2. Drag the icon on to the toolbar.

Excel 2007

You will need to customise the Quick Access toolbar. Click the down arrow to its immediate right and choose More Commands. From the Choose Commands From list select Commands Not in the Ribbon, and then locate the Camera. Select it and click on the Add button. Click on the OK button. Now the camera is on the Quick Access Toolbar.

Taking a snapshot

  1. Select the range.

  2. Click on the Camera.

  3. Navigate to and click where you want the snapshot to go.

  4. Repeat for other areas to assemble bits of worksheets onto one page for printing.

Back to Hints and Tips Index                                             Back to Main Index