Importing and Exporting Data
Import data from text files (insert, drag and drop)
In this example, we will be importing a
comma-separated value (.csv) file into Excel. Suppose, for instance, we wanted
to import the .csv file named Travel.txt, shown below, into Excel
Our first step is to open the Text Import
Wizard by clicking Data => Get External Data => Import
In step one of the Text Import
Wizard dialog box, specify the data type of the text file, delimited or
fixed-width. In this case, we will make sure that Delimited is
In step 2 of the Text Import Wizard,
we will place a check next to Comma to tell Excel that the file to be
imported is comma-delimited.
In step 3 of the Text Import Wizard,
click within each column under Data preview and choose the appropriate
data format beneath Column data format. Choose Text for columns
that contain only text; choose General if the column contains both text
In the Import Data dialog box,
choose whether you would like the imported data to appear on the open worksheet
or on a new, blank worksheet. If you select Existing worksheet, click the
Collapse Dialog button to select the starting cell where the imported
data will be placed once you click OK.
Here is the finished product:
If you have a lot of faith in Excel, you
can actually sidestep the above procedure by simply dragging the text file icon
into the Excel window. Excel will do its best to translate the text file on the
fly into understandable format. However, you are usually much better off
importing the text file by using the Text Import Wizard.
Import from other applications
To import a spreadsheet file from another
application, such as Lotus 1-2-3, click File => Open. In the
Open dialog box, open the Files of type: drop-down list box and
select the appropriate file type. Then, browse to locate the desired file and
Import a table from an HTML file (insert, drag and drop-including HTML round
To import an HTML document that contains a
table, use the procedure above to locate the HTML file. Then, make sure that you
select Open in Microsoft Excel from the Open drop-down list.
Otherwise, the file may open in your default Web browser instead of
Export to other applications
To save an Excel workbook in another file
format, click File => Save As.... Next, select the appropriate
file format from the Save as type: drop-down list in the Save As
To save an Excel list as an Access
database, ensure that you have installed the Access Links add-in, and
that you have Access installed on your system. To make the conversion, click
Data => Convert to MS Access.... In the Convert to Microsoft
Access dialog box, select either New database or Existing
database. If the latter, you can click Browse to simplify the task of
locating the target Access database.
Excel will place a big comment box on your
worksheet notifying you of the name and location of the new Access database that
contains the list data. On the Access side, the Access Import Spreadsheet
Wizard and Table Analyzer Wizard will take over helping you convert
the spreadsheet data into Access table data. (Note: you need the two
previously-mentioned Access add-in components already installed on your system
for this conversion to successfully complete in Access.)
To create a new Excel document using a
template, other than the default template, click File =>
New.... In the New dialog box, select the desired dialog tab and
template and click OK. The number of alternate templates displayed in the
New dialog box will correspond to how Excel 2000 was installed on your
To open a template for editing, click
File => Open.... In the Files of type: drop-down list of
the Open dialog box, select Templates (*.xlt). Next, browse to
where the templates are located. If you installed Excel 2000 using the default
path, they should be located in \Program Files\Microsoft
Office\Templates\1033. Select the template to open and click Open.
You can now edit the template file directly.
When creating a template, you should open a
copy of an existing template, make changes, and save the template under a new
name. To save an Excel file as a template, click File => Save
As... and before you type the file name make sure to select Template
(*.xlt) next to Files of type: in the Save As dialog box. The
file path should automatically change to \Application
Data\Microsoft\Templates, which is where Excel stores custom workbook
Using Multiple Workbooks
Using a workspace
A workspace allows you to save multiple
workbooks and save their zoom level, window size, screen position, and so on. To
save a workspace, click File => Save Workspace... in any open
workbook. In the Save Workspace dialog box, give the workspace
file a name and a storage location. Note that Excel gives workspace files the
To open the workspace, simply double-click
the appropriate .xlw file.
To link workbooks means to create a formula
in one workbook that references a cell or cell range in another workbook. To do
this, follow these simple steps:
1. Create the formula in the target cell up
to the point of the external reference.
2. Use the Window menu or open the
workbook that contains the external reference.
3. Select the appropriate
You should notice that your formula shows
the external reference by displaying the workbook and worksheet name in addition
to any referenced cells in the target worksheet.
Apply number formats (accounting, currency, and number)
It may not be the most convenient, but the
most comprehensive method for applying number formats to cells is to click
Format => Cells... or right-click within a selected range and
select the same command from the shortcut menu. In the Number tab or the
Format Cells dialog box, choose a number category. Within each category,
you can choose several configurable options, such as the number of decimal
places to display, the number symbol and the format used for negative values.
The Sample area provides a preview of the selected cell
Create custom number formats
Please see above, "Apply number formats,"
for information on how to create custom number formats. Essentially, the options
in the right portion of this dialog box will change depending upon whether you
are creating a number, unit of currency, date, percentage or time.
Use conditional formatting
Use conditional formatting to format cells
a certain way if they meet a predefined set of conditions. For example, in the
graphic below, all salaries that are greater than or equal to $50,000 display in
red bold characters. To apply conditional formatting, first select the cells
that will receive the conditional formatting. Next, click Format =>
Conditional Formatting.... In the Conditional Formatting dialog
box, create a condition. In the graphic, I used the comparison operator
greater than or equal to. Plug in the test value (50,000 in the
example), and then click Format... to actually create the conditional
You can click Add>> to add a
second and/or third condition set.
To remove conditional formatting, select
the appropriate cell range, open the Conditional Formatting dialog box,
and press Delete.... Select the condition(s) to delete in the Delete
Conditional Format dialog box, and click OK.
Print and preview multiple worksheets
To preview multiple open worksheets of the
same workbook, click Window => New Window for each worksheet
that you would like to display simultaneously. Next, click Window =>
Arrange.... In the Arrange Windows dialog box, select the
appropriate window arrangement option, and make sure that you select Windows
of active workbook.
To print all worksheets in a workbook,
click File => Print... and select Entire workbook under
Use the Report Manager
The Report Manager is a convenient method
to print one or more custom views or scenarios at one time. Before you can use
Report Manager, you must install it. Click Tools => Add-Ins...,
place a check next to Report Manager in the Add-Ins dialog box,
and click OK. You will be asked for your Office 2000 CD-ROM, so hopefully
you have it handy.
Before we discuss how to use Report
Manager, it would be wise to define what "custom views" and "scenarios" are.
Since scenarios will be explained later in this Cramsession, we will start with
views. A custom view is sort of a "snapshot" of a worksheet that you can name,
save, and reuse multiple times. For example, for a single worksheet you might
have a view that hides certain rows and uses landscape page orientation, and
another view that uses a different zoom magnification and includes a what-if
To create a custom view, arrange the
worksheet in the desired manner. Next, click View => Custom
Views.... In the Custom Views dialog box, click Add.... In the
Add View dialog box, type a name for the new view and decide whether you
would like to include print setup and document settings in the view.
To open a custom view, open the Custom
Views dialog box, select the appropriate view and click Show.
Now then...on to Report Manager. To create
a report, click View => Report Manager.... In the Report
Manager dialog box, click Add.... In the Add Report
dialog box, type a name for the report (no spaces allowed). In the Section to
Add area, specify the sheet that contains the views or scenarios that you
want to include. Select the specific view or scenario from the View: or
Scenario: drop-down lists, and click Add. Under Sections in
this Report:, use the Move Up and Move Down buttons to reorder
the report sections. To add a single set of page numbers to the report, select
Use Continuous Page Numbers.
To print a report, open the Report
Manager dialog box, select the report under Reports:, and click
Print.... In the Print dialog box, specify the number of copies to be
printed, and click OK.
To delete a report, open the Report
Manager dialog box, select the report under Reports:, and click
Working with Named Ranges
Add and delete a named range
Named ranges makes working with cell ranges
much more user friendly. For example, you can reference the name
"January_Sales_Data" in a formula rather than having to key in "B11-B256." To
create a named range, select the cells that will receive a name, click Insert
=> Name => Define..., click in the Name box, type a name for
the cell range, and press ENTER. Spaces are not allowed in the name.
To view the current named ranges in the
open worksheet, click the down arrow next to the Name Box. To delete a named
range, click Insert => Name => Define.... In the
Define Name dialog box, select the named range under Names in
workbook: and press Delete.
Use a named range in a formula
To use a named range in a formula, you can
either type the name of the named range inside of your formula, or you can
select the range using the mouse. When you type the name of the named range, you
must type the name exactly, including capitalization. If you use the mouse,
Excel will substitute the name of the range for the ordinary addresses of the
Use Lookup Functions (HLOOKUP and VLOOKUP)
The best way to teach you VLOOKUP and
HLOOKUP is just to dive into an example. Look very closely at the following
graphic. In cell B8, we would like to compute Abrams' bonus amount based upon
his gross pay, referenced in cell B7. You will note that the bonus table is
located in the cell range F3:G9 (we don't need the header row for VLOOKUP or
HLOOKUP). Also, notice that the lookup table has two columns; the first column
gives the value range to be looked up, and the second column gives the return
value, or the bonus. Now, the syntax for VLOOKUP is easy:
In other words,
=VLOOKUP(B7,F3:G9,2) to find Abrams'
bonus, which happens to be $1,000.
The HLOOKUP function works the very same
way as does VLOOKUP, but we rotate the lookup table 90 degrees. The following
graphic illustrates this point.
In this case, the lookup table range is
B2:G3. Note the column number is still 2; column number 1 refers to the sales
amount, and column number 2 refers to the bonus amount.
Working with Toolbars
Hide and display toolbars
The fastest way to display toolbars is to
right-click on any available toolbar button. In the resulting shortcut menu, you
can show or hide any of Excel 2000's built-in toolbars, as well as any custom
toolbars that are attached to the current template. Of course, you can do the
same thing by clicking View => Toolbars.
Customize a toolbar
To customize a toolbar, you must first open
the Customize dialog box by clicking Tools =>
Customize.... On the Commands tab, select the appropriate
category, and scroll through the associated commands under Commands:.
To add a command to an existing toolbar,
simply drag the command from the Customize dialog box to its new location
on a toolbar. You can also add a command to a menu by dragging the command to
the menu. The menu will then open, allowing you to place the new command within
To remove a command from a menu or a
toolbar, simply drag the command into the worksheet area and release the mouse
button. Remember that you must have the Customize dialog box
Assign a macro to a command button
To assign a macro to a command button,
select the category Macros in Categories: list in the
Commands tab of the Customize dialog box. Next, drag the icon
labeled Custom Button to the appropriate location on a toolbar.
Right-click the custom icon and select Assign Macro... from the shortcut
menu. In the Assign Macro dialog box, select the appropriate macro from
the list and click OK.
You can do the same thing when assigning a
macro to a menu item: just drag and place the icon Custom Menu Item from
the Commands: list in the Customize dialog box.
To record a macro in Excel 2000, click
Tools => Macro => Record New Macro.... In the
Record Macro dialog box, type a name for the macro beneath Macro
name:. Spaces are not allowed. Assign a shortcut key if you would like, and
decide whether you would like the macro stored in the current workbook, a new
workbook, or your Personal Macro Workbook. Any macros you save in the Personal
Macro Workbook will be available for use all templates.
To run a macro, you can use a shortcut key
combination if you created one, a custom menu item or toolbar button (as
described above), or you can click Tools => Macro =>
Macros... to view the list of all macros that are available in the
current workbook. Select the macro that you would like to run, and click
To edit a macro, you must enter the Visual
Basic Editor. Open the Macro dialog box, select the macro that you want
to edit, and click Edit. The code for the selected macro will display on
the right side of the screen. Don't worry- the MOUS exam will only have you make
very basic, very intuitive changes to the macro code. The point here is ensuring
that you know how to get into and out of the Visual Basic Editor.
To exit the Visual Basic Editor and return
to Excel, click File => Close and Return to Microsoft
Auditing a Worksheet
Work with the Auditing toolbar
Excel's auditing feature is great for
troubleshooting formulas, especially in large worksheets. With it, you can find
out exactly to which cell(s) a particular formula is referring, as well as find
out to which formula a particular cell provides data. To display the Auditing
toolbar, click Tools => Auditing => Show Auditing
Trace errors (find and fix errors)
To trace an error in a formula, you first
must select the cell that you suspect contains an error. Next, click the
Trace Error button on the Auditing toolbar. In the example below, cell F2
contains an error because the precedent value $19,500 in cell E2 does not fall
within a valid lookup range in the bonus schedule beginning in cell B11. To fix
the problem, you would either have to boost Abrams' salary by at least $500 (I'm
sure he or she would love that!), or you would have to add additional data to
the bonus schedule to accommodate figures lower than $20,000.
To clear the tracer arrows, click the
Remove All Arrows button on the Auditing toolbar.
Trace precedents (find cells referred to in a specific formula)
To find the cells that are referred to in a
specific formula, first select a cell that contains a formula. Next, click the
Trace Precedents button on the Auditing toolbar. In the example below,
the formula in cell F5 is being evaluated. Notice that there are two precedents
to this formula: the gross salary figure in cell E5, and the lookup table, which
begins at cell B11 (actually the entire range B11:F12).
To remove only the precedent arrows from
the worksheet, click the Remove Precedent Arrows button from the Auditing
Trace dependents (find formulas that refer to a specific cell)
To find formulas that refer to a specific
cell, first select a cell and click the Trace Dependents button on the
Auditing toolbar. In the example below, cell E3 is being evaluated. The tracer
arrowheads "stop" at cells F3 and G3. That tells us that there are two formulas
that reference cell E3.
To clear only the dependent arrows from the
worksheet, click the Remove Dependent Arrows button on the Auditing
Displaying and Formatting Data
Apply conditional formats
Conditional formatting is covered above, in
the section "Use Conditional Formatting."
Perform single- and multi-level sorts
Before you attempt single or multi-level
sorts, you should make sure that your column headers occupy only a single row,
and that they are formatted differently from the rest of the list. Excel needs
some way to determine that you do in fact have a header row.
To perform a single-level sort, select a
cell within the column by which you want to sort and click the Sort
Ascending or Sort Descending buttons on the Standard toolbar.
To perform a multi-level sort, select a
cell within the sort list and click Data => Sort.... In the
Sort dialog box, specify whether or not your list has a header row by
making the appropriate selection under My list has. Next, sort using up
to three levels by specifying the column name and the sort order, ascending or
descending. Note that you can sort one level in ascending order and another
order descending order.
Use grouping and outlines
To have Excel automatically generate an
outline, you must first make sure that you use a summary formula such as SUM to
establish the logical outline levels. For example, in the graphic below you can
see that the "total" rows (i.e., QTR 1 TOTAL, etc.) will be where Excel decides
to automatically break up the data.
To create an Auto Outline, select a cell
within the list and click Data => Group and Outline
=> Auto Outline.
You use the PLUS buttons (also called the
Show Detail symbols) to expand outline levels, and you use the MINUS
buttons (also called the Hide Detail symbols) to collapse outline levels.
In the graphic above, you could also click the numeric buttons below the Name
Box to quickly view only one outline level at a time.
You can use Excel's Grouping feature to
customize an outline by manually grouping rows or columns. To do this, select
the rows or columns to be grouped. Next, click Data => Group And
Outline => Group.... To ungroup grouped rows or columns, select
the appropriate rows or columns and click Data => Group and
Outline => Ungroup....
To remove grouping or outlining from the
worksheet, click Data => Group and Outline => Clear
Use data forms
A data form is a convenient way to browse
records in an Excel list, one record at a time. A data form is also useful as a
data entry tool. To invoke the data form, click Data =>
Form.... Use the New button to create a new record, or the up
arrow and down arrow to scroll through the record set.
Note that you can also conveniently delete
entire records by using the Delete button in the data form
To use automatic subtotals, make sure that
your header row (the column labels) is in row 1 of the worksheet; it is also
advisable to perform an ascending sort on the first column data before executing
the subtotals. Once you have done all that, select a cell within the list and
click Data => Subtotals.... In the Subtotal dialog box,
select the appropriate column under At each change in:. In the example
given below, Excel will stop at each change in account number. Next, decide
which function you would like Excel to apply by making the appropriate choice
under Use function:. Finally, select the column that contains the numeric
data to be subtotaled under Add subtotal to:.
You will notice that you can expand or
collapse the grouped data by using the Show Detail and Hide Detail
buttons. Actually, subtotaled data behaves exactly as any grouped or outlined
To remove the subtotals from the worksheet,
click Data => Group and Outline => Clear Outline.
Apply data filters
Data filters are very cool. The AutoFilter
allows you to filter a list very quickly. To apply an AutoFilter, select a cell
within your list and click Data => Filter =>
AutoFilter. Each column header will contain a drop-down list from which
you can select your filter criteria. Note that with simple AutoFilters, you can
only apply a filter using one criterion at a time.
To apply a filter using multiple criteria,
choose (Custom...) from the column header AutoFilter list. In the
Custom AutoFilter dialog box, use the first drop-down list to select the
appropriate Boolean or comparison operator (for instance, equals or greater
than), and use the second drop-down list to select the appropriate item. Notice
that you can also use And or Or to develop complex filters. For
example, in the graphic below, the Custom AutoFilter will return records where
DIVISION equals Canada or Germany.
To remove a filter, open the drop-down list
associated with the filtered column and select (All).
To turn off AutoFilter, click Data
=> Filter => AutoFilter.
You can extract tabular data from a Web
page. To do this, click Data => Get External Data => New
Web Query.... In the New Web Query dialog box, type the URL or full
path to the HTML file that contains the data to be extracted into Excel.
Annoyingly, there is no Browse button for the file system. In step 2,
determine whether you want to extract the entire page contents or only the
table(s). In step 3, decide on how much formatting you would like to extract.
Note that you can save the Web query for reuse by clicking Save Query.
In the Returning External Data to
Microsoft Excel dialog box, specify where on the existing worksheet you
would like the newly extracted tabular data to be placed.
After the HTML data "lands" into Excel, you
can manipulate it as if it had been natively created in Excel.
You can query Microsoft Access or Microsoft
SQL Server databases from Excel 2000. Before you can do this, however, you must
have installed the Microsoft Query add-in. Follow the instructions given above,
under Use the Report Manager, for information on installing Excel
In this example, we will extract data from
the table called "Customers" in the Microsoft sample database called
From Excel, click Data => Get
External Data => New Database Query.... In the Choose Data
Source dialog box, you can select <New Data Source> to create
and save a data source, or you can select MS Access Database to run a
"one-shot" query. Let's just cut right to the chase and select MS Access
Database, and the click OK.
Next, browse for the database to be
queried. If you installed Microsoft Access in its default path, you will find
Northwind.mdb in C:\Program Files\Microsoft
Once Microsoft Query has connected to and
opened the database, your next step is to select the appropriate table and
choose which columns from that table you would like to extract into Excel. In
this example, I have selected the Customers table, and have chosen the
Customer ID, ContactName and City fields. Notice the arrow
buttons in the middle of the dialog box go in two directions. This is handy in
case you accidentally bring over a column you do not want. When you have
selected your columns, click Next to continue.
In the Query Wizard - Filter Data
dialog, you have the opportunity to filter data on up to three criteria. In
the graphic below, you will notice that I have specified that I want to see only
those records from the Customers table where City equals
In the Query Wizard - Sort Order
dialog box, you can sort your data up to three levels deep. In the graphic
below you will notice that I have chosen to sort by Customer ID, in
In the Query Wizard - Finish dialog
box, you can click Save Query... if you think that you will reuse this
query in the future. You can also specify where Excel should place the extracted
Access data. Verify that Return Data to Microsoft Excel is selected, and
The final step is specifying exactly where
you want the extracted data to wind up. In the Returning External Data to
Microsoft Excel dialog box, you can select Existing worksheet, New
worksheet, or PivotTable report. In this example, I have selected
Use data validation
In Excel 2000, you can establish data
validation rules to ensure that correct data is entered into your worksheets.
For example, you can set up a data validation rule that will accept only dates
within a certain range. To create a data validation rule, select the range that
will require validation and click Data => Validation.... In the
Data Validation dialog box, enter the validation criteria. In the example
below, I have specified that only dates between January 1 and December 31 2000
On the Input Message tab of the
Data Validation dialog box, you can create a pop-up message that appears
every time you select a cell within the validation cell range.
The Error Alert tab of the Data
Validation dialog box allows you to create a dialog box that appears when
data that violates the validation rule is entered into a cell. You can select
the dialog box icon to use from the Style: drop-down list box.
Assuming you have not established an error
alert, it is still possible to enter invalid data into a cell range that uses a
validation rule. To easily see the invalid cells, you can use the Circle
Invalid Data button of the Auditing toolbar. Remember that you can invoke
the Auditing toolbar by clicking Tools => Auditing =>
Show Auditing Toolbar.
To delete the circles, click the Clear
Validation Circles button of the Auditing toolbar.
Using Analysis Tools
Use PivotTable AutoFormat
PivotTable AutoFormat works much the same
way as Excel's standard AutoFormat feature. With it, you can instantly apply
color and pizzazz to your otherwise drab and boring PivotTable report. To apply
a PivotTable AutoFormat, select a cell within the PivotTable, display the
PivotTable toolbar, and click Format Report... from the Pivot Table
Here is a PivotTable with an AutoFormat
Use Goal Seek
Let's say you are trying to finance your
dream car. For a 60-month loan, your monthly payment is figured at $628.49.
However, you can only afford to pay $550. What input value can you adjust to
meet your desired monthly payment? Goal Seek can help you solve that problem. To
use Goal Seek, make sure that you are using a formula such as PMT in your
payment cell. (For information on how to use the PAYMENT function, see the Excel
2000 Core Cramsession). Next, select a cell within the list and click Tools
=> Goal Seek.... In the Goal Seek dialog box, enter the
cell address for your monthly payment next to Set cell:. Enter the
desired payment next to To value:. Note that the value must be NEGATIVE.
You will get an error if you use a positive value. (Recall that outgoing
payments in financial functions are designated as negative values). Finally,
enter the input value cell address (in this example, that would be Loan
Period) next to By changing cell:. In plain English, what Excel is
going to do is this: using your new monthly payment as a constant, it will
quickly find a loan period value that will pay off your loan, given the
specified interest rate.
Here is a graphic depicting the solution
reached by Goal Seek in the above example. In plain English, the loan period
would need to be increased to 71 months to accommodate a monthly payment of
Create Pivot Chart Reports
To create a PivotChart report, select a
cell in the list that is to be charted and click Data => PivotTable
and PivotChart Report.... In step 1 of the PivotTable and PivotChart
Wizard, specify the location of the data to be charted under Where is the
data that you want to analyze?, and select PivotChart (with PivotTable)
under What kind of report do you want to create? Click Next to
In step 2 of the PivotTable and
PivotChart Wizard, select the appropriate cell range or existing PivotTable
from the list and click Next.
The final step is to decide whether to
place the new PivotChart on a new worksheet or as an object in an existing
Here is a graphic displaying the finished
product. Notice that a PivotChart is fully interactive, as is a PivotTable
Work with Scenarios
Scenarios allow you to ask "what-if"
questions of your Excel data, and save the results for reuse. In this example,
we will ask the question "What if we increased our sales by 10 percent?" To get
started, click Tools => Scenarios.... In the Scenario
Manager dialog box, click Add.... In the Edit Scenario dialog
box, enter a name for the new scenario, and select the cell range that will be
modified under Changing cells: You can use the Collapse Dialog
button to get the dialog box out of the way if you need to.
The next step is kind of a pain.
Unfortunately, you will need to manually enter the changed values for each cell
in the range. In this example, I had to compute 10% over each value for
Quantity, and hand-type each modified value in the appropriate slot.
Click OK when you are ready to proceed.
At this point, you have a fully saved
scenario. Click Show to display the scenario results.
Because the scenario will overwrite the
original data on your worksheet, Microsoft suggests that you create separate
scenarios for both the "before" and "after" states.
Use Solver to perform what-if analysis
using multiple input values. Let's take an example. Take a look at the following
Suppose we wanted to see a net gain for
Sales Team 1 of $50,000. We can have Solver determine the new budget value for
all four sales teams, given the company's budget cap of $60,000. To start
Solver, you must first have this Excel add-in loaded on your system. Then, you
must click Tools => Solver.
In the Solver Parameters dialog box,
input the cell that contains the formula next to Set Target Cell:, and
next to Equal To: select the appropriate option. In the above example, I
want to set cell B5 to a value of $50,000. We want Solver to change the budget
values for all four sales teams, so I entered the appropriate cell range under
By Changing Cells:. Finally, I needed to tell Solver that we have a
budget cap of $60,000. To do this, click Add in the Subject to the
Constraints area of the Solver Parameters dialog box.
In the Add Constraint dialog box, I
added F3 under Cell Reference, select the equals comparison
operator, and entered 60000 under Constraint:.
Here are the finished Solver parameters for
the above example:
When you are ready to finish, click
Solve. If Solver is able to find a solution, you will see the following
Notice that you have the option to either
keep Solver's solution or to restore your original values. It is suggested that
you save a scenario of your original figures, because once you implement
Solver's solution, you won't be able to use Undo to restore your original
values. The following graphic displays Solver's solution to the above sample
Use data analysis and PivotTables
Pivot tables allow you to take a long,
complex list and view it in many different ways. To create a PivotTable, select
a cell inside the list range and click Data => PivotTable and
PivotChart Report.... In Step 1, you can just leave the defaults:
creating a PivotTable from a Microsoft Excel list.
In Step 2, you verify the list range to be
included in the PivotTable report. Click Next to continue.
In Step 3, you can click Layout...
to specify the PivotTable fields, but we will wait until the PivotTable has
already been placed in a worksheet to do that. In this case, leave the default
setting, New worksheet, and click Finish.
Setting up a PivotTable definitely takes
some getting used to. The bottom line here is that you can simply drag each list
field from the PivotTable toolbar to a location on the empty PivotTable layout.
In this example, I have used Region
as a page field, Year as a column field, Customer as a row field,
and Sales as a data field. Notice that Excel automatically performs the
summary calculations on your data field.
Notice also how interactive a PivotTable
report is. Click the black down arrows for your page, column, and row fields to
filter the results even further.
You can remove PivotTable fields by
dragging them off the PivotTable and into a blank area of the worksheet. They
will immediately disappear, and that field on the PivotTable will go blank.
Create interactive PivotTables for the Web
To save an Excel PivotTable report as a Web
page, select any cell inside the PivotTable and click File => Save
as Web Page.... In the Save As dialog box, select Add
interactivity and ensure that Selection: PivotTable is enabled. Next,
In the Publish as Web Page dialog
box, make sure that Items on Pivot Table is listed next to Items to
publish, and that the PivotTable is selected in the list. Under Viewing
options, ensure that PivotTable functionality is selected. Next to
File name:, type in the name of the HTML file along with its full path.
You can click Browse... to find a location to store the file if you
cannot remember the correct path. Select Open published web page in browser
to instruct Excel to preview the published PivotTable list in your default
Here is a graphic showing the finished
product. Note that you must have Office Web Components (part of the Office 2000
installation) installed on your system in order to display interactive
PivotTables in a browser.
Add fields to a PivotTable using the Web browser
Assuming that you have the Office Web
Components installed on your system and are running Internet Explorer 4.01 or
later, you can add or subtract fields to your browser-based PivotTable. To do
this, click the Field List button on the PivotTable toolbar. In the
PivotTable Field List pop-up box, you will see your table fields. Simply
drag and drop the fields on or off the browser-based PivotTable to modify the
table. Any modifications you make to the browser table do NOT update the
original PivotTable located in Excel.
Collaborating with Workgroups
Create, edit and remove a comment
To create a comment, select the appropriate
cell and click Insert => Comment. A small callout window will
appear, in which you can enter your comment.
Excel generates the default reviewer name
from information you might have provided in Excel options. To change the user
name, click Tools => Options, navigate to the General
tab, and modify the entry next to User name:.
To view a comment, simply hover your mouse
pointer over the small red triangle perched in the upper-left corner of a cell
that contains comments. To edit a comment, right-click inside a commented cell
and choose Edit Comment from the shortcut menu. Notice that the comment
window is just a text box: you can resize the box by dragging at the resize
To format practically any aspect of either
the comment text or the comment box itself, right-click the comment box border
and select Format Comment... from the shortcut menu.
To delete a comment, right-click the
commented cell and choose Delete Comment from the shortcut
Apply and remove worksheet and workbook protection
To protect the active worksheet, click
Tools => Protection => Protect Sheet.... In the
Protect Sheet dialog box, you have the option to protect the active
worksheet's contents and charts (select Contents), graphic objects
(select Objects), or what-if scenarios (select Scenarios). You can
also specify an optional password for additional security.
To unprotect a worksheet, click
Tools => Protection => Unprotect sheet.... You will
be prompted with the following dialog box.
To protect a workbook, click Tools
=> Protection => Protect Workbook.... In the Protect
Workbook dialog box, you can protect a workbook against changes made to
worksheet orientation (select Structure), or against window resizing or
closure (select Windows). Like worksheet protection, you can and should
supply a password to enforce the workbook protection.
To remove workbook protection, click
Tools => Protection => Unprotect Workbook... and
supply the correct password, if one was originally specified.
Change workbook properties
A workbook's properties sheet holds summary
information about the file itself (size, creation date, etc.), as well as
information about the file's creator (author name, manager, company, etc.). To
view a workbook's properties sheet, click File =>
Properties.... The General tab provides file specs such as its
save path; the Summary tab provides document title, subject, and author;
the Statistics tab provides file creation and last modified dates; the
Contents tab provides a snapshot image of the workbook; and the
Custom tab has a plethora of detail fields related to document completion
status. It is important to note that there are relatively few read-only fields
in a workbook's property sheet: it is up to the user to provide the rest of the
information related to the file.
Apply and remove file passwords
Sometimes you may want to restrict users'
ability to view a workbook without a password. To password-protect a workbook,
you must work from the Save As dialog box. This, in most cases, means
resaving the file by carrying out the File => Save As...
command. In the Save Options dialog box, you can require a password to
open the file read-only, and another password to open the file with full access.
To get to this dialog box, click Tools => General Options.
Read-only recommended will require users, who want to save changes, to
save to another file.
Okay- let's assume we have configured our
workbook security like the above screen shot, and have established both a
password to open and a password to modify the file, with the helpful option for
Excel to recommend read only status.
Following is the initial dialog when
someone attempts to open the protected file:
If the valiant user has supplied the
correct password, then they will be confronted with the following dialog box.
Note that the Read-only recommended check box we marked in an earlier
step is NOT the reason there is a button labeled Read Only in this dialog
box- you'd see that button anyway.
Again, assuming that the user supplies the
correct password to modify, NOW we see the indefatigable Read-only
recommended option kick in:
So there you have it- a quick primer on
Excel document security. Unfortunately, there is no simple menu command or
toolbar button to remove document passwords. You will find that if you open an
Excel workbook with full access, save a copy of the document, and open the copy,
the file will still be password-protected.
Track changes (highlight, accept, and reject)
To turn on revision tracking in an Excel
2000 workbook, click Tools => Track Changes =>
Highlight Changes.... Selecting Track changes while editing
also shares your workbook, which means that multiple network users can open and
edit the document simultaneously.
The option When: specifies the unit
of time for which you want to track changes. The default is All.
The option Who: specifies whether
you want to track your own changes along with everyone else's (the default
setting), or only to track the changes made by other editors (select Everyone
The option Where: allows you to
track changes for only a specified range of cells. Use the Collapse
Dialog button to mark your range, if this is the option that you need for
Select Highlight changes on screen
to make your revisions visible to you and your fellow editors.
To view tracked changes, hover your mouse
pointer over the small blue triangle perched in the upper-left corner of a cell
that contains changes. A call-out box similar to the comment box will appear,
containing detailed information regarding any changes made.
To accept or reject changes in a worksheet,
click Tools => Track Changes => Accept or Reject
Changes.... In the Select Changes to Accept or Reject dialog box,
specify what time frame for tracked changes that you'd like to review, whose
changes you'd like to review, and for what range in the worksheet you'd like to
The rest of the procedure is fairly easy.
The Accept or Reject Changes dialog box will present the changed
information, one change at a time. You will then have the opportunity to either
accept or reject each change. If you are getting tired of the whole mess, you
can click Accept All or Reject All and just be done with the whole
Create a shared workbook
To make your workbook available to other
network users and to allow simultaneous access, click Tools =>
Share Workbook.... In the Editing tab of the Share Workbook
dialog box, place a check in Allow changes by more than one user at the same
time. This also allows workbook merging. Remember that the workbook must be
saved to a shared network location for other users to be able to access the
The Advanced tab allows you to set
advanced configuration options related to issues such as whose changes "win" if
two simultaneous users make a change to the same cell.
After saving a workbook as a shared
workbook, the title bar will display [Shared] after the file name.
To stop sharing a workbook, simply uncheck
the Allow changes by more than one user at one time option in the
Share Workbook dialog box.
You can use Excel 2000's Merge feature to
merge changes from one copy of a shared workbook into another copy of the same
shared workbook. Some requirements to do this are that the two workbooks are
indeed copies of one another, are both shared by using the procedure outlined
above, and that they each have different names.
To merge workbooks, first open the original
workbook. Next, click Tools => Merge Workbooks.... (This menu
option is unavailable if the workbook has not been shared.) In the Select
Files to Merge Into Current Workbook dialog box, browse and open the
workbook(s) to be merged into the currently open workbook.
Excel will display changed information in
the same manner in which it normally displays tracked changes. You can then go
ahead and accept or reject each change as usual.