|
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
Method #2
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.
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.
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.
Automatically display page breaks and get a better idea of how your worksheet will print out.
You will notice a dotted
line on your worksheets where the pagebreaks will be.
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.
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.
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.
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:
NOTE: In
Excel 2000 & 2002, it's just PageUp and Page Down NOT CTRL
Would you like to add a
picture to the bars of an Excel chart to set them off? 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 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).
Named ranges let you calculate the total for all cells in a range with a formula such as
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.
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.
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.
Shortcut keys abound in
Excel. Following are a few that should be useful
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:
Want to find out where a column ends or where the next
blank cell is
This trick works for rows, too. So, for example, if you
want to find
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.
Selecting a RANGE of Cells using 'Extend
Mode'
You'll notice that the 'EXT' box in the lower right corner of Status Bar area appears
Note: If you make a mistake, you can simply click somewhere else to identify/highlight the range of cells
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:
When you're doing a formula and can't remember the range names that you need, you can:
You can wrap text in cells - highlight the cells then:
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
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.
You can 'see' your formulas instead of the results in a cell by:
[Reverse these steps and uncheck box to get your results back]
You can prefill a cell with characters like *'s, etc,
regardless of the size of the column
NOTE: For lower versions of Excel:
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.
You can insert line breaks when you want labels or headings on multiple lines in your Excel worksheet, just as you can in WORD.
|