Visit us often. Computer tips updated
daily. Click here to--> "Tell a friend" so they can get updated
computer tips, too. Please visit our clients, as they support the
computer tips page.
If you would like to submit a tip send us an email with
your tip to
Creating a more versatile PivotTable with the List feature (Excel
- Excel 2003's List feature, which allows you to segregate,
manipulate, and quickly analyze your data, also helps you create a more
efficient PivotTable. In previous versions of Excel, if you create a Pivot
Table from a range of data, and later expand the range to include more rows,
you also needed to re-create the PivotTable based on the new data range.
However, if you make a range of data into a list before you create its
PivotTable, the PivotTable refreshes to include new rows that you add to the
list. To do so, select the data you wish to include in the list and press [Ctrl]L.
Then, click OK in the Create List dialog box. Now, to create a PivotTable that
can expand as your list expands, select the list and choose Data | PivotTable
And PivotChart Report from the menu bar. Click Finish in the PivotTable And
PivotChart Wizard to accept Excel's default options. Now, if you add data to
the list's insert row (the row with an asterisk), the list expands to include
the new data And, if you click the Refresh Data button on the PivotTable
toolbar, the new row of data is integrated into the PivotTable.
Tell a story with pictures (2007)
- One of the most dramatic additions to the Excel 2007
Conditional Formatting menu is the addition of icon sets. These icons are
illustrative pictures that you can assign to data values for fast data
analysis. The icons range from stop lights to flags to check marks and x’s.
- For example, you can assign green, yellow, and red flags to
your sales reps’ monthly sales. Green flags indicate sales in the top third
percentile, yellow flags indicate sales in the middle percentile, and the red
flags designate sales in the bottom third percentile.
Chart a static value with a straight line for easy comparisons (Excel
- When you're plotting points on a chart, a flat line of
static data can provide a visual point of reference, so it's easy to compare
the rest of the chart's data to that value.
- To add a line to represent a static value in a chart, first
create the chart. Be sure to include a column of data in the chart's source
data with all the same values. For instance, in a chart comparing a product's
price at different stores, you might want to include a column to hold the
item's national average price. When your chart is complete, right-click on one
of the static values' data points and choose Chart Type from the resulting
shortcut menu. In the Chart Type dialog box, choose Line from the Chart Type
list box and click OK.
Display a cheat sheet for your function right in the formula bar
- You’ve probably seen the ToolTip that appears when you
begin to type a formula. Once you enter the opening parenthesis, the arguments
for that function appear below the Formula bar or active cell.
But, many power users find these ToolTips more annoying than helpful,
especially for functions they know by heart. You can disable the ToolTips and
still view Excel’s cheat sheet when you get stuck.
- To turn off the function ToolTips:
- 1.Launch Excel and choose Tools | Options to access the
Options dialog box.
- 2.Click on the General tab, if necessary.
- 3.Deselect the Function ToolTips check box in the
- 4.Click OK to disable the function ToolTips.
- To insert the function arguments right in the cell or
- 1.Enter the function in a cell, along with its opening
- 2.Press [Ctrl][Shift]A to enter the arguments right
within the active cell (or the Formula bar if you’re working directly n the
- 3.Replace each argument with your relevant data or cell
Match the worksheet to the job with a custom template
- When you insert a worksheet using the Insert | Worksheet
menu option, Excel automatically bases the new sheet on the default template.
However, you may not want to insert just a blank worksheet. For instance, say
your company uses a special Project Tracking template. A workbook is dedicated
to each client and there could be several project worksheets in each workbook.
Instead of inserting a blank sheet, you want to insert a blank Project
- Fortunately, you can select what template Excel uses to
create a new worksheet. Instead of choosing Insert | Worksheet from the menu
bar, right-click on a worksheet tab ([control]-click in 2004) and then choose
Insert from the shortcut menu. The Insert dialog box appears, displaying your
PC's templates. Simply select the appropriate one and click OK.
Move worksheets between workbooks in a flash (2000/2002/2003)
- Sometimes Excel users overlook the simplest solutions — usually because
experience tells them it couldn’t be so easy! Here’s a pleasantly simple way
to move a worksheet to another workbook.
- To move a worksheet from one workbook to another:
- Launch Excel and open both the original workbook and the destination
- Choose Window | Arrange from the menu bar.
- Select the Horizontal option button in the Arrange Windows dialog box
and click OK.
- Drag the worksheet tab that you want to move to a position in the
- When you release your mouse button, notice that the worksheet displays in
the destination workbook, but it’s no longer in its original workbook.
Analyze characteristics of similar
items without creating multiple charts
- When you want to compare several aspects of
similar items, you probably create a chart for each one -- but it can be
difficult to visualize the comparison when you're eyeballing a bunch of
charts. Excel's radar charts can help you draw valuable conclusions by putting
all of the comparable data in one chart.
- To understand how radar charts can help with
data analysis, we'll:
- Review the basics of radar charts and
create our own.
- Draw comparisons with the data in our
- Tweak the chart by adding a category and a
- Work around a pitfall that can arise if
your data series doesn't use a consistent value scale.
Read More About This Tip Click Here >
MS Excel Tips
Highlight your top 3 — even when the
numbers are always changing
- You can't analyze your business unless you
know what works -- or doesn't work. By combining the RANK function with
conditional formatting, you can keep track of the top or bottom values in your
worksheet even when you update the data regularly.
- To emphasize the top three values in our
spreadsheet automatically, we'll:
- Add a column to the spreadsheet that ranks
departments based on their current sales totals.
- Present an alternative formula that
eliminates duplicate rankings.
- Apply conditional formatting that draws
attention to the top three departments, even when the ranks change with
sales figure updates.
Read More About This Tip Click Here >
MS Excel Tips
Create hyperlinks that point to areas
in a workbook (2000/2002/2003)
- Excel workbooks can store a huge amount of
data, and getting from one place to another in a file can be tedious. You can
simplify things by creating hyperlinks that jump exactly to a specific place
in the file.
- To insert a hyperlink to a specific location
in your workbook:
- 1. Select the cell you want to contain a
- 2. Enter the text that you want the
hyperlink to display.
- 3. Choose Insert | Hyperlink from the menu
bar to open the Insert Hyperlink dialog box.
- 4. Select the Place In This Document
button on the Link To bar on the left side of the dialog box.
- If your workbook contains named ranges, you
can select a name from the Defined Names list and the hyperlink will jump to
it when you click it. Otherwise, select the appropriate worksheet name under
the Cell Reference node and enter a cell or range address in the Type The Cell
References text box.
Extract unique entries based on two
columns of criteria
- It's challenging enough to weed out
duplicate records in a long list when you only have to focus on one piece of
duplicate data. But what if you have to find two pieces that match before
you're sure you have a duplicate? Our technique uses the Advanced Filter
feature to weed out only the unique records in a worksheet -- based on two
columns of criteria instead of just one.
- To list only the unique entries in a list
using two columns as our criteria, we'll:
- Display only the unique records in a list
using the Advanced Filter feature.
- Copy the unique items to a separate
- Discuss some of the limitations of the
Advanced Filter and how you can work around them.
MS Excel Tips
Hunt down duplicate data and delete it
fast with AutoFilter
- No matter how vigilant you are, duplicate
data seems to worm its way into your longer data lists, especially when you
regularly import new data. Fortunately, you can combine an Excel formula with
the AutoFilter feature to single out duplicate data, making it a snap to
- To find the duplicate data in our worksheet
and delete it, we'll:
- Design a formula that returns a 0 the
first time a data item is listed and a 1 for each duplicate of the data
- Filter our data list to show only the
items that are duplicates.
- Delete the duplicates and return to a data
list that now only displays unique items.
You could spend hours going through a large
data table and manually identifying and deleting any duplicate entries. The
chance for human error is high and you’ll spend too much valuable time on
the task. Our field-tested technique combines the power of an Excel formula
and the AutoFilter feature to cut the job down to minutes instead of hours.
Put the technique into action
To identify duplicates, we’ll add a column to
the list and use a conditional worksheet formula that returns a 0 the first
time that a particular data item is encountered in the list. Each additional
occurrence of the item will return a result of 1 in the column. To review or
remove the duplicates, we’ll use the AutoFilter tool to display only the
records flagged with 1.
To flag duplicate
1.Launch Excel and open our sample
workbook, or create one based on the data shown in
2.Select cell C1 and enter
Duplicates as the column header.
3.Press [Enter] to advance to cell C2 and
enter the formula =IF(COUNTIF(A$2:A2,A2)>1,1,0).
4.Double-click on cell C2’s Fill handle to
copy the formula down the column and flag duplicates, as shown in
We’ll flag duplicate entries using a
The duplicate records display a 1 in column C.
Break down the
This formula uses the
COUNTIF function, which counts the number of cells in a specified
range that meet particular criteria. This first argument in the
COUNTIF function, in this case A$2:A2, is the
range that Excel will search. The second argument, A2, indicates the
criteria the formula is looking for.
In our formula, we’re specifying that we
want to count the times that a value stored in cell A2 appears within
range A$2:A2. The COUNTIF function is nested
within an IF function, which returns the result
ultimately displayed in cell C2. If the result of the
COUNTIF function is greater than 1, our formula returns 1;
otherwise it returns 0.
You may have noticed that we only used the
dollar sign symbol in the first part of our COUNTIF
range address. Doing so means that we can copy the formula to other
worksheet cells and the address used in that part of the formula will
always refer to row 2.
that our formula only counts the occurrences in column A — Product codes.
This means that if two different product codes had the same product name,
Excel doesn’t flag it as a duplicate using our formula. Depending on your
data and how you want to define what a duplicate item is, you may need to
tweak this. In a future article, we’ll show you how to find duplicates
based on several criteria.
Do you need more order?
Remember that this formula is designed to
mark the first instance of a data item as unique. Excel flags every
instance that follows as a duplicate. This means that you might want to
think about the order of your data. For instance, what if the third
instance of a product code actually had the most up-to-date information?
Our formula would have thrown out the most recent item as a duplicate. It
may help if you add a Date column to your worksheet. This way, you can
sort the data table so that the most recent entries are at the top of the
list. Then our formula will be sure to keep the most recent information
and mark older instances as duplicates ready for deletion.
Remove the duplicates
Now that you’ve identified the rows with
duplicate product codes, you probably want to remove them. This is where
we’ll put the AutoFilter to work.
duplicate data items:
Select any cell within your data table.
Choose Data | Filter | AutoFilter from
the menu bar. Dropdown arrows appear to the right of the column
Click on the Duplicates column’s
dropdown arrow and select 1. Only the duplicate records are displayed,
as shown in Figure C.
Select the row headings for the
displayed rows and choose Edit | Delete Row from the menu bar.
Choose (All) from the Duplicates
column’s dropdown arrow to restore your view of the data table. Only
the unique items remain, as shown in Figure D.
The AutoFilter hides all of the first
occurrences, leaving only the duplicates.
The duplicate entries have all been
removed from our list.
Tip: Specify what your PivotTable
displays in place of empty cells and errors (2000/2002/2003)
- PivotTables can look incomplete and
unprofessional if there are empty cells or error messages. Instead of manually
replacing the error values you don’t want to see or placing zeros into blank
cells, you can set a quick preference that does the job for you.
- To set up specific text for empty cells or
- 1. Launch Excel and open the workbook with
- 2. Select the PivotTable and make sure the
PivotTable toolbar is displayed. If it isn’t, choose View | Toolbars |
PivotTable from the menu bar.
- 3. Click the PivotTable button on the
PivotTable toolbar and choose Table Options from the resulting dropdown menu
to open the PivotTable Options window.
- 4. Select the For Error Values Show check
box and enter the text you want to display in its corresponding text box.
- 5. Select the For Empty Cells Show check
box and enter the text you want to display in its corresponding text box.
- 6. Click OK to apply the settings to your
- If you want errors to display as blank
cells, simply leave the For Error Values Show text box blank.
Tip: Zoom in on your data in seconds
- If your mouse has a wheel, you probably use
it to scroll up and down through lengthy worksheets. You may not realize that
you can also use it to change the zoom percentage for your view of the
To do so, hold down the [Ctrl] key and move the scroll wheel. Rolling the
wheel down decreases the zoom percentage and rolling it up increases the zoom
percentage. Note that if you’re using Excel 2000, the zoom will only go up to
- If you’d rather have your mouse wheel zoom
by default, choose Tools | Options from the menu bar, switch to the General
tab, and then select the Zoom On Roll With IntelliMouse check box. After you
click OK, rolling the wheel zooms and rolling the wheel while holding the
[Ctrl] key scrolls up and down the worksheet.
Tip: Put a new slant on your data
- Minor changes can sometimes make a
worksheet’s visual appeal skyrocket. For instance, you may notice that our
sample worksheets occasionally include column headers that slant at a 45
degree angle. Want to see how it looks on your data table? It’s as easy as
- To slant the text in a cell:
- 1. Launch Excel and open the workbook in
which you want to change your text orientation.
- 2. Select the cell(s) that contain text
you want to slant.
- 3. Choose Format | Cells from the menu bar
and click on the Alignment tab.
- 4. Enter 45 in the Orientation panel’s
Degrees spin box and click OK.
Tip: Make international newcomers feel
at home within Excel (2000/2002/2003/2004)
- More often, your coworkers may not only be
new to the company but also to the country. There are a few things you can
adjust within Excel to make international users feel more comfortable in their
Excel work and navigation. One of these settings is aimed at those who are
used to reading right-to-left as opposed to the Western standard of
- To change your Excel screen to a
- 1. Launch Excel and open any workbook.
- 2. Choose Tools | Options from the menu
bar and click on the International tab.
- 3. Select the Right-To-Left option button
in the Right-To-Left panel.
- 4. Preview the effect by selecting the
View Current Sheet Right-To-Left check mark.
- 5. Click OK.
- When you click OK, you’ll see that Excel’s
row numbers now appear on the right. The columns begin with A furthest to the
right and move through the alphabet as you scroll to the left.
Tip: Never type a decimal point again
- If you consistently use the same number of
decimal places in your Excel values but typing the decimal point slows down
your data entry, there’s an easier way. You can permanently set all of the
numbers you enter in your Excel workbooks to include the same number of
- To always display the same number of decimal
- 1. Launch Excel and open the workbook in
which you want to keep a consistent number of decimal places for every
- 2. Choose Tools | Options from the menu
bar to open the Options dialog box.
- 3. Click on the Edit tab.
- 4. Select the Fixed Decimal Places check
box and then use the corresponding spin box to increase or decrease the
number of decimal places you want.
- 5. Click OK to apply the change.
- Now when you enter a number in Excel, it
automatically inserts a decimal point at the correct position — even though
you didn’t type one when you entered the number. This can save you data entry
time and minimize typos.
- This preference will apply to all of your
work in Excel until you open the Options dialog box again and deselect the
Fixed Decimal Places check box.
Tip: Chart your data with one keystroke
- Charts can take a lot of time to construct,
and there are so many ways to customize them that it’s often an intimidating
- The next time you need a quick-and-dirty
chart in seconds, just select any cell within the data table you want to chart
and press the [F11] key. Excel creates a default Column chart in a new
worksheet. From there you can either take the chart as is or tweak it to suit
your needs — just as you would any other chart.
Tip: Keep your scrolling under wraps —
use a shortcut key instead (2000/2002/2003)
- When you have a large set of data that spans
several Excel screens, scrolling to the end of the data isn’t a great option.
It’s difficult to control and you often end up exceeding the last row (or
column) of data.
- Fortunately, there’s a simple shortcut key
that not only finds but also selects every cell to the right and below the
currently active cell until it reaches the furthest point Just select your
starting cell (in our case, cell A1) and press [Shift][Ctrl][End].
- Beware of one caveat: If you’ve entered a
value and then deleted it, Excel may still consider it the last cell in your
worksheet’s used range. So, if you had data up to row 200 and then delete the
last 10 rows, the shortcut key selects up to row 200.
Tip: Display your bar chart’s data
labels right in the bars (2000/2002/2003/2004)
- Data labels are tough to work with because
they often make your chart look cluttered or cover up other important data
points. The next time you want to display labels for your bar chart’s data
points, you can conserve space by embedding them right in the bars. We’ll
assume that your chart already displays data labels.
- To nest the data point labels right within
the chart’s bars:
- 1. Launch Excel and open the worksheet
that contains your bar chart.
- 2. Right-click on one of the data labels
and choose Format Data Labels from the resulting shortcut menu to display
the Format Data Labels dialog box.
- 3. Click on the Alignment tab and then
choose Center from the Label Position dropdown list.
- 4. Ensure that Center appears in both the
Horizontal and Vertical dropdown lists.
- 5. Enter 90 in the Degrees spin box in the
Orientation panel and click OK to apply the changes.
- There are a few things you should be aware
of when it comes to this technique:
- You may need to change the color of your
data labels so they contrast well with your chart’s bars.
- You may need to widen your chart’s bars to
accommodate the numbers. You can do so by decreasing the gap width between
the chart’s data points.
Tip: Trick Excel’s conditional
formatting into giving you what you want (2000/2002/2003/2004)
- Conditional formatting is an excellent tool
if you know the ins and outs of using it. One of the most frustrating aspects
about it, however, is the error message you receive when you try to input a
conditional formatting formula that refers to data in another worksheet. Excel
tells you that you can’t refer to data outside of the worksheet in your
conditional formats — but we say you can.
- All you need to do is create a named range
for the data you want to use in another worksheet. For instance, you can
create a range called CFRange in Sheet2. Then when you want to use that range
in Sheet1’s conditional formatting formula, simply input CFRange in the
formula. Excel accepts it even though the data is from a different worksheet.
Tip: Breathe some life into your
PivotChart by making it 3D (2000/2002/2003/2004)
- PivotCharts are a visual version of your
PivotTable data — intimately connected with the PivotTable and its changes.
However, don’t forget that just because you’re working with a PivotChart, it
doesn’t mean that you can’t use your old familiar charting tricks. For
instance, don’t settle for the default PivotChart type. Make it pop off the
screen by using a 3D alternative.
- To make your PivotChart three-dimensional:
- 1. Launch Excel and open a workbook that
contains a PivotTable and PivotChart.
- 2. Right-click on the PivotChart’s data
series and choose Chart Type from the resulting dropdown list to open the
Chart Type dialog box.
- 3. Choose a chart type from the Chart Type
list box. There are 3D alternatives for the Column, Bar, Line, Area, Surface
and Bubble chart types.
- 4. Select a 3D chart option from the Chart
Sub-type section. You can read a description of any sub-type by selecting
- 5. Click OK when you’re ready to change
your chart type.
You can also get an idea of how a chart type will look before you click OK
by clicking and holding down on the Press And Hold To View Sample button.
Tip: Travel back a few centuries —
convert your numbers to roman numerals (2000/2002/2003/2004)
- Sometimes Excel’s vast options for computing
and communicating data are surprising. For instance, if you need to convert a
set of numbers to roman numerals but you can’t seem to get your L’s and your
V’s straight, let Excel do the work for you.
- There’s a little-known function in Excel’s
arsenal called the ROMAN function. You can use this function to convert a
static number (i.e., =ROMAN(145)) to a roman numeral. Or, just as you would
with any other function, you can use a cell reference. For example, =ROMAN(A1)
will convert the value in cell A1 to a roman numeral.
- Just keep in mind that the ROMAN function
cuts off its values to make them integers. If you type 46.8 in cell A1, cell
A2 still returns XLVI. Also, you can’t convert numbers larger than 3999, and
negative numbers will produce an error.
Tip: An alternative way to convert data
from text to number formatting (2002/2003/2004)
- If you have Excel 2002/2003/2004, you can
easily convert text into number formatting with SmartTags. First, you must
ensure that Excel flags numbers that you store as text values.
- To flag data that Excel should store as
numbers instead of text:
- 1. Launch Excel and open a workbook with
incorrectly formatted numbers.
- 2. Choose Tools | Options (Excel |
Preferences in 2004) from the menu bar, and then select the Error Checking
tab in the Options dialog box.
- 3. Ensure that the Number Stored As Text
text box is selected in the Rules panel and click OK.
- Any number that you store as a text value
now displays a green triangle in its upper-left corner. When you select the
cell, a SmartTag appears.
- To correct the errors and store the values
as numbers instead of text:
- 1. Select the data range of flagged cells
with values stored as text.
- 2. Click on the SmartTag that displays
when you select the data range.
- 3. Select Convert To Number from the
SmartTag’s dropdown list.
In Excel 2004, you must select cells individually to make the conversion
from text to number formatting.
Tip: Make sure your ampersand symbol
displays in a header or footer (2000/2002/2003/2004)
- If you’ve ever tried to display text that
includes an ampersand symbol (&) in a header or footer, you know exactly what
happens. You enter the symbol, and it looks fine when you’re creating the
custom header. However, when you click OK and return to the Page Setup dialog
box, the preview omits the ampersand symbol; the text Smith & Jones Law
Offices incorrectly displays as Smith Jones Law Offices.
- The problem is that Excel uses the ampersand
symbol as part of its insertion code for dates, times, and page numbers. It
doesn’t consider the ampersand as an actual text symbol. Fortunately, you can
work around this by simply entering two consecutive ampersand symbols in a
header or footer where you want an ampersand to display.
- To enter text that includes an ampersand in
- 1. Launch Excel and open the workbook in
which you’d like to create a custom header or footer.
- 2. Choose File | Page Setup from the menu
bar to open the Page Setup dialog box.
- 3. Click on the Header/Footer tab, if
necessary, and then click the Custom Header button to open the Header
- 4. Enter Smith && Jones Law Offices in the
Left Section text box.
- 5. Include any other information you’d
like in the Center Section and Right Section list boxes.
- 6. Click OK twice to return to your
- When you enter two ampersand symbols, the
header’s preview displays the symbol correctly.
Tip: Isolate your 3-D chart’s data
points with drop lines (2000/2002/2003/2004)
- Although Excel's 3-D charts do a pretty good
job of simulating 3-D objects on a 2-D screen, it can sometimes be difficult
to visually line up a data point with its corresponding position on the
chart's category axis. This is especially true when working with area or line
charts. The task can be even more difficult if the chart has been rotated or
the perceived angle of elevation has been changed. Fortunately, you can add
drop lines, which are visual aids that help anchor each data point to the
- To display drop lines in a chart:
- 1. Launch Excel and open the workbook that
contains your chart.
- 2. Select a data series on the chart, and
then choose Format | Selected Data Series from the menu bar. Alternatively,
you can press [Ctrl]1.
- 3. Click on the Options tab in the Format
Data Series dialog box.
- 4. 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 you
can also apply this formatting option to 2-D area and line charts.
Tip: Display a cheat sheet for your
function right in the formula bar (2000/2002/2003/2004)
- You’ve probably seen the ToolTip that
appears when you begin to type a formula. Once you enter the opening
parenthesis, the arguments for that function appear below the Formula bar or
But, many power users find these ToolTips more annoying than helpful,
especially for functions they know by heart. You can disable the ToolTips and
still view Excel’s cheat sheet when you get stuck.
- To turn off the function ToolTips:
- 1.Launch Excel and choose Tools | Options
to access the Options dialog box.
- 2.Click on the General tab, if necessary.
- 3.Deselect the Function Tooltips check box
in the Settings panel.
- 4.Click OK to disable the function
- To insert the function arguments right in
the cell or formula bar:
- 1.Enter the function in a cell, along with
its opening parenthesis—i.e., =SUM(.
- 2.Press [Ctrl][Shift]A to enter the
arguments right within the active cell (or the Formula bar if you’re working
directly in the Formula bar).
- 3.Replace each argument with your relevant
data or cell reference.
Tip: Get the lowdown on the CONCATENATE
function vs. the ampersand (2000/2002/2003/2004)
- There are two methods you can use for
joining text from different cells—or combining the flexibility of a function
within your text. You can use an ampersand (&) to combine a function with
text, or the text from more than one cell. Alternatively, you can use the
CONCATENATE function to combine different cell reference and text elements
into a single text string. So, what’s the difference? Which method should you
For the most part, these two methods produce the same results; they combine
text and/or a cell reference’s data into one cell, formatted as text. The only
real difference is the number of items you can combine. There’s no limit to
how many pieces you can combine with the ampersand symbol. However, the
CONCATENATE function can’t combine more than 30 items.
Tip: Customize chart axis labels even
if they aren’t in your source data (2000/2002/2003/2004)
- When you use the Chart Wizard to create a
chart from a data table, there are often missing elements. After all, you
probably weren’t intending to create a chart when you entered the data. If
you’re missing the convenient category (x-axis) labels that run across the
chart’s horizontal axis, you can quickly designate your own labels right in
the Chart Wizard.
- To generate custom category axis labels in
the Chart Wizard:
- 1.Launch Excel and open the workbook in
which you want to chart data.
- 2.Activate any cell within the data table
you want to chart and click the Chart Wizard button CHART WIZARD on the
- 3.Advance through the wizard until you
reach the Step 2 Of 4 screen.
- 4.Click on the Series tab, if necessary.
- 5.Enter the text for your labels in the
Category (X) Axis Labels text box, separating each label with either commas
(,) or semicolons (;).
- 6.Complete the rest of the Chart Wizard
with your desired settings.
When you enter the custom labels, you’ll notice that Excel converts them
into an array formula. This is a great alternative to choosing labels from
the worksheet, especially if you want abbreviated versions of the worksheet
labels or the worksheet doesn’t contain the correct category axis labels at
Tip: Apply number formatting to an
entire field of PivotTable data (Excel 2000/2002/2003)
- Sometimes, it's difficult to deal with
formatting in PivotTables. To make the job go faster, you can apply the same
number format to every value within a PivotTable field. To begin, open your
PivotTable in Excel and select any cell with a value that belongs to the field
you wish to format. Then, click the Field Settings button on the PivotTable
toolbar to access the PivotTable Field dialog box. Click the Number button to
open the Format Cells dialog box's Number tab. Apply the settings for your
desired number formatting and click OK twice to dismiss both dialog boxes.
Every value in the field now reflects your new number formatting.
Tip: Improve your printouts by
separating groups of data with page breaks (Excel 2000/2002/2003/2004)
- Oftentimes, your workbook contains thousands
of rows. When you print it out, the sheer volume of records may be
overwhelming, making it hard to locate the data you need. Fortunately, you can
insert manual page breaks to separate groups of data onto their own pages.
- To insert a new page break, first switch to
Excel's Page Break Preview mode. Launch Excel and open the workbook to which
you want to add page breaks. Choose View | Page Break Preview from the menu
bar. Then, select a cell (or an entire row or column) below the line where you
wish to insert a horizontal break, or to the right of a line where you wish to
insert a vertical break. Right-click on the selection and choose Insert Page
Break from the resulting shortcut menu.
Tip: Change the username Excel assigns
to your workbooks (2002/2003)
- You can easily update username metadata for
all future files you save in Excel. That way, the personal information
attached to your work in Excel is always correct. To do so, launch Excel and
choose Tools | Options from the menu bar. Click on the General tab in the
Options dialog box. Then, update or delete the information in the User Name
text box to reflect the name you want to assign to all saved workbooks.
Tip: Chart a static value with a
straight line for easy comparisons (Excel 2000/2002/2003/2004)
- When you're plotting points on a chart, a
flat line of static data can provide a visual point of reference, so it's easy
to compare the rest of the chart's data to that value.
- To add a line to represent a static value in
a chart, first create the chart. Be sure to include a column of data in the
chart's source data with all the same values. For instance, in a chart
comparing a product's price at different stores, you might want to include a
column to hold the item's national average price. When your chart is complete,
right-click on one of the static values' data points and choose Chart Type
from the resulting shortcut menu. In the Chart Type dialog box, choose Line
from the Chart Type list box and click OK.
Tip: Save smart tags as part of your
workbook (Excel 2002/2003)
- Since some smart tags relate directly to
your Outlook profile, Excel may not recognize the same ones for different
users or different operating systems. Fortunately, it’s easy to preserve your
workbook’s smart tags by embedding them. That way, you can let other users use
the same shortcuts you enjoyed, or make sure the smart tags are still
available when you open the same document on a different computer. To do so,
choose Tools | AutoCorrect Options from the menu bar. Then, select the Smart
Tags tab. Finally, select the Embed Smart Tags In This Workbook check box and
click OK. However, be aware that if a user doesn’t have the necessary
information, such as an Outlook Contact, the smart tag’s functions are
Tip: Preserve varying row heights when
you copy and paste in your macros (Excel 2000/2002/v. X/2003)
- It may be common place to copy and paste a
data range as part of a personalized macro. However, you may not realize that
the way you format the range reference can impact how Excel copies that data.
For example, if you enter a data range as 1:83 instead of A1:N83, you're
actually instructing Excel to copy and paste a range of rows, as opposed to a
range of cells. As a result, rows of varying height maintain those different
heights when Excel pastes them in a new location. If you copied and pasted the
data range as A1:N83, Excel wouldn't include row height in the attributes of
the data range. This can really come in handy if you're using smaller empty
rows to improve the worksheet's spacing!
Tip: Make your changes across the board
by selecting all of your worksheets first (Excel 2000/2002/v. X/2003)
- There are many scenarios in which the
worksheets in your workbook may need to have the same formatting and
structure. For example, you may have a worksheet for each month of the year,
all with identical structures and formatting, but different data. Before you
create one worksheet and copy it 11 times to cover each month, consider
selecting all of your worksheets before you create the spreadsheet's skeleton.
To do so, right-click ([control]-click in v. X) on any worksheet tab. Then,
choose Select All Sheets from the shortcut menu. Every worksheet is now
selected until you manually select an individual worksheet. Any changes you
make while they're all selected is applied to each worksheet instead of just
Tip: Rewrite history by changing the
length of time your History worksheet tracks (Excel 2000/2002/v. X/2003)
- By default, Excel's History worksheet lists
a shared workbook's changes tracked in the last 30 days. However, you can
change this setting by choosing Tools | Share Workbook from the menu bar.
Then, in the Share Workbook dialog box, click on the Advanced tab, if
necessary, and select the first option button in the Track Changes panel.
Enter the number of days you’d like to include in your History worksheet in
the corresponding spin box, and click OK.
Tip: Displaying text vertically in a cell
- You may come across some worksheet layouts
that would benefit from having text displayed vertically instead of
horizontally. For example, say you have created a complex reference
table and one row label caption could apply to many adjacent rows. You
can save space and make the table easier to read by merging the label cells
into one narrow, tall cell and then displaying the caption text vertically.
To display such text, select the appropriate cell and choose Format | Cells
from the menu bar. Then, switch to the Alignment tab. Finally,
click on the preview text box on the left side of the Orientation panel and
Tip: Prevent Excel from asking you
whether to update links to outside sources (2002/2003)
Tip: Excel 2002 provides quicker access to
special paste options
- When your workbook contains links to
external data (e.g., another workbook, another application, the web), Excel
prompts you upon opening the workbook whether you'd like those links updated.
In some cases, however, it may be more of a hindrance than a help. If you want
the information in your links to remain static, then you're always declining
the prompt. Fortunately, you aren't stuck with clicking the Don't Update
button every time you open the workbook. You can eliminate the prompt by
choosing Tools | Options to access the Options dialog box. Click on the Edit
tab, if necessary, and then deselect the Ask To Update Automatic Links check
box. Click OK to apply the changes and the prompt to update links no longer
appears when you open the workbook.
- In previous versions of Excel, the Paste
Special dialog box was often used to perform common tasks such as converting
formula results to values or transposing data. Of course, there were
other ways to do such tasks, but the Paste Special approach was usually the
easiest for most users. That's no longer true in Excel 2002. The
major drawback the Paste Special dialog box has is that it requires a lot of
steps to get straightforward results. First, you have to take a trip to
the menu bar to launch it. Then, you usually have to configure some
settings before you can get the results that you want. In Excel 2002,
the tasks that most often instigated launching the Paste Special dialog box
can be performed with an easily accessible toolbar option. To see for
yourself, copy some data. then, examine the standard Paste toolbar
button. You'll see that in Excel 2002 it now has a dropdown arrow
attached to it. When you click the arrow, Excel displays a menu of
often-used Paste Special settings that you can apply with a single click.
Tip: Find all the cells in a worksheet to which you've applied data
validation (Excel 97/2000/2002/v. X/2003)
- Data validation is a great Excel feature that allows you to
control what kind of data a user enters in your worksheet.
However, if you apply data validation often, it's possible to
lose track of which cells have data validation settings.
Fortunately, you can select all cells with any kind of data
validation in your worksheet, or you can select all of the cells
with the same validation settings as the active cell. To do so,
choose Edit | Go To from the menu bar to access the Go To dialog
box. Click the Special button, and then choose the Data
Validation option button. Two more option buttons become active:
the All option button and the Same option button. Choose the All
option button if you want to select any cell with data
validation. Choose the Same option button if you're interested in
selecting any cell with data validation that matches the
currently active cell. Click OK to apply the selection.
Tip: Hide items in a PivotTable's page field's
selection list (Excel 97/2000/2001/2002)
- In a PivotTable, a page field is a dropdown
list that lets you filter the PivotTable to show only the data associated with
the selected field item. For example, if a PivotTable's page field is
Country, you can filter the PivotTable to show just the data pertaining to
Canada. Then, selecting France from the dropdown list alters the
PivotTable to reflect the data associated with France. If you want to
prevent items from appearing in the page filter's dropdown list, right-click
on the page field's label ([control]-click on the Mac.) Then, select
Field Settings from the menu bar. Select the items you want to hide in
the Hide Items list box and then click OK. By default, data associated
with the hidden page field items is ignored from the PivotTable's totals.
Tip: Add a user-friendly button to run a macro in your workbook
(Excel 97/2000/2002/v. X/2003)
- There are several ways to access your macros, but some of them
are better suited to end users. For instance, it may be easiest
for end users who don't know as much about Excel to run a macro
by clicking a button. It only takes a few seconds to make this
possible. First, view the Forms toolbar by choosing View |
Toolbars | Forms from the menu bar. Then, click the Button icon
and click on your worksheet where you'd like the button to
appear. In the Assign Macro dialog box that displays, select a
macro rom the Macro Name list box and click OK. Or, if you've yet
to write the macro, you can enter the macro's name in the text
box, click the New button, and then create the macro when the
Visual Basic Editor opens.
Tip: Printing an entire workbook at once (Excel
- We recently showed you how to easily print a
particular worksheet range, as opposed to a complete worksheet. There
are probably also times when you want to print all of the worksheets in a
workbook. If so, you can use a technique similar to the one we
previously discussed: choose File | Print from the menu bar and select
the Entire Workbook option in the Print What panel. While this option is
definitely handy when you need to print a complete workbook that contains a
large number of worksheets, there may actually be an easier way if the file
only contains a few sheets. First, click on the first sheet tab in the
workbook. Then, while holding down the [Shift] key, click on the last
tab. Now, simply click the Print button on the Standard toolbar.
Since the default Print What setting in Excel is Active Sheet(s), you'll
produce the same result as if you had used the Print dialog box to set the
Entire Workbook option. Note that regardless of which technique you use,
printing all of the sheets does maintain any print area setting that may be in
place, so some sheets may not be printed in their entirety.
Tip: Counting text entries in Excel
- If you've ever tried to perform a simple
count, such as the number of names in a list, you may have been frustrated by
the results. For example, say you have a series of names in cells B2:B8.
In cell B16, you enter the formula:
- Instead of the expected answer of 7, the
formula returns 0. The reason is that Excel's COUNT function actually
only counts numeric values. To count any entry, regardless of type, you
must use the COUNTA function. For example, the formula:
- returns the number of name entries.
Note that this counts any non-blank cells in the specified range, so a cell
will be counted even if just displays an error message.
Tip: Printing a selected range without setting
a print area (Excel 97/2000/2002)
- By default, Excel is configured to print the
active worksheet in its entirety. This may often be fine for your needs.
However, there may be times when you want to print just a particular range on
a sheet. If you're going to print the same range repeatedly, you'll
typically set the worksheet's print area, but if you're just printing to meet
a one-time need, there's an easier way than going to the trouble.
Instead of setting a print area, 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. A nice
aspect to using this option is that it overrides a set print area without
requiring that you clear it if one exists for the worksheet with which you're
Tip: Removing unnecessary smart tag indicators
- Smart tags were introduced in Excel 2002 to
provide a quick way to act upon specific data entered into worksheets.
For instance, a smart tag that recognizes stock symbols provides an option
menu that lets you retrieve additional information about recognized stocks.
By default, smart tags are turned off. When the feature is activated, a
small triangle appears in the lower-right corner of cells containing
recognized data to let you know that an option menu is available.
Unfortunately, Excel may sometimes inappropriately interpret and apply smart
- For example, say that you have a worksheet
that contains stock symbols, and you want to take advantage of smart tags.
However, the same worksheet contains a cell with an internally used project
code entered in a cell: IMA. Since IMA is also a recognized stock
symbol, a smart tag indicator appears.
- To get rid of an unwanted smart tag
indicator, hover you mouse pointer over the cell containing it. Then,
click on the smart tag options button that appears and select Remove This
Smart Tag from the resulting menu.
Tip: Center your data on the page before you print it (Microsoft
Excel 97/2000/2002/v. X/2003)
- When you print a worksheet, you may feel that the printout would
look better if the data were centered on the page. If so, you
don't have to insert a lot of extra rows and columns or change
the individual page margins to get the data to print where you
want it. Instead, choose File | Page Setup from the menu bar.
Then, click on the Margins tab. You can now use the check boxes
in the Center On Page section to control how the data is
presented. Note that the centering options apply to the space
defined by the Top, Bottom, Left and Right margins, not the
physical dimensions of the page.
Tip: Easily remove all non-text entries from a
worksheet (Excel 97/2000/2001/2002)
- Even though using a worksheet template is
usually considered the ideal solution, it's common to just base an Excel
workbook on an already existing file when you need multiple files that share
the same general structure or data labels. For instance, you may create
monthly worksheets that list product sales for different regions. Since
the regions and product names are the same from month to month, it's easier to
copy the previous month's file and delete the sales figures than it is to
re-enter everything from scratch.
- In such cases, you may be able to reduce
your cleanup efforts, particularly if the variable data is in a lot of
noncontiguous ranges. Assuming you just want to keep the worksheet's
text entries, which act as descriptive labels, you can easily select all of
the numeric, date, and other remaining data at once and delete it.
- To do so, choose Edit | Go To from the menu
bar. Then, click the Special button to display the Go To Special dialog
box. Next, select the Constants option button and clear the Text check
box. Finally, click OK. Excel will select all of the non-text
entries. All you have to do now is press the [Delete] key to remove the
Tip: Take advantage of a shortcut key to redo an action (Microsoft
Excel 97/2000/2002/v. X/2003)
- Many Excel users are familiar with the shortcut key that allows
you to undo an action. By pressing [Ctrl]Z, it's easy to
eliminate the last action you made. However, what if you realize
that you should have kept the action, or the undo was accidental
in the first place? Fortunately, you can press [Ctrl]Y
([command]Y in v. X) to redo an action in a flash.
Tip: Change the default colors Excel assigns to
chart points (97/2000/2001/2002)
- You're probably so used to the standard
colors Excel assigns to chart lines and data points that you've never thought
about changing them. For example, when creating a column chart that
contains multiple data series, the first data series is always colored a
purplish-blue shade by default. The next series is a magenta color, the
next is pale yellow, and so on, for up to eight series. You may prefer
that the default colors are tailored to an overall design scheme or you may
simply want a change. Fortunately, it's easy to change the default
colors Excel uses to fill chart items and lines.
- To assign your own colors, 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 tab to 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.
- Any color setting changes you make will
apply only to the current Workbook. Also, changing the settings will
update any existing charts in the workbook that depend on the default settings
to use the new colors.
Tip: Quickly run your macros with keyboard shortcuts (Excel
- If you run a particular macro often, you may want to assign a
keyboard shortcut to run it so you don't have to access the Macro
dialog box each time you want to execute it. To do so, choose
Tools | Macro | Macros from the menu bar. Then, select the macro
to which you want assign the keystroke combination and click the
- When the Options dialog box opens, click in the Shortcut Key
text box and press the key that you want to assign to the macro.
By default, the shortcut key uses the [Ctrl] key in Windows and
an [option][command] combination on the Mac platform. Keep in
mind that there's a good chance you'll override an existing
keystroke combination that's built into Excel, so it's a good
idea to test the shortcut before assigning it to make sure you
aren't disabling a shortcut you're likely to miss having
- One way that you can reduce the impact on existing shortcuts is
to use a [Shift] combination. To do so, click in the Shortcut Key
text box, hold down the [Shift] key, and press the key you want
assigned to the combination. For instance, if you hold down the
[Shift] key and press the [G] key, you'll use the combination
[Ctrl][Shift][G] to run the macro on a Windows system. Mac users
can also use the [shift] key to create [option][command][shift] key
- Finally, click OK to close the Macro Options
dialog box. You can now close the Macro dialog box and run the macro using the
appropriate shortcut key.
Tip: Automatically open a workbook when you start Excel
- We recently discussed using your system's XLSTART folder when
you want to customize the default template that Excel uses for
new workbooks (in v. X, you use the Startup: Excel folder). We
thought we should point out that it can be used to automatically
open any workbooks when Excel starts. In fact, there are two
folders that Excel checks for workbooks when you start the
program. If any are found, Excel opens the files.
- Your operating system affects the XLSTART folder's location, so
the easiest way to access it is to use your operating system's
built-in Find or Search feature. In addition to the XLSTART folder, you can
specify another folder that Excel should check. To do so:
- 1. Choose Tools | Options from Excel's
- 2. Click on the General tab.
- 3. Enter the folder path in the At
Startup, Open All Files In text box (in older versions of Excel, the
Alternate Startup File Location text box.)
- 4. Click OK.
Tip: Require a password to access a
workbook (Excel 97/2000/2001/2002)
- If you want to prevent people from
opening a certain Excel workbook, you can assign a password to the file
to control who can access its contents. In addition, you can use a
password to limit the number of people able to edit the file. To
set such passwords, 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 password you want to use in
the Password To Open text box. As you type, the password is masked
with asterisks. this password determines who can view the file's
contents. Keep in mind that the password is case-sensitive, so
capitalization matters. If you also want to control whether edits
can be made, enter a different password in the Password To Modify text
- At this point, click OK. You'll
then need to 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 then click Yes to save over the original
workbook with the new password-protected workbook.
- When you later 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 now. If
you're unable to supply the right password, or you don't need to make
changes, you can click the Read Only button to open the file; however,
you won't be able to save changes you make to the workbook unless you
save to a new file.
Tip: Sort month and day names chronologically (Excel 97/2000/2002/v. X/2003)
- If you sort data based on a column of month
or weekday names, Excel sorts the data alphabetically. Chances are you'd
rather that it sort the information in chronological order. Fortunately, it's
easy to do so:
- 1. Select any cell in the column of month
or weekday data.
- 2. Choose Data | Sort from the menu bar.
- 3. Click the Options button.
- 4. Select the appropriate custom list
using the First Key Sort Order dropdown list.
- 5. Click OK on the open dialog boxes.
Tip: Change an existing Excel chart to a
different type (97/2000/2001/2002)
- After you create a chart, you may wonder how
the same set of data would appear as a different chart type. For
instance, you may start with a column chart, but then think that a line chart
might present the data better. In such cases, you don't have to start
from scratch with a new chart. You can simply change your existing chart
to a different chart type using a number of different techniques:
- The 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.
- 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. Select the chart type you want to use and click OK.
- 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
Tip: Format numbers as text currency values (Excel 97/2000/2002/v. X/2003)
- If you want to combine text strings with currency data, you may
find that results are unsatisfactory. That's because Excel simply
uses the numeric value when it concatenates the data, not the
formatting. To demonstrate:
- 1. In cell A1, enter $5,000.00.
- 2. In cell A2, enter the following formula:
="The total amount is " & A1
- Excel returns the following string:
The total amount is 5000
- Fortunately, it's easy to treat a numeric value as a text
currency string using the DOLLAR function, which uses the syntax:
- The number argument is the numeric value or
reference to the cell containing the value. The decimals argument specifies
the number of decimal places returned, but it's an optional argument. If you
leave it blank, Excel uses two decimal places.
- To try the function, change the formula in
cell A2 to the following: ="The total amount is " & DOLLAR(A1)
- Excel now returns: The total amount is
Tip: Refresh PivotTable data automatically (Excel 97/2000/2002/v. X/2003)
- You want to ensure that a PivotTable always
reflects the most current information if the data on which it's based is
volatile. Fortunately, you don't need to remember to refresh the PivotTable if
it's been a while since you've used its workbook. Instead, you can configure
Excel to automatically refresh the PivotTable when you open the file. To do
- 1. Select a cell in the PivotTable.
- 2. Open the PivotTable menu on the
- 3. Select Table Options.
- 4. Select the Refresh On Open check box.
- 5. Click OK.
Tip: Apply commonly used number formats with
shortcut keys (Excel 97/2000/2001/2002)
- Inevitably, you come across worksheet cells
that use number formatting that's inappropriate for the type of data they
display. For example, a formula that processes date values may
automatically return a value formatted as a date, even though it should really
appear as an integer. Or, involving a percentage value in a formula may
produce a result that's inappropriately formatted as a percent. To
resolve such situations, you probably select Format | Cells from the menu bar
and use the Format Cells dialog box to apply a more desirable number format.
In many cases, that requires a lot more work than is necessary. Instead,
you can use the following shortcut keys to apply several of the most
frequently used Excel number formats:
- [Ctrl][Shift][~] applies the General
- [Ctrl][Shift][$] applies the currency
number format with two decimal places and 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
Tip: Copy and paste noncontiguous ranges (Excel 97/2000/2002/v. X/2003)
- If you've ever tried to copy and paste
noncontiguous ranges, you may have found that Excel refused to comply with
your wishes. Although the error message Excel supplies can lead you to believe
that doing so is impossible, you actually can copy and paste noncontiguous
ranges--as long as the range selections are consistent among the columns
involved. Even better, Excel pastes the data as a contiguous range. To
- 1. Open or create a workbook that contains
data in range A1:C15.
- 2. Select range A1:A3.
- 3. While holding down the [Ctrl] key,
select ranges A8:A15 and C1:C3.
- 4. From the menu bar, choose Edit | Copy.
Excel returns an error message informing you that the command can not be
used on multiple selections.
- 5. While holding down the [Ctrl] key,
select range C8:C15.
- 6. Once again choose Edit | Copy from the
menu bar. This time, Excel displays the usual marquee border indicating that
the cells will be copied.
- 7. Select any empty cell in the worksheet
and choose Edit | Paste from the menu bar. Excel pastes the data without any
of the gaps that separate the source data.
Tip: Easily focus attention on a worksheet range (97/2000/2002/v. X/2003)
- If you ever need to zero in on a specific range in Excel,
there's an easy way to magnify the data so that you focus all
your attention on it. First, select the appropriate range. Then,
simply open the Zoom dropdown list on the Standard toolbar and
choose Fit Selection. Excel magnifies the view of the data by
whatever percentage is necessary to fill the viewable area of the
file's window, up to 400%. When you want to restore your normal
view of the worksheet, simply reopen the Zoom dialog box and
Tip: Easily check a range selection's
dimensions (Excel 97/2000/2001/2002)
- Judging a range's size can be a challenge,
particularly if the range extends beyond one viewable screen's worth of
worksheet real estate. Fortunately, there's an easy way to tell how many
rows and columns you've selected. 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. For example, the Name box displays 5R x 7C when the
selected range is five rows by seven columns. This can be handy for
situations that require you to select a specifically sized range, such as
certain copy/paste scenarios, transposing data, or creating array formulas.
Tip: Clarify data on line and area char with
drop lines (Excel 97/2000/2001/2002)
- Although Excel's 3-D charts do a pretty good
job of simulating 3-D objects on a 2-D screen, it can sometimes be difficult
to visually line up a data point with its corresponding position on the
chart's category axis. This is especially true when working with area or
line charts. The task can be even more difficult if the chart has been
rotated or the perceived angle of elevation has been changed.
Fortunately, you can add drop lines, which are visual aids that help anchor
each data point to the category axis.
- To display droop 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 and line charts.
Tip: Use a graphic file as a worksheet background (97/2000/2002/v. X/2003)
- You can easily replace the default white background that Excel
applies to all worksheet cells with a picture. To do so, choose
Format | Sheet | Background from the menu bar. Then, locate and
select a graphic file. Most common file types are supported,
including BMP, TIF, JPG, and GIF. Click the Insert button to
apply the background. Excel displays the image in a tiled format.
Note that the background is only visible onscreen--it won't
appear on printouts. To remove a background graphic, choose
Format | Sheet | Delete Background from the menu bar.
Tip: Print PivotTable groups on separate pages (97/2000/2002/v. X/2003)
A PivotTable can group data summaries using
multiple row fields. For instance, a PivotTable report might provide sales
revenue data summarized by several nested levels of detail. At the top level,
the PivotTable summarizes by state, then by county, then city, then individual
store, and finally by employee.
When a PivotTable incorporates multiple row
fields, you can print groups on their own pages. For instance, you may want to
print a new page whenever a new state value is encountered. To set up this
printing option, double-click on the appropriate field. Then, click the Layout
button. Select the Insert Page Break After Each Item check box and click OK on
the open dialog boxes. Note that you can enable this setting for all but the
lowest level of grouping fields (the rightmost row field).
Tip: Quickly size columns and rows to fit your
data (Excel 97/2000/2001/2002)
- It's incredibly easy to modify a worksheet's
column and row sizes. All you have to do is click and drag the
separating line between two column or row headings. However, if you're
constantly adjusting the sizes to fit changing data, there's an even easier
way to ensure that a column or row is big enough to display your data.
- Instead of dragging the heading separator
line, double-click on it. 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 accommodate 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.
Tip: Speeding data entry with AutoComplete
- You hardly have to do any data entry before
you stumble across Excel's AutoComplete feature. As you enter text into
a cell, Excel checks the other adjacent cells that precede it in the column to
see if any existing entries match what you're entering. As soon as Excel
detects a possible match, it fills the cell with the anticipated word.
You can then exit the cell to accept the suggestion keep typing to remove the
suggested word and finish your entry.
- For example, open a new workbook and enter
United States in cell A1. Then, enter U in cell A2. As soon as you
press U, Excel displays United States in the cell. Now, press the [Down
Arrow] key to select cell A3 and enter United Kingdom. Until you press
the K key, Excel assumes that United States is the entry you want to make.
As soon as you press K, Excel clears the suggested entry and you can finish
typing kingdom. Finally, select cell A4 and enter United States again.
excel can't differentiate between the prior entries until you press the S key,
at which point it makes an assumption about your current entry.
- In a case like this, waiting to get to the
character that differentiates similar entries can be tiresome.
Fortunately, you can speed the process. to do so, press [Alt] and the
[Down Arrow] key whey you begin your new entry. For example, select A5
and 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 alot of
entries. To demonstrate, enter Canada, France, Uganda, Ukraine, and
Zambia beneath the entries in the current column. Now, let's say that
you want to enter United States again. In the column's next available
cell enter Un and press [Alt][Down Artrow]. The dropdown list opens and
the first entry matching the criteria, United Kingdom, is selected.
Although you need to manually select the United States item, this technique is
much faster than scrolling through the entire list or typing the name until
Excel recognizes it.
Tip: Easily remove unprintable characters from entries
- We recently told you about the TRIM function, which removes
extra spaces from a text string. Unnecessary spaces are a problem
you often run into when importing data. Another common occurrence
that plagues imported data is the presence of unprintable
characters. For instance, text might contain control characters
that indicate carriage returns or line feeds. When viewed
onscreen, they often appear as small squares within cell entries.
You can easily remove such characters using the CLEAN function.
- Let's say your data is in cell A1. In cell A2, simply enter the
- This returns a string without the offending characters. You'll
usually want to save the results as static values so that you can
eliminate the original inappropriate entries. To do so, select
cell A2 and choose Edit | copy from the menu bar. Then, choose
Edit | Paste Special from the menu bar, select the Values option
button, and click OK.
Tip: Quickly create hyperlinks to data in
Office applications (Excel 97/2000/2001/2002)
- You're probably aware that Excel has long
been able to display hyperlinks in worksheets. For example, entering
in a worksheet cell automatically creates a hyperlink by default. You
may also already be aware that worksheet hyperlinks can point to data stored
in Excel worksheets, as well as information stored in Word and PowerPoint
files. Even if that's the case, you may not realize how easy it is to
create hyperlinks that point to data stored in Office documents.
Ordinarily, you might use Hyperlink dialog box to create your hyperlink.
In many cases, there's an easier way to create a hyperlink. In fact,
it's basically as easy as Copy and Paste.
- For example, say you want to create a
hyperlink on the first sheet in a workbook that pints to data on the last
sheet. First, select the cell containing the data to which you wan the
hyperlink to point. Then, choose Edit | Copy from the menu bar.
Next, select the cell that will contain the hyperlink to point. Then,
choose Edit | Copy from the menu bar. Next, select the cell that will
contain the hyperlink. Finally, choose Edit | Paste As Hyperlink from
the menu bar.
- This approach works best when the cell to
which the hyperlink points contains text data. In such a case, the text
you copy is used as the hyperlink's text. When the destination cell
contains numeric data, which includes dates, the hyperlink instead uses the
sheet name and cell reference for the hyperlink text. This means you
have to edit the hyperlink anyway, in which case the standard Insert |
Hyperlink approach actually may be more efficient.
- As mentioned, Excel's hyperlinks can point
to data in other Office applications. The Paste As Hyperlink technique
can be used with this data as well. For example, open a Word document
and select some text. Copy it as you normally would, then open an Excel
workbook and choose Edit | Paste As Hyperlink. Clicking the newly
created hyperlink displays the appropriate section in the Word document.
Tip: Customize Excel's default workbook (97/2000/2002/v. X/2003)
- If you'd like certain settings to always be applied to new
workbooks, you can override what file Excel uses as the basis for
new files. All you have to do is create a special template file
and save it in your XLSTART folder.
- Before you actually save the file, you need to identify where
your XLStart folder is located (or your Startup: Excel folder if
you're using a Mac). The location varies depending on both your
Office version and operating system, so your best course of
action is to use your operating system's Find or Search feature
to locate the folder. You may actually find multiple copies of
the folder, depending on whether your system incorporates user
profiles--choose whichever makes the most sense for your needs
and make a note of its path.
- Once you have the folder path, configure a workbook as you'd
like and then save it as a template. To do so, choose File | Save
As from the menu bar. Next, choose Template (*.xlt) from the Save
As Type dropdown list (the Format pop-up menu on a Mac). Using
the Save In dropdown list, locate and open the XLStart or
Startup: Excel folder. Finally, change the File Name setting to
Book.xlt (just name the file Workbook on a Mac) and click Save.
Tip: Always open a specific workbook whenever
you launch Excel (97/2000/2001/2002)
- If you almost use a particular file when
working with Excel, you should know that it's possible to have Excel
automatically open it for you every time you start the program. When you
launch Excel, it checks two folders and attempts to open all of the files
contained within them. To open a file automatically, simply move it into
one of the folders. If you don't want to move the file, you can instead
place a shortcut to it in one of the folders.
- The folder that Excel checks by default is
named XLSTART. Your operating system affects its location, so the
easiest way to access it is to use your operating system's built-in Find or
Search feature. In addition to the XLSTART folder, you can specify
another folder that Excel should check. To do so, 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.
Tip: Prevent startup Excel macros from running
- Macros are supposed to make your life
easier, but sometimes they may get in the way. For instance, you may
have workbooks that automatically run macros when they're opened. When
you need to quickly check out some existing data as you're rushing off to a
meeting, sitting through the macro can seem to be an eternity.
Fortunately, you can easily prevent startup macros from running. Simply
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.
Tip: Remove extra spaces from cell entries (Excel 97/2000/v. X/2002/2003)
- Every once in a while you'll discover that some text data in a
worksheet has trailing space characters. For example, information
you've imported from another data source may pad all entries with
extra spaces so that they are a uniform length. You can easily
remove such spaces using the TRIM function. The syntax is simply:
- The TRIM function isn't just good for fixing trailing spaces. It
removes all spaces except those that separate words. For
instance, let's say that cell A1 contains the text:
- THIS IS A TEST
- In cell A2, enter the following formula:
- The formula produces the result: THIS IS A TEST
- Keep in mind that if you want to permanently
save the converted text, you'll need to convert the formula results to static
Tip: Easily sum filtered results (Excel
- When you use the AutoFilter feature to
analyze a list, you'll often want to sum a field in the filtered results.
For example, if you have a table of all your company's sales, and you want to
see the sum of the sales for a particular state, you can filter the list on
the State field and sum the Sales field. If you change the list to
display a different state's sales results, you want the sum to update
- To achieve such results, you can't use the
SUM function as you normally might. Instead, you must use the SUBTOTAL
function as you normally might. Instead, you must use the SUBTOTAL
function with the filtered list. Fortunately, it's easy to create the
SUBTOTAL formula. In fact, the AutoSum button automatically creates a
SUBTOTAL formula when it's used in conjunction with a filtered list.
- 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 lists filter
criteria and the SUBTOTAL formula will return the sum of whatever data is
visible in the list.
Tip: Simplify moving related objects on a worksheet (Excel 97/2000/v. X/2002/2003)
- You can add some fairly elaborate graphical
elements to a worksheet using Excel's various drawing tools. For instance, you
can construct custom flow charts using AutoShapes and arrow objects, annotate
a chart using callout shapes, or overlay WordArt and shapes to identify a
worksheet's key sections. Unfortunately, a graphical masterpiece you've
created can cause headaches if you find that you need to move it. Moving an
intricate flowchart becomes a tedious reconstruction effort of moving and
realigning each piece--unless you take advantage of Excel's ability to group
objects. Once grouped, the objects can be repeatedly moved and formatted as a
- To create a group, hold down the [Shift] key
and click on the individual items you want grouped. Then, open the Draw menu
on the Drawing toolbar and choose Group (if the toolbar isn't visible, choose
View | Toolbars | Drawing from the menu bar). You're still able to format or
manipulate an individual item in the group if you need to. Simply click once
on the group, wait a moment, and then click on the individual object. To
ungroup the objects, simply click on the group, open the Draw menu, and choose
Ungroup. Note that you aren't limited to grouping drawing shapes. You can also
create groups that incorporate things such as charts, diagrams, and embedded
Tip: A simple way to convert numbers entered as
text (Excel 97/2000/2001/2002)
- You may occasionally come across numbers in
Excel that were entered with a preceding apostrophe ('), which formats the
number as text. You're especially likely to run into this problem when
you import data into Excel from some other application source. IF you
want to convert the value to a genuine number, you may find that the task
isn't as easy as you'd suspect. You might logically assume that changing
the cell's format to a number format would do the trick. Unfortunately,
such is not the case. If you only need to convert one or two values, you
can just re-enter them. However, for a large volume, there are more
- If you have Excel 2002, there's an easy
solution. You can take advantage of a smart tag that addresses this
specific problem. Simply 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.
- Things are a bit trickier if you have an
older version of Excel, but the process is still better than a brute force
re-entry effort. 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.
Tip: Find all occurrences of a word at once (Excel 2002/2003)
- In Excel 2002 and later, you can quickly
locate and navigate to all instances of a particular character string. To do
so, choose Edit | Find from the menu bar or press [Ctrl]F to launch the Find
And Replace dialog box. Then, enter the text you want to find in the Find What
text box. Finally, click the Find All button. The dialog box expands to show a
list of all the found matches. Clicking on the items in the list selects the
relevant cell, which is especially handy when working with large worksheets.
Another plus is that you aren't just limited to finding occurrences in the
current worksheet--you can search an entire workbook. To do so, click the
Options button on the Find And Replace dialog box. Then, choose Workbook from
the Within dropdown list and click Find All.
Tip: Change chart formatting without a dialog
box (Excel 97/2000/2001/2002)
- When you modify chart formatting, you
probably open one of the usual formatting dialog boxes. For example, to
change the shading and font properties for the chart's legend, you likely
select the legend and then choose Format | Selected Legend from the menu bar.
While there's certainly nothing wrong with this approach, you may not realize
that you can also format many chart elements using the same toolbar buttons
that you normally use to format worksheet cells. For example, the Bold,
Underline, Font Color, Fill Color, and other font-related toolbar controls can
all be used to format selected chart elements. although chart-specific
properties such as data marker styles and tick marks still require trips to
formatting dialog boxes, you'll probably find that a lot of your work is
simplified if you get into the habit of using the toolbar buttons.
Tip: Inserting a sheet based on a custom
template (Excel 97/2000/v. X/2002/2003)
When you insert a worksheet using the Insert |
Worksheet menu command, Excel automatically bases the new sheet on the default
template. However, you may not want to insert just a blank worksheet. For
instance, say your company uses a special Project Tracking template. A workbook
is dedicated to each client and there could be several project worksheets in
each workbook. Instead of inserting a blank sheet, you want to insert a blank
Project Tracking form. Fortunately, you can select what template Excel uses to
create a new worksheet. Instead of using the menu command, right-click on a
worksheet tab (or [control]-click on a Mac) and then choose Insert from the
shortcut menu. The Insert dialog box appears, displaying your PC's templates.
Simply select the appropriate one and click OK.
Tip: Move or copy a sheet from one workbook to
another (Excel 97/2000/2001/2002)
If you work with several related workbooks, there
may come a time when you decide you'd rather have all of the worksheets brought
together into a single file. Fortunately, you don't have to go through a
lot of tedious copying and pasting into new worksheets. Instead, you can
simply move or copy the existing sheets directly into another workbook.
There are two basic ways to accomplish these
tasks. You can drag the sheets to the appropriate file or use the Move Or
Copy dialog box.
To move a sheet from one workbook to another using
the mouse, start by opening both workbooks. Arrange the workbooks so that
you can see both simultaneously. An easy way to do so it to select Window
| Arrange from the menu bar, select the Horizontal option button, and click OK.
When both workbooks are visible, simply drag the appropriate worksheet's sheet
tab to move the sheet to the other workbook. To drag and create a copy of
the sheet, leaving the original in place, press and hold the [Ctrl] key when you
drag the tab (use the [command] key on the Mac.)
To use the Move Or Copy dialog box, open both the
source and destination workbooks. Then, select the sheet you want to move
or copy and choose Edit | Move Or Copy Sheet from the menu bar. When the
Move Or Copy dialog box appears, choose the appropriate workbook from the To
Book dropdown list. Next, choose which existing sheet in the destination
file should follow the new one from the Before Sheet list box. If you want
to copy the worksheet instead of move it, select the Create A Copy check box.
Finally, click OK.
Tip: Grab Excel's latest security and bug
patches (2000/2001/2002/v. X/2003/2004)
Microsoft has released a number of fixes for
Excel 2000 that plug both new and previously addressed security holes. In
addition, Excel 2004 for Mac receives its first service pack. Here's an
overview of the recent batch of patches and updates. Details on the specifics
behind the latest Excel security vulnerability are (probably deliberately)
sparse at this point. However, the exploit involves a now familiar method of
attack. It's a web-based attack that could give a remote evildoer complete
control of your system if you to visit a maliciously coded website while
logged into your PC with administrator rights. Microsoft lists the
vulnerability patch as a Critical update for Windows users and as an Important
update for Mac users. The affected versions are:
Excel 2002 (XP)
Excel 2001 for Mac
Excel v. X for Mac
(Excel 2002 isn't affected if you have Office
XP Service Pack 3 SP3 installed.)
Tip: Reduce unnecessary stops while spell
checking (Excel 97/2000/2001/2002)
- Spell checking worksheets helps you catch
typos, but it can be cumbersome. For instance, if a worksheet contains
acronyms, abbreviations, or field names, you may find yourself repeatedly
hitting the Ignore button. Fortunately, Excel can be configured to skip
some words that you may not want checked by ignoring all uppercase words.
To do so, run the spell checker on data that causes the Spelling dialog box to
be displayed. Then, select the ignore UPPERCASE check box and click OK.
You should be aware that this setting carries through to all subsequently
spell-checked workbooks until it's specifically disable.
Tip: Eliminate warnings about non-existent
macros (Excel 97/2000/2002)
- Depending upon your set security level,
Excel will usually warn you if a workbook you're tying to open contains
macros, providing you with the option to cancel the process. However,
sometimes you may wonder what Excel is warning you about--the workbook in
question doesn't seem to contain any macros.
- Most likely, a macro was at one time
associated with the file. Even though the macro is now gone, the VBA
module that is was stored instill exists. The module is empty, but Excel
displays the warning message whenever you open the file.
- Fortunately, it's usually easy to eliminate
this nuisance. However, before making any changes to your file, it's a
good idea to make a copy of the workbook, just in case you accidentally make a
change that prevents something from working correctly.
- When you're ready to modify the file, the
first step is to see if there's a VBA code module associated with the file.
First choose Tools | Macro | Visual Basic Editor from the menu bar.
Next, look for a folder named Modules in the Project Explorer. If the
Project Explorer isn't visible, choose View | Project Explorer from the menu
- Assuming you find a Modules folder
associated with your workbook, double-click on it to reveal its objects.
The folder can contain several module objects. Double-click on each
module. If the macros were deleted, you should see blank code windows
(although they might contain statements like Option Explicit, you shouldn't
see any actual macro procedures).
- Assuming that a module is indeed blank, you
should be able to delete it with out any repercussions. To do so,
right-click on the module icon in the Project Explorer and then choose Remove
Module1 (or the appropriate name) from the shortcut menu. You'll then be
given the chance to export it. Since you should have nothing to export,
just click No. Repeat these steps for each module in the workbook,
Finally, close the VBE and save the workbook.
Tip: Apply a smart tag action to several
cells at once (Excel 2002)
- Excel 2002 uses smart tags to simplify
fixing several types of common errors. When you want to apply the same
error correction smart tag action to a number of cells, you don't have to work
with each cell individually.
- For instance, say that you've imported data
into Excel and it contains numbers formatted as text. When you select an
individual cell, the smart tag button appears and you can use the associated
menu to convert the data to a number. While smart tags are designed to
be timesavers, applying such a change to each cell individually would be a
- Fortunately, you can apply a smart tag
action to a range. The range must be contiguous and the cell that's
active when you finish the selection must contain the appropriate smart tag.
After you make the selection, the smart tag button appears next to the
highlighted range. Simply choose the appropriate smart tag menu item to
apply the action to all of the relevant cells in the selection.
Tip: Quickly add several sheets to an Excel
- If you need to add worksheets to an Excel
file, chances are that you use the Insert | Worksheet menu command.
There's a much faster way to go about this task: Simply press
[Shift][F11] and Excel adds a new blank worksheet before whichever sheet you
were working on at the time. If you need to add several worksheets to a
workbook, you can use either technique to create all of the sheets at once.
While holding down the [Shift] key, click on the same number of worksheet tabs
as new sheets you want to create. Then, use either the menu command or
shortcut key as you normally would.
Tip: Calculate the number of days in a month
- You may have struggled if you've ever needed
to create functions based on the last day of any month, or the number of days
in a variable month. If so, this technique may come in handy.
There's a trick involving the DATE function that will return the last day of a
month. It even takes leap years into account. The DATE function
uses the syntax:
- =DATE(year, month, day)
- A formula such as
- returns the date for September 17, 2003.
- The trick for returning the last date in a
month is to specify a month argument that's one after the month you really
want and a day value of 0. For instance, a formula of
- returns the date February 28, 2003.
- To calculate the number of days in a month,
you can wrap the DATE formula in a DAY function. For example, the
- returns 31, the number of days in
- As mentioned, the technique takes leap years
into account. For instance, the formula
- returns 28, the standard number of days in
February. However, the formula
- return 29 because the year 2000 was a leap
Tip: Copy settings from one Excel chart to
- If you've ever needed to see a set of data
displayed as several different chart types, you probably wished that Excel's
Format Painter could be used to copy any customized formatting you've applied
from chart to chart. As it is, if you create a chart and make formatting
changes like customized fonts and backgrounds, you need to make the same
changes to subsequent charts you create to preserve a consistent look.
- Here's an easy way to reduce your workload.
Let's say that you need to create a bar chart and a line chart based on the
same set of data. Create your bar chart and set up the formatting you
need. Then, select the chart and choose Edit | Copy from the menu bar.
Next, click in your worksheet and choose Edit | Paste from the menu bar to
duplicate your chart. Now, simply choose Chart | Chart Type from the
menu bar, select the chart type you need, and click OK. You may still need to
make some formatting changes, but the majority of your work should be done.
Tip: Forecasting trends using charted data
- When you add a trend line to a chart, you
can extend the trend line to predict likely results based on your existing
data. To do so, select an appropriate chart and then choose Chart | Add
trend line from the menu bar. Select an appropriate trend line from the
Trend/Regression Type panel (you can use any type except Moving Average with
this technique). Then, click on the Options tab.
- You can extend the trend line forward or
backward from your existing data using the controls in the Forecast panel.
Once you've set the desired number of units, click OK. Now, your trend
line is extended forward or backward and you can use the trend line to
visually deduce the approximate y-value for a given x-value.
TO VISIT BUSINESS WEBSITE LINKS'
Home | Company Info | Pricing | Contacts |
Client Directory | Computer
Tips | News |
Business Website Links, LLC
• 8041 Via Hacienda
Palm Beach Gardens
Copyright ©2005 all rights reserved by
Business Website Links, LLC
Web Host and Design by Business Website Links, LLC