MATH298B
TUTORIAL
PACKET
Excel
Equation Editor
PowerPoint
Word
Blank Page Inserted for Double −Sided Printing
TABLE OF CONTENTS
PART 1: Microsoft Excel Tutorial
PART 2: Equation Editor Tutorial
PART 3: Microsoft PowerPoint Tutorial
PART 4: Microsoft Word Tutorial
Please note that each Part has its own page −numbering system. Blank Page Inserted for Double −Sided Printing
COVER PAGE
MATH298B
Excel
TUTORIAL
2003 and beyond, Lawrence Morales
Excel Tutorial - Page 1 Intentionally blank for double-sided copies.
Excel Tutorial - Page 2
TABLE OF CONTENTS
COVER PAGE ................................................................................................................................................................. 1
TABLE OF CONTENTS................................................................................................................................................. 3
BASIC EXCEL FACTS AND USAGE........................................................................................................................... 5
BASIC INTRODUCTIONS TO A SPREADSHEET................................................................................................................... 5
ENTERING DATA............................................................................................................................................................. 6
EDITING CELL CONTENTS................ 8
Changing Cell Formats............................................................................................................................................. 8
Changing Row and Column Sizes ........................................................................................................................... 11 Contents.......................................................................................................................................... 12
WORKING WITH FORMULAS.......... 12
COPYING FORMULAS..................... 12
ABSOLUTE REFERENCES................ 16
SORTING DATA.............................. 18
BASIC MATHEMATICS COMPUTATIONS............................................................................................................ 25
BASIC MATHEMATICAL FUNCTIONS............................................................................................................................. 25
Addition................................................................................................................................................................... 25
Subtraction.............................................................................................................................................................. 26
Multiplication.......................................................................................................................................................... 27
Division 27
Powers and Exponents............................................................................................................................................ 28
The Natural Base, e................................................................................................................................................. 29
Square Roots........................................................................................................................................................... 30
The Natural Logarithm .......................................................................................................... 30
The Value of π......................................................................................................................................................... 31
Rounding................................................................................................................................................................. 31
Rounding Up and Rounding Down ......................................................................................................................... 32
BASIC STATISTICAL FUNCTIONS.... 33
Averages and Means............................................................................................................................................... 33
Sums........................................................................................................................................................................ 33
Minimums......................................... 33
Maximums........................................ 34
The Range ...................................................................................................................... 34
DATABASES............................................ 34
BASIC INTRODUCTION TO DATABASES ......................................................................................................................... 34
THE DCOUNT COMMAND............ 36
FILTERING .................................................................................................................................................................... 41
THE DAVERAGE, DSUM, DMIN, DMAX COMMANDS ............................................................................................ 43
SPECIAL EXCEL FUNCTIONS.................................................................................................................................. 45
THE COUNT COMMAND............... 45
THE IF COMMAND............ 46
THE RAND COMMAND.................. 48
THE RANDBETWEEN COMMAND.............................................................................................................................. 49
THE IF COMMAND......................... 50
THE VLOOKUP COMMAND.......... 56
THE HLOAND 58
THE FUNCTION WIZARD AND THE VLOOKUP AND HLOOKUP COMMANDS.............................................................. 58
THE INDEX COMMAND................. 60
OPTIONAL: HOW I BUILT THE PHONE LOG FILE ........................................................................................................... 62
Excel Tutorial - Page 3 GRAPHING.................................................................................................................................................................... 63
BASIC GRAPHING ......................................................................................................................................................... 63
GRAPHING FUNCTIONS.................. 77
GHISTOGRAMS............... 82
Bins and Midpoints ................................................................................................................................................. 84
Histogram Wizard............................................................................................................... 87
Formatting the Histogram ...................................................................................................................................... 88
TRENDLINES................................................................................................................................................................ 93
Excel Tutorial - Page 4
Basic Excel Facts and Usage
Basic Introductions to a Spreadsheet
A spreadsheet is a software tool that allows you to work with data (mainly numerical) in very
complex and helpful ways. In MAT116 and MAT117, Excel will be used extensively to complete
the projects in each of the course. Hence, it is crucial that you have a basic understanding of how
spreadsheets work. Here is a basic screen shot of Excel. Please take time to read the callouts to
learn some basic spreadsheet vocabulary.
These two rows are called Toolbars.
These are the Menus used This particular button calls up the
to issue commands. Function Wizard.
This is the formula bar where
you enter and edit formulas.
The Address Box
tells you what cell Each of these boxes is called a “Cell.” Cells contain,
is currently active. numbers, text, formulas, or even pictures. A cell’s
You can enter in a address is given by the intersection of the column letter
cell address to go and the row number. This particular cell’s address is
to directly to a cell, B3. When you click on or move to a cell, its border
or use it to give a will become highlighted, as shown here.
cell a specific
name.
These tabs select Sheets that you can work in. The current sheet being worked in
is in bold letters. This class will often use files with several sheets included so pay
attention to whether or not these are labeled or named to indicate more than one
sheet exists.
Excel Tutorial - Page 5 A spreadsheet may be used to quickly process numerical data, like the simple sales tax sheet shown
below:
Or, they could be used to simulate complex random sampling situations for complicated and
intricate mathematically based problems, such as those we will encounter in MAT116 and
MAT117.
Entering Data
Entering data into a spreadsheet is as simple as clicking once on a cell and entering the data. You
can also use the arrow keys on your keyboard to navigate to a cell. The active cell will be
highlighted and its address will appear in the Address Box. If you have not opened up Excel yet,
please do so now by opening the file, Blank.xls. Practice moving around the cells if you are new to
a spreadsheet. (In this tutorial, any text that is blue and underlined is a link to an existing file. To
open the link, click on it. If that does not work, try to hold down the Ctrl button on your keyboard
while you click once on a link.)
The simplest kind of data to input is text and numbers.
Practice
Open Navigate to Cell A1 and type in the words “Tax Rate” followed by the Enter key. In
Cell B1, type in the number .0865 (which corresponds to 8.65%). Continue to edit your
worksheet until it looks like this…