Captain Excel

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.

One can go "FAR" in today's world of financial reporting if one can get Fast, Accurate & Reliable data to manage. Microsoft query is a means to create a report made from elements of multiple databases. This exercise will show how to write a query in Excel. The example will link two Microsoft Access tables , and use a pivot table using a calculated field to reconcile the difference. For your convenience, this tutorial is available for download.

Lately, there's been a lot of brew-ha-ha concerning the IRS enforcement on the treatment of personal usage on business cell phones. Currently, the employee should be allocated as compensation the personal portion of the total calls. The employee is responsible for tracking the phone number and time of each individual call. Then each call must be classified as personal or business, and, if business, state the business purpose and retain the supporting documentation. Businesses must maintain these records in order to support the income exclusion from the employee's wages.

Invariably, I will create and distribute an Excel template only to fall victim to the dreaded formula overwrite offense. It's frustrating when a vacation schedule is returned that doesn't foot, or an item is coded incorrectly in an expense report after significant time has been taken to try and make a template idiot proof. Then some "idiot" spoils the whole thing by hard-coding a number over a formula.

Use the "Protect Sheet" option to help ensure that unwanted changes are not made into your Excel templates.

To illustrate, I have downloaded a template from Microsoft Online.