Track Your Collection in Excel
February 17, 2005
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.
