Pivot Tables - A powerful tool.

At work I use pivot tables nearly every day. My work does not involve analyzing business or financial data and still I find pivot tables very useful in manipulating data sets, performing on-the-fly calculations, and preparing final reports.

So, what is a pivot table?

A pivot table is a data summarization tool found in data visualization programs such as spreadsheets (e.g. Microsoft Excel). Among other functions, they can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. Pivot tables are also useful for quickly creating cross tabs. The user sets up and changes the summary’s structure by dragging and dropping fields graphically. This “rotation” or pivoting of the summary table gives the concept its name. The term “pivot table” is a generic phrase used by multiple vendors. However, the specific form PivotTable is a trademark of Microsoft Corporation^[1]. - Pivot Table wikipedia entry

See the ExcelBlog.net In-Depth page for more information on Pivot Tables.

Send post as PDF to

Name Manager for Excel

Name Manager, now at Version 4, is a Excel Add-In that I find useful. Many times I have had to deal with workbooks with more than 100 names, many that are no longer valid. Name Manager helps manage such a mess.

If you are in need of a utility to manage defined names in your workbooks, this one is a must-have. List all names in your active workbook. Filter them using 13 filters, e.g. “With external references”, “With errors”, Hidden, Visible. Show just names that contain a substring. Show just names unused in worksheet cells. Edit them in a simple dialog or make a list, edit the list and update all names in one go. Delete, hide, unhide selected names with a single mouse click.

Available free at http://www.jkp-ads.com/officemarketplacenm-en.asp

Available for 97, 2000, XP, and 2003. And now a version for 2007.

Update (March 2008): Name Manager and Name Manager 2007 is now at version 4.1 (Build 599). New features: 1) When detecting unused names, all objects are searched as well as your VBA project. 2) A tiny but extremely handy toolbar to the VBE has been added. Selecting a name will paste that name at the current insertion point in your code!

Send post as PDF to

Version 3.5 of Autosafe

An Excel file saving utility from Jan Karel Pieterse.

Autosafe.zip (28 February 2007, 150k, Downloaded: 362 times) Version 3.5 of Autosafe enables use in environments with long paths/filenames. The standard Autosave (note the spelling) utility that ships with Excel just saves workbooks at a set interval, overwriting the file on disk. This is not very convenient if you planned to leave the master file intact and save the changed workbook using a different filename. It also does nothing to simplify recovery of unsaved/changed documents after a system crash. This Autosafe utility creates copies of open workbooks at regular intervals in a separate (user-selectable) directory. It does not overwrite the master file(s), that is up to the user to do, using normal methods. As soon as a workbook is closed the backup copy is deleted from the backup directory. If an abnormal termination of Excel occurs, the backup copies remain on disk, and Autosafe finds them the next time Excel is started and presents recovery options to the user. Includes the following languages: English, Dansk, Deutsch, Español, Français, Indonesia, Italiano, Nederlands, Norsk, Hrvatski, Slovenščina.

Source: JKP-ADS Download page

Send post as PDF to