Overview

A few months ago my mother passed away. This left me and my siblings with a large condo filled with personal property, much of it with value, either to us personally or monetarily. We needed a way to keep track of it all so we could:

• make it available to each of us so we could indicate which items we were interested in keeping
• organize the items none of us wanted (most of it) so it could be sold, donated, recycled, or otherwise disposed of
• create a record of it all to have in the future for posterity, and to substitute for actually keeping things we are attached to but really don’t need

It was decided to inventory everything of value. We gave each item an item number and created a spreadsheet with information about the items. The spreadsheet has one row per item, with the item number, a description and other information, a place for each of us siblings to mark whether we were interested in the item, and pictures.

This has worked well for us. The spreadsheet has gotten quite big since there are thousands of items, but is still manageable, and it’s fairly easy to export a PDF of it which is easy to email, view, or print.

I’ve written a program to help manage the pictures in the spreadsheet. That makes it easier and quicker to add and manage items in the spreadsheet. More about the program (ADDPIX) in a section below, and in a separate post linked to from that section.

This spreadsheet format could be useful for many tasks involving the management and documentation of large groups of items. Examples would be such things as art, records, stamps, or other collectibles. There are no doubt many applications in business, such as an auction, where managing an inventory of things might be useful.

The Spreadsheet

I use LibreOffice’s Calc program. It works as well as Excel, is compatible with it, and is free. I urge you to give it a try. Most of these instructions will still be useful if you prefer to use Excel or another spreadsheet application.

The spreadsheet format has column headings in the first row, and item rows starting in row 2. There are no formulas or macros. The columns are:

• Column A: Item# – formatted as MDnnnn where “nnnn” is the item number
• Column B: Location – where the item is
• Column C: Description
• Columns D, E, F: JCC/ECH/JRC – where siblings (initials) can mark items they want
• Column G: Est Val – value
• Column H: Disposition – what to do (or has been done) with the item
• Column I: eBay# – last four digits of eBay item number for listed items
• Columns J, K, L, M: Pic1/Pic2/Pic3/Pic4 – up to four JPG images for the item

Detailed column descriptions and usage suggestions are in a later section of this article.

Columns can be added or deleted to match your needs. Be aware that if you wish to use my program (ADDPIX) to manage the pictures in the spreadsheet, you have to retain a few aspects of my format exactly, or be willing to do some fairly complex programming to adapt the program to your desired spreadsheet format.

Suggested Usage

This is how I use the spreadsheet: I have small stickers with item numbers on them to identify individual items. I prepare stickers in advance so it’s easy to stick them on items as I inventory them. While inventorying, I have a laptop open with the spreadsheet loaded. For each item, I attach a sticker, and enter the item description into the spreadsheet. I then take one or more pictures of the item. This process goes quickly. [I can provide stickers at a reasonable cost, as described below.]

As described, you start with each item row containing an item number, a description, and optionally a location and/or some pictures. All the other columns get filled in later, as needed, as part of the process of using the spreadsheet to manage your item inventory.

All this sounds like a lot of work, but it goes very quickly once you get good at it, and the results are worth it.

Adding Photos to the Spreadsheet

Occasionally (often enough so that I won’t forget which picture goes with which item), I download the pictures from my camera to an “Images” folder located in the same folder as the spreadsheet. I’m fussy about pictures so I take several shots of each final picture then delete all but the best one using Photo Viewer (see below). I then use an image editing program (I like “Paint.Net” – https://www.getpaint.net/) to crop each picture, adjust the image quality if necessary, and save it.

Images must be stored with a name of the form “mdnnnnx” where the “mdnnnn” is the item number (the first two letters are lower case), and “x” is a letter (“a” for the first picture, “b” for the second, etc.) specifying the picture sequence.

Note that you don’t need to scale the pictures before adding them to the spreadsheet; the spreadsheet program will store them actual size but can scale them to fit in the cell.

You can do it any way you like, of course, but following this pattern lets you use ADDPIX as is, or a modified version of it if you go that route. It’s also a good, and simple, way to keep your pictures organized.

To insert your pictures into the spreadsheet, either run ADDPIX if you are able to, or manually insert the pictures by clicking on the cell where the picture belongs and using “Insert Image…”. If you do this manually you will have to scale the pictures so they fit in the appropriate cells, by right-clicking on each picture and picking “Fit To Cell Size”. ADDPIX does this automatically, and also centers the picture in the cell which cannot easily be done manually.

Windows Photo Viewer

Windows Photo Viewer is a simple program that used to be included as part of Windows, but is missing from the latest versions of the OS. It’s actually still there, but there’s no easy way to enable it — you have to edit the registry to do so. Luckily people have created registry files to make the necessary changes. Search online for “enable Windows Photo Viewer” along with “Windows 10” or whatever version you are using, and you’ll find instructions and copies of the file(s) you’ll need. It only takes a minute, is safe and reversible, and you’ll end up with it on your right-click menu for photos..

I use Windows Photo Viewer to review the photos I’ve taken and delete all but the best ones. It’s easy and quick.

The ADDPIX Program

I’ve mentioned my ADDPIX program several times so far. I wrote it specifically for this job, and it makes the task of adding pictures to the spreadsheet very easy. I have a separate article specifically about the program at https://exeyesoftware.com/index.php/2023/07/23/addpix-a-sample-program-to-work-with-the-libreoffice-sdk-using-c-adding-images-to-a-calc-spreadsheet/. If you think you want to try ADDPIX, start by going to that article to download the zip file containing it.

Note that ADDPIX is a Windows command-line program that needs to be run from a command prompt. This is easy to do if you’ve never done it before; search for “how to run a Windows command line program” for instructions.

The linked article is primarily for programmers who want to use ADDPIX as a source of code snippets to be used in similar programs that they write. However, it has instructions for using ADDPIX, a detailed description of what it does, and a link to a zip file containing the program and a sample spreadsheet and images. It also has instructions for modifying ADDPIX (not for the faint of heart) should you wish to change the spreadsheet format and still be able to use ADDPIX.

If you want a modified version of ADDPIX but don’t want to learn to be a programmer, I can make modifications to it for you for a reasonable fee. I can also answer simple questions for free but I have to charge for complex assistance. Please contact me if you’d like more information or a quote.

Details of Spreadsheet Format

Earlier in this article I described the spreadsheet format in a summary form. Here I will go into more detail. You’ll need this information if you intend to use ADDPIX or want to follow my process closely. Of course, if you intend to come up with your own spreadsheet format, you are free to do so, and I expect most people will. This section may help you to do so effectively.

The spreadsheet starts with column headers in row 1. I like to freeze the column header row (instructions for doing so below) so it stays visible as you scroll, but LibreOffice (and ADDPIX) tend to lose this setting so you’ll have to keep doing it. It’s mostly important if you are going to print (or export to PDF) a copy of your spreadsheet.

Remaining rows contain data about individual items, one item per row. I use rows that are 1″ high, which gives room for small but useful pictures as well as detailed descriptions of items. Row formatting of text centers it vertically, and (mostly) centers it horizontally, with the option “Wrap text automatically” turned on. This lets you easily use multi-line paragraphs of text for the description and for other fields.

Individual Column Descriptions

Column A (Item#) contains the item identifying number. It is of the format “MDnnnn” where “MD” is a fixed string (it stands for “Mom & Dad”) to identify the types of items in the spreadsheet, and to make the item numbers recognizable as such. If you expect to use this format for multiple projects, you should choose a different two-letter prefix for each, for example the initials of the client. The “nnnn” is the actual item number. Usually you’ll start with “0001” and assign numbers sequentially, but you don’t have to, and don’t even have to have item numbers in ascending order.

Column B (Location) tells, of course, where the item is. There is room for a detailed multi-line description. It can include a room name, a box number, description of a specific place, a lot number, or whatever works for you.

Column C (Description) is the description of the item. Again there is room for a lot of information. As many of the items I inventory are destined for sale on eBay or elsewhere, I try to include everything I’d need to create an eBay listing, such as model number, manufacturer, specifications, condition, color, etc.

Columns D, E, and F (JCC/ECH/JRC) are used to record the wishes of the three parties with an interest in the items, specifically myself and my brother and sister, as indicated by our initials. For each item, we each mark a “-” if we don’t want the item, an “X” if we definitely want it, or a “?” if we are interested in it. Blank (” “) means that that person hasn’t made a determination. Once all three columns are marked, we can dispose of the item per the markings. Use whatever coding scheme works for you.

Column G (Est Val) is intended for recording value-related information when relevant. I use it for appraised value, eBay asking or selling price, and/or a guess at the value when that would be useful. There’s room for multiple values such as appraised value and selling price.

Column H (Disposition) tells your intention for what is to happen to the item, or what actually happened if disposition has been completed. For example, who got the item, where it was sold, to whom it should be shipped, or whether it was recycled or thrown out.

Column I (eBay#) is for identifying number(s) of potential or completed sale transactions. I use it for the last four digits of the eBay item number, which I find is sufficient to identify the transaction in my records (but which won’t help one find it on eBay). You can enter something similar, like an invoice number.

Columns J,K,L,M (Pic1/Pic2/Pic3/Pic4) are the columns where you put up to four pictures for the item. Pictures can go in any column, but you must suffix the picture image file name with “a” through “d” denoting columns Pic1 through Pic4 respectively. For example, a picture for item MD0123 to go in column K (Pic2) must be named “md0123b.jpg”.

You can use whatever headings you want for these columns and use them as you wish. You can use whatever codes you need in the columns.

Item Number Stickers

I suggest labeling items with small item number stickers. I typically prepare such stickers in quantity beforehand. I can print sheets of sequentially numbered item number stickers for you at a reasonable cost, as I have written a program to generate them quickly. Contact me for details.

Keep In Mind What You Will Do With the Spreadsheet

If you decide to add columns or change their width, keep in mind the overall width of the spreadsheet. It should stay as narrow as possible.

This is especially important if you need to be able to print it, or export it as a PDF file. When doing so, you’ll want to scale the output so that the entire spreadsheet width will fit on a single page width, probably in landscape mode. The scaled output should not be shrunken so much that it is hard to read and that the pictures are no longer clear.

Note that if you use a row height sufficient to be able to see the pictures stored in the row (I use 1″), you’ll have lots of room for multi-line text fields in the text columns. You can put a whole paragraph of information in the Description column, for example.

Changes you can make in the spreadsheet and still use ADDPIX

Some things need to stay the same to be able to use ADDPIX, but many can be changed. You can change:

• column widths
• row heights
• cell formatting
• column headings
• the number of columns

You can’t change:

• the item number format (“MDnnnn”)
• location of the item number in column A
• image file names (“mdnnnnx.jpg”)
• number of images allowed per item (0-4)
• location of the images in columns J-M
• image file format (JPG)

Location of Spreadsheet and Images Folder if using ADDPIX

ADDPIX is written to expect the spreadsheet file to be “C:\ods-addpix\Sample_Items.ods” and the image files to be in “C:\ods-addpix\Images”. There are options to ADDPIX that let you specify the paths and the spreadsheet filename, so you can use what you want, and can set up a BAT file to add those options automatically to make it easier to run ADDPIX. The BAT file should contain (all on one line):

<optional path to ADDPIX.EXE location>ADDPIX %1 %2 %3 %4 %5 -ssh "<path and filename of spreadsheet>" -img "<path to image file folder>“

It’s easiest to put ADDPIX, the ADDPIX.BAT file, the spreadsheet file, and the “Images” folder in the same folder. Then use “SHIFT-right-click->’Open command window here'” to open a command window in that folder. If you do this you don’t need complicated paths in your ADDPIX command.

Sharing the Spreadsheet with Others

At some point you’ll probably want to share your spreadsheet with others. You can do this by distributing the actual spreadsheet file, creating a PDF version, or by printing it out.

Formatting the Spreadsheet for Printing or Exporting

For the latter two choices, you’ll want to set up some formatting so the individual pages have the column headings, a header and footer, appropriate margins and portrait/landscape mode, and are scaled so the entire spreadsheet width fits on the page. The paragraphs below tell how to do this, because: 1) it is important, and 2) it’s very much not easy to figure out yourself.

These instructions are specific to the LibreOffice Calc program. If you use Excel or something else, you’re on your own, and the procedure will be different.

As you follow these instructions, use Print Preview often to check that you are getting the results you want. Select File->Print Preview and you’ll be able to see the spreadsheet formatted exactly the way it will be printed or exported as a PDF. This gives you a chance to get things right before actually printing anything. Exit the print preview by repeating File->Print Preview.

Here’s how to set things up for printing a nicely-formatted document. If you’re going to be distributing the spreadsheet itself to others, instead of printing it for distribution, the recipients will appreciate that you have taken the time to format it well.

1. Freeze the top (header) row: A) select row 2 B) select View->Freeze Rows and Columns.
2. Set the print range: A) select entire spreadsheet area (cells) that you want to print B) select Format->Print Ranges->Add.
3. Turn on column headings for print range: A) select Format->Print Ranges->Edit… B) set the Rows to Repeat dropdown box to – user defined – and set the value to $1 (row 1). Note that freezing the top row of the spreadsheet won’t automatically do this for printing/exporting.
4. Set the page width for printing: A) select Format->Page Style… B) pick the Sheet tab C) under Scale pick the Scaling mode: option Shrink print range(s) to width/height in the dropdown box. D) make sure the Width in page(s): box is checked E) enter 1 for the value (or whatever you want) F) uncheck the Height in page(s) box.
5. Set page orientation: A) select Format->Page Style… B) pick the Page tab C) for Orientation: pick Landscape. This results in fewer lines per page but more readable text and bigger pictures.
6. Set page margins: A) select Format->Page Style… B) pick the Page tab C) under Margins: enter whatever values you’d like. I like to keep margins small to fit more on a page, so I use 0.50″ or less for all four.
7. Set page header: A) select Format->Page Style… B) pick the Header tab C) make sure the Header on box is selected D) Pick Edit… E) type the header text you want in the three boxes. I like to have the date on the left, a description in the center, and “Page n of nn” on the right. Use the buttons below the boxes to enter variable data fields — it’s pretty easy to figure out.
8. Set page footer: just like setting the page header. I prefer to put everything in the header so I can leave off the footer. Just uncheck the Footer on box.

Sharing the Spreadsheet File Itself

You’ll want to share the spreadsheet file itself if you want others to be able to work on it. I do not recommend having multiple copies that people are working on, because if the copies get different from one another it is virtually impossible to merge the differences or even tell if they are different. But there may be cases where you’ll want to do this anyhow. Just be careful, especially if people are sending edited copies back to you.

The spreadsheet file can get very large, as much as several gigabytes for a moderately large project. So emailing it is probably impossible. Alternatives are a cloud drive like Google Drive, or a file-sharing service like FileMail.

Printing or Exporting a Copy of the Spreadsheet

In most cases you are better off making a PDF, which can often be small enough to email, or printing out the spreadsheet and distributing the printed copies. Be sure to follow the instructions for formatting the spreadsheet well, before doing so.

Before printing out or exporting a PDF copy of the spreadsheet, use Print Preview as described above. This gives you a chance to see what the output will look like, and get things right before actually printing anything. Printing a big spreadsheet or exporting a PDF can take a long time to complete, so you want to make sure it will be what you want.

To print the spreadsheet, use the familiar Edit->Print… dialog. Review the options you are given to make sure they are ok. Usually the defaults will be fine. Under More (which you may have to expand) you’ll find options for what to print, simplex/duplex, and number of copies.

To create a PDF of the spreadsheet, use the Edit->Export as PDF… dialog. You can go through the various option panes, but most of the time the defaults are what you want. When you’re ready, pick the Export button and you’ll be asked for a file name and destination folder for your PDF. Enter it, and exporting will begin.

Be aware that it takes a long time, especially for very big spreadsheets, that you get very little feedback that it’s happening, and that at times it will appear that it (and your computer) is hung up. If you’re not sure it is working, go get a cup of coffee or something for a while.

A warning: don’t try to use a PDF printer driver to “print” the PDF. Doing so will add a layer of unwanted formatting to your output, and you’ll probably not get what you want. Using Export as PDF guarantees that you’ll get what the Print Preview showed you. (or it’s supposed to).

Printing Sections of the Spreadsheet

You may find it valuable to print or export a section of the spreadsheet rather than the whole thing. For example, I do this when I add a number of new items to the spreadsheet and want to send just those items to my brother and sister for review. A PDF of my whole spreadsheet is over a hundred pages; by exporting only the new items I can send them a short PDF for them to review.

The first step in printing/exporting a portion of the spreadsheet is to select the cells you want. It doesn’t work to select the rows you want; you have to select the exact block of cells to be printed. Note that the Row 1 header shouldn’t be selected; it will appear as the first row on each page if turned on in Print Ranges. Also note that you have to select a contiguous block of cells; it doesn’t work to pick blocks of cells from various places.

Don’t try to use File->Print Preview to see if the selected area of the spreadsheet will be printed correctly. Print Preview always displays the full spreadsheet; worse, it deselects the section you’ve selected. However, the Print Preview box in the Print dialog will correctly show you the selected cells that will be printed.

If you are printing the selected part of the spreadsheet, pick File->Print… as usual. In the General tab of the dialog, make sure the More section is expanded, and in the From which: dropdown box, pick Print Selected Cells.

If you are exporting a PDF of the selected part of the spreadsheet, pick File->Export as PDF… as usual. Under the General tab, in the Range section, make sure that Selection/Selected Sheet(s) is checked.