|Microsoft Excel Tips and Tricks|
To size and move
legends to use chart space efficiently (Excel 97/2000/2001/2002):
display text vertically in a cell (Excel 97/2000/2001/2002):
access Excel 2002's special paste options:
To apply previously
created validation rules to new cells (Excel 2002):
To print an entire
workbook at once (Excel 97/200/2001/2002):
counting text entries in Excel (97/2000/2001/2002):
To remove unnecessary smart tag indicators (Excel 2002): Hover your mouse pointer over the cell containing the indicator. Then, click on the smart tag options button that appears and select Remove This Smart Tag from the resulting menu.
To assign a password to access a workbook (Excel 97/2000/2001/2002):
Open the file and choose File | Save As from the menu bar. Then, click the Options button on the
Save As dialog box (if your dialog box doesn't have that button, choose General Options
from the dialog box's Tools menu). Type the case-sensitive password you want to use in the
Password To Open text box. If you also want to control whether edits can be made, enter a
different password in the Password To Modify text box. Click OK. Confirm any password
selections you've made. If you assigned two passwords, the first one you confirm is the one you
entered in the Password To Open text box. When you return to the Save As dialog box,
click Save and click Yes to save over the original workbook with the new password-protected
workbook. When you reopen the file, you'll be prompted to enter its password. Enter it in the
Password text box and click OK. If you supply an incorrect password, Excel displays a
warning and the open process is cancelled. If you also assigned a password to control
modifications, you'll be prompted to enter it.
To apply commonly used number formats with shortcut keys (Excel 97/2000/2001/2002):
[Ctrl][Shift][~] applies the General number format.
[Ctrl][Shift][$] applies the Currency number format with two decimal places/negative values in parentheses.
[Ctrl][Shift][%] applies the Percentage number format with no decimal places.
[Ctrl][Shift][^] applies the Scientific number format with two decimal places.
[Ctrl][Shift][#] applies the Date format in the form 15-Mar-04.
[Ctrl][Shift][@] applies the Time format in the form 12:00 AM.
[Ctrl][Shift][!] applies the Number format with two decimal places, thousands separator, and minus
sign (-) for negative values.
To enter the same data into multiple cells simultaneously (Excel 2002):
Select the cells in which you'd like the data to appear. To select adjacent cells, just click and drag
until you've selected the cells you want. To select nonadjacent cells, press and hold the [Ctrl] key
and then click on each cell you'd like to select. After you've selected the appropriate cells, enter the
data that you want them to hold. When you've finished, press [Ctrl][Enter]--the data appears in all
To create hyperlinks to data in Office applications (Excel 97/2000/2001/2002):
Select the cell containing the data to which you want the hyperlink to point. Then, choose Edit | Copy
3from the menu bar. Next, select the cell that will contain the hyperlink. Finally, choose Edit | Paste
As Hyperlink from the menu bar.
To force a carriage return in Excel 2000 chart legends:
When you enter the text, simply press [Alt][Enter] wherever you want to force a carriage return.
To open a specific workbook whenever you launch Excel (97/2000/2001/2002):
Choose Tools | Options from Excel's menu bar. When the Options dialog box appears, click on the
General tab. Then, enter the folder path in the Alternate Startup File Location text box.
(In Excel 2002, the text box is labeled with a more descriptive name: At Startup, Open All Files In.)
Once you've entered the appropriate folder path, click OK.
To insert symbols and special characters in Excel 2002:
Choose Insert | Symbol from the menu bar when you need to insert a symbol or special character
into your cell text. Excel displays the Symbol dialog box, which contains two tabs, Symbols and
Special Characters. The Symbols tab is similar to Character Map. You can select one of the fonts
installed on your system and see its characters displayed in a grid. The Special Characters tab
provides a static list of commonly used characters. Regardless of which tab you're using, simply
select the appropriate character and then click the Insert button to add the character at your
insertion point. When you've finished, click the Close button to dismiss the dialog box and
return to regular keyboard entry.
To indent text in a cell without resorting to the [spacebar](Excel 2002):
First, select the cells that contain the data you want to indent. Then, press [Ctrl]1 to
launch the Format Cells dialog box, click on the Alignment tab, and select Left (Indent)
from the Horizontal dropdown list. Then, use the Indent spinner box to set the number
of spaces you want to appear between the left edge of the selected cells and the start
of your text data. You can set an indent of up to 15 spaces. When you've finished, click
the OK button. To create (or remove) an indent even faster, select the cell containing your
data and use the Decrease Indent and Increase Indent buttons on the Formatting toolbar
to decrease/increase the size of the indent incrementally.
To reposition data point labels to make charts easier to read(Excel 97/2000/2001/2002):
Select one of the data labels. When you do, all of the related labels are automatically selected.
Next, choose Format | Selected Data Labels from the menu bar and then click on the Alignment
tab. Select the desired location from the Label Position dropdown list and click OK.
You can also selectively change the Label Position setting for individual labels. To do so,
select the labels as previously described. Then, wait a moment and click on the single
data label you want to change. Doing so selects the individual label. You can then
change Label Position setting as you did before. If the results are still undesirable,
you can manually drag the data labels to another location place. To do so, follow
the previous steps to select an individual label. Then, click and drag the label's
border to move it to the desired spot.
To sum filtered results (Excel 97/2000/2001/2002):
To create an subtotal formula, select any cell in the list you want to analyze and
choose Data | Filter | AutoFilter from the menu bar to display dropdown arrows
in the lists top row. Then, use any one of the dropdown arrows to filter the list for a
particular criterion value. Then, select the cell immediately beneath the column of
numbers you want to sum. Click the AutoSum button and you'll see that
Excel inserts a SUBTOTAL formula into the cell. Click the AutoSum button
again to complete the formula. You can now change the list's filter criteria and the
SUBTOTAL formula will return the sum of whatever data is visible in the list.
To convert numbers entered as text (Excel 97/2000/2001/2002):
Select the cell or range of cells that you want to convert. When the smart
tag button appears, click on it and choose Convert To Number from the
action menu. On older versions of Excel, first enter the number 1 in any
blank cell. Next, select the cell and choose Edit | Copy from the menu bar.
Then, select all the cells containing values you want converted. Choose
Edit | Paste Special from the menu bar. Finally, select the Multiply option
button and click OK.
To apply pictures to Excel chart data points:
Select the data series you want to format on the chart by clicking on it.
Then, choose Insert | Picture | From File from the menu bar.
Locate and select the picture you want to use--Excel can work with
most graphic file formats. Finally, click Insert and Excel applies the
picture to each data point, resizing the image as needed. The method
we used is easy, but other chart types require different approaches.
To hide items in a
PivotTable's page field's selection list (Excel 97/2000/2001/2002):
To print a selected range without setting a print area (Excel 97/2000/2002):
Select the range that you want to print. Then, choose File | Print from the
menu bar. When the Print dialog box appears, choose the Selection
option in the Print What panel. Finally, click Preview to view the output
onscreen or OK to print it.
To paste column widths along with data (Excel 2003):
Copy and paste the data as you normally would. Then, immediately click on the
Paste Options smart tag that appears at the bottom right corner of the pasted
selection. From the shortcut menu, select the Keep Source Column Widths
option. The selection and the column(s) you pasted it into will then take on the
same column width(s) as the original data.
To access financial data with the Research task pane (Excel 2003):
Select Research from the Tools menu to launch the Research task pane. In the
Search For text box, enter the company name. Click the arrow in the dropdown
list, and scroll through the list of research sources. Near the bottom, click Gale
Company Profiles under the All Business And Financial Sites category. In a flash,
you'll see the vital stats for the company. If you need more info, click on View
Complete Profile at the bottom of the company information. If you use Internet
Explorer 5.01 or later, your browser will launch and the Research pane will
stay parked next to it. From the Thomson Gale Web site, you can choose
to pay for the complete profile. To get back to your spreadsheet, just close the
browser window. If you're on the hunt for hard performance numbers or the
current stock price, MSN money is your up-to-the-minute resource. Keep the
Research task pane open, type in the company's stock ticker abbreviation in the
Search For text box and select MSN Money Stock Quotes from the dropdown
list. To insert the current stock price into your spreadsheet's active cell, click0
Insert Price. Look for Charts under More On MSN Money and select 1 Year, 3
Year, or 5 Year to create dynamic chart comparisons.
To change an existing Excel chart to a different type using Chart Wizard:
Select the chart object and then click the Chart Wizard button on the Standard toolbar.
Doing so displays the first screen of the Chart Wizard, allowing you to pick a different
chart type, as well as make any other changes you normally can with the wizard.
Select the chart type you want to use and click OK.
To change an existing Excel chart to a different type using the Menu Bar
Select the chart object and then choose Chart | Chart Type from the menu bar.
This displays the Chart Type dialog box, which is essentially the same as the
Chart Wizard's first screen. Selectthe chart type you want to use and click OK.
To change an existing Excel chart to a different type using the Chart Toolbar):
Select the chart object. If the Chart toolbar doesn't automatically appear, choose
View | Toolbars | Chart from the menu bar. This toolbar contains a Chart Type toolbar
button, which has a small dropdown arrow associated with it. Click on the arrow to
reveal a palette of 18 commonly used chart types. Simply select the chart type you
want to use and the current chart is automatically reconfigured.
To check a range selection's dimensions (Excel 97/2000/2001/2002):
When you select a range, the Name box next to the Formula bar displays its
dimensions as long as you have the mouse button pressed.
To give column labels vertical orientation to fit them all on one page (Excel 2003):
First, click the column label cell. Then, select Format | Cells from the menu bar.
(Alternatively, right-click the cell you want to change and select Format Cells from the
shortcut menu.) Select the Alignment tab in the Format Cells dialog box and take a
look at the Orientation section. To change your label so it reads from the top of the
cell down, click on the bottom diamond in the Orientation semi-circle. If you want
the text to read from the bottom up, click the top diamond. If you want your text to
read with the letters stacked on top of each other so the reader won't have to turn
the paper or his head to read the labels, click the Text bar on the left side of the
Orientation section. You can choose to display your column labels at any angle
by selecting a point on the semi-circle or choosing a negative or positive value
in the Degrees spin box. When you're satisfied with the orientation, click OK to
take a look. Note that you may need to change your row height to incorporate
the new vertical label.
To create diagrams quickly in Word, Excel, and PowerPoint:
To browse diagram types, first display the Drawing toolbar. Do this by choosing
View | Toolbars | Drawing from the menu bar or right-clicking on any toolbar and
choosing Drawing from the dropdown list. Click the Insert Diagram Or Organization
Chart button. In the Diagram Gallery dialog box, click each type of chart and read its
description. Once you've decided on a diagram, double click on it to insert the
empty diagram into your file. You can add labels to the diagram by clicking on the
text placeholders and entering your text. If you want to change the overall
appearance of the diagram, click the AutoFormat button on the Diagram
toolbar, choose a style, and click OK. Note that once you've applied a style,
you can't change the shape or color of the individual objects in your diagram.
Explore the Diagram toolbar to control the size, position, and shape of your diagram.
If you're not satisfied with the diagram you've chosen, just click the Change To
button to see how your data looks in another diagram type.
To access Excel 2002's
special paste options:
To change the default colors Excel assigns to chart points):
Choose Tools | Options from the menu bar while the workbook that will
contain your charts is open. Then, click on the Colors tab. The sample
squares next to the Chart Fills and Chart Lines labels indicate the colors
that Excel sequentially assigns to chart items. To change a particular
color, select the appropriate square in the Chart Fills or Chart Lines sequence.
Then, click the Modify button. Pick one of the standard colors from the
color wheel or click on the Custom tabto create a new color. Finally,
click OK. If you ever want to restore Excel's defaults, click the Reset
button to restore all of the color items to their original settings. Finally,
click OK to save the color modifications.
To clarify data on line and area chart with drop lines:
To display drop lines, select a data series on the chart and then
choose Format | Selected Data Series from the menu bar. Then,
click on the Options tab. Select the Drop Lines check box and then
click OK. Vertical lines now extend from each data point to the
category axis, eliminating confusion and guesswork. Note that
this formatting option can also be applied to 2-D area andline charts.
To size columns and rows to fit your data (Excel 97/2000/2001/2002):
Double-click on the heading separator line. When you do so on a
column separator, Excel automatically resizes the column so it's
wide enough to display the widest item in the column. Likewise,
double-clicking on a row heading separator changes the row height
to acccomodate the tallest row entry. You can apply this trick to
multiple columns and rows at once. To do so, select the multiple
row or column headings that you want to resize. Then, double-click
on a separator line associated with any one of the selected headings.
To accelerate data entry with AutoComplete (Excel 97/2000/2001/2002):
Press [Alt] and the [Down Arrow] key when you begin your new entry.
For example, select cell A5and press [Alt][Down Arrow]. Excel displays a
dropdown list of the column's previous entries. Use your mouse or
keyboard's directional arrows to select an item and press [Enter] to insert
it into the cell. You can also display the item list by right-clicking on a
cell ([control]-clicking on the Mac) and choosing Pick From List. When
working with a long list of column entries, enter the first
few letters of the word you're looking for prior to displaying the dropdown
list so you don't have to scroll through a lot of entries.
To link data to your
document using Paste Link(Word/Excel 2003):
To prevent error values
from printing (Excel 2003):
To get help entering functions with Excel 2000's Formula Palette:
Click the equal sign (=) in the Formula Bar and select the function you need to work with from the dropdown list to the left of it. You can then use it to define the arguments for the function. If the Formula Palette obscures the range of cells you're working with, drag it with your mouse to a section of your worksheet that's out of the way of your data.
To prevent startup Excel macros from running (97/2000/2001/2002):
Hold down the [Shift] key when you open the file. Note that you'll need to
keep the [Shift] key pressed throughout any warning dialog boxes that may
appear during the process.
To adjust margins within Print Preview in Excel 2000:
Click the Margins button. This reveals the header, footer, and page
margins, which appear as dotted lines that can be moved with your mouse.
In addition, you'll notice several small black handles at the top of the page.
These correspond to your worksheet's column borders, and you can drag
the handles to resize your columns as needed.
To use an Outlook task to keep track of work in Excel:
Save your workbook and then display the Reviewing toolbar by right-clicking
on any toolbar and selecting Reviewing from the shortcut menu. Make sure
Outlook is open, and then click the Create Microsoft Outlook Task button on the
Reviewing toolbar. A new task form is displayed with a shortcut to the open workbook
inserted into the form. In the Subject text box, type the name of
the task. You can add more descriptive text beside the workbook shortcut if desired.
Next, select the Reminder check box and set the appropriate time and date.
Finally, click Save And Close. When the reminder time and date arrive, you'll see a
Reminder box appear (as long as Outlook is running at the time). In the Reminder
box, click the Open Item button to open the task item. Then, double-click on the
workbook shortcut to open the workbook. You can also open the Outlook task
item from Outlook to access the workbook shortcut.
To freeze Excel columns and rows for precise scrolling control:
To freeze a row, your header row for example, select the row that's
immediately beneath it. Then, select Window | Freeze Panes from the
menu bar. Excel inserts a thin line on the bottom border
of the frozen pane. To freeze a column, select the column that is
immediately to the right of it and select Window | Freeze Panes. You
can also freeze particular rows and columns simultaneously.
Just click on the cell that's in the upper-left corner of the spreadsheet
area you want scrollable and turn on Freeze Panes. When you save
your worksheet, you'll also save your Freeze Panes
settings. To remove the panes, select Window | Unfreeze Panes.
To preview how an Excel 2000 spreadsheet will look as a Web page: Choose File | Web Page Preview from the menu bar, and an HTML
version of your worksheet is opened in your browser.