Other Tip Collections

• General Excel Tips

  Excel Data Entry Tips and Tricks

  General Microsoft Office Tips

Tools | Data Analysis

• If Data Analysis doesn't show up as an option in your Tools menu, add it by going to Tools | Add-Ins and checking the Analysis Toolpak.
• Tools | Data Analysis | Histogram asks for a Bin Range. These are the breaking points for the bins you want to sort your data into. You'll have to add a column of data with these breaking points; this will be your Bin Range.
• Many would caution you to question the reliability of statistical procedures in Excel.

Charting

• You can add a regression line, known as a Trendline in Excel, by right-clicking on a data series in your chart and choosing Add Trendline. Be sure and go to the Options tab of the Trendline dialog box. There are checkboxes that let you display the line equation and R-squared in a textbox along with the line on the chart.
• To add error bars (for the Y values in an X-Y chart):
  1. Add two columns (or rows) alongside your data; one column contains the value for error above each data point, the second column contains the value for error below each point.
  2. Right-click the data series in your chart and choose Format Data Series
  3. Go to the Y Error Bars tab
  4. Select the Custom radio button
  5. Point the + field to your column of error above values
  6. Point the - field to your column of error below values
• To capture your Excel chart in a high resolution JPG, TIF, or GIF file
  1. To get the resolution you need, set your desktop/screen resolution to something obscenely high -- 2048x1536 (usually we're working at something like 1024x768)
     • Right-click your Desktop (background) and choose Properties | Settings tab and click and drag the screen resolution slider to the resolution you want.
  2. Resize your chart as needed in your big workspace of this high resolution mode.
  3. [Alt][Print Screen] will capture an image of your currently active window to the clipboard. Then you just Paste that into a graphics program (IrfanView, PaintShop, Photoshop) and crop to the image you want.

Pivot Tables

• Grouping on a particular field can be very useful if you want summarize data over a range of values -- Right-click the field label and choose Group and Outline | Group...
• Blank rows in the data set will prevent you from using Grouping in your Pivot Table.
• A summary of Pivot Table functions in Office 97 can be found at: http://www.cof.orst.edu/net/software/excel/pivottab.php

Excel Functions

• The IF function is frequently useful -- 
      IF(CONDITION, action or value if CONDITION is true, action or value if CONDITION is false)
• The Database functions in Excel produce results for records in your data that meet certain conditions. DSUM for example returns the SUM of the values in the specified column where the records used in the SUM must meet the conditions specified by the Criteria pointed to in your DSUM formula.
  • Your data must have column labels, these are the field names for your database.
  • Use the f-of-x, Function Wizard, icon in the toolbar and go to the Database group to see the available Database functions.
  • Specify Criteria for your Database function by listing the field name in one cell and giving the condition in the cell below
  • Logical AND is interpreted for conditions specified in adjacent columns.
  • Logical OR is interpreted if you specify more than one condition in rows below a single field name.
• If you have standard text to enter, maybe one of 5-10 possible entries, maybe using the INDEX function would help.
  • List of possible values in E1:E9
  • If you want the first value, you enter 1 in B1, 3rd value in the list, enter 3, ...
  • Copy this formula down Column C -- =INDEX($E$1:$E$9, B1)
  • Once you have the values you want in Column C, Add a column, and then use Copy | Paste Special | Values to copy the formula results to your new column
• MATCH provides the complimentary operation for INDEX - given a value you want to find, look it up in an array of possible values, and return the index number for that  value's location in the array.
  • MATCH (location of possible values, lookup value, match type)
    If match_type is omitted, it is assumed to be 1. 
    If match_type is 1, array of values must be sorted in ascending order and 
       MATCH finds the largest value that is less than or equal to lookup_value. 
    If match_type is 0, MATCH finds the first value that is exactly equal to 
       lookup_value. Lookup_array can be in any order.
    If match_type is -1, array of values must be sorted in descending order and 
       MATCH finds the smallest value that is greater than or equal to
       lookup_value.
• The INDEX function technique above can be combined with List Box or Combo Box tools from the Forms (or Control Toolbox) toolbar.
  • Once you've added your List Box or Combo Box the key is to right-click the Box and choose Format Control...
  • In the Control tab you specify (using the example above), Input Range: $E$1:$E$9
  • Choosing a value from the list will populate the cell specified in Cell Link, say: B1
  • You'd still have to convert this number to a value in another column using the INDEX function discussed above.
  • The List Box or Combo Box will only populate one cell with a value.
• Close relatives of the INDEX function are VLOOKUP, HLOOKUP, and LOOKUP
  For Example:
  • The lookup table of the INDEX example would be extended to two columns.
  • Column D would number the values in Column E, that is, Column D contains the numbers 1 through 9.
  • In Column B you enter the number of the value you want to pick from the lookup table.
  • Column C contains the formula: =VLOOKUP(B1,$D$1:$E$9,2)
  • Lookup the value in B1 in the first column of the table in $D$1:$E$9, find the value in a row in the table, return the value in the 2nd column of the table.
• If you need to calculate or dynamically enter a cell reference for a formula, INDIRECT is a useful function.  SUM(INDIRECT("A"&D5):A15)  would concatenate A with the row number specified in D5 and sum in column A from that row through A15.
• OFFSET can also be useful when column and row references must be calculated. This sample spreadsheet illustrates the use of the OFFSET function to manage moving rows of data to the right to line up data columns at the right-hand end of the rows and move blanks to the beginning, or left, of each row.

Filtering, Outlining, and Subtotals

• Data | Filter | Auto Filter -- If you filter on a particular value, Excel will automatically generate a row of SUBTOTALs at the bottom of the filtered results. The first parameter in the SUBTOTAL(FncNum, Range) is the summary function used in the subtotal.
  1 AVERAGE
  2 COUNT
  3 COUNTA
  4 MAX
  5 MIN
  6 PRODUCT
  7 STDEV
  8 STDEVP
  9 SUM
  10 VAR
  11 VARP
• Data | Subtotals lets you do Outlining specifying the data breaks and what kind of subtotals you want at those breaks. The Outlining plus and minuses at the left let you control the level of detail that is displayed.
• You may want to Copy the SUBTOTAL results and Paste Special | Values to preserve the results for additional calculations.
• When you've filtered data or hidden rows or columns, selecting a block of cells will also, disappointingly, select the cells that are not visible. There is a hidden command that will let you select only the visible cells. It's hidden in that you have to add this command to your toolbar in order to use it.
  1. Right-click in the gray area of the toolbar to the right of Help
  2. Choose Customize
  3. Go to the Commands tab
  4. Choose the Edit category
  5. Scroll all the way to the bottom of the list of Edit Commands
  6. You should find the Select Visible Cells command
  7. Click and drag this command and drop it where you want it to appear on a toolbar.
  Now anytime you have cells hidden, you can click the Select Visible Cells icon to select all the currently visible cells WITHOUT selecting those cells you can't see.

Miscellaneous

• Tools | Options | Calculation | Precision as Displayed  checkbox could prevent you from falling into a potentially embarrassing "rounding error" situation 
• Format | Conditional Formatting might be useful for error checking (invalid values could show up formatted in red)
• There may be certain portions of the worksheet that you'd like to protect from any possible changes. By default all the cells in the worksheet are locked but the locks are ignored. Tools | Protection | Protect Sheet activates recognition of the locks. Before using Protect Sheet you would unlock all the cells you want to be able to edit when the rest of sheet is protected -- Select Cells, Format | Cells | Protection tab and uncheck the default lock. Then use Tools | Protection | Protect Sheet.
  If most of your cells are going to be unprotected with just a few protected --
  1. Use [Ctrl]-A to select all the cells in the sheet
  2. Format | Cells | Protection tab and uncheck the default lock
  3. Select the cells you DO want to protect
  4. Format | Cells | Protection tab and CHECK the lock back on
  5. Tools | Protection | Protect Sheet
• When you've imported data sometimes numbers will come in as text. That is, they look like numbers but don't act like numbers. A quick way to identify this problem is to use the Excel feature that when a block of cells is selected the SUM of the cells will appear in the Status Bar. Numbers that are text will have a value of zero in the SUM. To convert text to numbers:
  1. Select the cells to convert
  2. Right-click in the upper lefthand corner of a block of blank cells large enough to accommodate the cells you want to convert
  3. Choose Paste Special
  4. In the Paste Special dialog box choose the Operation, Add
  Doing this arithmetic operation of adding the cells' text value with the zero value of the destination cells forces Excel to convert the text value to a number.
• To write a formula in one worksheet that references a value in another worksheet (perhaps in another workbook), have both worksheets open in Excel. Write the formula by pointing at the cells of interest -- type the equals sign, then just navigate your way to the other sheet as needed, click on the cell to be used in the calculation, then continue typing your formula, tap [Enter] when you're finished entering the formula.
• Electronic Post-It notes might be useful to make comments on your worksheet, Insert | Comment. For more information see: http://www.cof.orst.edu/net/software/office/tips/#Comments
  Options for printing comments with the sheet are in: File | Page Setup | Sheet | Comments option dropdown
• Data | Text to Columns is a useful tool for separating into multiple columns text that is currently contained in one column. For example, maybe you have social security numbers in column A and you just need the last four digits of the SSN. Data | Text to Columns would let you separate the SSN into two columns.
• The Wizard used in Data | Text to Columns is the same tool you have available for opening text files you want to import into Excel. File | Open with some text file will bring you to Step 1 of the Data Import Wizard.
• Sometimes codes used in data entry need to be sorted in some order that is not alphabetical or numeric. You can define a custom sort order in Tools | Options | Custom Lists. Rather than Adding the list, the easiest way to enter the values is to Import list from cells. To use your list, Data | Sort | Options button has a dropdown where you can choose your list for the sort order.