Tim Gavin, CPA
I provide audit, assurance, and consulting services to the public sector and not-for-profit organizations in the Chicagoland area.
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:
- Go to Start –> Control Panel and select “Regional Settings” or “Region and Language,” depending on your operating system.
- In the Short date format list, select the desired format:
- Click OK.
Now, unless the dates in Excel were formatted using the format menu, they should now display as you’ve defined them.
Posted in Excel on May 23, 2011
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:
Posted in Windows 7 on May 20, 2011
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.
Posted in Adobe Acrobat on May 19, 2011
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.
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.
Posted in Excel on May 18, 2011
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).
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.
- Select the region to sort.
- On the Data ribbon, select “Sort.”
- Choose the amount column (or whichever column contains the color-coding) to sort by.
- Choose Cell Color to sort on.
- Select the color you would like on top.
- Add additional lines to specify the order of the remaining colors.
- Click OK to complete the sort.
My sort window looks like this:
The result looks like this:
Posted in CaseWare on May 18, 2011
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:
Posted in IDEA on May 17, 2011
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.
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.