Excel Tips

 
 
TIP #1 - USING SHORTCUT KEYS (Versions 97, 2000, 2002)

Lots of people using Excel are constantly using the mouse to move up to the formula bar (just below the toolbar buttons) simply to edit a formula.

Here are 2 shortcuts to edit a cell without going into the formula bar area:

Method #1

  • To edit the selected cell        F2
  • Finish editing cell                 ENTER
  • Cancel edit (don't save changes)  ESC

Method #2

  • Doubleclick in cell you want to edit
    ENTER when finished (or ESC to cancel)


TIP #2 - GET ON THE GRID (Versions 7.0, 97, 2000)

If you link data in Word as an Excel 7.0 worksheet, you won't see any gridlines. However, if you make some changes to the linked worksheet in Excel, the gridlines will appear in Word. This happens because Excel creates a default printer (not screen) metafile and Word therefore displays the sheet as it would print. If Excel is set to print gridlines, then they will show in Word. To get around the problem, you can switch to Excel after you establish the link and retype a cell's contents. Word will now display it correctly. Alternately, you could set Excel to print the gridlines. To do this, choose File|Page Setup. Choose the Sheet tab and select Gridlines. Click OK.

 



TIP #3 - QUICK HIGHLIGHTING TIPS (Versions 7.0, 97, 2000 & 2002)

  • Ctrl + Spacebar  (Select entire column) 
  • Shift + Spacebar  (Select entire row) 


TIP #4 - SAVE YOURSELF A LOT OF TIME AND TROUBLE (All Versions, although version 2002 requires a specific AutoSave AddIn installed).

AutoSave is a handy Excel feature--one that saves many of us from disaster. Although Excel doesn't use AutoSave by default, you can easily add it.

  • Choose Tools/Add Ins
  • Check box that says AutoSave and then click OK

After you select AutoSave, the command will appear in the TOOLS menu. Choose Tools/AutoSave now and you can tell Excel how often you want to invoke AutoSave. After you enter the time period of your choice, click OK.

 



TIP #5 - AUTOMATICALLY DISPLAY PAGE BREAKS (Versions 5.0, 7.0)

Automatically display page breaks and get a better idea of how your worksheet will print out.

  • TOOLS/Options
  • Select the VIEW Tab
  • Under the Windows Options Box, select the Page Breaks option
  • OK

You will notice a dotted line on your worksheets where the pagebreaks will be.
NOTE: In Excel 97 & 2000 & 2002, you VIEW/Page Break Preview
 



TIP #6 - SET THE PRINT AREA QUICKLY AT THE CLICK OF A BUTTON:(Versions 5.0, 7.0 & Mac)

  • From the menu bar, select VIEW/Toolbars 
  • Click the [Customize] button 
  • Select the Print Area button (looks like a printer with 2 lines top/left - it will tell you what the button is if you click on it) then drag it onto YOUR toolbar area 
  • Close the Customize dialog box 

When you're ready to print, select the area you wish to set as the print area, Then click the Print Area button on the toolbar.

To DELETE, simply click on the RESET button under VIEW/Toolbars. WARNING...  this will delete all the buttons that you've created with the customize feature.
NOTE:  In Excel 97/2000/2002, highlight area, FILE/Print Area/Set Print Area
 



TIP #7 - BREAKING UP IS HARD TO DO: (All Versions)

To break up a long cell entry into separate lines, position the cursor where you want a new line to start and press Alt+Enter.  Excel will expand the row to accommodate wrapping lines of text.  Press Enter to complete the entry.

 



TIP #8 - INSERT AN AUTO SUM (Versions 97, 2000, 2002)

Insert an AutoSum  Alt = (equal sign)

Excel will try to guess what cells you want to total up, either the cells immediately above or to the left - but you can change the range of cells to whatever you need.

 



TIP #9 - JUMP AROUND IN PRINT (Versions 7.0 and 97)

When you're in Print Preview and you have zoomed in on the page, use the arrow keys to move around the page.

If you're viewing the entire page when you've zoomed out, you can use the following shortcuts to move between pages:

  • Ctrl + PageUp (To go to previous page)
  • Ctrl + PageDown (To go to next page)
  • Ctrl + UpArrow  (To go to first page)
  • Ctrl + DownArrow  (To go to last page) 

NOTE: In Excel 2000 & 2002, it's just PageUp and Page Down NOT CTRL
 



TIP #10 - YOU GOTTA HAVE ART (Version 7.0)

Would you like to add a picture to the bars of an Excel chart  to set them off?
Try this, create a chart, then click away from the chart

Choose INSERT/Object/Microsoft ClipArt

Choose the figure that you think will look best and click OK to insert it into Excel.

Now choose EDIT/Cut, then double-click one of the bars and now choose EDIT/Paste, and
the image will appear in the bar.

NOTE: This does not work in Excel 2000 & 2002. For these versions, you click on the bar to select it and then INSERT/Picture/Clipart (or use clipart button).
 



TIP #11 -  AUTOMATIC RANGE NAMES USED IN FORMULAS (Versions 97, 2000, 2002)

Named ranges let you calculate the total for all cells in a range with a formula such as 

  • SUM(Products) instead of a cryptic 
  • SUM(B4:B12). 

With Excel 97, you can often get the benefit of named ranges without having to define them. 

To see how these natural-language formulas work, enter the name Products for Column C and Price for column D (highlight the column and then type the name in the Range name text area at the left of the fx symbol). Then enter the formula =Products*Price in column E cells. Now, as you fill in numbers in the Products and Price columns, Excel automatically calculates totals in the third column. 
 



TIP #12 - BRING EXCEL DATA INTO ACCESS (Versions 7.0 & 97, 2000, 2002)

To bring data from a Microsoft Excel spreadsheet into Access, first open both programs. In Access, open the database and select tables from the list of database objects. Then minimize Access.

Highlight the data in Excel that you want to import into Access.  Point to the edge of the highlighted data, then ctrl/drag to the Taskbar (dragging without CTRL key held down will MOVE the data rather than copy) and hold above the Access icon (DO NOT release the mouse button) until Access opens up.

Access will automatically import the data.

In version 2002, keep the left mouse button down and drag your mouse to the tables list background, then let go.
 



TIP # 13 - GET ATTENTION WITH EXPLODING CHARTS (Version 7.0)

If you want to place widely varying Excel data into a pie, doughnut, or three-dimensional pie chart, you'll find that it can be hard to see the smallest segment. When you insert a chart in a worksheet, you can choose an exploded pie chart if you like.

To insert a chart, select the data and choose INSERT/Chart. Use the mouse to draw the chart's area. Now follow the chart wizard and choose a pie-type chart. When the wizard offers different pie chart styles, choose one of the exploded views. If you choose the view that shows one slice separated from the rest of the pie chart, that slice will be the smallest of the group. This  makes the data easier for viewers to see.

Versions 2000 & 2002 allow you to choose from among various chart styles including those that provide "subcharts" of user defined values.
 



TIP # 14 - SHORTCUT KEYS (Versions 2000 & 2002)

Shortcut keys abound in Excel.  Following are a few that should be useful
for your daily tasks. (Keystrokes are the same in Windows and Mac unless
noted.)

  • F4 (Windows) - Repeats the last action
  • Command/Y (Mac) - Repeats the last action
  • Ctrl/Shif/+~ - Applies the General number format
  • Ctrl/Shift/$ - Applies the Currency style (two decimal places)
  • Ctrl/Shift/% - Applies the Percentage format (no decimal places)
  • Ctrl/1 - Brings up the Format Cells dialog box
  • Ctrl/Shift/plus sign -  Inserts a blank cell
  • Ctrl/9 - Hides rows
  • Ctrl/Shift/( - Shows rows
  • Ctrl/0 [zero] - Hides columns
  • Ctrl/Shift/) - Shows columns


TIP #15 - SOCIAL SECURITY FORMAT (Versions 97, 2000, 2002)

If you've ever used the Social Security format that Excel offers, you may have run into this problem.  If the social security number that you're formatting ends with a zero, the format doesn't work. 

One common reason the Social Security format removes zeros is if you have Excel's default decimal place setting set to something other than 0. (If you're in finance or accounting, you may have set the default to 2 decimal places so that you could enter financial amounts without having to type a decimal every time.) 

So before you enter Social Security numbers, try this: 

  • TOOLS/Options
  • Click the EDIT TAB
  • Set Fixed Decimal Places to 0
  • Click OK


TIP #16 - QUICK-CLICK MOVEMENT(Versions 97, 2000, 2002)

Want to find out where a column ends or where the next blank cell is 
in the column? 

  1. Select a cell. 
  2. Double-click the bottom edge of the selected cell, and you're whisked to the last filled cell BELOW the selected cell in the current column. 

This trick works for rows, too. So, for example, if you want to find 
the last filled cell--or the next empty cell--in the current row: 

  1. Select a cell. 
  2. Double-click the right edge of the cell. 


TIP #17 - ADDING A TRENDLINE TO CHARTS (Versions 97, 2000, 2002)

You can add a trendline to a chart (bar, line, column, stock, or another 2D chart) - this is a away that you can focus on someon's attention to the area of the chart that's very important and really makes it easier to see how the data changes.

  • Click the DATA series of the chart for the trendline
  • From the MENU, select CHART/ADD Trend Line
  • Click the Trendline or moving average you would like to have represented on you chart
  • Click OK


TIP #18 - USING 'EXTEND MODE'(Versions 97, 2000, 2002)

Selecting a RANGE of Cells using 'Extend Mode'
You can highlight a range of cells by using 'extend mode' rather than using the mouse or SHIFT/arrows keys to select cell range:

  • Click in the first cell of the range you want to select/highlight
  • Touch [F8] to turn on 'Extend Mode'

You'll notice that the 'EXT' box in the lower right corner of Status Bar area appears

  • Click the cell at the opposite corner that would identify the range of cells you want to highlight/select

Note:  If you make a mistake, you can simply click somewhere else to identify/highlight the range of cells 

  • Perform whatever functions you want to with the highlighted cells
  • To exit 'Extend Mode' when you're finished, touch [F8] to turn off


TIP #19 - COPYING A 'CHART FORMAT' (Versions 97, 2000, 2002)

You can copy the format of a chart - if you've got a chart with the formatting/colors, etc. set already, you can copy the format to another new chart:

  • Click on the formatted chart
  • Click [COPY] button (or CTRL/c or EDIT/Copy from the menu)
  • Click the chart that you want to format
  • From the menu, select EDIT/Paste Special, and when the Paste Special dialog box appears, click the [FORMATS] button.
  • Click [OK]


TIP #20 - REMEMBERING YOUR CELL NAMES (Versions 97, 2000, 2002)

When you're doing a formula and  can't remember the range names  that you need, you can:

  • Touch [F3] - (a list of range name appear)
  • DOUBLECLICK the range name that you want to include in formula


TIP #21 - HOW TO WRAP CELLS WITHIN TEXT (Versions 97, 2000, 2002)

You can wrap text in cells - highlight the cells then:

  • From the menu, select  FORMAT
  • Select CELLS
  • Click the ALIGNMENT TAB
  • Under Text Control, check the box WRAP TEXT

NOTE: If you want to create a 'break' in the text rather than have it automatically wordwrap, place your cursor at the point where you want to break the line within the cell, and touch ALT/Enter 

 



TIP #22 - 'AUTOCORRECT' (Versions 2000, 2002)

How about 'AutoCorrect', this is a neat tool to have when you seem to make the same typo errors over and over again. If  you've typed a mispelled word, as long as the typo is in the AutoCorrect feature it will automatically correct itself, works also with abbreviations.

  • From the menu bar select, TOOLS/AUTOCORRECT ('AUTOCORRECT OPTION' in 2002)
  • Enter the abbreviation of the word or typo error of the word in the REPLACE field
    • ex:  type theese in the REPLACE field (or Assist) 
  • Enter the correct spelling of the word  or the full word in the WITH field
    • ex:  type these in the WITH field (or Assistant)
  • Click ADD and this will add the new abbreviation to the Autocorect list
  • Click OK and this will end and close the AUTOCORRECT feature


TIP #23 - HOW TO SELECT ALL OBJECTS AT ONE TIME  (Version 2000)

  • EDIT/GO TO
  • Click on the 'SPECIAL' option
  • Choose 'OBJECTS' 
  • Click OK


TIP #24 - 'VIEW' YOUR FORMULAS INSTEAD OF RESULTS IN CELLS (Versions 2000 & 2002)

You can 'see' your formulas instead of the results in a cell by:

  • TOOLS/options
  • Click 'VIEW' tab
  • Check the box 'FORMULAS'
  • Click [OK]
    • <your formulas appear>

[Reverse these steps and uncheck box to get your results back]

 



TIP #25 - 'PREFILL' A CELL (Versions 97, 2000, 2002)

You can prefill a cell with characters like *'s, etc, regardless of the size of the column
In Excel 97/2000, you would:

  • Highlight the Cell (s)
  • From the menu, select FORMAT/Cells
  • Click the 'Alighnment' tab
  • At the 'Horizontal' setting, click the drop down and select 'FILL'
  • Click [OK]
    • <Anything you type in the cell will 'FILL' the entire cell, regardless of the width>

NOTE:  For lower versions of Excel:

  • FORTMAT/Cell/Alignment tab
  • Click 'FILL' radio button
  • Click [OK]


TIP #26 - 'Autoshow Top 10' in a Pivot Table (Versions 2000 and 2002)

You can Autoshow the Top 10 values for a Row or Column Field in a Pivot Table. Double click on one of the Row or Column Fields in your Pivot Table and then click the Advanced button. Select the “On” option button for Top 10 Auto Show. You can choose the number of the Top to display. For example, you can change the 10 to a 3 to display the top 3.

To turn the 'Autoshow Top 10' off, double click on the Row or Column field again, click the Advanced button and select the Off option button.

 



TIP #27 - Using a Carriage Return to Insert a Line Break Within A Cell 

You can insert line breaks when you want labels or headings on multiple lines in your Excel worksheet, just as you can in WORD.

  • Click on the cell where you want to type your label/heading
  • Type your first line, then touch ALT/Enter
  • Type your second line, then touch ALT/Enter
  • Repeat if you need to type additional lines of text
  • Press ENTER when finished