Excel - PowerPoint PPT Presentation

About This Presentation
Title:

Excel

Description:

The math that goes on behind the scenes on the paper ... Let's create a column chart from the student grade data from before. First, highlight the data. ... – PowerPoint PPT presentation

Number of Views:110
Avg rating:3.0/5.0
Slides: 89
Provided by: zha84
Category:

less

Transcript and Presenter's Notes

Title: Excel


1
Excel
  • Sen Zhang

2
What is a spreadsheet?
  • Spreadsheets have been used for many, many years
    in business to keep track of information such as
    expenses, help make decisions and for other
    calculation purposes.
  • Spreadsheets allow you to organize information in
    tables (which are composed of rows, columns and
    cells).

3
The formal definition of the traditional
spreadsheet.
  • A piece of paper with rows and columns for
    recording financial data for use in comparative
    analysis.

4
If using paper and pencil
The math that goes on behind the scenes on the
paper spreadsheet can be overwhelming. If you
change the loan amount, you will have to start
the math all over again (from scratch), and you
probably will make mistakes somewhere even with
the aid of calculators and you have to start
again.
5
What is an Excel spreadsheet?
  • Simply put, it is the computer equivalent of a
    paper-based spreadsheet, processed by Microsoft
    Excel software.
  • More than simulating the paper-based spreadsheet,
    Excel spreadsheet, however, is more powerful than
    the simple spreadsheet for its added bonus of
    automatic mathematics.

6
What is an Excel spreadsheet?
  • Excel is an environment that can make number
    manipulation easy and somewhat less painfull than
    otherwise.
  • Excel eliminates the tedious recalculations
    required by manual methods.

7
If using Excel
  • All you need to do is to design your formula
    precisely once, then formulas will do all the
    calculation for you, always precisely and
    correctly!
  • The best thing is that you can experiment with
    numbers without having to RE-DO all the
    calculations.

8
What makes up a spreadsheet in Excel?
  • Spreadsheets are made up of
  • COLUMNS. Letters are used to designate each
    COLUMN'S location.
  • ROWS. Numbers are used to designate each ROW'S
    location.
  • CELL is defined as the space where a specified
    row and column intersect. Each CELL is assigned a
    name according to its COLUMN letter and ROW
    number. When referencing a particular cell, you
    should put the column first and the row second.

9
More about names (addresses or identifiers)
  • In computing world, everything is modeled an
    object, and every object has a name. (That is why
    computers can do things precisely.)
  • Operating system manages data in different files,
    folders, drives, computers or network domains
    etc.
  • In Excel, every object here stands for a cell, a
    column, a row, a worksheet or a workbook file.

10
Locate a cell
  • For example, the cell in the uppermost left
    corner would be "A1." The current cell(s) will
    always be listed in the "Name Box," which appears
    on the left below the standard and formatting
    toolbars (you can drag it to different places.)

11
Navigating the Spreadsheet
  • You can use the "Up," "Down," "Left," "Right," to
    move (one cell at a time) throughout the
    spreadsheet. You can also simply click the cursor
    into a cell). The "tab" button will move one cell
    to the right. The "Enter" button will confirm the
    entered information and move one cell down.

12
What can you do with those cells?
  • A cell is the basic unit of a worksheet into
    which you enter data or information.
  • In each cell there may be the following types of
    data
  • text (labels)
  • number data (constants)
  • formulae (mathematical equations that work on
    number data, always being prefixed by an
    assignment sign, .)

13
Formulas
  • They are entries that have an equation that
    calculates the value based on the values of other
    cells or constants.
  • Formulas reflect business rules.
  • In formulas cells, we DO NOT type in the numbers
    we type in the equation, because the values of
    these cells are supposed to be derived from the
    values of other cells.

14
reference
  • This cell reference can either be a relative or
    an absolute reference

15
Use formulas as much as possible!
  • When we are entering formulas into a spreadsheet
    we want to make as many references as possible to
    existing data.
  • If we can reference that information we don't
    have to type it in again.

16
Intelligence need to be designed.
  • Excel is good at repeating and precise
    calculation.
  • But Computer is not as smart as human beings. It
    is your responsibility to precisely tell the
    Excel what you want it to do by defining right
    formulas in proper cells.

17
How can Excel make calculation easier?
  • Excel will keep track of numbers you place in
    cells. Furthermore, if you have defined formula
    cells to refer to each other, any changes made in
    one cell will be reflected in these referring
    cells.
  • It sounds a bit complicated, but Excel makes it
    all a breeze.

18
Two stages
  • Design stage. Implement all business rules using
    formulas. You are the designer, not necessarily
    to be the final user.
  • Production stage. you give your well-designed
    Excel workbook which consists of pre-allocated
    blank cells and preset formula cells to the final
    users, who might or might not be yourself.

19
  • Layout design,
  • text, label and row header and column header
    information.
  • Numerical data directly input from users.
  • Derived data defined by formulas (including both
    straight formulas and predefined functions.)
  • Format and lighten up the dry data appearance
  • Generate charts
  • Web support (two ways)
  • Some data could be retrieved from Web.
  • Save as webpage

20
An example
  • This is what a basic spreadsheet may look like,
    keeping track of the grades for five students. As
    you'll notice, numbers automatically align to the
    right, while text automatically aligns to the
    left. Room has been allowed at the top and the
    left for column and row headings, which have been
    placed in bold.
  • We will show grade 1, 2, 3 can be generated
    easily.

21
Simple Formulas
  • "92.67" was not entered as the contents for cell
    "E2." The "formula bar" has the following entered
    into it
  • (B2C2D2)/3

22
repeating
  • If you wanted to do the same for students 2
    through 5, you would enter in similar formulas
    for each cell from "E3" to "E6" replacing the
    column letters and row numbers where appropriate.

23
Copying by dragging fill handler
  • An easy method to replicate formulas is to select
    the cell which contains the original formula
    ("E2" in this case), click the bottom right
    corner of the selection box, and drag down
    several rows (to "E6" in this example). The
    formula will be copied down in each cell, and
    will change itself to reflect each new row.

24
Relative addresses used in formulas
  • Cells information is copied from its relative
    position. In other words in the original cell
    (e2) the equation was (B2C2D2)/3. When we
    paste the function it will look for the three
    cells to the left. So the equation pasted into
    (e3) would be (B3C3D3)/3. And the equation
    pasted into (e4) would be (B4C4D4)/3.

25
Absolute address in formulas
  • Sometimes it is necessary to keep a certain
    position that is not relative to the new cell
    location.
  • This is possible by inserting a before the
    Column letter or a before the Row number (or
    both). This is called Absolute Positioning.

26
Absolute address
If we were to fill down with this formula we would have the exact same formula in all of the cells C1, C2, C3, and C4. The dollar signs Lock the cell location to a FIXED position. When it is copied and pasted it remains EXACTLY the same (no relative).
A B C
1 5 3 A1B1
2 8 2 A1B1
3 4 6 A1B1
4 3 8 A1B1

27
Relative position Absolute position
  • Relative position, AB12
  • Absolute position, AB12
  • Mixed reference A2 with only one dollar sign
    before either the column or the row .
  • Unless you use absolute positions in your
    formula, the smart Excel will take it for granted
    that the reference is relative, that means they
    will change positions based on its intelligence!

28
More examples about relative position in formula
  • D5 cell contains a formula, which is d4c4.
  • If this formula is copy to d8 what is the result?
  • D7c7, why.

29
  • D5d4c4
  • What is the relative position.
  • Compared with d5, d4 means same column, denoted
    0 one row above denoted as -1.
  • Compared with d5, c4 means one column left,
    denoted -1 one row above denoted as -1.

30
  • This relative information will be maintained in
    new cell
  • D8D7C7

31
Absolute position in formula
  • D5d4c4

32
What are Excel functions?
  • An Excel function is a predefined formula.
  • Sum, max, average etc.
  • Many more

33
A function exampleGenerate a random value
between two limits
  • Enter the following values in cell A1 and A2 65,
    84 (these values will act as the lower and upper
    bound limits)Enter the following formula in
    cell B1 RANDBETWEEN(A1,A2)A random number
    between 65 and 84 will be generated

34
  • Different views ctrl (accent symbol)

35
A new Excel Workbook
  • Microsoft Excel will automatically open with a
    blank spreadsheet spanning many columns and rows.
    You will notice a number of toolbars with many
    more options included.

36
A Workbook window
  • Title bar
  • Menu bar, tool bars, floating and docking at top
    or bottom of the window.
  • Status bar
  • Worksheet windows

37
Excel Fundamentals
Worksheet Layout
Standard Toolbar
Formatting Toolbar
Cells
C6
Status Bar
B12
6
38
Excel Fundamentals
Worksheet Layout
Formula Bar
  • Worksheet Layout, continued

f
Name Box
Sheet Tabs
7
39
Excel Fundamentals
Excel Capabilities
Use a function to analyze the data ...
AutoSum Tool
Formula Bar
Formula in Cell
28
40
Printing Worksheets
  • Select File, then Print
  • Always a good idea to select Print Preview

Select Printer
Print Selection
Number of Copies
Print Preview
?
7
41
General Options
  • Select Tools, then Options

Set the number of blank worksheets to start with
Manage Recently used file list
Set Standard Font
Set File Location
Set User Name
13
42
Custom Formats
  • Select Format, then Cells
  • Select the Number Tab, then Custom. Type the
    format in the box

Select Custom
11
43
Excel Fundamentals
Entering Data
Enter and edit some data ...
Text is Bold and Centered
27
44
Linking Worksheets, identifying cells across
multiple sheets
Formula
Cell D4
On-Hand Worksheet
3
45
Sorting
  • One of Excels powerful features is its ability
    to sort, while still retaining the relationships
    among information. For example, lets take our
    student grade example from above. What if we
    wanted to sort the grades in descending order?
    First, lets select the information we want to
    sort.

46
Sorting
  • Now lets select the Sort option from the
    Data menu.
  • A new window will appear asking how you would
    like to sort the information. Lets sort it by
    the average grade, which is in Column E be sure
    to set by Descending order. If there were other
    criteria you wished to sort by as secondary
    measures, you could do so lets select Then by
    as Grade 3 just for the practice of doing so
    (Descending order, as well).

47
Sorting
48
Sorting
  • Excel will sort your information with the
    specifications you entered. The results should
    look something like this

49
Dry spreadsheets
  • Spreadsheets full of numbers can be pretty dry,
    so we need some tools to dress them up a little.
  • You can add bells and whistles to not only
    decorate the appearance of your sheets, but also
    improve their and readability.
  • We can use most of the tricks in our word
    processor to do the formatting of text. We can
    use bold face, italics, underline, change the
    color, align (left, right, center), font size,
    font, etc.

50
Design for Looks
  • Here is some (fictitious) data from an authors
    book tour.
  • When unformatted, the table is hard to read and
    understand

15
51
Design for Looks
  • The same data, with some formatting.
  • Give this a try!

16
52
Cell Formatting
  • You may have noticed that, by default, Excel will
    leave as many decimal points as possible within
    the cells width restraints as you increase the
    cells width, the number of decimal points
    increases.

53
  • Select Cells from the Format menu. A new
    window will appear with a wide variety of ways in
    which to customize your spreadsheets.
  • For example, if we wanted to set the percentages
    fixed to only two decimal points, you can make
    this selection under the Number category within
    the Number tab. You can also set the formatting
    for things such as the date, time, currency, etc.
  • The Font tab will also allow you to change the
    default font used on the spreadsheet. The other
    tabs provide even more ways to customize your
    spreadsheet and its appearance experiment with
    the settings to see what works best for you.

54
(No Transcript)
55
A picture is worth a thousand words.
  • People communicate all the time in graphical
    languages.
  • In many cases, you can get a message across more
    quickly with graphs, charts, or maps than with
    words or tables of numbers.
  • Excel allow the users to make information.
    graphically articulate.
  • Not only chart, but you can also add picture, art
    work and so on.

56
Chart Wizard
  • Excel allows you to create basic to
    intermediate charts based of information and data
    within your spreadsheets.
  • Lets create a column chart from the student
    grade data from before. First, highlight the data.

57
  • Next, select Chart from the Insert menu.
  • A new window will appear asking which type of
    chart you would like to create. For this example,
    lets do a basic pie chart. Select Column from
    the Chart Type on the left side, and pick the
    first sub-type on the right (a normal, 2D column
    chart).

58
(No Transcript)
59
  • Click Next. In this window, youll be asked to
    select your data range this is the area of
    your spreadsheet that you wish to generate a
    chart from. Since youve already selected the
    area before, it should already be entered into
    the appropriate area. Series in allows you to
    choose by which value you want to arrange the
    chart. Lets arrange it by rows this will break
    it down by Grade (such as Test 1, Test 2, etc.)
    and comparing the student scores next to each
    other.

60
(No Transcript)
61
  • Click Next. In step three you can give the
    chart a name (Chart Title), label the X and/or
    Y axis, etc.
  • Click Next. The final step will ask whether you
    want the chart as an object in your current
    spreadsheet or in a new one generally, you will
    place it within the same spreadsheet.

62
(No Transcript)
63
(No Transcript)
64
  • Click Finish, and your chart will appear in
    your spreadsheet!

65
Charts
  • The completed chart can be placed to enhance
    presentation, yet remains linked to its data
    source. If the data changes, the chart will too.
    In orther words, if you change values in the data
    source, Excel automatically updates the chart to
    reflect the change.

9
66
  • Pictures
  • Cliparts
  • borders and shading

67
Web Feature
  • Save as a webpage
  • More importantly, it can receive alive data
    online through web services. (Hot!)

68
Web queries
  • Grab dynamic data from the Internet through web
    service support.
  • Can retrieve information from the Internet.
  • Data -gt import data

69
Save as web pages
  • Static
  • Dynamic

70
Orientation preview
  • Orientation
  • Portrait
  • Landscape
  • Preview

71
Some unexpected symbols?
  • Excel use this string of symbols to
    represent a value that is so large that it cannot
    be displayed within the width of the cell. To
    view the value in the cell, you must either
    increase the width of the column or hover your
    mouse pointer over the cell.
  • Something starts with a sign
  • REF! indicates that there is an invalid cell
    reference in the formula

72
Recognizing Errors
  • Excel error messages begin with
  • Common Error Messages
  • Cell isnt wide enough to show the data
  • VALUE! Wrong type of data for a function
  • DIV/0! Tried to divide by zero
  • NAME? Cell name not defined or (usually)
    misspelled
  • REF! Cell reference is not valid
  • NUM! Function requires a number
  • NULL! Called a non-intersecting range of cells

20
73
  • If you enter text or numbers that span further
    than the column allows, simply place your cursor
    on the line dividing two columns next to their
    respective letters, and drag to the right or left
    until the desired width is achieved. You can also
    double-click this dividing line to have Excel
    automatically choose the best width.

74
Conditional function
  • IF(B2gt90,"a", IF(B2gt80,"b", "c"))

75
Conditional Formatting
  • Excel can be set to watch for certain values
    in your spreadsheet
  • It responds to the values by changing the cells
    to a format you specify

9
76
Conditional Formatting, user friendly appearence
  • Apply conditional formatting to Forecast
    Example.xls
  • Highlight cells to be formatted (C3 to O26)
  • Set values between 0 and 5 to be filled with
    red, 6 to 10 yellow, and
  • 11 and above green

12
77
Comments
  • When additional information is necessary in a
    worksheet, a
  • comment can be inserted
  • Select Insert, then choose Comment
  • A cell with comments is marked by a red triangle
    at the upper right corner of the cell

3
78
  • What does a green triangle mean in Excel?
  • Answer Error

79
Windows
  • Ctrl W close current windowCtrl P print
    current documentCtrl F find certain text in
    current document
  • Alt F4 quit current program

80
INTERNET EXPLORER
  • Tab moves selection between address bar and
    links on current page
  • F3 find certain text in current document
  • F5 refresh current page (also Ctrl R)F6
    set text input to address bar (also Ctrl Tab)
  • F11 switch to full-screen mode
  • Ctrl D add current page to favorites list
  • F4 expand address bar downwards to view list of
    recently visited sites

81
EXCEL KEYBOARD SHORTCUTS
  • Ctrl D fill up
  • Ctrl G go to some position
  • Ctrl H find and replace
  • Ctrl R fill right
  • Ctrl 1 (one) format cells
  • F7 spelling and grammar
  • Ctrl K insert a hyperlink

82
  • Renaming worksheet name
  • Adding new worksheet
  • Options customization

83
Goals
  • After completing this lecture you will be able
    to
  • Do math by typing simple formulas to add, divide,
    multiply, and subtract.
  • Use cell references in formulas, so that Excel
    can automatically update results when values
    change or when you copy formulas.
  • Use functions (prewritten formulas) to add up
    values, calculate averages, and find the smallest
    or largest value in a range of values.

84
Excel Exam Study Outline
  • Spreadsheet (Functional part)
  • Charts and Graphs
  • Formatting

85
Spreadsheet
  • Change print alignment
  • Enter text
  • Cell alignment (right, center, left)
  • Indenting text in cells
  • Formatting cells (percent, currency, decimal
    places, font, text wrap, fill, etc.)
  • Merge and Center command
  • Formulas
  • Multiplication, subtraction, division, etc. of
    cells with each other and/or by constants
  • Copying formulas using Relative, Mixed and
    Absolute cell reference
  • Writing complex formulas involving more than one
    calculation.
  • Adjusting column width
  • Using the AutoSum feature
  • Using common functions

86
Charts and Graphs
  • Accenting data in multiple rows or columns.
  • Building a chart using the Chart Wizard
  • Changing appearance
  • Move legend
  • Change colors
  • Change title
  • Change background
  • Change font, etc.
  • Data labels
  • Embedded chart resizing

87
Entering multiple lines of Text within a Cell
  • Altenter

88
End
  • After you try Excel, you'll never go back to a
    calculator, pencils or paper.
Write a Comment
User Comments (0)
About PowerShow.com