Excel: Formulas Simplified. But First Some Basics.

Posted by Margaret Daisley

If you’re like me, and like Tom Mighell of the InterAlia blog, we’re “just no good at creating formulas.” I was trying to use the “ranking” formula yesterday (from memory) and it took five tries before I finally got it right: =rank(B2,$B$2:$B$101). Summing things up?  No problem!  Excel has that handy Sigma sign at the top of the Excel page – though it sometimes guesses the wrong range. (No! I wanted to add those rows, not those columns!)Mighell also says, “No problem!", and offers this link to Microsoft’s handy page of commonly used Excel formulas, with straight-forward instructions on how to create them.

Still, in my experience, unless you are the type of person who uses Excel daily – not just to calculate, but to organize bodies of information – you need to start with some basic basics, such as:

• Most important: Know where the "Undo" button is at the top of the page, with the standard toolbars. You will need to use it often. A shortcut trick is to use Ctrl+Z.
• Typing/editing inside of a cell:  You type something in a cell, a number or text. But when you go back to edit it, if you begin typing in the same cell, everything will be deleted. Hit the "Undo" button. Notice that the number/text also appears in the open bar area near the top of the page, preceded by the symbol/letters "fx". This is where you do the editing, by positioning your cursor on that line.
• Sorting: Never, ever highlight one column or one row of information for sorting -- unless you only have one column or one row to begin with. If you only highlight one row or column, and then sort, all your other data will remain in one place, making your whole data set useless as it won't match up. Instead, choose "Data" from the menu bar, then "Sort" from the next menu. Notice that ALL your data will be highlighted in the background while you perform this function. The menu will prompt you to choose which column you want to sort. You will also get two more choices for sorting order (for example, you want to sort by revenue, and then by firm name in case there is a tie). You also have the choice of sorting in ascending/descending order.
• Right clicking (for PC users) on anything is your friend…you can format your cells, copy, paste, and much more If you need to create a “series” such as January through December as headers to your sheet, you can use the fill handle to do this very fast, instead of typing each month out in a different cell.  Just type January in the first cell and hit enter to accept it.  Next, click back on that cell and move your cursor so that you see a small black cross in the lower-right corner of the cell.  Last, simply click and drag across or down to create your series, you will see Excel filling in the months as you go across.  Try it for other series as well, like numbers, or repeated groupings.
• Remember you can’t always just copy and paste, you may have to “Paste Special” (right click on the new cell, choose past special).  Sometimes you need to copy values in a column or row to a new spot, if you just paste, you may lose the formulas, or you may want just capture the values and not the formulas.  Paste Special will let you do this