by
updated 17 April 2011

Interested in how many times you have worked each DXCC entity? You can use a Pivot Table in Excel to find the answer.

The first thing to know is that Excel spreadsheets are limited to 65,535 lines. If you logbook contains more than that many QSOs, you will have to filter it in some way. In my case, I eliminated QSOs with my home country using an F8 search:

Prefix = !K

This reduced my log of almost 80K QSOs down to a more manageable 51K.

Next, open the Logbook window (Window -> Logbook), ensure that it includes the Prefix field, and save it to a .CSV file.

Now open the .CSV file in Excel. For the purposes of this note, I'm using Excel 2003. I found the instructions for creating the Pivot Table in this part of the on-line help:

Count how often multiple values occur by using a PivotTable report

The instructions from the Help are as follows:

  1. Select the column containing the data (in this case, Prefix).
  2. On the Data menu, click PivotTable and PivotChart Report.
  3. Click Finish.
  4. Drag the Prefix column label from PivotTable Field List to Drop Row Fields Here.
  5. Drag the same column label from PivotTable Field List to Drop Data Items Here.

Here are the instructions for Excel 2010 (2007 may be the same):

  1. Select the column containing the data (in this case, Prefix).
  2. On the Insert tab, click on the down-arrow under PivotTable and select PivotTable
  3. Leave the defaults and click OK. This will drop the Pivot Table onto a new worksheet
  4. On the right-hand side, you should see the PivotTable Field List. Select and hold the Prefix field in the top box, and drag it into the Row Labels box at the bottom.
  5. Select and hold the Prefix field in the top box, and drag it into the Values box at the bottom.

Once this is done, you'll have two columns containing the results: Prefix and Total. But you can't sort it, so:

  1. Select the cell containing the word Prefix.
  2. Scroll down to the bottom of the spreadsheet, where it says Grand Total.
  3. Holding down the SHIFT key, select the cell containing the last count value in the Total column. This will select all the Prefix/Total cells in your worksheet.
  4. ^C to copy these values to the clipboard.
  5. Open a blank spreadsheet or worksheet.
  6. Click on a blank cell and then paste the copied values into the new worksheet (^V).

Now you can use the sorting function in Excel to put the values in numerical order.

You can filter your logbook any way you wish, and sort any data value you choose (callsign, CQ Zone, etc.).