Contextures

Home > Skills > FAQs > Excel 2003

Excel 2003 Tips and Tutorials

Tips and tutorials for Excel 2003 features.

how to unmerge cells in excel 2003

Organize Data in Excel Lists

In Excel 2003, use the List feature to create a dynamic list. It is a built-in tool that will create a List object on the worksheet. In Excel 2007 and later versions, Lists have been replaced by Excel Named Tables.

To create a List, follow these steps:

  • Select a cell in the list
  • Click the Data menu, click List, then click Create List

create list in excel 2003

  • In the Create List dialog box, check that the range is correct, and fix the range, if necessary
  • Add a check mark to the "My List has Headers" option.
  • Click OK

create list in excel 2003

When a cell in the List is selected, the list has a solid blue border around it, and there are filter drop down arrows in the heading row. There is also a blank row at the end, where you can add new items.

create list in excel 2003

Video: Fill Blank Cells

This video shows the quick steps to fill blank cells in Excel 2003. Written steps are on the Fill Blank Cells in Excel 2003 page.

Create a drop down list to help users enter data, and to prevent typing errors and invalid entries on the worksheet.

For written instructions on how to create a drop down list, see Excel Data Validation - Basics.

Highlight Duplicate Values

To highlight duplicate values in an Excel list, follow these steps.

  1. Select the cells to format -- range A2:A11 in this example
  2. Choose Format|Conditional Formatting
  3. From the first drop down, choose Formula Is
  4. For the formula, enter
    =COUNTIF($A$2:$A$11,A2)>1
  5. Click the Format button.
  6. Select a font or fill colour for highlighting.
  7. Click OK, click OK

Unmerge Cells - Entire Workbook

Note: To remove merged cells on the active sheet only, skip step 2 in the instructions below

To get rid of all the merged cells, on all worksheets, in an Excel 2003 or earlier workbook, follow these steps:

  1. Make a backup copy of the workbook, and store it somewhere safe.
  2. Right-click one of the sheet tabs, and click Select All Sheets
  3. On the active sheet, click the Select All button, at the top left of the worksheet
  4. On the Format menu, click Cells
  5. In the Format Cells dialog box, on the Alignment tab, remove the check mark from Merge Cells.
    • If the check box is grey, you’ll have to click twice
      • click once to add the check mark (in a white box)
      • click again to remove it.
how to unmerge cells in excel 2003

Change Column Headings to Letters

When you open an Excel file, you might see numbers -- 1, 2, 3 -- across the column headings, instead of letters -- A, B, C. This is R1C1 reference style, and it can be a useful feature sometimes, but most people prefer to see letters at the top of a worksheet.

Why do columns switch to numbers?

Here's what usually happens:

  • Someone sent you a workbook, and they saved that workbook while they were using R1C1 reference style.
  • That workbook was the first Excel file that you opened today.
  • When you open the first workbook in an Excel session, that workbook's reference style is used for all the other workbooks that you open, later in that session.

Fortunately, you can go back to letter headings, and get rid of those numbers.

Change column headings to letters in Excel 2003 and earlier versions

To get letter headings again, follow these steps

  1. Choose Tools > Options and select the General tab.
  2. Remove the check mark from 'R1C1 reference style'

    r1c1 reference style

Create Pivot Table

Follow these steps to create a Pivot Table in Excel 2003

  1. Select a cell in the database
  2. Choose Data>PivotTable and PivotChart Report
  3. Select 'Microsoft Excel List or Database', click Next.
  4. For the range, type your range name, e.g. Database
  5. Click Next
  6. Click the Layout button
  7. Drag field buttons to the row, column and data areas
  8. Click OK, click Finish

PivotTable and PivotChart Wizard

Show All Pivot Field Items

When you create a Pivot Table, it only shows items that have data. In the pivot table shown below, not all colours were sold to each customer.

Make the following change for each field in which you want to see all the data. even the items with no data:

  1. Double-click the field button, to open the PivotTable field dialog box.
  2. Check the 'Show items with no data' check box.
  3. Click OKgo to top

pivot items no data

List Pivot Table Formulas

If you’ve used calculated items and calculated fields in your pivot table, you can quickly create a list of all the formulas.

NOTE: The list shows all of the formulas in the selected pivot table's pivot cache, even if those formulas are not currently displayed in the pivot table.

List the Formulas

Follow these steps to create the list of pivot table formulas:

  1. Select any cell in the pivot table.
  2. On the Pivot toolbar, click PivotTable.
  3. Click Formulas, then click  List Formulas.

    List Formulas 2003

A new sheet is inserted in the workbook, with a list of the calculated fields and calculated items .

Change Comment Shape

To add a bit of interest to an Excel worksheet, change the shape of a comment, from its default rectangle.

change the comment shape

To change the shape of a comment in Excel 2003, follow these steps:

  1. Right-click the cell which contains the comment.
  2. Choose Edit Comment
  3. Click on the border of the comment, to select it.
  4. On the Drawing toolbar, click the Draw button
  5. Choose Change AutoShape, and choose a category.
  6. Click on a shape to select it.
  7. When finished, click outside the comment.

Database

Video: Add Picture to Comment

Instead of text, you can show a picture in an Excel comment. Watch the steps in this short video, and the written instructions are below.

To add a picture to a comment in Excel 2003, follow these steps:

  1. Right-click the cell which contains the comment.
  2. Choose Show/Hide Comments, and clear any text from the comment.
  3. Click on the border of the comment, to select it.
  4. Choose Format|Comment
  5. On the Colors and Lines tab, click the drop-down arrow for Color.
  6. Click Fill Effects
  7. On the picture tab, click Select Picture
  8. Locate and select the picture
  9. To keep the picture in proportion, add a check mark to Lock Picture Aspect Ratio
  10. Click Insert, click OK, click OK

Comment Picture

More Excel 2003 Tutorials

Advanced Filter Basics Excel 2003

AutoFilter Basics Excel 2003

Conditional Formatting Intro Excel 2003

Format Based on Other Cell Excel 2003

Fill Blank Cells Excel 2003

Dependent Drop Downs Excel 2003

Sort Lists in Excel 2003

Add Macro Code to Excel 2003 Workbook

 

About Debra

 

Last updated: January 8, 2024 3:32 PM