Excel Quick Reference Cards

An extensive collection of Excel QRCs is available:

Add a Drop-n List Filling in the Gaps

Allow user to select a predefined entry. Steps to fill in missing cell values.
Adjusting a Range's Starting Point Importing Data From Text Files
Allows user to modify selection. Restructuring imported data.
Cell Formatting: Numeric vs. Stylistic Hyperlink Fix for Excel 2002
Differences between. Procedure for recreating hyperlinks.
Changing Text to Numbers Locate Phantom Links in a Workbook
Resolves imported numbers. Identify and eliminate workbook links.
Compare Lists With Conditional Formatting Making Gantt Charts
Identifies changes with highlighting. Creating in Excel.
Count AutoFiltered Rows Managing Conditions
Display a permanent count of rows. A quick look at how the functions work.
Creating a Default Chart Type Obscure Excel Shortcuts
Allows user to customize default chart. Using obscure user-interface elements.
Date Calculations Plot Every Nth Data Point
Create specific date calculations. Plot hidden data.
Defined Names Auto Update Chart Range Shade Alternate Rows
Update a chart automatically. Steps to apply shading.
Display Multiple Charts on a Sheet Sorting Dates by Month
Single Chart sheet for multiple charts. Sort a list of based on the month.
Dynamic Chart Titles The Power of Pivot Tables
Adding 'active' or 'live' titles and text. Using as an interactive summary of data.
End-of-Month Calculations Track Progress with Thermometer Chart
Method for calculating month end date. Create a chart that tracks progress.
Excel Charts in PowerPoint Slides Understanding Names
Adding a chart to PowerPoint. Using names to represent cells/ranges.
Excel Protection Options Using Excel with Other Office Applications
Protecting content and structure. Steps to use when copying.
External Data Validation Retaining Formatting After a Paste Multiply
Using the data validation feature. Using the Paste Special command.

Add a Drop-n List

Inserting a drop-n list to a cell will ensure that erroneous data will not be entered into a cell.

Assume that you have an input cell in which the user is supposed to enter a month name:
January, February, and so on. Here's how to add a drop-n list to that cell to save some
keystrokes.
 Step Action

1 Enter the items for your drop-n list into a list on the worksheet, one item per cell.
In this example, we'll assume that the month names start in cell E1 and extend n
to E12, but they can be in any out-of-the-way location on the worksheet. (In Excel
terminology, a rectangular group of cells (such as E1 to E12) is called a range.)

2 Select the cell that will contain the drop-n list. If you'd like more than one cell to
display the same list, just select them all now rather than setting them up one at a
time. (Click and drag to select a range; hold n Ctrl while you click to select non-
adjacent cells.)

3 Choose Data > Validation to display the Data Validation dialog box.

4 Click the Settings tab.

5 In the Allow field, select List.

6 In the Source field, specify the range that contains the list items. In this example,
the items are in cells E1 to E12, so type =E1:E12 into the field. (Include non-
adjacent cells with a comma, as in =E1:E12, E15, F5, H18.)

7 Click OK.
After performing these steps, you'll see a drop-n arrow whenever any of the drop-n cells you just
defined is "active" (that is, selected and awaiting input). Click the arrow and choose a month from
the list. If you try to type something else into the cell, you'll get scolded in the form of a pop-up
message.

If you'd like to provide your own wording for invalid entries, use the Error Alert tab in the Data
Validation dialog box, and enter your own text in the "Error message" field.

If your list of items is relatively small, you can bypass Step 1 and enter the list items directly into
the Source field in Step 6. Just separate each list item with a comma.

Adjusting a Range's Starting Point

When selecting a range of cells in a worksheet, the most common method used is to hold n the
Shift key and use the cursor-control keys to move to the ending point in the range. Sometimes,
after selecting a range, you may realize that you should have started a bit earlier in the selecting.
For instance, you may originally select the range C3:H12, and then realize that you should have
started the selection at B2 instead of C3.

While you could start the selection all over again, there is a much simpler way to extend
the selection so that it includes the revised starting point. The key is to remember that
when you hold n the Shift key, selections are always expanded (or contracted) in relation
to the "currently selected cell." Try these steps, and you will see what it means:

Step Action
 1 Using the keyboard, select the range C3:H12. Notice that the selected range is
shaded, but one cell (C3) is a different color than the others. This tells you that C3
is the selected cell.

2 Release the Shift key, and then press Ctrl+. (the period) two times. Notice that the
selected cell moves first to H3 (the top right corner of the selection) and then to
H12 (the bottom right corner of the selection).

3 Hold n the Shift key and press the Up Arrow once and the Left Arrow once. Notice
that the selection is extended on the top-left corner, opposite of the selected cell.
There is another way to accomplish the same task, which involves one less keystroke. All you
need to do, in step 2, is continue to hold n the Shift key and press the Tab key. H12 immediately
becomes the selected cell.

Remember that all range extending is done relative to the currently selected cell. With your initial
range selected, you can press Tab or Shift+Tab to step through the cells in the range, one at a
time. When you press Tab, you cycle through them from left to right and top to bottom; when you
press Shift+Tab, you cycle through them in reverse order.

Try different combinations of "active cells" and holding n the Shift key while pressing the arrow
keys.

Cell Formatting: Numeric vs. Stylistic

Excel provides two ways of cell formatting: numeric and stylistic.

Numeric formatting refers to how a value appears in a cell. You can choose from a list of
predefined formats or define your own. The number format does not affect the actual value stored
in the cell. Assume a cell contains the value 3.1415926535. If you apply the format to display two
decimal places, the number appears as 3.14. When you use the cell in a formula, the actual value
(3.1415926535) not the display value is used.

Stylistic formatting refers to the cosmetic formatting (color, font, border, shading etc) that can be
applied to a cell.

Formatting Tips
 To enter a custom number format choose Format, Cells and click the Number tab. Select
Custom from the category list and enter your format in the Type box. Following are a few
custom number formats.
 To display values in thousands, enter the following in the Type box: 0, (that's a zero and
a comma). When this number format is used, the cells will contain the correct numerical
value, but will be displayed without the last three digits. So 25000 is displayed as 25.
 To display values in millions, use an extra comma: 0,,
 To use one or more decimals use a decimal point and zeroes: 0.0, This will display 25100
as 25.1
 Some types of data, such as stock market quotes, normally display as fractions, not
decimals. To enter a fraction in Excel, type the whole number (or integer) followed by a
space, and then type the fraction, using a slash (for example, 5/8). If you type only a
fraction, Excel may interpret it as a date (so it might read 5/8 as May 8). To avoid this
mistranslation, enter 0, a space, and then the fraction.
 When you enter a fractional value, Excel automatically applies a fraction number format
that reduces it to the smallest possible denominator. For example, if you enter 16 2/8,
 Excel displays the number as 16 1/4. In some cases, however, you'll want the fractions to
use a common denominator. For example, you might want the value 16 2/8 to be shown
as 16 4/16. To obtain this result, select your cells and choose Format, Cells. Then select
the Number tab and choose Fraction from the Category list. Finally, select the desired
number format from the Type list.

Changing Text to Numbers

Sometimes when you import files from other sources, numeric values may appear to be numbers
but act like text values. To resolve this behavior, these values must be converted into numbers.

Method 1: Multiply these text values by 1.

To convert the text values, follow these steps:

Click a blank cell in the worksheet and make sure that the cell is not formatted as text. Then type
1 in the cell.

1. With that cell selected, click Copy on the Edit menu.

2. Select the range that contains values you want to convert to numbers.
3. Click Paste Special on the Edit menu.
4. Under Operation, click Multiply, and then click OK.

This method converts the text to numbers. You can tell whether you successfully converted the
text values by viewing the alignment of the number. If you use the General format and the values
are aligned to the right, the values are numbers; text values are aligned to the left.

Method 2: Use the Text Import Wizard to convert text to numbers.

To convert the text values by using the wizard, follow these steps:

1. Select the range that contains values you want to convert to numbers.
2. On the Data menu, click Text to Columns.
3. Click Next twice to proceed to step 3 of the wizard.
4. In Column Data Format, click General, and then click Finish.

This method converts the text to numbers. You can tell whether you successfully converted the
text values by viewing the alignment of the number. If you use the General format and the values
are aligned to the right, the values are numbers; text values are aligned to the left.

Comparing Two Lists with Conditional Formatting

Excel's Conditional Formatting feature has many uses. Suppose you need to compare two lists,
and identify the items that are different. The figure below shows an example. These lists happen
to contain text, but this technique also works with numeric data.
 The first list is in A2:B19, and this range is named OldList.
The second list is in D2:E19, and the range is named
NewList. The ranges were named using the Insert > Name >
Define command. Naming the ranges is not necessary, but it
makes them easier to work with.

As you can see, items in OldList that do not appear in

NewList are highlighted with a yellow background. Items in
NewList that do not appear in OldList are highlighted with a
green background. These colors are the result of Conditional
Formatting.

How to do it
Step Action

1 Start by selecting the OldList range.

2 Choose Format > Conditional Formatting.

3 In the Conditional Formatting dialog box, use the drop-n list to choose Formula Is.

4 Enter this formula:

=COUNTIF(NewList,A2)=0

5 Click the Format button and specify the formatting to apply when the condition is
true (a yellow background in this example).

6 Click OK.

The cells in the NewList range will use a similar conditional formatting formula.

Step Action

1 Select the NewList range.

2 Choose Format > Conditional Formatting.

 3 In the Conditional Formatting dialog box, use the drop-n list to choose Formula Is.

4 Enter this formula:

=COUNTIF(OldList,D2)=0

5 Click the Format button and specify the formatting to apply when the condition is
true (a green background in this example, as shown above).

6 Click OK.
Both of these conditional formatting formulas use the COUNTIF function. This function counts the
number of times a particular value appears in a range. If the formula returns 0, it means that the
item does not appear in the range. Therefore, the conditional formatting kicks in and the cell's
background color is changed.

The cell reference in the COUNTIF function should always be the upper left cell of the selected
range.

Count AutoFiltered Rows

Q. When using AutoFiltering, Excel's status bar displays the number of qualifying rows. But for no
apparent reason, that number often vanishes. How can we keep this number visible while we
work?

A. AutoFiltering a list hides rows that don't meet your filter criteria. After applying filtering criteria,
Excel shows the record count on the status bar, but this value disappears when the sheet is
calculated.

To display a permanent count of the visible rows in an AutoFiltered list, create a formula using
Excel's SUBTOTAL function. The first argument for SUBTOTAL specifies the type of operation.
(An argument of 2 displays a count of the visible cells in a range.)

The figure below shows a list in rows 6 through 3006. The formula in cell D3 is:

=SUBTOTAL(2,A6:A3006)

The formula counts the number of visible cells in the range (minus the header row). Apply
different filtering criteria, and the formula updates to show the new count. The SUBTOTAL
function only works for AutoFiltering and outlining. If you hide rows manually, it won't return the
correct result.

Creating a Default Chart Type

If you use a specific chart type when you graph worksheet data, or if you spend a lot of time
changing the settings on one of the standard charts, you can save time by making that chart type
(or those settings) the default. You can use a chart you have already created as the basis for the
new default chart type. This is especially useful if you have specific chart options or formatting
that you want to appear.

To change the default chart type, follow these steps

Step Action

1 Display the chart that you want to use as a sample for the default chart type. If the
chart is an embedded chart, click the chart to select it. If the chart is on a chart
sheet, make it the active sheet.

2 Right-click in a blank area of the chart, away from the chart objects. From the
shortcut menu, choose Chart Type. In the Chart Type dialog box select the
Custom Types tab.

3 Click the Set As Default Chart button. A message appears confirming that you
want to change the default chart type. Choose Yes. The Add Custom Chart Type
dialog box may appear. If it does, enter a name and description for your new
default chart. Choose OK to accept the name and description.

4 Choose OK to complete the default chart type change.

The Custom Types tab lists the built-in custom chart types. If you select the User-Defined button
on this tab, the list changes to display the default chart type and any custom chart types you have
added. Displaying the User-Defined list is not a requirement for changing the default chart type,
but you can use it to verify the change.

Date Calculations

Calculating the number of years between two dates

The following formula calculates the number of years between two dates. This formula assumes
that cells A1 and B1 contain dates.

=YEAR(A1)- YEAR(B1)

This formula uses the YEAR function to extract the year from each date, and then subtracts one
year from the other. If cell B1 contains a more recent date than the date in cell A1, the result will
be negative.

Note that this function doesn’t calculate full years. For example, if cell A1 contains 31-Dec-1998
and cell B1 contains 1-Jan-1999, the formula returns a difference of one year, even though the
dates differ by only one day.

Calculating a person’s age

A person’s age indicates the number of full years that the person has been alive. The formula
above will not work correctly in this case. You can use two other formulas to calculate a person’s
age.

The following formula returns the age of a person whose date of birth you enter into cell A1. This
formula uses the YEARFRAC function, available only when you have the Analysis ToolPak add-in
installed.
=INT(YEARFRAC(TODAY(),A1,1)

The following formula doesn’t rely on an Analysis ToolPak function. It uses the DATEDIF function
to calculate an age.

=DATEDIF(A1,TODAY(),”Y”)

Defined Names for Automatically Updating a Chart

Range

To set up a chart that will automatically update as you add new information to an existing chart
range, you will create defined names that dynamically change as you add or remove data.

This QRC includes two methods for using defined names to automatically update the chart range.

Note: The methods in this section assume there are no more than 200 rows of data. You can
revise the defined names so that they use the appropriate number and reflect the maximum
number of rows.

Method 1: Use OFFSET with a Defined Name

To use this method, follow these steps:

Step Action

1 In a new worksheet, type the following data:

A1:Month B1:Sales
A2:Jan B2:10
A3:Feb B3:20
A4:Mar B4:30

2 On the Insert menu, point to Name, and then click Define.

3 In the Names in workbook box, type Date.

4 In the Refers to box, type:

=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)

5 Click Add.

6 In the Names in workbook box, type Sales.

7 In the Refers to box, type:

=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)

8 Clear cell B2, and then type the following formula:

=RAND()*0+10

Note: This formula, which uses the volatile RAND function, automatically updates
the OFFSET formula that is used in the defined name "Sales" when you enter
new data into column B. The value 10, which is used in this formula, is the original
 value of cell B2.

9 Select $A$1:$B$4.

10 Create the chart and add the defined names in the chart.
a. On the Insert menu, click Chart to start the Chart Wizard.
b. Click a chart type, and then click Next.
c. Click the Series tab. In the Series list, click Sales.
d. In the Category (X) axis labels box, replace the cell reference with the
defined name Date. For example, the formula might be similar to the
following:

=Sheet1!Date

e. In the Values box, replace the cell reference with the defined name Sales.
For example, the formula might be similar to the following:

=Sheet1!Sales

f. Click Next.
g. Make any changes you want in step 3 of the Chart Wizard and click Next.

h. Specify the chart location and click Finish.

Method 2: Use a Database, OFFSET, and Defined Names

You can also define your data as a database and create defined names for each chart data
series.

To use this method, follow these steps:

Step Action

1 In a new worksheet, type the following data:

A1:Month B1:Sales
A2:Jan B2:10
A3:Feb B3:20
A4:Mar B4:30

2 Select the range A1:B4, and then click Set Database on the Data menu.

3 On the Formula menu, click Define Name.

4 In the Name box, type Date.

5 In the Refers to box, type:

=OFFSET(Database,1,0,ROWS(Database)-1,1)

6 Click Add.

7 In the name box, type Sales.

8 In the Refers to box, type:

=OFFSET(Database,1,1,ROWS(Database)-1,1)
 9 Select $A$1:$B$4

10 Repeat step 10 from method 1 to create the chart and add the defined names to
the chart.
As long as the data that you want to appear in your chart is defined as a
database, the chart is updated automatically as you add new data.
NOTE :If you are creating a series chart that plots every value in an adjacent
block of cells in single column, and the block of cells starts with the first row, you
can use either of the following formulas in the Refers to box for the defined name:
=INDIRECT("Sheet1!$a$1:$a"&COUNT(Sheet1!$A:$A))

Or
=Sheet1!$A$1:OFFSET(Sheet1!$A$1,COUNT(Sheet1!$A:$A),0)

To use a block of cells that start with a cell on a row other than the first row,
reference that row in the first cell reference and add the starting row number to
the count to find the last row number. To plot adjacent nonnumeric entries (for
example, labels), use COUNTA instead of COUNT.

Display Multiple Charts on a Single Chart Sheet

An Excel chart can appear embedded in a worksheet or reside in a separate Chart sheet. Here's
an option for storing multiple charts on a single Chart sheet.
Create charts as usual, placing them in a worksheet. Select any blank cell in the worksheet and
press F11; this creates an empty Chart sheet to hold the embedded charts.

Reactivate your worksheet, click an embedded chart, and select Chart > Location to display the
Chart Location dialog box. Choose "As object in" and specify the empty Chart sheet. Excel will
transfer the embedded chart to your Chart sheet.

Select your remaining charts, and use Chart > Location to move them to the Chart sheet. Now
you can arrange and size of the charts any way you like. Putting multiple charts on a single Chart
sheet lets you use the View, Sized with Win command (available when the Chart sheet is active)
to scale the charts to the win size and dimensions.

At right is an example of a Chart sheet that contains three charts. When the win is resized, the
charts adjust to fill the win automatically.
Dynamic Chart Titles

Adding 'active' or 'live' titles and text to charts allows you to make a change in a worksheet and
have that change reflected in a title in the chart.

Follow these steps

Step Action

1 Create your chart

2 Add titles, datapoints, axis names, etc.

3 Select the title you want to modify. Small handles should appear around the title.

4 In the formula bar enter the address of the cell you want to use for the title. For
instance, if the title is in cell A1 of the MySheet worksheet, you would enter the
following in the formula bar:
=MySheet!$A$1

Whenever the contents of A1 are changed, Excel updates the information in the chart's title.
End-of-Month Calculations

A method for calculating the date at the end of the next month is to use the EOMONTH function.

One approach is to AutoFill for the last days. If you want the last days of a series of months in the
first column, beginning at A4, use the following steps:

1. In cell A4, enter the last day of the current month, such as 30 Jun 2000.
2. In cell A5, enter the last day of next month, such as 31 Jul 2000.
3. Select both cells, A4 and A5.
4. Click on the small square handle at the bottom right corner of the selection.
5. Drag the mouse nward as many cells as desired.

The result is that the mouse you are dragging over in Step 5 is filled with end-of-month dates for
the next however many months. A slight variation on these steps could also be used:

1. In cell A4, enter the last day of the current month, such as 30 Jun 2000.
2. Select cell A4.
3. Right-click on the small square handle at the bottom right corner of the selection.
4. Drag the mouse nward as many cells as desired. When you release the mouse button,
Excel displays a Context menu.
5. From the Context menu, choose Fill Months.

If you do not want to use AutoFill, and prefer to use formulas, you could enter the starting end-of-
month date in cell A4, and then the following formula in A5:

=DATE(YEAR(K32),MONTH(K32)+2,1)-1

This formula calculates the date for the first day of the month two months in the future, and then
subtracts one from it. The result is the last day of the next month. The formula wraps around the
end of year, since the DATE function increments the year properly if the month value provided is
greater than 12.

Another formulaic approach is to use the following:

=A4+32-DAY(A4+32)

This formula works because it adds 32 to the starting date (to make sure you are past the end of
the following month), and then subtracts the number of days the result is past the end of the
month.

Excel Charts in PowerPoint Slides

By default, charts in PowerPoint are accomplished using Microsoft Chart. Many people are
surprised that charts can also be added to PowerPoint slides with Excel. Microsoft Chart appears
when you tell PowerPoint you want to have a "Chart" slide (for example, by using Format > Slide
Layout, or New Slide). You'll also get Microsoft Chart if you choose a "Content" slide, and then
you click on the picture of the chart on the slide.
Putting an Excel Chart in a Slide
Each version of PowerPoint has a slightly different method for putting an Excel chart on a slide,
but all of them will work with the old-fashioned Insert > Object approach.

If you want to put a "static" Excel chart in a PowerPoint slide – that is, a chart that doesn't
have any moving parts – here's how:

Step Action

1 Use New Slide or Format > Slide to put an "Object" or "Content" box on a slide.

2 Click once on the "Object" or "Content" box.

3 Click Insert > Object > Microsoft Excel Chart. PowerPoint responds with a
placeholder chart attached to a spreadsheet.

4 Click Sheet1 and you'll be able to see the data that Excel is using to draw the
chart.

5 Change the data to suit your circumstances. Remember that you have all of
Excel's tools at your disposal, so you can readily copy another spreadsheet, for
example, or perform calculations, hide columns, and so on.

6 Click Chart1 to bring the chart back, then right-click on the chart and choose Chart
Type to pick the kind of chart you'd like to see. Then bring up the Chart Toolbar,
change axes labels, fonts, and so on.

7 Finally, click on the outside of the chart and re-size it to fit the slide any way you
like.

Animating an Excel Chart

If you want to animate an Excel chart – that is, make different parts of the chart enter or
leave the slide as you click on it – you have to use a slightly different technique.

Step Action

1 It's easiest to create the chart in Excel before you bring it into PowerPoint. Start
Excel, enter your data, and click on the Chart Wizard to create your chart. Put the
chart on its own Chart sheet, and save the whole file before you start working with
PowerPoint.

2 In PowerPoint, use either New Slide or Format > Slide to apply a "Content" or
"Chart" box on the slide that you want to contain the animated chart.

3 If you have a "Content" box, click once on the picture of a chart. If you have a
"Chart" box, double-click on it. In either case, PowerPoint brings up a Microsoft
Chart chart.

4 Click Edit > Import File and find the spreadsheet you created in Step 1. Click on
the Chart sheet that you want, and click OK.

5 Your chart should appear in the slide. Click outside the chart, to go back to
PowerPoint. Click Slide Show > Custom Animation.

6 If you're using PowerPoint 97, check the box next to the chart you want to
 animate, then click the Chart Effects tab. That's where you can choose how to
introduce chart elements ("by Series" means all the bars with one color appear,
then all the bars with the next color, etc.; "By Category" means that each bunch of
bars corresponding to one tick on the y-axis appears, then the next bunch, etc.)
Click the Preview button a few times and you'll get the hang of it.
If you're using PowerPoint 2002 (the version in Office XP), you have to start by
choosing an animation effect for the chart as a whole. Once you've animated the
chart as a whole, you can click the n arrow next to the Chart and pick Effect
Options > Chart Animation. Choose the way you want the chart elements to be
animated in the Group Chart drop-n list.

It isn't easy, but it's very effective and powerful. Don’t forget this quick reference card if you import
charts from existing worksheets.

Excel Protection Options

Excel provides many ways to protect your spreadsheets. This QRC serves as an overview of
options:

 Worksheet Protection
 Workbook Protection
 VB Project Protection

For more details, consult Excel’s help-file (Topic: Security - Workbook and Worksheet Protection).

Worksheet Protection
This section deals with protecting the contents of cells and objects on a worksheet.

To protect an entire worksheet

Activate the worksheet to be protected, then choose Tools > Protection > Protect Sheet. You will
be asked to provide a password (optional). If you do provide a password, you will need to use it to
unprotect the worksheet. When your protect a worksheet using this procedure, Excel does not
allow you to change any of the cells.

To protect only a portion of the worksheet

There may be times when you only want to protect some of the cells in a worksheet as opposed
to an entire worksheet. Every cell has two key attributes: Locked and Hidden. By default, all cells
are locked, but they are not hidden. Furthermore, the Locked and Hidden attributes come into
play only when the worksheet is protected. To allow a particular cell to be changed when the
worksheet is protected, you must first unlock that cell.

Unlock a cell as follows:

1. Select the cell or cells that you want to unlock.

2. Choose Format/Cells.
3. In the Format Cells dialog box, click the Protection tab.
4. Remove the checkmark from the Locked checkbox.

Keep in mind that locking or unlocking cells has no effect unless the worksheet is protected.

Hide a cell as follows:

1. Select the cell or cells that you want to unlock.

 2. Choose Format/Cells.
3. In the Format Cells dialog box, click the Protection tab.
4. Add a checkmark to the Hidden checkbox.

Keep in mind that changing the Hidden attribute of a cell has no effect unless the worksheet is
protected.

When a cell's Hidden attribute is set, the cell is still visible. However, it's contents do not appear in
the Formula bar. Making a cell Hidden is usually done for cells that contain formulas. When a
formula cell is Hidden and the worksheet is protected, the user cannot view the formula.

New Protection Features in Excel 2002

Excel 2002 (also known as Excel XP) offers new features for protecting worksheets that let you
specify what the user can do when the worksheet is protected:

 Select locked cells  Delete columns

 Select unlocked cells  Delete rows

 Format cells  Sort

 Format columns  Use AutoFilter

 Format rows  Use PivotTable reports

 Insert columns  Edit objects

 Insert rows  Edit scenarios

 Insert hyperlinks
To set things up so VBA macro can make changes to Locked cells on a protected sheet, use a
macro that protects the worksheet, but still allows changes via macro code. The trick is to protect
the sheet with the UserInterfaceOnly parameter. Here's an example:

ActiveSheet.Protect UserInterfaceOnly:=True

After this statement is executed, the worksheet is protected but your VBA code will still be able to
make changes to locked cells and perform other operations that are not possible on a protected
worksheet.

Protection Versus Security

Please do not confuse protection with security. Protecting your worksheet with a password does
not secure your worksheet; Worksheet Protection is not a security feature. The fact is that Excel
uses a very simple encryption system for Worksheet Protection. When you protect a worksheet
with a password, that password as well as many others can be used to unprotect the worksheet.
Consequently, it's very easy to "break" a password-protected worksheet.

Worksheet Protection is not intended to prevent people from accessing data in a worksheet. If
someone really wants to get your data, they can. If you really need to keep your data secure,
Excel is not the best platform to use.

What Worksheet Protection is useful for is preventing accidental erasure of formulas. A common
example is a template that contains input cells and formulas that calculate a result. Typically, the
formula cells would be Locked (and maybe Hidden) the input cells would be Unlocked, and the
worksheet would be protected. This helps ensure that a novice user will not accidentally delete a
formula.

Protecting a worksheet can also facilitate data entry. When a worksheet is locked, you can use
the Tab key to move among the Unlocked cells. Pressing Tab moves to the next Unlocked cell.
Locked cells are skipped over.

Workbook Protection
Excel provides three ways to protect a workbook:

 Require a password to open the workbook

 Prevent users from adding sheets, deleting sheets, hiding sheets and unhiding sheets
 Prevent users from changing the size or position of wins

Requiring a Password
To save a workbook so a password is required to open it, choose File > Save As. In the Save As
dialog box, click the Tools button and choose General Options to display the Save Options dialog
box, in which you can specify a password to open the file. For additional security, click the
Advanced button to specify encryption options.

The Save Options dialog box (described above) also has a "Password to modify" field. If you
enter a password in this field, the user must enter the password to overwrite the file after making
changes to it. If the password is not provided, the user can save the file, but he/she must provide
a different file name.

You may wonder if requiring a password to open a workbook is secure. The answer depends on
the version of Excel that is being used. Password-cracking products exist and typically work very
well with versions prior to Excel 97. For Excel 97 and subsequent versions, password-cracking
products typically rely on "brute force" methods. Therefore, you can improve your file security by
using a long string of random characters as your password.

Prevent Users from Adding/Deleting and Hiding/Unhiding Sheets

To prevent a user from adding or deleting sheets you need to protect the workbook's structure,
select Tools/Protection/Protect Workbook. In the Protect Workbook dialog box, make sure that
the Structure checkbox is checked. If you specify a password, that password will be required to
unprotect the workbook.

When a workbook's structure is protected, the user may not:

 Add a sheet
 Delete a sheet
 Hide a sheet
 Unhide a sheet
 Rename a sheet
 Move a sheet

Workbook Win Protection

The Tools - Protection - Protect Workbook command enables you to protect a workbook so no
one can add or delete sheets, or resize or move the workbook win.

Check the option “Structure” to protect the structure of a workbook so that sheets can't be
deleted, moved, hidden, unhidden, or renamed, and new sheets can't be inserted. Check the
option “Wins” to protect a workbook's wins from being moved, resized, hidden, unhidden, or
closed.
VB Project Protection
To prevent others from viewing or changing VBA code, activate the VB Editor and select your
project in the Projects win. Then choose Tools/xxxx Properties (where xxxx corresponds to your
Project name). In the Project Properties dialog box, click the Protection tab. Place a checkmark
next to Lock project for viewing, and enter a password (twice). Click OK, then save your file.
When the file is closed and then re-opened, a password will be required to view or modify the
code.

External Data Validation

You can use the data validation feature to specify a range of values that are considered
acceptable for user input. Normally, Excel expects you to specify your validation range as being
on the same worksheet where you are defining the validation rule. If you try to enter a range that
is on another worksheet or in another workbook, Excel gives you an error message.

If you want the validation range to be on another worksheet, follow these steps:

Step Action

1 Select the range of cells you want to use as your data validation values.

2 Choose Name from the Insert menu, and then Define from the submenu. Excel
displays the Define Name dialog box.

3 Enter a unique name for your data validation range, such as MyValRange, then
click on OK.

4 Select the cell for which you want to define a validation rule.

5 Choose Validation from the Data menu. Excel displays the Data Validation dialog
box.

6 Use the Allow and Data drop-n lists to specify how you want Excel to validate the
data. Depending on your selections, you will see a Value, Minimum, or Maximum
boxes appear in the dialog box.

7 In the appropriate boxes (Value, Minimum, or Maximum), specify the name you
gave your data validation range, in step 3. For instance, you could enter
MyValRange.

8 Click on OK.
This approach works great if the data validation range is in the same workbook. if you want to use
a data validation range that is in a different workbook entirely, you can trick Excel into accepting
your external reference if, in step 7, you enter a formula such as the following:

=INDIRECT("[Book2]Sheet1!D6")

This formula uses the INDIRECT function to return the value at a cell on another worksheet, and
the data validation feature will accept it with no problems. In this case the cell being checked is at
cell D6 on Sheet1 of Book2. In order for this to work, you will need to make sure that Book2 is
open at the same time that your main workbook is open.
Filling in the Gaps

When you import data, you can end up with a worksheet that looks something like this:

In the example shown above, an entry in column A applies to several rows of data. Sorting such a
list makes a mess of your data and you won't be able to tell who sold what anymore.

When you have a small list, you can enter the missing data manually. But for bigger sheets, you
need a better way of filling in the missing cell values. The following instructions describe how:

You may open the spreadsheet used in this example to try it yourself:

Sample.xls, (Microsoft Excel, 14KB)

Step Action

1 Select the range (A3:A14 in this example).

2 Press Ctrl+G to display the Go To dialog box.

3 In the Go To dialog box, click Special.

4 Select the Blanks option and click OK.

5 In the Formula bar, type = followed by the address of the first cell with an entry in
the column (=A3 in this example), and press Ctrl+Enter.

6 Now the blanks are filled in, but with formulas instead of text. Reselect the range
 (A3:A14 in this example) and choose Edit > Copy.

7 Select Edit > Paste Special, choose the Values option, and click OK.

Importing Data from Text Files

Very often the data we work with in Excel comes from other sources. But what if the data is not
neatly structured? This quick reference card shows you what to do. Suppose you have a file
generated from an old COBOL report. After opening the file in Excel, you see 2500 lines of this:

LastName | FirstName | Middle | Address | City

State | Zip
Useless LINE1
Useless LINE2
LastName | FirstName | Middle | Address | City
State | Zip
Useless LINE1
Useless LINE2

You need to get the ASCII report into a useful format: one line per customer, with State
and Zip appended to the rest of the information. You also want to zap the 2 useless lines.
Here is one way to work with the ASCII report:

Ste
Action
p

1 Insert two blank With the formulas:

columns to the left of
the data.

2 Add a header row

above the data.

3 Column A is called
"Sequence."

4 Column B is called
"RowType."

5 Column C is called
"Data."

6 Make all of the

headings bold.

After changing to values

7 Column A is going to be used to assign a number to each logical record in the report.
Because this report has 4 physical lines for each logical record, we need each set of
4 lines to have the same logical record number. I usually enter values for the first
record and then design formulas for the 2nd record that can be copied n the entire
report.

8 a. In Cells A2:A5, enter a 1.

b. In Cell A6, enter =A5+1.
c. In cell A7, enter =A6.
d. Copy A7 to A8 & A9.

You now have a copyable set of formulas for the 2nd logical record of the report.

9 a. Select A6:A9 and hit Ctrl C to copy.

b. Select A10:A2501 and hit Ctrl V to paste.

10 Column B is going to be used to identify if the particular row is the 1st, 2nd, 3rd, or
4th segment of the logical record.

11 a. In cells B2:B5, enter 1, 2, 3, and 4.

b. In cell B6, enter =B2.

c. Copy cell B6 from B7:B2501.

12 You now have sequence numbers and row types for all of your data and you need to
change the formulas to values. Select A2:B2501. Edit > Copy, Edit > Paste Special
> Values > OK.

13 Now that sequence numbers and row types have been assigned for all rows, you are
almost done.
Sort the data by Row Type as the primary key and Sequence as the secondary key.
This will cause the 625 top lines of each record to float up to cells C2:C626. The 2nd
line of each record will be in C626:C1251. The "useless" lines will start in C1252 and
can be deleted.
Move cells C626:C1251 to cell D2. In Cell E2, enter the formula =C2&D2. You can
copy this formula from E2 to E626. Use the same Paste Special Value trick to
 change from formulas to values, delete columns A-D and you have your result.

14 From here, you can use the Text to Columns wizard to process this data further.
You can easily customize this procedure to deal with any variety of ASCII reports.
You will need to figure out how many physical printed lines make up a single logical
record on the report.

Hyperlink Fix for Excel 2002

Hyperlinks created in Microsoft Excel 97 do not function with Microsoft Excel 2002. You can
correct this issue using any of the following methods:

Reapply Settings Manually Via Excel

Step Action

1 Open Excel on Workstation 2003. From the Tools menu, select Options.

2 Select the General tab and click the Web Options button on the right.

3 Select the Files tab, uncheck the Update links on save option, and click OK.
 4 Click OK at the Options screen to complete the procedure.

Locate Phantom Links in a Workbook

If you have opened many Excel files, you have probably seen the message asking if you want to
update links. Occasionally, however, Excel asks this question even when the spreadsheet you
are opening appears to contains no links! What can be done to convince Excel that the workbook
has no links?

Excel is usually correct about identifying links, so there's an excellent chance that your workbook
does contain one or more links – but they are probably not formula links.

Follow these steps to identify and eliminate any links in a workbook.

Step Action

1 Select Edit > Links from the menu bar. In many cases, this command may not be
available. If it is available, the Links dialog box will tell you the name of the source
file for the link. Click the Change Source button and change the link so it refers to
the active file.
 2 Select Insert > Name > Define.
Scroll through the list of names in
the Define Name dialog box and
examine the Refers to box (see
the figure below). If a name
refers to another workbook or
contains an erroneous reference
such as #REF!, delete the name.
This is, by far, the most common
cause of phantom links.

3 If you have a chart in your workbook, click on each data series in the chart and
examine the SERIES formula displayed in the formula bar. If the SERIES formula
refers to another workbook, you've identified you link. To eliminate the link, move
or copy the chart's data into the current workbook and recreate your chart.

4 If your workbook contains any custom dialog sheets, select each object in each
dialog sheet and examine the formula bar. If any object contains a reference to
another workbook, edit or delete the reference.

5 Next, save your workbook and then re-open it. It should open up without asking
you to update the links.

Making Gantt Charts

Gantt charts are used in planning a project, showing tasks and durations over time. MS Project
makes extensive use of these charts, but you don't need this program to create one. For simple
planning, Excel can create these charts.

Here's how it works.

Step Action

1 Enter data as shown in the picture below. The formula in cell D2 is: =B2+C2-1.
This formula is copied n the column.
2 Use the Chart Wizard to create a stacked bar chart of the range A2:C8. Select the
second subtype as shown here:

3 In Step 2 of the Chart Wizard, select the columns option. *Note that Excel uses
the first two columns as axis labels. This is incorrect (to be addressed below.)

4 Click on the Series Tab. Set the series to the following (add the second series if
necessary):
Series 1: B2:B8
Series 2: C2:C8
Category X-axis labels: A2:A8
 5 In Step 3 of the Chart Wizard, remove the Legend and click Next.

6 In Step 4, create the chart where you want (object or separate sheet).
Now we need to make some more formatting changes to get the final result.

7 Adjust the height of the chart, or the font size, to make all the labels visible.

8 Right-click on the horizontal axis and open the Format Axis dialog. Adjust the
scale values so they correspond to the earliest and latest dates used. (Did you
know you can enter dates in the edit boxes?)

9 Right-click on the vertical axis and open the Format Axis dialog. On the Scale tab
select the options 'Categories in Reverse order' and 'Value Y axis crosses at
maximum'.

10 Select the Series1 data and open the Format Data Series dialog. On the Patterns
tab set Border to 'None' and Area to 'None'.

11 Now you have a Gantt chart. Attached is a spreadsheet with this example.
Sample.xls, (Microsoft Excel, 14KB)

Testing for a Single Condition

You can use the SUMIF and COUNTIF functions to either sum a range of values or count the
number of matching entries based on a single criteria.

Here is a quick look at how the functions work. The formula =SUMIF(A2:A5,"Yes",B2:B5) will add
up all the values in B2:B5 for which the corresponding entry in A2:A5 is Yes and it will return 550
(350+200). COUNTIF works similarly and counts how many matching entries there are. It would
read =COUNTIF(A2:A5,"Yes") and would return 2.

A B C

1 Staff Wage Department

2 Yes 350 Accounts

3 No 100 Sales

4 Yes 200 Sales

5 No 300 Accounts

Testing for Multiple Conditions

Testing for more than one condition? For example, if you want to know how much you've paid to
Staff in the Accounts department, SUMIF doesn't handle multiple criteria so it can not be used.
To do this you can use an array formula. An Array formula is a more complex formula to create
but it works very well in situations like this one. The array formula which will solve our problem is:

=SUM(IF((A2:A5="Yes")*(C2:C5="Accounts"),B2:B5))

or even shorter,

=SUM((A2:A5="Yes")*(C2:C5="Accounts")*B2:B5)

These formulas only add the corresponding entry in column B if the corresponding tests on the
data in columns A and C return true values. Excel calculates this by treating the values in
columns A, B & C as one dimensional arrays and it performs the calculations using these arrays.
It evaluates the tests on the data in row 2, then row 3, then row 4 and finally row 5 and then sums
the results.

To count the matching entries (rather than sum them) use:

=COUNT(IF((A2:A5="Yes")*(C2:C5="Accounts"),B2:B5))

However, you can't just enter either of these formulas into a cell and expect it to work - it won't
unless you press Control + Shift + Enter which indicates to Excel that you're using an Array
formula. If you forget, you'll see #VALUE! appear in the cell and you should select the cell, press
F2 as if to edit it and press Control + Shift + Enter to enter the formula in the correct manner.

Array formulas like these make multiple calculations and return a single result from them. While
many calculations can only be made using array formulas, this is one calculation you could have
made another way -- using Excel's Conditional Sum Wizard add-in. Test this by loading the Add-
in -- choose Tools, Add-ins, enable the Conditional Sum Wizard and click Ok. You'll find it is now
installed on the Tools menu - select Wizard then Conditional Sum. To create the formula, first
select the entire data area as the list to work with, then specify the two tests which must be met
(the Staff value must be equal to Yes and the Department value equal to Accounts, and finally
indicate the cell to contain the answer. The resulting formula will be different to the two we've
shown but works just as well.

Now let's look at how you can make calculations which use, or conditions to make, calculations of
the type, for example, "Sum Wages where Staff = Yes or Department = Accounts."

Consider again this worksheet fragment:

A B C

1 Staff Wage Department

2 Yes 350 Accounts

3 No 100 Sales

4 Yes 200 Sales

5 No 300 Accounts

One of the formulas above, which we used to add the Wages for employees who were both staff
(Yes) and who worked in Accounts was this:
=SUM(IF((A2:A5="Yes")*(C2:C5="Accounts"),B2:B5))

This formula works on the basis that each test will evaluate to TRUE or FALSE (1 or 0) and the
results of both tests are multiplied together. Only if both tests valuate to TRUE (1) will the result of
the multiplication be 1, and the Wages amount for that row will be included in the total.

You can use this same process to make an OR calculation, but this time you should add the
results, rather than multiplying them. Now, when the results of the tests are evaluated and added,
the Wages amount in any row in which any one of the tests evaluates to true will be included in
the result. If both tests evaluate to true, the amount will still be included. So, this formula will sum
all the Wages for all employees who either work in Accounts or who are staff:

=SUM(IF((A2:A5="Yes")+(C2:C5="Accounts"),B2:B5))

Remember that this is an array function, so you must press Control + Shift + Enter after you've
typed it for it to work. If you don't, press F2 to edit the cell and then press Control + Shift + Enter.
You'll know you've got it right when you check the cell formula and it's surrounded by curly
brackets {}.

Similarly, you can use array formulas to count the number of employees who either work in
Accounts or who are staff:

=COUNT(IF((A2:A5="Yes")+(C2:C5="Accounts"),B2:B5))

But the action doesn't stop here. You aren't limited to COUNT and SUM and you can use other
functions like AVERAGE for example. Try these functions which average the Wages for both our
previously stated AND and OR test criteria:

=AVERAGE(IF((A2:A5="Yes")*(C2:C5="Accounts"),B2:B5))
=AVERAGE(IF((A2:A5="Yes")+(C2:C5="Accounts"),B2:B5))

You can also use MAX and MIN as shown here:

=MAX(IF((A2:A5="Yes")*(C2:C5="Accounts"),B2:B5))
=MAX(IF((A2:A5="Yes")+(C2:C5="Accounts"),B2:B5))

Once you see how easy it is to create array formulas you can extend them to calculate multiple
criteria, for example, this will calculate the Wages for all employees who work in Accounts and
are Staff, or those who are not Staff and who work in Sales:

=SUM(IF(((A2:A5="Yes")*(C2:C5="accounts"))+
((A2:A5="no")*(C2:C5="sales")),B2:B5))

When you need to do tedious calculations like this, array formulas allow you to reduce the
workings to a single cell which is both neat and very easy to troubleshoot when things go awry.

Obscure Excel Shortcut Keys and Mouse Clicks

Excel is certainly no slouch when it comes to user interface elements. You can right click on just
about anything, and you'll get a shortcut menu that often contains the command you're looking
for. And, of course, it supports dozens of shortcut key combinations.
Most of the shortcut keys are documented, but the typical user probably knows five or six
of them. Following are some of the more obscure user-interface elements in Excel.

Obscure Excel Shortcut Keys and Mouse Clicks

 Shift+F10 displays the shortcut menu for a cell or range selection (equivalent to
right clicking the cell or range).

 Right click the Excel icon in the menu bar (to the left of the File menu), and you'll
get the same shortcut menu that appears when you right click a workbook's title
bar (which is not visible if the workbook is maximized). Included on this menu is the
handy View Code command – which takes you to the ThisWorkbook code
module.

 You're probably familiar with the VCR

navigational controls to the left of the row
of sheet tabs. But have you ever right
clicked on those controls? Try it. You'll get
a handy menu of all sheets in the
workbook. Click on a sheet name, and
you're there!

 If you press Shift and click either of the inner VCR navigational controls, the sheet
tabs will scroll a screen's worth of tabs at a time.

 Most people know about the F5 key (equivalent to Edit > Go To…), which brings
up the Go To dialog box. This is used to go to a named range. You can also type a
cell reference (such as AZ902) into the Reference box and click OK to go directly
to that cell.

 If you're a VBA programmer, you might be interested in the fact that you can also
type a VBA procedure name in the Go To dialog's Reference box. Click OK, and
the VB Editor will be activated, and the cursor will be at the first statement in the
procedure.

 In the Define Name dialog box (Insert > Name > Define…), the Refers to box can
be very frustrating. If you want to edit the reference and press press an arrow key,
the cell reference changes. To get into normal edit mode in the Refers to box,
press F2 first. Then you can edit the range reference using standard techniques.

 If you use the Move selection after Enter setting (Tools > Options… > Edit tab),
you can override this by using Ctrl+Enter. The cell cursor won't change when you
have a single cell selected.
  You've probably noticed that the status
bar displays the sum of the selected
cells. But many people haven't
discovered that right clicking the status
bar lets you change the function that's
applied to the selected range.

Plot Every Nth Data Point

Normally Excel doesn't plot data that resides in a hidden row or column. You can sometimes use
this feature, because it gives you control over what data appears in the chart.

Suppose you have a lot of data in a column and you only want to plot every tenth data point. A
simple way to do this is to use AutoFilter (Menu Data > Filter > AutoFilter) in combination with a
formula. This is shown in the attached spreadsheet file.

Sample.xls (Microsoft Excel, 52KB)

Cell A1 contains the value 10. This value determines which rows to hide. Column B contains
identical formulas that use the value in A1. For example, the formula in cell B4 is:

=MOD(ROW(),$A$1)

The MOD function is used to calculate the remainder when the row number (returned by the
ROW function) is divided by the value in A1. As a result, every nth cell (the value in A1 sets n)
returns 0. Then, use the Data > Filter > AutoFilter command to turn on AutoFiltering. Set up the
AutoFilter to display only the rows that contain a zero in column B. Note that if you change the
value in A1, you have to re-specify the AutoFilter criteria for column B (the rows will not hide
automatically).
On the other hand, sometimes you do want hidden data to display in a graph. To do this, select
the chart object, and select Tools > Options and go to the Chart tab. Remove the check mark
from the box labeled 'Plot visible cells only.'
Shade Alternate Rows

Excel's Conditional Formatting feature has many uses, and here's one that's particularly handy.
Conditional Formatting makes it simple to apply cell shading (green or otherwise) to every other
row in a worksheet range. For a lengthy list, shading alternate rows can improve legibility.

Here's how to do it:

Step Action

1 Highlight the range of cells or rows or columns that you want to format.

2 Choose Format, Conditional Formatting to display the Conditional Formatting

dialog box.

3 Select Formula Is from the first drop-n list box, and enter
=MOD(ROW(),2)=0 in the second box.

4 Click the Format button to bring up the Format Cells dialog box.

5 Select the Patterns tab and specify a color for the shaded rows. You'll probably
want to choose a light color, so that the default black text will still be legible. Or,
you can go all out and change the text color as well (do this in the Font tab of the
Format Cells dialog box). For example, you might select a dark blue background,
accompanied by a mellow yellow text color.

6 Click OK twice to return to your worksheet.

The best part is that the row shading is dynamic: The alternate row shading persists even if you
insert or delete rows within the original range. By the way, if you get tired of this new look and
want to get things back to normal, just select the range, choose Format, Conditional Formatting,
and click the Delete button in the Conditional Formatting dialog box.
Sorting Dates by Month

Sometime you may have a need to sort a list of information based on the month represented in a
particular column. For instance, you may have a list of people and their birthdays, and you want
to sort the list by birthday month so that you know whose birthdays occur within a particular
month.

The easiest way to do this is to add a new column to your table. This column will be named
something descriptive, such as "Birth Month" or "Month." For instance, let's say that you have
people's birthdays in column B; you could add the new column in column C. In this column you
could then use the MONTH function, as follows:

=MONTH(B3)

This particular formula would go in cell C3, but similar formulas would go in each cell of column
C. The result is that column C will contain numbers ranging between 1 and 12, representing the
birth months of the people. You can now sort the list based on the contents of column C, with the
result that the list is sorted by month.

This approach works fine, but you may not be able to add another column to your
worksheet. If this is the case, you can follow these steps to sort by month:

Step Action

1 Select the cells in column B (assuming that column B contains the birthdates).

2 Choose Cells from the Format menu. Excel displays the Format Cells dialog box.

3 Make sure the Number tab is displayed.

4 In the Category list, choose Custom.

5 In the Type box, enter four lowercase Ms (mmmm) for the format.

6 Click on OK. The cells in column B should now show only the birth month of the
people in your list. (Don't worry; the underlying birthdates are still there.)

7 Select your entire list.

8 Choose Sort from the Data menu. Excel displays the Sort dialog box.

9 Use the controls in the dialog box to specify that you want to sort by the contents
of column B.

10 Click on the Options button. Excel displays the Sort Option dialog box.

11 Use the First Key Sort Order drop-n list to specify that you want to sort by months.
(Choose the option that states January, February, March, etc.)

12 Click on OK to close the Sort Options dialog box.

13 Click on OK to close the Sort dialog box and actually sort the list.
You may be wondering why you need to reformat the display of the cells containing the birthdates
(steps 1 through 6). The reason is that when you finally sort your list (steps 7 through 13), if you
simply have the original full dates displayed, Excel will effectively sort the list chronologically
rather than by month.

The Power of Pivot Tables

Excel has some impressive list management functions, but Pivot Tables offer more functionality.
Think of a Pivot Table as an interactive summary of your data (read: list). You can quickly change
the functions you use for this summarization (counts, sums, averages and so on), change the
fields across which the data is tabulated, flip through groupings and sub-groupings (pages), chart
these groupings, refresh the source data, drill n or up to see more or less detail, change
formatting and more.

The following list of data shows the Pivot Table functions.

 Example.xls (Microsoft Excel, 162KB)

This list is compiled from the example database that comes with Microsoft Access. Right-click on
the link to save a WinZip file containing an example spreadsheet.

It lists the Northwind Orders and we will use it to do an analysis of freight amount by destination
country by employee.

The following steps show how to start a PivotTable, and uses the spreadsheet above as a
reference when showing how a PivotTable works. You can use the following steps to
create a PivotTable with your own data. After Step 7, use the data from your own
spreadsheet, instead of the example as it’s written.

Step Action

1 Open the file by double-clicking on it.

2 Click any cell in the list, then choose Data > PivotTable and PivotChart
Report… to start the wizard.

3 Step 1 of 3: Select Microsoft Office Excel list or database. Also select

PivotTable. Click Next.
 4 Step 2 of 3: A PivotTable Wizard dialog box will appear. The range is already
selected, click Next.

Note: This is just an example of the dialog box. The range of your spreadsheet or
list will show different values.

5 Drag the Employee field's button into the Column area.

6 Drag the Freight field's button into the Data area. Excel defaults to a Sum function
(more on how to change this later).

7 Click the Next > button, and then click Finish to accept all of Step 3 of 3's
defaults. Excel inserts a new sheet with the pivot table on it.
Once you have created a Pivot Table, you manipulate it with the PivotTable toolbar. To
quickly update the format of the calculated field (in this case Sum of Freight)

Step Action

1 Click any detail cell inside the table.

2 On the PivotTable toolbar click the PivotTable - Field button.

 3 Click the Number button.

4 Select the Currency format from the Category list.

5 Set decimal places to 2 and verify that the correct currency is selected.

6 Choose the bottom-most Negative numbers format.

7 And click on OK twice.

To see just how flexible a pivot table is, let's count the mouse clicks required to change
the analysis from Sum to Average:

Step Action

1 Click any detail cell inside the table.

2 Click the Pivot Table - Field button (on the PivotTable toolbar).

3 Click Average.

4 Click OK.
Only four mouse clicks (three if the active cell is already inside the table).

To compress the pivot table's display such that you see employee orders in a single
column, instead of across multiple columns, and selectable by employee, you need to use
the Page feature:

Step Action

1 Click the PivotTable menu item on the toolbar, then choose Wizard.

2 Now you're in the Step 3 of 4 dialog so drag Employee from the Column area into
the Page area.

3 Double-click the Average of Freight button to change the calculation back to Sum
of Freight.

4 Click Finish.
Column B is now an AutoFilter like column with a drop-n control in B1 showing "(All)", which can
be changed to show any one of the nine employee IDs or to tabulate data for each country for all
employees. See below.
If you want to see the total freight, minimum freight, maximum freight, and order count
values for each country by employee:

Step Action

1 Click the PivotTable menu item on the toolbar, then choose Wizard.

2 Drag Freight into the Data area (the button label reads "Sum of Freight2").

3 Again drag Freight into the Data area (the button label reads "Sum of Freight3").

4 Drag OrderID into the Data area.

5 Double-click on the Sum of Freight2 button and change the calculation to Min.

6 Double-click on the Sum of Freight3 button and change the calculation to Max.

7 Double-click on the Sum of OrderID button and change the calculation to Count.

8 Click Finish and compare the result to the figure below.

These simple examples show how a pivot table can help you obtain information out of any list you
have, no matter how complex.

Track Progress Towards Goal With a Thermometer Chart

This is an example of a chart that tracks the progress toward a goal. It involves creating a chart
that uses a single cell (containing a percentage value) as a data series.

Sample.xls, (Microsoft Excel, 22KB)

This chart was created as follows: select cell B21 and click the Chart Wizard button. Notice there
is a blank row above cell B21. Without this, Excel would use the entire data block for the chart,
not just the single cell. Since B21 is separated from other data, the Chart Wizard uses only the
single cell.

In step 1 of the Wizard, select a Column chart of the first subtype (clustered column). Click Next
twice and make some additional entries on the third page (Chart Title), remove category (x) axis,
remove the Legend, and specify Show Value on the Data Labels tab. Click finish and make the
following additional adjustments: double-click the column to display the Format Data Series
dialog. Click the Options Tab and set the Gap Width to 0 (this makes the column occupy the
entire plot area). You can also change the pattern used in the column (Patterns tab). The
example has a gradient fill effect.

Next, double click the vertical axis to bring up the Format Axis dialog. In the scale tab set the
minimum to 0 and the maximum to 1. Make other changes as you like.
Understanding Names

You already know that Excel allows you to use cell ranges within formulas and as arguments to
functions. For instance, the following is a formula that uses the SUM function. This function uses
a cell range as an argument:

=SUM(D3:D18) * 1.05

While this is perfectly allowable, it might not be very understandable to someone else or to
yourself at a later date. You might need to go back and figure out exactly what the cell range
D3:D18 represents. To overcome such a problem, Excel allows you to use names to represent
individual cells or cell ranges. For example, the cell range D3:D18 could represent the regional
sales detail; you could assign the name Regional Sales to this range, and the formula could be
entered as follows:

=SUM(Regional_Sales) * 1.05

When compared to the previous formula, this is much more understandable. Automatically you
know what sum the formula is using.

You can define a name using Excel's menus by following these steps:

Step Action
 1 Select the cell or cell range you want the name to represent.

2 Choose Name from the Insert menu, and then Define from the resulting submenu.
Excel displays the Define Name dialog box.

3 Replace the suggested name (if any) with the name you want to use for the cell or
cell range you selected in step 1.

4 Click on Add. Your name is now defined.

5 Click on OK to close the Define Name dialog box.

The names you define in Excel can be up to 255 characters in length and contain any numbers or
letters, as well as the period, backslash, question mark or underscore. Names cannot contain any
other special characters, including spaces. In addition, names must start with either a letter,
backslash or the underscore.

You can also define a name by using the formula bar. To use this method, follow these
steps:

Step Action

1 Select the cell or cell range you want the name to represent.

2 Click in the area at the very left of the formula bar.

3 Type the name you want to define, pressing Enter when done.
The name is defined and appears in the formula bar area whenever the cell or cells are selected.

Using Excel with Other Office Applications

Copy - Paste Methodology

When you copy from one Microsoft Office application into another, the default Copy-Paste
sequence embeds an object of the source application into the target application's document.
Generally this is helpful, because it maintains a link between the pasted object and the program
used to edit the embedded object. Copied PowerPoint and Word objects are generally small
pieces of a PowerPoint or Word document, so the target application's document remains a
reasonable size.

Copying an Excel object is different. Because of possible links within an Excel workbook
(formulas and chart source data), the designers of Excel chose to copy the entire source
workbook and paste it into the target application's document. This behavior has two important
implications:

 If you paste five charts from a 1 MB workbook into a PowerPoint presentation, you are
adding 5 MB to the size of the presentation.
 If you are sending the presentation to someone else, you may inadvertently send
proprietary information which exists elsewhere in the workbook.
This behavior also makes formatting the container of the embedded object an adventure. For
example, changing the size of the container with the intent of resizing the chart it holds may move
the chart partly out of the container's view, allowing some of the worksheet to show. Or
attempting to change the size of a worksheet view may have curious effects on how much of the
worksheet is shown.

When copying an Excel chart into another application, always copy the chart as a picture. Copy
an Excel range as a picture before pasting into PowerPoint. Word allows the option of pasting a
range as Formatted Text, which means as a Word Table, so you don't have to paste a picture of
an Excel range into Word.

Note: Copying a chart as a picture eliminates the ability to reformat the chart using Excel's chart
formatting features. To copy an Excel chart and retain this formatting ability, first copy the chart
and just the specific data it is based on into an empty workbook. Then copy the chart the "regular"
way and paste it as an Excel chart into the target document.

To copy a selected Excel chart or range object as a picture, hold n the Shift key while
opening the Edit menu. The Copy command becomes Copy Picture..., which brings up
the following dialog box, with the preferred options selected:

When copying an embedded chart:

When copying a worksheet range or chart sheet:

 Choosing the Picture Format option allows you to ungroup and
adjust the elements of the imported chart within the target application. Using the "As shown on
screen" option places no limitation on the size of the image you can copy. If you choose the "As
shown when printed" option, however, and if the chart or range you are copying is larger than the
margins of a printed page, you will get this warning.

When copying a chart sheet, notice there is no option to select its size. You lose control
over the size and over other formatting if you rely on chart sheets. A useful practice is to
use charts embedded in worksheets, to control sizes and formatting of copied charts. The
gridlines of the worksheet make it very easy to align charts and size them consistently:
hold n the Alt key while moving or sizing a chart, and the chart's edges will cling to the
worksheets gridlines.

Summary

When pasting charts into other When pasting Excel worksheet ranges
applications. into other applications:

 Use charts embedded in worksheets Copy the range normally, then paste
for greater control over the size and as formatted text into Word.
formatting of the charts.  Copy the range as a picture, to
 Copy the chart as a picture, to paste into PowerPoint.
minimize the data transferred with the
chart.  Use the 'As shown on screen' and
 Alternatively, copy the chart and 'Picture' options for greater flexibility.
only its specific data into an empty
workbook, and copy-paste this as a native
chart.

 Use the 'As shown on screen' and

'Picture' options for greater flexibility.

Retaining Formatting After a Paste Multiply

One of the features of Excel is the many ways you can manipulate data using the Paste Special
command. This command allows you to do all sorts of things to your data, as you paste it into a
worksheet. One such manipulation you can perform is to multiply data as you paste. For instance,
you can multiply all the values being pasted by -1, thereby converting them into negative
numbers.

To do so, follow these steps:

Step Action

1 Place the value -1 in an unused cell of your worksheet.

2 Select the value and press Ctrl+C. Excel copies the value (-1) to the Clipboard.

3 Select the range of cells that you want to multiply by -1.

4 Choose Paste Special from the Edit menu. Excel displays the Paste Special
 dialog box.

5 Click on the Multiply radio button.

6 Click on OK.

Note: At this point, Excel multiplies the values in the selected cells by the value in
the Clipboard. Unfortunately, if the cells in the selected range had special
formatting, the formatting is also now gone, and the format of the cells is set to be
the same as the cell you selected in Step 2.
To make sure that the formatting of the target cells is not changed while doing the
Paste Special, there is one other option you need to select in the Paste Special
dialog box: Values. In other words, you would still select Multiply (as in Step 5),
but you would also select Values before clicking on OK.
With the Values radio button selected, Excel only operates on the values in the
cells, and leaves the formatting of the target range unchanged.