Create a summary count of your file extensions

When we receive a new source of data, it can be important to get a handle on it as soon as possible. So much time can be wasted, opening various folders in Windows Explorer, only to not have a permanent record of what you have discovered. The easiest way to start is to create a new catalog using any of Filecats Standard or Filecats Professional (which need Excel installed) or Filecats Explore or Filecats Metadata (which don’t). Once a catalog is created, one good metric to establish is the number of documents of each file type – for instance, how many Microsoft Word documents there, the number of photographs.

Filecats Standard and Filecats Professional

Most of the rest of this article relates to Filecats Standard and Filecats Professional; if you are using Filecats Explore or Filecats Metadata, see the bottom of this article. There are two ways to approach this. Firstly, you can use the filter in the “File Type” column and count the number of results – this number can be found in the status bar. For more information about filtering, see this article on filtering in Excel. Secondly, and perhaps a more preferred way, is to create a PivotTable. This allows you to easily document this information in a handy table. For this example, I’m going to use the catalog below. Please download it if you want to follow along. Music catalog This is a list of music files which have been captured by iTunes and cataloged using Filecats Standard. Please download it, extract the spreadsheet and open it. Music141208.zip Compressed archive in ZIP format [2.6 MB]

Create a PivotTable

It may sound more difficult than it is. However, it’s not actually that hard, as it can be created in 3-5 steps – very easy steps as well. The following are for Excel 2007, 2010, 2013 or Office365 – it’s slightly more complicated in Excel 2003 and earlier. So, take a deep breath, and here we go. Step 1 – click on the top-left of the data table proper (in our example, this is cell A4). It is important that the focus is not on the first three rows. Step 2 – go to Insert – PivotTable and then click OK on the resulting dialog box. A new sheet has now been created, with a list of the fields in a new pane on the right-hand side, and an empty box on the left-hand side – this is the PivotTable. The pane will only be visible when the cursor is within the PivotTable. Step 3 – Drag “File Type” down to “Row Labels”, and “Open” to “Values”. And that’s it! Immediately the PivotTable becomes populated with the various file types and the file count. You can see here that the vast majority of the files in this catalog are .m4a. Incidentially, if I had wanted the long description of (.m4a) which on my computer is “MPEG-4 Audio File”, then I would have cataloged this folder using Filecats Professional, which includes dozens of different types of metadata. But let’s see what else we can do with this table. Optional Step 4 – If you want the items in descending order of popularity, click on cell B4 and click on the PivotTable Tools – Options – ZA. Optional Step 5 – If you want to exclude certain file types, such as Folders, then click on the drop-down box in cell A3 and deselect those types. Finished – And that’s it! Instantly you have a summary of all the data, in just 3-5 easy steps. I just re-did all of these steps, and it took less than one minute. How long did it take you second time through?

One small drawback…

There is one small feature you should be aware of. Pivot Tables do not auto-recalculate. That means, if you change the data which it is based on (say, by deleting a few thousand rows), PivotTables do not update their figures unless you ask them to. To ask it to update, right-hand click within the PivotTable and select “Refresh”. If you have multiple PivotTables and want them all to update, then go to PivotTable Tools – Options – Data – Refresh and click the drop-down arrow at the bottom. Then select “Refresh All”. So, if a PivotTable is out-of-date, just ask it to process the data again. Simple (once you know how!)

Filecats Explore and Filecats Metadata

In Filecats Explore and Filecats Metadata, it is very easy to create this analysis.

• Click on Analysis – Summary of files by extension.

And that’s it. If you want it in order of number of files or size, click on that column twice.

What Next?

Of course, you don’t just have to do it on the types of files. While that is one of the most important textual files using that Filecats Standard and Filecats Explore catalogs, the metadata extractors Filecats Professional and Filecats Metadata adds document properties such as Author, Company, Subject and much more. You can investigate these in the same way, such as the articles like “What metadata is used in Microsoft Word files?” What do you think? Does the word “PivotTable” sound scary? Is it easier than you thought? How much time (and therefore money) could you save with PivotTables? What could you do with PivotTables? I’d love to hear from you – please add your comments to the bottom. Did you like this article? If so, please like it or follow me on Facebook and Twitter using the icons to the left or the right of this page. Spread the word – PivotTables are not as difficult you might think, but are so useful. Do you want to replicate this Pivot Table? Then try it with the spreadsheet above – or create your own! Download any of our FIlecats programs – there’s a free 7-day trial.

Next Article

We continue our exploration of PivotTables in the second article in this series on investigating date ranges of your files.

We’ve got plenty more – you can read more articles here.