Useful Excel Tips

Have been using Microsoft Excel 2007 for my lab report. Found some annoyances while working through the report and I Googled for various solutions/workarounds. This list of tips should be rather useful to those basic Excel users like me out there:

  1. Shifting Cell References
    1. One usually useful but occasionally annoying aspect of Excel is that the program will sometimes change a formula’s cell references when you copy the formula to another cell. Here’s the secret: the formula changed when you copied it because you used relative references (which can change) in the original formula instead of absolute references (which can’t change).
    2. For example, if you copied the formula =SUM(F3:F14) from cell F15 to E15, Excel would change the formula to =SUM(E3:E14). If, however, you wrote the formula in cell F15 as =SUM($F$3:$F$14), Excel would copy the formula as =SUM($F$3:$F$14) no matter where you moved it. The dollar sign in front of a row or column designator indicates that the reference is an absolute reference, which should not change when the formula is copied.
    3. You can mix absolute and relative references in a cell designation, so (for example) the rows referenced could change but the columns couldn’t. Some of the possibilities:
      1. $A$1 keeps both the row and column constant.
      2. $A1 keeps the column constant but allows the row to vary.
      3. A$1 keeps the row constant but allows the column to vary.
      4. A1 allows both the row and column to vary.
  2. Function of F2 key
  1. From Excel Help: “Edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion point into the Formula Bar when editing in a cell is turned off.”
  2. Basically pressing F2 key when a cell is selected allows you to edit the contents without having to double click on it
  • Finding Intercepts of 2 Graphs
    1. Short answer: Impossible.
    2. Long answer: Use trendlines to approximate the equations of the curves. One may need to switch between the various trendline options such as Linear, Polynomial, Logarithm to find the closest fit to the original curve. Once the equations are obtained, hit Graphmatica. Key in the equations and let the program help you find the intercepts.
    1. Incidentally Graphmatica also helps one to find gradients about a point too. Totally useful for finding the gradient of a curve from those lab results.

    That’s all for now. Now pardon me as I give my brains/eyes/wrists a hard-earned rest. Damn those long lab reports. Grr~


    2 thoughts on “Useful Excel Tips

    Leave a Reply

    Fill in your details below or click an icon to log in: Logo

    You are commenting using your account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s