Excel 2003 Tips and Tutorials

Tips and tutorials for Excel 2003 features.

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

• 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

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.

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.

Video: Create a Drop Down List

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

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.

• NOTE: For steps in later versions of Excel, go to the Excel Worksheet Tips page.

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'

   

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

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 OK

Change Comment Shape

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

• NOTE: For steps in later versions of Excel, go to the Excel Comment Tips & Tricks page.

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.

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.

• NOTE: For steps in later versions of Excel, go to the Excel Comment Tips & Tricks page.

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

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