Inventory your home with Microsoft Excel

An inventory of your possessions is invaluable for insurance claims. You can create and sort one with ease

Does your home insurance cover all your possessions?

The chances are it doesn’t, unless you’ve audited everything you own, worked out a full replacement value for each item and presented a list to your insurer.

Even if you are fully covered, how good would you be at listing the contents of every room on an insurance claim form after a fire or burglary?

Try it now, without looking. What’s in your living room? When did you buy each item? How much did you pay?

Excel at keeping inventories
Compiling an inventory of your possessions can be done easily using Excel.

It offers total flexibility so users decide what kind of information to store. It also lets users with no knowledge of databases or programming techniques sort, filter and print a list of their possessions in a variety of ways.

Values can be adjusted automatically to account for inflation, sub-totals for items such as jewellery can be computed, the contents of each room can be printed as a list, and ‘in situ’ pictures of valuable items can be included to prove their existence to an insurance company. Even if you’re an Excel beginner, we’ll tell you everything you need to know to get started.

The main reasons for keeping a detailed inventory is to arrange adequate insurance and to help when making a claim should the worst happen. However, a list of contents for each room in your home can also be invaluable when moving house or managing internal renovation and building projects. By creating a worksheet in Excel, you will be able to record the correct information for each item in your home.

Once a worksheet has been set up, the matching data for it can be collected by using, say, a clipboard or a voice recorder of some kind. Certain mobile phones and portable music players can be called into service for this purpose, while the best plan for notebook PC owners is to carry the computer from room to room, typing the details straight into an Excel worksheet.

Our instructions are for Excel 2003, but similar tools are found in other versions.

Designing a worksheet
An inventory should include a description and quantity for each item, plus a location, purchase price, purchase date and replacement value.

In case of theft, it’s a good idea to store the make, model and serial numbers of electrical items, and if a category is assigned to each item (such as furnishings or jewellery) it enhances the ways in which lists can be sorted and printed.

Two other useful features are space for suppliers’ names and additional notes. Copy the worksheet used in our example on the right and modify it to suit. The headings should be typed into row 1, starting at column A and finishing in column L.

They are: Item, Qty, Make, Model, Serial, Date, Supplier, Price, Location, Category, Value and Notes. Having typed these in, it’s a good idea to save the workbook, resaving your changes regularly.

Data entry and formatting
As soon as you start filling in the worksheet you will notice that some columns are too narrow to display their full contents and others are wider than they need to be. To resize the columns, point at the dividing line between any two columns (this must be done on the very top row where the letters are).

When the screen pointer changes to a pair of arrows pointing left and right, hold down the left mouse button and drag the dividing line to the left for a narrower column, or to the right for a wider one.

Release the mouse button to complete the action. While this isn’t essential because the scroll bar will bring columns that don’t fit on the screen into view anyway, it makes life easier.

The worksheet can be formatted in much the same way as Word or any other Office application. Simply highlight the cells to be changed and then use the icons on the Formatting Toolbar to apply text effects, alignment and colour.

To change the appearance of an entire column of cells, click on the letter at the top of the column to select it, then apply the desired format.

When there are hundreds of rows in the inventory and the headings have long since scrolled off the screen, it can be hard to know where you are, so a useful tweak is to lock both the headings and the
left-hand column into place so they remain visible at all times. To do this, click on cell B2 and then open the Window menu and select Freeze Panes.

Once all the data has been typed and there is a figure in the Price column for every entry, you can compute the total value of your possessions with a simple formula.

To create this, click in the cell immediately beneath the list of prices, hold down Alt and tap the ‘=’ key on the top row of the keyboard to start the Auto Sum feature. Then hit Enter. Auto Sum stops working when it meets a blank cell, so make sure there’s an entry in each Price cell, even if it’s just a zero.

Set up an automated filter
To view only those items in a particular room, use Excel’s Auto Filter function. Turn it on by clicking Data, Filter, then Auto Filter, which causes the right-hand side of each column header to sprout a button. Click the button at the top of the Location column and choose the room you’re interested in from the dropdown list.

This has the effect of hiding items stored in any other location, leaving only the ones you need. To once again display the entire inventory use the same Auto Filter button and select All.

To sort the inventory, first select the entire sheet by pressing Ctrl and A, and then open the Data menu and click Sort. Choose up to three columns to sort, then click OK. When printing the complete inventory for the purpose of doing a walkthrough at the end of the year, use the Location and Category filters for the sort. This will order the items first by room, and within each room by category.

To print the entire inventory, use the Print option on the File menu. To print a section of the inventory (perhaps after filtering and sorting the data), highlight the cells you wish to print. Then, from the File menu, select Print Area, followed by Set Print Area. Printing can then be performed in the usual way.

Use the Print Preview feature in the File menu list to check that the print area you have selected fits on the paper size. Note that the print area remains selected until you deselect it manually by using File, followed by Print Area and finally Clear Print Area.

Adding pictures and items
There are three ways to include pictures in the inventory. The least satisfactory is by opening the Insert menu and selecting Picture, followed by From File. The picture will float on top of the worksheet and get in the way.

Another way of using pictures is to insert a hyperlink that points to the location of a file on disk, but the file will not be visible until the hyperlink is clicked. Even then the picture will open in a separate viewer, not in Excel, which is inconvenient.

The best way to add pictures is to include them in cell notes. A picture will then pop up whenever the mouse pointer is hovered over its host cell. For information on how to do this, see our 'Add pictures as comments' step-by-step Workshop.

Pictures stored in this way are unobtrusive and easy to view. Furthermore, because they are saved along with the workbook, there’s no need to include them separately on to the CD or USB memory key as a backup copy of the inventory. If you plan to add a lot of pictures, see 'Quick-pic macro' below, which details how a short macro can virtually automate the process.

Adding new items to the inventory is easy: just tack them onto the end. Each Excel worksheet can have more than 65,000 rows, so space isn’t a problem.

Items that have been replaced can be overtyped with new data, while items that have been disposed of can be deleted. The easiest way to delete an item is to right-click its row number and select Delete Row from the context menu. The item is then deleted and its gap automatically closed up.

When entering the initial data, the replacement values in column K should be estimated. For new items, the replacement values are the same as the purchase price in column H, but for older items inflation will have to be taken into account.

Once a year, before the renewal premium is due, it’s advisable to bring replacement costs up to date. This can be done on an item-by-item basis, but a quicker way is to place a formula in column M that multiplies the replacement value in column K by the current rate of inflation.

For a four per cent annual inflation rate you’d type =K2*104% into cell M2. This formula can then be copied into all the other cells in column M. Items which you know have increased in value by more than the rate of inflation can be adjusted manually.

The great thing about spreadsheets is that they don’t tie users into a fixed way of working. If you decide to modify it, save a backup copy of the original version with a new name before tackling a major revision. We also advise you to keep a backup of the inventory on a CD or USB memory key, and away from the house.

Quick-pic macro
Adding more than a few pictures soon becomes tedious, so if there are many to be added it’s worth setting up a macro to automate the process.

Click Tools, then Macro and then on Macros to open the dialogue box. Type InsertPic as the name of macro and click Create. When the Visual Basic editor opens, there are two lines there. They are: Sub InsertPic() and End Sub. Type the following code between these two lines, pressing the Enter key where indicated in the square brackets:

Dim picname [press Enter]
If ActiveCell.Comment Is Nothing Then ActiveCell.AddComment [press Enter]
picname = Application.GetOpenFilename(“All files (*.*), *.*”, , “Select picture”, , False) [press Enter]
If CStr(picname) <> “False” Then ActiveCell.Comment.Shape.Fill.UserPicture picname [press Enter]

After entering the code, close the Visual Basic window. To assign the macro to a shortcut key, click Tools, Macro, Macros. Select the Insert Pic macro and then click Options. Select ‘I’ as the shortcut key and click OK. From now on, to insert a picture, simply click in the host cell and use the Ctrl and I shortcut.