What is it?
The first "killer app" for PC's. The first what-if
software to process and display numbers and data sets.
Excel,
Lotus 1-2-3, Sphygmic
(free)
Microsoft's Excel is the clear market leader. Their MOUS
(Microsoft Office User Specialist) certification
program has an examination to validate expertise. We don't need the exam,
but we can use the certification objectives as a starting point.
MOUS's Excel: Core
Objectives | Expert
Objectives
PC Webopedia's Spreadsheets
Skills
managing
numerical data
making tables and
charts
building numerical
models, especially financial
preparing accounting
working papers
simulating economic
conditions via difference equations/"cellular automata"
modeling mathematical
conditions
solving linear and
nonlinear programs
modeling loans
quick example: Erich Neuwirth's visualizing correlations
the MBA program needs an XPUG -- an Excel Power Users Group.
Everyone needs to belong.
MBA 503 skills
Going into MBA 503, it would be best if you knew:
using a workbook
labeling worksheets
formatting
adjusting column and row height
changing fonts etc
inserting symbols
using text boxes
style (number, text, currency, %)
sorting
charts
add-ins
using formulas
referring to other cells
formula auditing
History of spreadsheets
Spreadsheet:
Its First Computerization (1961-1964)
by Richard Mattessich
The term "spread sheet" (nowadays
"spreadsheet") has a long tradition; reference to its non-computerized
version can be found in the first edition of Eric L. Kohler's Dictionary for
Accountants (1952) and refers to a worksheet providing a two way analysis of
accounting data (e.g., an accounting matrix in which the columns and rows
constitute either debit and credit sides respectively or reverse).
In the beginning, there were
no spreadsheets ...
A Brief History of
Spreadsheets
by D. J. Power
Excel (1987) was one of the first spreadsheets to use a
graphical interface with pull down menus and a point and click capability using
a mouse pointing device. The Excel spreadsheet with a graphical user interface
was easier for most people to use than the command line interface of PC-DOS
spreadsheet products. Many people bought Apple Macintoshes so that they could
use Bill Gates' Excel spreadsheet program.
MBA 503 textbook
Doing Statistics for Business with Excel: Data, Inference, and
Decision Making, 2nd Edition
by Marilyn K. Pelosi, Theresa M. Sandifer
Excel
Hell
by Alice Dragoon
Darwin magazine, May 2002
Still using Excel to plan your budgets? Still frustrated by
inaccurate forecasting? ...
As the economy gasped and sputtered in the fall of 2001, countless CEOs found
themselves squirming in their seats as they briefed Wall Street analysts on
their company’s prospects during conference calls. They squirmed because they
were forced to tell the analysts that they “lacked visibility.” In other
words, they admitted that they didn’t have a clue what was going to happen in
the coming year.
Basic Excel Skills
Creating a Workbook
Create a new workbook
Move around the worksheet using the mouse and keyboard
Select cells using the mouse and the keyboard
Enter text, values and dates in cells
Edit cell contents
Use Undo and Redo
Work with series of data and custom lists
Work with AutoFill smart tag
Use Save and Save As
Save an AutoRecover file automatically
Recover a workbook if Excel stops working
Locate and open an existing workbook
Create a folder
Editing a Worksheet
Go To a specific cell or named range
Move, copy and paste cells using the Windows or Office Clipboard
Use Find and Replace
Creating a Formula
Understand what a formula is and how to create simple
calculations
Use AutoSum
Enter a formula by typing or by using the Formula bar
Enter a range within a formula
Revise a formula
Use Insert Function
Use basic functions (AVERAGE, SUM, COUNT, MIN, MAX)
Use relative and absolute cell references
Recognize common error messages
Formatting a Worksheet
Apply font styles (typeface, size, color and styles)
Modify the alignment and orientation of cell contents
Merge and unmerge cells (to center headings across columns)
Apply value formats (currency, percent, dates and comma)
Adjust the decimal place
Modify the size of columns and rows
Clear cell content and formats
Working with Worksheets
Insert and delete rows and columns
Insert and delete selected cells
Insert and delete worksheets
Move and copy a worksheet
Rename a worksheet
Change the color of a worksheet tab
Resources
A spreadsheet, like a word processor and like a sophisticated
web page, is just a fancy front-end for a database. In fact, many people use
Excel as a word processor or to make tables for which Access or another database
program would be more appropriate.
Microsoft's Excel Tour | Tips
(customizing, shortcuts, etc.) | newsletter
| Support
Don't forget the help system that comes with Excel. Press F1 or
pull down the Help menu.
Terrific animated tutorials, especially for beginners!
Excel Viewlets
and Guides are for Excel 2000, but all these features are available in the
latest Excel.
About.com's Microsoft Excel
Tutorials - free Excel tutorials broken down into basic, intermediate, and
advanced levels.
About.com's Importing
a Spreadsheet into Access
by Mike Chapple
Florida Gulf Coast's Online Tutorials: Excel 2000
Bay City Public Schools' Excel
tutorial
Russell Schulz's comp.apps.spreadsheets FAQ
Linda's Fun Stuff To Do in
Microsoft Excel
Washington State Community College's
Spreadsheet Basics using Excel 2000
J. Walk's The Spreadsheet
Page
Steven Bullen's Excel utilities
Alan Barasch's Formulas
Ray Panko's Spreadsheet
Research (SSR) -- research on spreadsheet development, testing, use, and
technology.
Roger Myerson's Simtools
- add statistical functions and procedures for doing Monte Carlo simulation and
risk analysis
Roger Myerson's Formlist
- a simple auditing tool that adds procedures for displaying the formulas of any
selected range
Erich Neuwirth's Statistics
Demonstrations with Excel: Correlation Visualized, Central Limit Theorem,
and Binomial Simulation
stats applets and other tools
Let's Put the Squares in Least-Squares
by Erhan Erkut and Armann Ingolfsson
INFORMS Transactions on Education, 2000
An interactive spreadsheet to teach simple linear
regression, which we have found to be an effective teaching tool. Unlike most
textbooks that cover regression, this spreadsheet displays what is being
minimized – the squares of the errors – and it allows students to experiment
with the parameters of the line before optimizing them.
Note at the bottom of that page:
least-squares calculators
The user enters the data and the applet displays a scatter
diagram and reports the optimal parameter values and some other statistics.
John C. Pezzullo's
Web Pages that Perform
Statistical Calculations!
( StatPages.net )
Over 600 Links (including 380 Calculating Pages) -- And
Growing!
MathTools -
helpful examples and tutorials of Excel tools to make your work easier, for
example, pivot
tables.
This one's a little more advanced -- what you do with
pivot tables. Some of these ideas will add a lot to your presentation by telling
the others why they need to know more about pivot tables.
Analyzing Data
with Microsoft Excel 2002
Web Queries
The Web is, by design, the perfect vehicle for sharing and
distributing information. The ability to view dynamically created reports via
the Web helps corporations keep employees up to date on business critical
information. But many users are used to getting their reports in Excel
spreadsheets and prefer their reports this way because Excel allows them to do
their own analysis of the presented data.
Getting
Data From the Web in Excel 2002
Web queries can assist you in inserting refreshable data
from the Web into your worksheets. ... You can now navigate to any Web page
while setting up a Web query, the same way as you would navigate to that page in
your browser. Once you are at the page, you can select one or more tables by
clicking the icon images automatically added to the page by Excel.
About
retrieving data from a Web page
To analyze data on a Web page, you can use a Web query to
retrieve a single table, multiple tables, or all of the text on a Web page.
After you retrieve the data, you can analyze it by using the tools and features
in Microsoft Excel.
Use a Web query when you want to get data from the Web for use in Excel — for
example, to retrieve and analyze stockmarket quotes. A Web query brings only the
data and not the entire contents of the Web page into Excel.
publishing Excel data to the Web
1) In Excel, choose File, Save As Web Page, choose the
Selection: Sheet option, enable the Add interactivity checkbox and click
Publish. (For worksheets only, not whole workbooks.)
2) Choose the sheet to publish and, from the "Add
interactivity with" dropdown list, choose "Spreadsheet
functionality."
3) Add a Title, specify a filename, and FTP the page.
Note: If you have Internet Explorer with the Microsoft Office
Web Components installed, you can view the page and test the interactive
elements before uploading it to your Web site.
Learn
more
take a screen shot of a range of cells
1) In an open spreadsheet, select the area you'd like to copy.
2) Hold down SHIFT, then click Edit | Copy Picture.
3) Select how you would prefer the data to be copied.
4) Open Paint Shop Pro and Edit | Paste As New Image
Amazing things happen when you hold down that SHIFT key before
you click.
Tips and uses
Kelly Russel's Excel Tips
Beyond Technology's Spreadsheet Power Tips
Nifty stuff that costs money but saves time
Add-ins
The Spreadsheet
Assistant - $40
select
or fill ranges without the screen moving
perform any math
action on a range of cells
insert Sticky Notes
anywhere in your worksheets for quick reminders
The Sheet
Navigator and many more productivity
add-ins
Help!! My files got corrupted
They'll be terrible role models. Here's a couple of solutions
that will cost you some money.
Excel Fix -
hundred-dollar utility to recover and rebuild damaged .xls files.
ExcelRecovery
- hundred-fifty-dollar utility to fix corrupted spreadsheets.
Spreadsheet
