My firm’s technology division caught wind of my awesome tech tips and chose to add me as an author on their blog. This is good for a few reasons:
- I can reach a wider audience.
- My readers can enjoy even more accounting tech-related articles on more topics than just tips.
- I no longer have to post to both this blog and my firm’s internal blog.
So, for a while, I will be posting old tips to gradually move my content to the new home. I will mix in some new tips here and there, so never fear.
Visit my new home here: Sikich Technology Blog
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.
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:
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.
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.
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:
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: