Document Sample
					                                                          EXCEL TIPS and TRICKS
                                                               ED CRANE

            (LAST UPDATE, FEB 15 2008)
   This was intentionally created in Excel so that you could open it up on your computer and use it as a scratch pad
   to try out the tips as you read them. You might want to save your marked up copy under another name,
   so as not to screw up the original, though you can always get a new one from the internet.

   1a) Do you see what's called the "formula bar" just above the column names (A, B, C, etc)?
   It looks like this:

   When you click on a cell with something in it, the contents should show up just above the column letters.
   This is the default, but sometimes it gets turned off.
   If not, go to "tools/options/view" and make sure the "formula bar" is checked.
   This is the easiest place to edit things. You can also double click on a cell and edit right in the cell.


   1b) On the same tools/options/view screen you can turn on and off the gridlines and change their color.
   …but this does not effect printing.

   1c) To print gridlines or not, go to "file/page setup/sheet", and check or clear "gridlines box" in the print section.
   Actually "page set up" is a very important set of screens for printing. "Page set up" controls all aspects of printing
   spreadsheets. Printing is not one of Excel's better features.
   Click "print preview" at any time to see how you are doing.


   2a) Here's a tip that I never saw in the books.
   Ever have some text that will not fit in a column, and can't spill over because the other columns are occupied?
   Example:                                              a column, and can't spill over because the other columns are occupied?
                Ever have some text that will not fit in123      456        789
     Click on cell C44. The text is there, just truncated by the data in the next column.
   Well, you can force the row height to expand to accommodate the text.

                   Ever have some text that
                    will not fit in a column, and
                   can't spill over because the
   Example:         other columns are occupied?       123      456      789

02ecf159-4101-4216-a124-0c6e79a01ed7.xls, SHEET: EXCEL TIPs             Page 1 Of 8                                               Printed on 2/26/2012
                                                            EXCEL TIPS and TRICKS
                                                                 ED CRANE

                 Type what you want on the first
                 Hold down "alt" key and press
                 Continue typing
                 Do the alt/enter as needed
                 for additiona llines.                 123       456         789
   You may want to adjust the column width (drag the line between C and D, for example to effect the width of col C)
   …Or, left click, then right click on the column C designator, and select "column width"
   Don't like how it looks? Right click on the cell, select "format cells/alignment", and make changes.
                 Type what you want on the first
                  Hold down "alt" key and press
                          Continue typing
                     Do the alt/enter as needed
   centered->           for additiona llines.          123       456         789

   2b) Here's a better way if you have a lot of cells like this. (also works for just one cell).
   Select all the cells (left click on the first cell, drag to the last cell while holding the mouse key down). Let go.
   Point to the area selected and right click
   select "format cells/alignment" and check "wrap text"
   This will pre-condition the cells to adjust the row height as you type.
   TIP: you use the same process to format cells for almost anything you can imagine. Click on the
   tabs at the top of the format cell window to see what's available.

   You probably know this by now?. Right click on almost anything anywhere in any MS program and you
   get a "context menu" that lists the most common things you can do to the object selected.
   This is one of the more powerful MS features!

   Ever get a cell all formated the way you want, and then realize that you would like another cell(s)
   to look the same way, but don't want to go thru all the steps (some of which you don’t even remember?)
   Well, Bill gave us an easy way…it's called the format painter. There should a be symbol in your standard tool bar
   that looks like a paint brush.


   Left click on the first cell (the formated one) to select it, left click the paint brush. Move back to the body
   of the spreadsheet. The pointer will change to a paint brush (that Bill's a clever guy, eh?)
   Then left click the new cell to be formated, and presto, all your formating has been copied to the new cell.
   Cool, eh?
   Press the "escape" key at any time to abort the process and get your pointer back.
   Got a lot of cells to format the same way? Double click the paint brush. It will make the format avaible to
   use multiple times, until you press the escape key.

   TIP: this works just as well for whole columns or rows. Copy the format of one, paint it to the other.
   Click on the letters for columns, numbers for rows.

   Don't see a tool bar you need? Right click anywhere on a blank space in the tool bar area,
   and make sure the tool bar you want is checked.
   If you get a free standing toolbar in the middle of your spreasdheet, and you want to keep it open,

02ecf159-4101-4216-a124-0c6e79a01ed7.xls, SHEET: EXCEL TIPs                 Page 2 Of 8                                   Printed on 2/26/2012
                                                            EXCEL TIPS and TRICKS
                                                                 ED CRANE

   drag it to the tool bar area.
   It will be there next time you open excel.

   Things in your printout not paged the way you want? Click on "view/page break preview"
   You can drag and drop page breaks to organize your printout.

    Trouble finding something in your spreadsheet. You know Bill Gates is in your phone list somewhere
   but you just can't find him. Not to worry. In your standard tool bar there is a pair of binoculars.
   Click on them. This is also handy if you mispelled Gatez 15 times on your spreadsheet. You can fix them
   all at once.


    Three ways to effect column width...
   a) point to and drag the line between col B and col C to increase/decrease the column width

   b) Click on B at the top of col B to select the entire column. Click "format/column/width"
   and enter a number to change width. handy if you are doing more than one col
   and want to set them all to say "20"

   c) Select the cell that has the thing in it that is too big. Click "format/column width/autofit selection".
    Be careful you picked the biggest text, or you will make the col too small for other stuff.
   Can be dangerous in a large SS. Something 10 pages down suddenly won't fit in it's cell anymore.

   If you ever plan to print a spreadsheet don't even think about it until you have mastered "pageset up"
   a) go to file/page set up (or use page setup in the tool bar)
   b) click on the "header/footer" tab
   Note the 4 tabs across the top. These control all aspects of printing spreadsheets. Without them you are lost.

   3) click on custom header or custom footer

02ecf159-4101-4216-a124-0c6e79a01ed7.xls, SHEET: EXCEL TIPs                 Page 3 Of 8                             Printed on 2/26/2012
                                                          EXCEL TIPS and TRICKS
                                                               ED CRANE

   Try them out. They won't bite!
   The technique is to try something, then click the "preview" button. Each tab has a preview button

   c) Headers and Footers
   these are areas at the top and bottom of the printed page that are common to all pages.
   Click on the "header/footer" tab

   See the three windows? those are the three sections (1/3 each) of the footer. See the row of buttons above?
   Those are functions you can use to add stuff to the windows. Or you can just click in a box and type.
   Use the "A" button to alter font.

   the other buttons:
   page # , page_of [so you would type "page (click 2nd button) of (click 3rd button)] in one of the boxes. This is dynamic.
    If you add 50 more pages, it will adjust itself accordingly.
   If you screw around in page view or add page breaks, this will adjust itself. WOW!

   buttons 4 & 5 are date and time. you would type in a box [this page printed on (press the date function) at
    (press the time function)] caution: this is no good for issue control of an xls file because it's always today's date.
   I use it when I'm going to make a PDF. It gives me the capture date of the PDF. It's a soft issue control for PDF's.
02ecf159-4101-4216-a124-0c6e79a01ed7.xls, SHEET: EXCEL TIPs               Page 4 Of 8                                          Printed on 2/26/2012
                                                           EXCEL TIPS and TRICKS
                                                                ED CRANE

   If someone for example mails me a flyer off a website I can tell at a glance which version of the PDF i'm looking at.
    People download these PDFs once and then print a zillion copies.
    Three years later they are still mailing in forms that are now way out of date. This helps to control that.

   next three buttons are dynamic path, spreadsheet name, and sheet locators. If you rename the file and
   move it this will change. If you send it to me and I save it, this will be my information not
    I like this because they give you a reference on the printed page of where the file is located on YOUR computer,
   it's name, and even the sheet name in the spreadsheet. Saved my ass a few times when I pulled out an old spreadsheet
   printout and had no idea what I had named it or where it might be stored.

   last two buttons are for inserting and formatting pictures in the footer.

   while in the preview window, click margins. click and drag margin bars to neatly space header and footer info.
   (you need a math degree to use the margins tab in page set up. I only use this tab for the hor and vert centering feature.)
   Play with'll see how easy it is.


   Open up a new workbook. Right click on the tab for the first sheet
   You can do all sorts of things. One important one is to create a copy

   Be sure to check the copy box, tell it where you want to put the copied sheet (it can be in any other spreasheet that is open
   Say where you want it (Before sheet what?) Click OK
   You'll get this:

   Great if you are doing something like a budget for 12 months. Just name the first tab Jan, then copy it to Jan (2),
   then rename Jan(2) to Feb, etc

   11) Gridlines vs Borders, or how to view/print what and when?
   Excel has both grid lines and borders. They are somewhat related, but can get tricky when printing.
   Perhaps on your screen your sheet has all these nice boxes around everything, but when you print it

02ecf159-4101-4216-a124-0c6e79a01ed7.xls, SHEET: EXCEL TIPs                Page 5 Of 8                                             Printed on 2/26/2012
                                                          EXCEL TIPS and TRICKS
                                                               ED CRANE

   the boxes don't print?
   Go to "Tools/Options". You will see this screen:

   Turn gridlines on and off by checking or unchecking this box. Then you can select the color of the
   gridlines you see on the screen. (this color will also effect the color of printable borders)
   Now, how to print these?
   Go to "file/page setup/sheet" and you will see this screen.
   You can check the box to print the gridlines (or clear it to not print the gridlines)

   But wait, You don't want boxes around each cell!
   Well, you need to learn about borders!!
   Your Excel tool bar should have a button that looks like this:

   This is your border dropdown box.
   Click on the arrow to see the border options

02ecf159-4101-4216-a124-0c6e79a01ed7.xls, SHEET: EXCEL TIPs            Page 6 Of 8                      Printed on 2/26/2012
                                                          EXCEL TIPS and TRICKS
                                                               ED CRANE

   Here's how borders work:
   -first, you must select the cell or cells ( it could be any number of cells)
   -these are the cells to apply the borders to
   -then click on the border icon in the tool bar, select the border style you
   like, and presto, you have borders
   -the big deal is that these borders are under your control and will always print
   You can also print gridlines (or not) at the same time.
   -to a great extent, borders and grid lines are mutually exclusive
   However, the color you select for gridlines will be the color of your borders
   If you don't like this, set the grid line color to automatic.

   12) Numbering columns and rows
   Ever have a spreadheet that is a list of things and you have numbered the rows (or columns)?
   Let's say you have listed 10 names, as shown below:
                1 bob
                2 cindy
                3 dave
                4 ed
                5 joe
                6 maddog
                7 pinky
                8 ralph
                9 salley
              10 sam
   You have then in alphabetical order, and all is well. But, now you need to add "henry"
   First thing to do is to add a new line above Joe. Point to the row, #411, that Joe is on.
   Right click, click insert, and type henry on the new line.

   But, now your numbering is all messed up. What to do?
   You could retype them all but that would be dumb.
   Excel has the uncanny ability to EXTRAPOLATE as series of numbers if
   given as few as three numbers to work with. Let's use that feature.
   Here' our list again, with henry added
               1 bob
               2 cindy
               3 dave
               4 ed
               5 joe
               6 maddog
               7 pinky
               8 ralph
               9 salley
             10 sam

   Now select the three numbers above the blank next to henry
   Point to "2"
   Hold down your left mouse buton and drag a box around 2, 3, & 4

02ecf159-4101-4216-a124-0c6e79a01ed7.xls, SHEET: EXCEL TIPs           Page 7 Of 8                 Printed on 2/26/2012
                                                           EXCEL TIPS and TRICKS
                                                                ED CRANE

   Notice the little box in the lower right hand corner? That's the "drag" handle that
   allows you to drag this box down.
   Point to the little box, hold down your LEFT mouse button, and drag the box down
   to the line Sam is on. Release the mouse button and your numbers have been repaired!

   Cool, eh?
   Tip 1: if you have a big spreadsheet the dragging can be awkward.
   Select the three numbers
   hold down your shift key
   Use your ARROW or PAGE DOWN keys to move rapidly to the end of the spreadsheet
   Tip 2: Another clever way to do this is to use a formula
   Let's say the first box in your list is cell C2. Type the number 1 in that box
   In the box below that type "=C2+1", without the quotes
   Copy that formula to all the remaining boxes.
   Now after you have inserted your new line you only have to drag the box above the new
    line down two cells.

   13) How to use the Find and Replace command
   "Find and Replace" in excel is pretty powerful. Check out the options.
   It defaults to not being case sensitive, but you can change that. It does have
   one quirk. Say you are looking at one sheet in a work book and open up
   Find and search for something. Hmm...not there, so you click on another tab
    and go to another sheet. Your "find" box is still there so you click
   "find all", but it still doesn't find what you are looking for, even though you know
   it's there. HERE'S THE QUIRK. "Find " is not sheet specific. Until you click
   on at least one cell in the new sheet the find command is still searching
   the original sheet for the data and it won't find it. So, click on any cell on
   the new sheet to "anchor" "find" to the new sheet and then click "find next".
    It it's on the new sheet "find" will now find it.

02ecf159-4101-4216-a124-0c6e79a01ed7.xls, SHEET: EXCEL TIPs                 Page 8 Of 8    Printed on 2/26/2012

Shared By: