I plan to keep updating this page with handy tips on various software programs that might be  useful to you so check back. If you have tips that you want to share please feel free to email them to me and I will post them here for others to read.

Current Tips are for Excel -just some quick but handy tips... For PowerPoint tips, click here.

Formatting: 8/13/08
 To replace a zero with a dash in a financial statement, simply applying the Accounting numer format. If you don't want the dollar sign to display then change the Symbol choice to None. 


Click here and then go to page 31 to read my article on SumIF  published by the Missouri Society of CPAs in The Asset in May 08.

Checkout this June 18, 2008 article at CFO.com about sloppy spreadsheet practices. Hopefully, you laugh and think- Hey, I've seen that and not.. Hey.I do that!

New Sort Feature in Excel 2007-Sort by Color- 5/8/08
If you use background colors or font colors in cells, you can now sort on those attributes.
For a quick sort  on a specific color -Right- click the cell with the particular color you want to sort on and select Sort>Put Selected Color on Top.

AND, OR NOT Functions with an IF Function: 4/22/08
The AND, OR and NOT functions are usually not used alone - instead one of these functions is usually housed within an IF.  Combined  it can be very powerful as the AND and the OR functions allow you to test up to 30 different conditions.  An IF  and AND together will be true if all the conditions are met. An IF and OR together will be true as long as one of the conditions is true. For an example, click here.

Time in Excel- Formatting Time: 3/20/08
If you add up a series of hours and minutes, Excel will not display the correct answer until you format the number into an hh:mm format.  In Excel 2003, go to Format>Cells>Custom and find [h]:mm:ss. In Excel 2007, from the Home menu, click Format on the Cell tab and then select Format cells>Custom and find [h]:m:ss.

Finding Your Way Around 2007:  Tired of playing "hide and go seek"?  2/22/08

GoalSeek-1/14/08- updated 2/22/08
GoalSeek is an Excel What-IF tool that allows users to find a specific value for a cell included in a calculation by changing one variable in the equation. It is pretty simplistic and nowhere as robust as Scenario Manager or Solver but if you haven't used What-If tools it is a good place to start. To see two different ways to use Goal Seek - click here (The what-if component no longer works with a chart in Excel 2007)

LOOKUP functions -12/19/07
I've been working on a lookup manual that was just added to the course course catalog. VLOOKUP, HLOOKUP and INDEX MATCH but thought I would mention the LOOKUP Wizard here.
Excel offers a Lookup wizard which will easily retrieve a value from a worksheet if the data is contiguous and all the values are unique. It doesn't work in all cases particularly if you have repetitive data and are not familar with lookup functions it is worth a look. It is based on the Index Match function.
This Wizard is an Add-in Program and does need to be added into Excel. [If you are not familiar with Add-in programs, go to Help in Excel].
Once you have done so in Excel 2003 go to Tools>Lookup or in Excel 2007 go to the Solutions tab   and click on Lookup. The Wizard will walk you through the 4 steps.

Subtotals:
COPYING SUBTOTALS-11/2/07
 I had my husband ask me about this one the other day - if you want to copy subtotals to another page and only copy the collapsed subtotal info- do the following:
1. Select the data you wish to copy
2. Click Edit
3. Click Go To
4. Select Special..
5. Select Visible Cells Only
6. Click OK
7. Click Edit>Copy
8. Move to an empty cell in the worksheet or a new workbook
9. Move to another location or sheet
10. Click Edit>Paste
Of course, I told him that if he had gone through my Excel Data Management Course he would have already known that. :)

Nested subtotals -11/30/07
They are quite easy to do.
Simply remove the check mark on replace current subtotals at the bottom of the Subtotal dialog box.


IF and Error... In Excel 2007,  Microsoft has combined the two together for those of you wanting to get rid of those N/A and DIV!s in your columns.  =iferror(value,text). It's not perfect, but it is a start as long as you don't mind hiding the column with the errors.

SUMIF and SUMIFs
SumIf is a handy function since it allows you to sum up a column of numbers quickly if it meets a given criteria. In Excel 2007, Microsoft has added a new function called SUMIFs which allows you to set up to 27 criteria. Click here to see a screenshot example. In the example Java Joe is adding up Quantity Sold (Column E) if the Product is French Roast and if the French Roast coffee costs $7.99 or more. And.. there is now an AverageIFs and CountIfs in Excel 2007 too. They all work the same way except of  course.. one adds.. one averages... one counts........

Evaluate a complex or formula one step at a time
You can see the different parts of a nested formula evaluated in the order the formula is calculated.
1. Select the cell you want to evaluate. Only one cell can be evaluated at a time.
2. On the Tools menu, point to Formula Auditing, and then click Evaluate Formula.
3. Click Evaluate to examine the value of the underlined reference. The result of the evaluation is shown in italics.

If the underlined part of the formula is a reference to another formula, click Step In to display the other formula in the Evaluation box. Click Step Out to go back to the previous cell and formula.
4. Continue until each part of the formula has been evaluated.
5. To see the evaluation again, click Restart.
To end the evaluation, click Close.

Note  The Step In button is not available for a reference the second time the reference appears in the formula, or if the formula refers to a cell in a separate workbook.

Navigating to Different Worksheet Tabs
At the bottom of the Excel screen, Right-click on the worksheet tab controls on the left and the first 15 worksheet tabs will display. Simply click on the tab you wish to go to.

How to Close Multiple Files Simultaneously -4/08 updated
If you have multiple files open the easiest way to close them all at once is to hold down the Shift key as you click on the File menu. When you do this the Close command changes to Close All which allows you to close all the open workbooks. Bad news- it doesn't work in Excel 2007!!

Watch Window
This is a great feature if you are working on complex sheets or files and need to keep track of certain numbers. Right-click on the cell you want to watch and select Add Watch. A window will appear and as you select other sheets and/or files you can keep track of the selected cell(s). If you are watching many cells it is is easier to keep track of them and their changing values if you have given them  range names.

Pasting Leading Zeroes

When you paste information into Excel it tries to format it as best it can. If you paste in numbers it formats them as numbers. At times however you may not want Excel to do that. For example, if you are working with zip codes or general ledger account numbers you may not want them to display as numbers since Excel automatically parses leading zeroes on numbers.  With charts of accounts or zip codes you would want Excel to treat them as text in order to retain the leading zeroes.  So, before pasting the numbers into the target cells, simply format the target cells as text. Format>Number> and select Text category.

Navigating
Pressing Ctrl+End takes you to the bottom cell of the worksheet you are in. This is a quick way to get to the end of your spreadsheet. Unfortunately if you have deleted some rows or columns and press Ctrl+End, Excel will still take you to the original bottom cell until you save the file. Once you save the file, Excel recalculates and determines where the new bottom cell is.

Create a Chart in a Second
In a hurry or just want to see a trend? Simply select your data and press the F11 key on your keyboard and presto- a chart is automatically created and put on a chart page for you. Now all you have to do is add some documentation to it.