Captain Excel

My good friend Mitch , a highly technical computer buff but not an avid Excel user told me about an Excel add-in called Pivot4U. This add-in will allow for edits into the data field of a pivot table. My first thought was "That's not right!". Why would anyone want to edit the data field in a pivot table? That would destroy its integrity, and pivot presentations would become questionable . This may be a bad idea, so I had to try it.

I have to admit it.  I don't have all of the answers, and many times there are multiple answers to an Excel problem.   Here's a case in point.  Below I have a table that has approximately 20,000 rows.  It consists of records from the accounts receivable detail table (Customer, Invoice Number and Amount).  I have added a VLOOKUP formula in the next column (see my  blog  Linking with the VLOOKUP Formula dated  7/7/09 for an explanation and example of a VLOOKUP). 

The first thing I learned back in accounting 101 was the formula for a bank reconciliation.  Bank balance plus deposits-in-transit less outstanding checks equals the book balance.  Many accounting software packages such as QuickBooks have a bank reconciliation module within the software itself so there's no use for an Excel spreadsheet to do a bank reconciliation.  There are, however situations where a spreadsheet works better.  In practice, the hardest item to track in a bank reconciliation for me has been the outstanding checks.  Here's a couple of methods to trac

It's happened to everybody. I need to access this file, but where did I put it? Which directory and folder did I use? Now I have to consider a keyword search, but what did I name it and when did I last use it?

I find this lapse in memory occurs more often as the gap in file acquisition time increases and, quite frankly, as I get older. However there are certain features and techniques that can be utilized to assuage file acquisition issues:

• Use Header and Footer elements
• Expand the recently used file list
• Pin the file

About four years ago I was asked to give a presentation on Excel tips and tricks. That can be tricky because of the various backgrounds of the participants. One person’s tip is old hat to someone else. I needed a system to determine what tips and tricks to include. I decided to ask some fellow Excel users to give me their best Excel “stuff” be it a formula, function, methodology or whatever that has helped him or her to become more productive using Excel. I wanted to compile the best tips and tricks I received back from them.