I'm David and this is my blog.


Monday, April 24, 2006

Word and Excel Tricks

I often help people do things in Word and Excel. I really love Excel...must be my desire to turn everything into a number so I can graph it and discover trends. In any case here are a few tips that you might be able to use to speed up your Word and Excel usage:

* Excel: Probably my favorite trick of all time with Excel is to use the $ in formulas. No, not because it creates money out of thin air. It doesn't. What it does do however is make it possible to write a formula once and then drag that formula(autofill) without the reference changing. This is great if you have a list of people's names and balances and want to subtract one number
from each person's balance the number in one cell. Just make sure the reference to that number looks like $B$12.

* Word: Show the paragraph symbol. So many times when you're wrestling with some particular layout in a word document, it's so hard because you can't actually see that that whitespace is a combination of tabs, spaces, and indents. With the paragraph symbol(it's the backwards P-looking-thing in the standard toolbar) you'll be able to see just how your layout was "composed" before.

* Both: If you're working on a document or file of any importance, you may need to make changes but still want to be able to return to before you made those changes. In the programming world, we'd use something like CVS or Subversion but for simplicity I just number my files with the version number. It's not elegant in an architecture astronaut kind of way but it works and non-programmers can use it! An example: Dave's File.doc is the current version. My previous 2 versions are named Dave's File.1.doc and Dave's File.2.doc.

* Excel: Resizing all the rows or columns. This is easy to do once you know...just click on the little empty block above A and to the left of 1 in the column and row headings of any sheet. Voila, the whole sheet is selected. With this selection done, it's easy to resize by dragging the boundary between for example 1 and 2 or A and B. Just look for an icon like this "<-|->" before you start dragging.

* Excel: Cutting and Pasting. Lots of times you need to cut and paste a set of rows and if you don't select the whole row, it can turn into a mess. To make this simpler, just click on a row heading like 3 on the left side of the sheet and drag it down to another heading(like 10). All the cells in all the rows from 3 to 10 should then be selected. Right click in the selected area and choose cut. Then click on a cell right below where you want to paste those rows. Right click and choose Insert Cut Cells. Voila! This works for columns too.

I could go on for days with these but I'm trying to keep this blog interesting. I know I've probably failed at that long ago but if I can offer any concession, it's that you can email me if you have a specific question.

2 Comments:

  • I'M LOOKING FOR A WAY TO AUTOFILL DATA FROM ONE AREA OF A DOCUMENT TO OTHER AREAS OF THE DOCUMENT WHERE THE SAME DATA IS NEEDED SUCH AS A NAME ON A CONTRACT. MUCH LIKE ONE DOES IN EXCEL WITH THE =CELL.

    By Anonymous Anonymous, At 7/31/07 7:35 AM  

  • Well anonymous, you can probably do this by using the = for the first cell in your range. Then drag the little black square in the lower right hand corner of that cell down or to the right to autofill the formula. Excel is smart enough to update the formula so each cell maps to another cell the same distance away.

    If you don't want it to change the formula as you drag it, you can use $ in the formula. For example $A5 will stop Excel from changing the A to another letter and $B$6 won't allow excel to change either during an autofill operation.

    In the future, please lay off the caps lock key. thanks.

    By Blogger David, At 7/31/07 9:47 AM  

Post a Comment



<< Home