Archive for category Excel
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:
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:
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.
- Right-click on the automatic document you wish to export
- Select “Save as Excel file…”
- 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.
Ever have to expand a list of numbers in Excel that has a total at the bottom? Ever get annoyed when the line between the list and the total sticks with the list (see below)?
A while back, a client shared a trick with me that keeps the line with the total. The key is to place a TOP border on the SUM line, not a bottom border on the last item in the list. It is quite simple to do.
- After creating the list and adding the sum at the bottom, select the total cell.
- On Font section of the Home ribbon, click the arrow on the borders button.
- Select Top Border from the list.
Now when you add a new row at the end of the list, the line stays with the total:
To make this an even quicker process, see this blog post on customizing the Quick Access Toolbar to add a button for top borders.
Do you find yourself looking for a button on the toolbar that used to be quickly accessible in the old version of Office? Do you frequently use an uncommon feature? Customizing the Quick Access Toolbar in Office 2007 can put the features you use the most right where you want them.
- Right-click on the Quick Access Toolbar and select “Customize Quick Access Toolbar…”
- Use the menu that appears to find the commands you would like to see. Click the “Add” button to add to the toolbar. Click OK when finished.
- You can also right-click on the toolbar and select “Show Quick Access Toolbar Below the Ribbon” to make it even more accessible.
As shown below, I’ve added buttons for creating a new document, doing a print preview, and printing the document. I’ve also moved the toolbar below the ribbon.
BONUS: If you extensively customize the Quick Access Toolbar, you can double-click on the title section of the ribbon to hide it and give yourself more space for your document! Double-click again to bring it back.
I am a very visual person. I like to glance at something and glean some basic important information. For example, I maintain a spreadsheet that serves as a client dashboard. I designed it in such a way that I can quickly gauge the status of my jobs in relation to certain due dates. To accomplish this, I use the Conditional Formatting function in Excel 2007 to show me a green, yellow, or red icon depending on how close today’s date is to the due date. Note: This only works in Excel 2007 or later.
Here is an example:
Here is how to create this:
- Set cell B1 to today’s date using the formula “=now()” without quotes. That way, the date will always be current.
- List the project due dates in column B.
- In column C next to the due dates, add a formula to calculate the difference between the due date and today’s date. In this example, cell C4 contains the formula “=B4-$B$1” without quotes. Drag the formula down to all project rows.
- In column C, set the font size to 18 and the text color to white so we do not see the actual values.
- Select cells C4 through C7 and go to Conditional Formatting –> New Rule… on the Home ribbon.
- Under “Select a Rule Type,” leave the default selected (Format all cells based on their values).
- Under “Format Style” select Icon Sets
- Set the rules as shown below and click OK. Note: Set the Type to Number first before setting the Value.
Your worksheet should now look like the example. This should be a jumping off point for more creative uses of conditional formatting and the expanded options available in Excel 2007 and later. If you develop something particularly creative, please share in the comments!