Excel/Windows: Change default date display format

By default, the “short date” display format for Windows (and thus Excel) is “M/d/yyyy.” For example, June 1, 2011 would be displayed as 6/1/2011. For varying reasons, you may want to zero-fill your dates and have June 1, 2011 read as 06/01/2011. Doing this actually involves a Windows setting, not an Excel setting. So, be prepared to have your dates displayed accordingly across the whole operating system and many other programs.

To change the default date format in Windows:

1. Go to Start –> Control Panel and select “Regional Settings” or “Region and Language,” depending on your operating system.
2. In the Short date format list, select the desired format:
   
3. Click OK.

Now, unless the dates in Excel were formatted using the format menu, they should now display as you’ve defined them.

Excel: Just one sheet by default, please

Why do I need three worksheets automatically generated when I create a new workbook? I don’t. I see so many workbooks with “Sheet1” full of data while “Sheet2” and “Sheet3” are sitting there sad and empty. As an auditor, I need to spend my precious minutes checking those other two sheets just to make sure there is nothing I wouldn’t want in a locked-down audit file. Plus, creating a new sheet when needed is easy enough that Excel should really only give you one to start with.

If you share my frustration, here is how to make Excel only provide one sheet by default:

1. Go to Office Button –> Excel Options.
2. Under popular, you should see an option for “Include this many sheets.” Change this to 1.
   

Windows 7: Dual screen keyboard acrobatics

I have become a huge fan of keyboard shortcuts as they seem to shave precious milliseconds off the time taken to perform certain tasks with the mouse. A colleague of mine shared with me some cool keyboard shortcuts for managing windows in a multiple monitor environment:

• Windows Key + Left/Right Arrow:maximize the current window to the left/right pane of the screen
• Windows Key + Left/Right Arrow (repeated):move the window to the other monitor
• Windows Key + Shift + Left/Right Arrow:move the window to the other monitor (without an intermediary step)
• Windows Key + Up Arrow:maximize the window to the full screen
• Windows Key + Down Arrow: restore the window from being maximized/minimize the window

This post at Technospot.net has a few more useful shortcuts in this category.

Adobe Acrobat: Help! My client cannot see my annotations on a PDF file

Newer Adobe Acrobat files do not often play nice with older versions of Acrobat. Sometimes you may receive a file, add annotations in Acrobat 8 or 9, save it, then send it to someone who is using Acrobat 6 who cannot see the changes you made.

The workaround for this is to re-print the file to PDF.

1. Go to File -> Print…
2. Make sure to select “Document and Markups” as shown below:
   
3. Click OK.

Why does this work? When you “save” a PDF file, it is saved as the version of the program you are using. When you “print” a PDF file, it is compatible with versions back to 5.0 (released in 2001).

The only caveat is that your annotations are now hard-coded and therefore cannot be changed in the resulting file.

Excel: Sort by cell color

Note: Applicable to Excel 2007 or later

Scenario: I have a spreadsheet of checks issued for the year. For each check, I need to determine if the disbursement was made for a fixed asset or not. As I go through each item and view the invoice, I shade the amount cell either green (fixed asset), yellow (missing invoice or question), or red (not a fixed asset).

For example:

After I have gone through this list, it would be nice to be able to sort by the colors I used. I know there are a thousand and one ways to accomplish this task without the use of colors, but I am a very visual person. The colors give me a good picture of the results of my work.

Solution:

1. Select the region to sort.
2. On the Data ribbon, select “Sort.”
   
3. Choose the amount column (or whichever column contains the color-coding) to sort by.
4. Choose Cell Color to sort on.
5. Select the color you would like on top.
6. Add additional lines to specify the order of the remaining colors.
7. Click OK to complete the sort.

My sort window looks like this:

The result looks like this:

CaseWare: Close a client file when the Document Manager is closed

Closing a client file in CaseWare does not have to be a two-step process. If you would like the client file to close when you close the Document Manager, do this:

1. Go to: Tools -> Options
2. In the General section, check the box that says, “Close file when Document Manager is closed”
   
3. Click OK

IDEA: Right-click on the data for quick filtering

Side note: if you have not been made aware of the amazing, data-crunching power of IDEA, I highly recommend taking a look.

Within a data file, you can quickly and easily create a filtered list using the right-click menu. Simply locate the particular data you want to use for your filter (in the screenshot below, an account number), and click “Display all records containing…”

A dialog box will appear with several options.

Here, you may do any of the following:

• Edit the value used for the filter
• For a numeric filter (for example, a dollar amount), you may change the “Operator” option to >, <, >=, <=, or <>to utilize the amount in different ways
• By default, the operator is “==”, but that can be changed to “=” to shorten the value and expand the filter. For example, if I wanted the above filter to show all account numbers that begin with “111”, I would change the operator to “=” and shorten the value to “111”.
• If there is already a filter criterion on the data file, you’ll have the option to select or deselect the “Add to current criteria” checkbox. This allows for multiple criteria to be implemented one step at a time.

Once the filter is applied, it will appear in the Properties section on the right side of the screen. Here, you can clear, edit, or copy the criteria.

Also, if you initiate an extraction, export, or any other database operation, the criteria will automatically appear in the resulting dialog box.

For more detail on entering criteria using equations, go to Help -> Contents -> Using the Equation Editor -> Building Equations.

Windows 7 and Vista: Quick screen zoom with keyboard shortcut

This tip is courtesy this Lifehacker post and applies to all Windows 7 and Vista users. There may be times when zooming in on a program or item on the screen is desirable. In my daily life, I often zoom in when I am showing something on my screen to another person. These keyboard shortcuts make it really quick and easy to zoom in and out using the Magnifier tool in Windows 7 and Vista:

• Windows key and + will zoom in in 100% increments
• Windows key and – will zoom back out

With the Magnifier tool up, the following shortcuts are available, according to Lifehacker:

• CTRL + L to bring up the lens display view
• CTRL + D to dock the magnification area.
• CTRL + F brings you back to the full screen mode.

For many other applications (regardless of your Windows version):

If I am using Word, Excel, Acrobat, or any number of other applications, I use CTRL + scroll wheel to zoom in and out of a document. This shortcut does not work on ALL programs, but it is very useful when it does.

CaseWare: Export automatic documents directly to Excel to preserve formatting

It is often helpful to send a client an Excel copy of the trial balance or any other automatic document. The latest versions of CaseWare provide a very easy way to do this.

1. Right-click on the automatic document you wish to export
2. Select “Save as Excel file…”
3. Choose a destination, enter a filename, and click “Save”

The exported file should look just like the automatic document:

NOTE: The totals and subtotals are NOT formulas.

Adobe Acrobat: Be very careful with confidential information in PDF files

As CPAs, we often deal with confidential information, and hopefully our IT departments have implemented safeguards to protect against such information getting into the wrong hands. What they cannot protect against, however, is the accidental sharing of information that you thought was hidden.

Glitch leaves Obama info out in open

The news article linked above refers to a “glitch” that allowed a PDF with blacked-out text to be fully readable. A reader of the document simply selected the text, including the blacked-out text, copied it, and pasted it into another program. What is referred to as a “glitch” in the article was probably just an uninformed use of the highlighter tool in Acrobat.

As shown below, the highlighter tool can be used to black out text when the color is changed to black:

The problem is, the text can still be selected, copied, and pasted as if it were never obscured in the first place. Although reprinting the document to PDF leaves the text not-selectable, it will still be faintly visible:

If you need to share a document that contains confidential information:

1. Determine if the document needs to be shared in the first place.
2. Don’t rely on Acrobat’s (or any other program for that matter) built-in tools that appear to do the job.
3. Try printing the document to paper, blacking out text with a marker, and scanning the document back in.
4. If you are unsure, consult with a tech savvy supervisor or a member of the IT department to determine any potential vulnerability before sending to a third party.

Above all, keep in mind the potential damaging effects of sharing information that should not have been shared, especially by accident.