Excel Tips

Spend a bit of your time to learn some small skills, save a lot of your time (and others) in return.

Basic Level

Know where the hidden jewels are.

• Most useful shortcuts
  • F2: Edit text in the box
  • Ctrl+z: Undo
  • Ctrl+PageUp/PageDown: scroll through worksheets
  • Shift+Space: Select Row
  • Ctrl+Space: Select Column
  • Ctrl+Shift+ + : Insert Row/Column depends on Row/Column selection
  • Ctrl+Shift+ - : Insert Row/Column depends on Row/Column selection
  • Ctrl+Shift+8: Select a range of data
• View your data
  • Keep your headlines: "Split" and "Freeze panes" in "Windows" menu of Excel
  • Highlight important values: "Format"->"Conditional Formatting..."
• Manage your data
  • Use "A01", "A02" rather than "A1", "A2" when you have a list of values so that you can sort later. Now when you have "A10", it will be put not next to "A1" but to "A09".
• Simple calc.
  • On status bar, some simple function available for selection: Ave. Count, Max, Min, Sum
• Cell Dragging
  • Automatic numbering

Intermediate level

Make full use of available functions in Excel

• Be familiar with the Data->Filter->AutoFilter menu item
• Be familiar with the Format->Conditional Formatting... menu item
• Be familiar with the VLOOKUP & HLOOKUP functions
• Functions in the Bioinformatics Tool from the Informatics group or check with Yufeng for a bug-fix and updated version.

Advanced level

Write your own macros and functions

• List worksheets and make links in an Excel workbook

The following VBA codes will generate a list of worksheets in a worksheet named "Index" in the same workbook.

      Sub listWorksheets()
      Dim ws As Worksheet
      r = 2
      rc = "A2"
      For Each ws In ActiveWorkbook.Worksheets
          Worksheets("Index").Range(rc).Select
          Worksheets("Index").Range(rc).Value = ws.Name
          ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
              ws.Name & "!A1", TextToDisplay:=ws.Name
          r = r + 1
          rc = "A" & r
      Next
      End Sub

To run:

1. Insert the code in the workbook: Excel->Tools->Macro->Visual Basic Editor, find the workbook.
2. Copy and paste the code into module1
3. Go back to the workboot and make sure there is a worksheet named "Index"
4. Excel->Tools->Macro->Macros or Alt-F8
5. Run listWorksheets