More Excel Tips

6. Lists. So you need a list of numbers from 1 to 100. There has to be an easier way then typing them all right? Of course there is! But there is one rule; the numbers must have the same increments. That is if you want a list from 1 to 100 then you have to want all 100 numbers or all multiples of 10, not the first 10 numbers and then multiples of 10. Excel cannot, yet, read your mind. With that being said, to get the list from 1 to 100, type 1,2,3 each in separate cells, highlight all 3 cells, click on the small square in the lower left corner of the last cell, then drag until you have filled all intended cells. The numbers appear as if by magic. This also works for months of the year.

7. Merge and Center. Let’s say you want to put a header at the top of your excel table. Do you just put the text in the middle and hope that it prints out right? No. Try typing the text in the left most cell at the top of the table, highlight all the cells that make up the top of the table, click the merge and center button on the toolbar (just to the left of the dollar sign). Excel will automatically make just one large cell and center your text in the middle. But oops how do I undo it? Press the merge and center button again and it will return to the way it was before.

8. Shortcuts. My favorite shortcuts are as follows

Ctrl + Z The shortcut for the undo button, p.s. this works in almost any program.

Ctrl + C The shortcut for copy. Highlight the text press ctrl + c

Ctrl +V The shortcut for paste. Click where you want the text to past then hit ctrl + v

Alt + Tab This allows you to toggle to a different window. For example if you have a Word document open and you want to see the excel document you have open in the start bar press alt tab and then use the tab button to move between the programs.

9. Find and Replace. Is a wonderful tool and is best illustrated with an example. Let’s say that you abbreviated the name of a customer to ABC now you want to go back and write out the full name. By going to Edit then Replace and typing in first the ABC and then the full name in the appropriate boxes Excel will search the workbook and change each one.

10. Formulas. I know that they can be confusing, just when you think you have every piece entered right excel comes back and says nope not good enough. Below are some common formula errors and solutions.

Start every formula (or function for math types) with an equal sign. This lets excel know that you are indeed using a formula.

Match all open and closed parentheses. This ensures that your function will work properly. Anyone remember the order of operations song?

Use a colin (: ) to enter a range of cells. For example say you would like to sum all the numbers in column A from A1 to A15 the formula in A16 would look like this
=SUM(A1:A15)

One last thing if there is a green triangle in the upper left corner of the cell that indicates that you have a formula error in that cell.