Custom Formats

MORE CUSTOM FORMATS
 

Wow... I am on a roll here........

Another useful format that some people just love is #,####,

This format takes a longer number and essentially truncates it for you so that you do not see the thousands. Excel still reads it as the longer number but you don't see it- even if I do calculations unless you re-format it.

Column A shows the actual number but when copied that number into Column B and then applied the custom format #,####, it redisplayed the shortened number. So, if you don't like dealing with a lot of extraneous zeroes, take a look at this format. Although you can change the display units in a chart itself, this is another way to show simpler units on a chart you are presenting.

• Right-click on the cell(s)
• Select Format Cells
• Under Category, select Custom Formats
• Enter #,####, in the Type
• Click OK

The # tells Excel it is a number - don't forget that last comma... that's important as that is telling Excel that there are additional numbers after that but not to display them.

Keeping Leading Zeroes

LEADING ZEROES


Excel drops leading zeroes which can be a problem sometimes when you want to sort invocie numbers, account numbers or zip codes. If you want Excel to keep the leading zeroes then you need to create a custom format.

• Select the range you want to format


• Right-click and select Format Cells


• Select the Number tab


• Under Category, select Custom


• Enter the number of zeroes that your longest entry will have
  (In my example, I said 5)

• Click OK.

If you are just working with zipcodes, an little bit easier method is to select Special under Category and then select Zipcode. (Social security and telephone formats are also there.)

Find the Numbers

I'm not a "huge" football fan but What a Weekend... The Colts WON! And then of course I had to watch Minnesota and the Saints. Imagine... getting to the superbowl for the first time ever based on a field goal!

Okay... back to work. I wanted to share a tip that one of my customers emailed me. I thought you might find it useful. I call it FIND THE NUMBERS.

If you have a spreadsheet with numbers and formulas and you need to update certain "input" numbers each month and

1) you want to make sure that they are all updated
and
2) you don't want to waste time clicking on a cell only to discover it is a formula or worse yet- overwrite it in error

then this tip is for you.

• Open the spreadsheet
• Go to Edit >Goto Special if you have Excel 2003 or go to Find & Select >Go to Special if you have Excel 2007
• In the dialog box, put a checkmark in constants and one in numbers

• Click OK - Excel then selects just the cells that contain numbers - the Input cells you need to change
• Click the fill button (paint can) and apply a color or change their font color.
• As you update and change the number, remove the color formatting you applied.

By the end of the month, all the colors should be gone and you can be confident that your spreadsheet is up to date.

Nice tip Chris. Thanks.

Additional Comment Added : 1/26/10

If you have Excel 2007, you can do this even a little bit faster. Click on Find and Select and click on Constants. It will select all the constants. Apply a color such as light blue or green and you are done.

If you have a tip to share, drop me a line at patricia@cpaselfstudy.com

Using Arrays to Add Columns Containing Error Messages

I wanted to finish up my discussion of the other day - how to calculate columns containing error messages and also introduce you to Arrays. Arrays are very interesting and I think I will spend some time next week talking about them. They are extremely powerful.


This is a totally different approach from my last post which showed you how to use SUMIF if you wanted to add up a column of numbers with errors.


This approach uses an array to replace all the error messages with zeroes. Here we telling Excel to test for any errors and replace them with zeroes and to then sum the column.
In cell B8, I typed =SUM(ISERROR(B2:B7),0,B2:B7)) and then pressed Control+Shift +Enter at the same time.

The key thing to remember about arrays is that after you create the formula, you must press Control+Shift+Enter instead of just pressing enter. The 3 keystrokes tell Excel that it is an array and that all the cells are treated as a single block of cells and is depicted by the { } marks you see around the formula.
Obviously you would not use a 0 if you were trying to average the column. To get around that you could use " " to put blanks in the cells with errors.

If you are interested in arrays, you may want to check out my Arrays- A Powerful Tool course (If you are not a CPA and/or don't care about the CPE, just the Ebook is available for purchase on this site- in the far right column).
It walks you through how to put arrays together step-by-step.
Have fun and have a great weekend because... hey- it's Friday.

Summing a Column with Error Messages

I received a number of emails from people who read my last newsletter about my tip on summing a column containing error messages so I thought I would share it here with you.

SUMMING A COLUMN CONTAINING ERROR MESSAGES
If you don't already know how to do this, then this should make your year because everyone runs into it at some point.
In Excel 2003, you can use the SumIF function to tell Excel to sum all the cells in a column if they are greater than 0. By doing this, Excel will ignore any error messages, such as #N/A and DIV!0 in the column.
In the example, below, I am adding up column B and telling Excel to sum the numbers if they are greater then 0.

What to do if you have negative and positive numbers in the column? Simply add another SUMIF that tells Excel to add just negative numbers.

As with everything, there is more than one way to do this. A more elegant way is to use an array and I will talk about that tomorrow.

Have a great day and keep looking... the sun is out there somewhere

Outlook- Setting up Multiple POP3 accounts

I just purchased a new computer so I have been having so much fun transferring files, loading software and learning that my "old" printer is apparently incompatible with my new PC. Anyway, I digress.
I have multiple email accounts, for personal and business and I use Outlook. In my effort to organize myself (another of those New Year's resolutions that comes back to haunt you) I set Outlook up to handle all of my POP3 accounts so that I can check all my mail in one place. I thought you might be interested in this.

What this does for you is to create separate folders for each email, with the accompanying subfolders (Inbox, Outbox, Junkmail etc...). All you have to do is click on the Inbox of each to read the messages pertaining to that email. It is sweet.

It is pretty easy. My explanation assumes that you have already created the different email accounts in Outlook. If you don't know how to do that, go to Tools>Account Settings. Click on the Email tab and click New. Outlook walks you through the steps. Make sure the accounts work before bothering with setting up separate folders.

Two caveats:

1. Check with whoever hosts your email so that you have the correct settings. For example, Yahoo has a help page with the settings and port information that you will need.
2. Make sure to put a checkmark in Leave messages on server so that if you want to access emails from a different place, you can.

Here is how you set up the different folders:

• Go to the Tools menu and select Account Settings
• Select the email account that you want to direct to its own separate folder section
• Select Change Folder
• Seelct New Outlook Data File...
• Select Office Outlook Personal Folders File (.pst)
• Give the .pst field a name and location (select a location that you periodically back up)
• A new dialog box will pop up- give the display a new name (I suggest naming it after the account ie. Yahoo, Hotmail, CPASelfstudy but up to you)
• Click OK

You can repeat these steps if you have additional email accounts.
So give it a try and get yourself organized.
Have a great weekend.

2010

2010 Already.Here We Come.....
I'm spending this week organizing and organizing and streamlining.... yes.. you guessed it, my new years resolution.
I'll get back to writing this blog next week but wanted to mention a site that might be of interest to you.
I have been looking into online backup sites and tripped across this article on "must have" software programs. It is updated periodically and I thought it might be of interest to some of you.

http://www.makeuseof.com/tag/roundup-15-free-must-install-programs-for-your-new-pc/
CNET.com usually also has good reviews although there you need to specify what category you are looking for.

Have a good week and stay warm. It is about 5 degrees here and my husband had the nerve to complain about the cold weather in Boston over Christmas! Grab a cup of coffee or a chai and check out the link.