Excel Tips and Tricks

(These tips should work with most versions of Excel)


Moving columns:

- Select the Column or Row you want to move        

- Move your mouse pointer to the edge of the selection until it changes from a cross to a regular pointer arrow. Note: The mouse pointer must be on one of the highlighted cells and not on the row or column headings.

- Hold down the shift key and click and drag the column/row to the new position. A faint I bar that runs the entire length of the row or column, along with a box indicating where the new row or column appears.

- Release the mouse button and then leave the SHIFT button, and your row or column is moved.

(You can do the same thing in OpenOffice Calc - except you don't hold down the Shift key. Just highlight the entire column, and drag using any cell that's highlighted. BUT in OpenOffice it replaces any column you "land on" - so be careful. You need to insert a blank column in the desired location before dragging. In Excel, it automatically inserts a new column to hold the data you've dragged.)

Combining data in cells:

-          If you need to merge data from one cell to another for instance, you need to combine a "lastname, firstname"

o       A1&","&B1

-          If need to get LastNameFirstInitial and you have LastName in cell A1 with FirstName in cell B1, you can use this example. In the cell where you want the data to appear enter this function:

o       =concatenate(A1,left(B1,1))

Formula Errors
Whenever typing one of Excels functions (especially nested ones) into a cell always use lower case. This way when you push Enter Excel will capitalize only the names of the functions you have entered correctly.

Personal Help
To add your own text to any of the Office Assistants help files, push F1, enter your question then open the file. Go to Options>Annotate and type in your own text then click OK. You will now notice a paperclip symbol next to the heading, this will let you know that you have added your own Help in a way you will understand.

Auto Fill
To fill a series across columns or down rows type January or Jan in any cell and place your mouse pointer over the bottom right corner of the cell (Fill handle) until the mouser pointer changes to a small black cross. Left click and drag down or across. This can also be done with Numbers, Weekdays, Quarters or any text that ends in a number e.g. Day1.

Excess pages when you print a spreadsheet
Somehow I have a singe page worksheet, but when I print it I get 3 extra blank pages. How do I get rid of these blank pages?
Here are the steps:
- Highlight the desired print area, then
- File-Print area-Set print area

Helpful web sites: