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.

Excel Tips

Excel Tips

Look like the master of Excel with these easy tips

1. Freeze Panes. Have you ever scrolled through an excel sheet and wished that the column and row headings would scroll with you? They can. Simply go to View – Windows – Freeze Panes. It may take a few tries to get the exact result that you want. For further tips search Freeze Panes in the Help menu.

2. Landscape. It may sound silly but nothing is worse than printing out an excel sheet and expecting just 1 page and getting 2 or oh no 3. Most of the time if you change the page orientation to landscape many problems can be solved. Change the orientation by going to Page layout – Orientation – Landscape. Psssst, if you hit the print preview button and then click close, little dash lines appear where the page cutoffs are.

3. View Formulas. Ever wonder what formula is lurking in a cell? Press Ctrl and ~ at the same time. Excel will display the cell formulas in place of the results. But don’t worry the actual numbers are still there, just press the same combination again and excel will once again hide the formulas.

4. Wrap Text. Once a little known feature, the Microsoft Gods have now granted it premium real estate on the home tab in excel 2007. Wrap text makes all the text that you have typed in a given cell visible, usually by increasing the height of the cell. In older versions of excel the wrap text feature is still available select the cell you would like to wrap text in – right click – select format cells – then select the alignment tab – check the wrap text box.

5. Paste Special. Ok so there’s paste and this other thing called paste special which one do I use? Well, use paste when you just want to well paste. But let’s say that not only do you want to paste but you want the formula or the formatting of the cell to paste too. Well you could click paste and then reformat the cell or you could click paste special and have excel do the formatting automatically. To get to paste special you have to right click on the cell, it’s not one of the buttons at the top of the page.