I am currently a student at UofL, and it is typical that my professors impinge upon my ability to ignore certain pieces of software, specifically Microsoft Excel.  Almost everyone who works in an office uses (or could use) Excel, and some of us have a bit more time to learn the program than others (and some of us are forced to learn it against our will).  I primarily use Excel to manage my finances, to work homework problems, and to write macros.  Since I was forced to learn Excel, I am by no means an expert; I do, however, remember a lot of the things that were the most frustrating to learn on my own.  I use Excel 2010.

Basic Excel Tips

1. Double click a row or column’s border to automatically size it.  You can select multiple rows or multiple columns and double click the border of one of these (click at the column or row label border at the top or left of the cells you wish to change).
   

   Select the top right border to re-size a column.

2. Hit enter to exit a cell and select the cell beneath, or hit tab to exit a cell and select the cell to the right.
3. 

   Cell Opened for Editing

   To subscript or superscript text, you have to double click the cell to edit it, highlight the text you want to sub/superscript, and open the font panel (ctr+shift+F) to perform this.  There isn’t an easier way (in Word there are very convenient buttons), and current extensions/macros you can download (or make) do a poor job.  I looked for hours…

4. Pivot tables are an excellent way to display large amounts of tabular data, check them out.

Formula Tips

1. The formula bar can be dragged down if you need more space.
2. 

   Cell Selected

   If you type a formula in one cell, and you want to move the formula to other cells, Excel has some powerful features to do that.  When you select a cell with anything entered into it, you will notice a small black square at the bottom right of the cell border.  You can click and drag that to copy your formula.

3. The F4 key – When you copy a cell as described in step 2, the cell references will change.  When editing a formula, you can lock a cell that is referenced in the formula bar by putting your cursor on the cell reference in question, and hitting F4.  If you hit F4 multiple times, you can lock the cell reference with respect to either its row or column, or neither.  A locked cell reference has a dollar sign ($) in front of its row and column components.  (Cell A1 locked would be $A$1, which locks the cell’s row and column.  $A1 would just lock the column, and A$1 would just lock the row.)
4. Functions.  Most people don’t feel comfortable using programming statements (functions) in the formula bar in Excel.  If you have ever done any programming, formula statements are super easy to pick up.  If not, they can be very confusing. Functions can do things like sum a large range of cells (so you could type “=sum(A1:A10)” instead of “=A1+A2+A3+A4…”).  Functions allow you to do averages, conditional statements (i.e. if cell A1>5, cell A2 should say: “yes”, otherwise: “no”).  If you have to use Excel a lot, they are super useful.
   

   The Excel Sum Function

5. You can make a formula an array formula by hitting (ctr+shift+ent), exiting the cell.  You need to do that every time you exit editing the cell to keep the formula as an array formula (you will notice {brackets} around your formula).  If you don’t know what an array formula is, and you want to, check out this link
   

   Excel Summation vs. Array Summation vs. Sum Product (equations in D3:D5)

6. Google it.  Sometimes specific functions you need are hard to find.  Just yesterday I learned how to use the Index function.  The key to learning Excel (or any software, typically), is Google.  If you don’t know how to do it, or what you even need, ask the internet and odds are, someone else has asked the question online and another person has answered it.

Macros (VBA)

Formulas in excel can do a lot, but they can’t do everything.  Formulas have a number of limitations imposed on them; but Excel doesn’t let you down like that.  I don’t have an exhaustive knowledge of Excel formulas, but the limitations imposed on them are frequently a bit too much for me.  What if I need to run one formula 10,000 times?  Or import data from an Access database regularly?  What if I want to hit a button and import a list of files from a folder?  Macros can do that.  Excel has it’s own programming language, all of Microsoft Office does, called VBA (Visual Basic for Applications).  I can’t really give you a tutorial on that, though there are plenty on the internet.  They aren’t too hard to learn on your own since there are plenty of tutorials; however, if you have a hard time with the formula bar, macros might not be for you.

To start with Macros, you will need to enable developer options on your ribbon, and then open the VBA editor.  You can then type your script, and save it.  You can call the script from the developer tab on the ribbon, or by assigning the macro to a button.  The most frustrating part of learning how to do Macros for me was getting into the editor, learning the script was fairly easy.  If you need help opening the developer ribbon, try Google.