About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store


Track Your Collection in Excel

Amber MacArthur, Bill Jelen, Leo LaPorte, Andy Walker on the set of Call for Help on TechTV Canada.

As mentioned on the show, you can get a free chapter a week from the new book by signing up here.

Collectors can track their collections using an Excel spreadsheet. Today, we have some tips on cool features in Excel for helping to track your collection.

To start, type column headings in Row 1. Use headings that are appropriate to your hobby. For the database of Jetsons collectibles, I chose columns for Name, Year, Manufacturer and a column for each of the characters. You will list one item per row.

The first trick is how to make sure that you can always see the Row 1 headings as you scroll down through the list. To freeze items in row 1 at the top of the screen, place the cell pointer in cell A2. Everything to the left and above the cellpointer is frozen, so it is really important to put the cell pointer in the right place. From the menu, select Window - Freeze Panes. Now, as you scroll through the list, you can always see the headings.

If your data has a heading above every column, you can easily sort by any field. Select a single cell in the column to be sorted and click either the AZ or ZA toolbar button to sort ascending or descending.

Without doing any programming, you can create an easy-to-use form for entering new records. Select one cell in your list and from the menu, select Data – Form. This form allows you to add rows, use criteria, etc.

You can create easy ad-hoc reports of your data using the AutoFilter tool. Select a single cell in the data and choose Data - Filter - AutoFilter. You now have dropdowns at the top of each column. Choose to filter the dataset to records that include Elroy.

Bonus tip: here is how to insert a pop-up box with the picture of the collectible.
  • Insert a comment.
  • Left-click on the diagonal lines surrounding the comment to changes the border to dots
  • Right-click on the dots and choose Format Comment
  • Go to Colors and Lines tab.
  • From the Color dropdown, choose Fill Effects
  • On the Fill Effects dialog, choose Picture
  • Choose Select Picture
  • Choose the picture. Choose OK twice.
  • Resize the comment so that the picture looks about right
  • Anytime you hover your mouse over that cell, the picture pops up.

For the BEST TV show on technology, check out Call for Help.

This tip was originally published on February 17, 2005 and aired on TechTV on March 30, 2005. The permanent URL for this page is

If you are looking for show notes from another episode, visit my complete list of TechTV appearances. Consulting can be hired to implement this concept, or many other cool applications, with your data. provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.


Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

All contents Copyright 1998-2008 by MrExcel Consulting.