MICROSOFT EXCEL
Excel is the spreadsheet program created by Microsoft. Although you can use any
spreadsheet program for analyzing data, the instructions given here are specific for Excel and
you must use Excel for the three Excel quizzes. NOTE: Microsoft also makes a less powerful
spreadsheet program as part of Microsoft Works or some similar title. Some of the features
that we will use in these exercises are not found in MS Works, so you will not be able to
complete all the exercises using MS Works.
Excel is, in its most basic form, a very fancy calculator. The information given in this quick
tutorial is meant to give a working knowledge of how to use Excel. There are usually several
different ways to perform the same function in Excel, this tutorial will usually just give one
way. If you need more information on how to use Excel, there are many web sites dedicated
to using Excel, a simple 'google' search will find many of them. In addition, accessing the
HELP menu from within the program can also be useful!
Major Uses for Excel
Excel is used widely in any financially-related activity. The ability to create new
spreadsheets where users can define custom formulas to calculate anything from a simple
quarterly forecast to a full corporate annual report makes Excel highly appealing. Excel is
also used widely for common information organization and tracking like a list of sales leads,
project status reports, contact lists, and invoicing. Finally, Excel is a useful tool for scientific
and statistical analysis with large data sets. Excel's statistical formulas and graphing can help
researches perform variance analysis, chi-square testing, and chart complex data.
Basic operation
Microsoft Excel has the basic features of all spreadsheets, [4] using a grid of cells arranged in
numbered rows and letter-named columns to organize data manipulations like arithmetic
operations. It has a battery of supplied functions to answer statistical, engineering and
financial needs. In addition, it can display data as line graphs, histograms and charts, and with
a very limited three-dimensional graphical display. It allows sectioning of data to view its
dependencies on various factors for different perspectives (using pivot tables and the scenario
manager).[5] It has a programming aspect, Visual Basic for Applications, allowing the user to
employ a wide variety of numerical methods, for example, for solving differential equations
of mathematical physics,[6][7] and then reporting the results back to the spreadsheet. It also has
a variety of interactive features allowing user interfaces that can completely hide the
spreadsheet from the user, so the spreadsheet presents itself as a so-called application,
or decision support system (DSS), via a custom-designed user interface, for example, a stock
analyzer,[8] or in general, as a design tool that asks the user questions and provides answers
and reports.[9][10][11] In a more elaborate realization, an Excel application can automatically poll
external databases and measuring instruments using an update schedule, [12] analyze the
results, make a Word report or PowerPoint slide show, and e-mail these presentations on a
regular basis to a list of participants.
How Excel Works
An Excel document is called a Workbook. A workbook always has at least one Worksheet.
Workseets are the grid where you can store and calculate data. You can have many
worksheets stored inside a workbook, each with a unique worksheet name.
Worksheets are laid out in columns (vertical) and rows (horizontal). The intersection of any
given row and column is a cell. Cells are really where you enter any information. A cell will
accept a large amount of text, or you can enter a date, number, or formula. Each cell can be
formatted individually with distinct border, background color, and font color/size/type.
Number of rows and columns
Versions of Excel up to 7.0 had a limitation in the size of their data sets of 16K (2 14 = 16384)
rows. Versions 8.0 through 11.0 could handle 64K (2 16 = 65536) rows and 256 columns (28 as
label 'IV'). Version 12.0 can handle 1M (220 = 1048576) rows, and 16384 (214 as label 'XFD')
columns.
Microsoft Excel up until 2007 version used a proprietary binary file format called Excel
Binary File Format (.XLS) as its primary format. [23] Excel 2007 usesOffice Open XML as its
primary file format, an XML-based format that followed after a previous XML-based format
called "XML Spreadsheet" ("XMLSS"), first introduced in Excel 2002.[24]
Worksheets
Microsoft Excel consists of worksheets. Each worksheet contains columns and rows. The
columns are lettered A to Z and then continuing with AA, AB, AC and so on; the rows are
numbered 1 to 1,048,576. The number of columns and rows you can have in a worksheet is
limited by your computer memory and your system resources.
The combination of a column coordinate and a row coordinate make up a cell address. For
example, the cell located in the upper-left corner of the worksheet is cell A1, meaning
column A, row 1. Cell E10 is located under column E on row 10. You enter your data into the
cells on the worksheet.
Data Types, Formulas, and Functions
The types of data that a cell can hold include:
numbers;
text;
dates and times;
Boolean values;
formulas.
Formulas are used for calculations - usually incorporating data contained in other cells. These
cells, however, may be located on different worksheets or in different workbooks.
Creating a formula starts by entering the equal sign in the cell where you want the answer
displayed. Formulas can also include cell references to the location of data and one or more
spreadsheet functions.
Functions in Excel and other electronic spreadsheets are built-in formulas that are designed to
simplify carrying out a wide range of calculations - from common operations such as entering
the date or time to more complex ones such as finding specific information located in large
tables of data.
Excel and Financial Data
Spreadsheets are often used to store financial data. Formulas and functions that are used on
this type of data include:
Performing basic mathematical operations such as summing columns or rows of numbers;.
Finding values such as profit or loss.
Calculating repayment plans for loans or mortgages.
Finding the average, maximum, minimum and other statistical values in a specified range of
data.
Carrying out What-If analysis on data - where variables are modified one at a time to see how
the change effects other data - such as expenses and profits.
Excel Formulas
You can create simple and complex formulas in Excel to calculate just about anything.
Inputs to a formula may be other cells, the results of other formulas, or just straight-forward
math (5*2+3). Excel includes a formula library for calculating things like Net Present
Value (NPV), standard deviation, interest payments over time, and other common financial
and mathematic formulae. Excel's formula bar includes a feature to help you search for a
formula you need, and also helps you select the appropriate cells in your workbook to
calculate the formula.
Excel Charting
Excel offers a wide array of charts to visualize data. They range from simple line graphs to
bubble and radar charts. Excel has two main tools for charting: standard charts and pivot
charts.
A standard chart is relatively static one you create it. As you change data in your
spreadsheet, related cells will refresh the chart to show the new updates. A pivot chart is
more dynamic, allowing you to "slice-and-dice" your data in several different ways by
choosing which columns contain the information you want to group by, or apply
mathematical operations (sum, count, average, find the maximum, etc).
Excel became the first spreadsheet to allow the user to define the appearance of spreadsheets
(fonts, character attributes and cell appearance). It also introduced intelligent cell
recomputation, where only cells dependent on the cell being modified are updated (previous
spreadsheet programs recomputed everything all the time or waited for a specific user
command). Excel introduced auto-fill, the ability to drag and expand the selection box to
automatically copy cell or row contents to adjacent cells or rows, adjusting the copies
intelligently by automatically incrementing cell references or contents. Excel also introduced
extensive graphing capabilities.
