Transcript AGB 260: Agribusiness Information Technology

AGB 260: Agribusiness
Information Technology
Excel Basics
Useful Chapters in the Textbook
Regarding this Lecture
•
•
•
•
•
•
•
Chapter 1: Introducing Excel
Chapter 2: Entering and Editing Worksheet Data
Chapter 3: Essential Worksheet Operations
Chapter 4: Working with Cells and Ranges
Chapter 10: Introducing Formulas and Functions
Chapter 6: Worksheet Formatting
Chapter 21: Visualizing Data Using Conditional Formatting
Spreadsheet Introduction
• A spreadsheet is an important tool that is used in business for
working with different types of information.
• It is a tool that can be used to analyze and work with data that
can be put into a tabular form.
• It is a very useful tool for examining data in many different ways
including visually.
• Microsoft Excel is currently one of the most used spreadsheet
programs used in business.
Structure and Layout of
Microsoft Excel
• You start with a workbook that contains worksheets.
• Worksheets have two basic layers:
• A data layer
• The data layer is made of rows and columns of cells.
• Rows are denoted by numbers and columns are denoted by letters.
• An objects layer
• The object layer is where charts and other objects are held, e.g., text
boxes, equations from equation editors, graphics from clip art, etc.
• Excel 2013 has a Quick Access toolbar where you can place
commonly used items.
• It has ribbons that contain commonly used items in Excel.
• Excel has a cell reference area that tells you which cell is
active.
Structure and Layout of Microsoft
Excel Cont.
• Excel has a formula bar that you can enter text, custom
formulas, and Excel predefined functions.
• This formula bar shows you what is actually in the cell of a
worksheet.
• Example of a formula:
• =B1+C1
• Example of a function:
• =Sum(B1,C1)
• Note that you can enter a formula into a cell directly rather than
using the formula bar.
• At the very bottom of Excel, you should notice the zoom
feature and the different page views.
• Also at the bottom, you can customize the status bar to show you
useful information.
Notes on Rows and Columns
• Row heights can be changed.
• You can do this by resizing it with your mouse or you can right
click on the row and select Row Height
• Column widths can be changed.
• You can do this by resizing it with your mouse or you can right
click on the column and select Column Width.
• Rows and columns can be hidden.
• You can do this by right clicking your mouse while a row/column
is selected and choosing Hide.
• Rows and columns can be unhidden.
• How do you think you can do this?
Notes on Rows and Columns
• You can insert a row or a column and you can delete a row or
a column.
• How do you think you can do these tasks?
• A single cell can be added to or deleted from a row or
column.
• A cell can be added or deleted by right clicking the cell and
choosing Insert or Delete.
• When you add or delete a cell, Excel will usually ask you what you
want to do.
Excel Ribbons
• Excel typically has the following ribbons:
•
•
•
•
•
•
•
Home
Insert
Page Layout
Formulas
Data
Review
View
• Excel ribbons are customizable and some ribbons will appear
in certain context, e.g., working with Tables or Charts.
Cells in a Worksheet
• A cell is the fundamental component in a worksheet.
• It can have the following entered into it:
•
•
•
•
Text
Numbers
Formulas
Functions
• A cell can have formatting related to it, a comment related to
it, and a name associated with it.
• Cells are used for storage of information/data and
calculations.
• A cell is typically considered active when it has a black box
around it and the formula bar has the information shown that
is in the cell.
Important Aspects to Know
About Excel
• Excel has many shortcut keys that can save you time.
• You may want to visit: http://office.microsoft.com/en-us/excelhelp/keyboard-shortcuts-in-excel-HA102772370.aspx
• The Help icon located in the upper right corner of the program
that has a question mark (Shortcut Key: F1).
• Excel is context specific.
• When you right click your mouse, it will bring up many useful
options that you can do.
• Since Excel is such a widely used tool in business, there are
many different sites that you can go to for helpful information.
• One of the best ways to learn Excel is by experimentation.
Initial Useful Shortcut Keys
• Generally Helpful:
• Alt
• Moving Around:
•
•
•
•
•
•
•
•
•
Page Down
Page Up
Home
CTRL+[Right Arrow]
CTRL+[Left Arrow]
CTRL+[Up Arrow]
CTRL+[Down Arrow]
CTRL+[Page Down]
CTRL+[Page Up]
• What happens when you use the Shift key with the commands above?
• What happens when you replace the CTRL key with the Shift key?
Initial Useful Shortcut Keys
Cont.
• Simple Tasks:
•
•
•
•
•
•
•
•
•
•
•
CTRL+C
CTRL+V
CTRL+X
CTRL+1
CTRL+A
CTRL+B
CTRL+F
CTRL+G
CTRL+I
CTRL+N
CTRL+O
•
•
•
•
•
•
•
•
•
•
•
CTRL+P
CTRL+R
CTRL+S
CTRL+T
CTRL+U
CTRL+Z
Alt+Shift+F1
F2
Alt+F4
F9
Esc
In-Class Activity: Demonstration of Putting a Formula
into a Cell and Renaming a Worksheet
• In cell A1 type the following and press enter:
• =11*11
• What did you get?
• In cell A2 type the following and press enter:
• =12*11
• What did you get?
• In cell B1 type the following and press enter:
• =11*12
• What did you get?
• In cell B2 type the following and press enter:
• =12*12
• What did you get?
• On the tab of the bottom left, right click on the phrase Sheet1.
• Click on Rename and type Demo 1 then press enter.
• Right click on Sheet2 and drag it left until it is before Demo 1.
• What should you do if Sheet2 does not exist?
• Rename Sheet2 to be Demo 2.
Absolute Vs. Relative
References
• An absolute reference means that you always point back to a
particular cell, column, or row.
• The way an absolute reference is made is by putting a $
symbol in front of either the letter and/or the number of a cell
reference, e.g., =$A$1; this is an absolute reference to a single
cell.
• Copy this formula into cells A5, B5, A6, and B6 in the Demo 1
worksheet using CTRL+V for pasting.
• There are at least three ways to copy the cell and at least three ways
to paste the cell, what are they?
• What happens when you drop $ in front of the A above, i.e.,
=A$1, and copy and paste it to A8, B8, A9, and B9 in the Demo 1
worksheet?
• This is a mixed relative absolute reference holding the row constant
and letting the column designation change when pasting.
Absolute Vs. Relative
References Cont.
• What happens when you drop $ in front of the 1 above, i.e., =$A1,
and copy and paste it to A11, B11, A12, and B12 in the Demo 1
worksheet?
• This is a mixed relative absolute reference holding the column constant
and letting the row designation change when pasting.
• What happens when you drop both $ symbols in front of the A and
the 1 above, i.e., =A1, and copy and paste it to A14, B14, A15, and
B15 in the Demo 1 worksheet?
• This is a relative reference in terms of both the rows and the columns.
• Quick note on absolute and relative references:
• When you click on the formula in the formula bar and then press F4,
you can toggle through the different types of relative and absolute
references.
In-Class Activity:
Demonstration of Auto Fill
• Type a 1 into cell A2 of Demo 2 worksheet then type a 2 in cell
A3.
• Using the mouse, highlight these two cells by left clicking on
cell A2 and holding down the left button on the mouse and
dragging your pointer to A3.
• Let the left mouse click button up.
• Move your mouse pointer down to the lower right of the
highlighted two cells until it turns into a black plus sign.
• Left click on the plus sign and drag your mouse down until you
get an 8 in the cell A9.
• Now do the same thing starting in B1, but instead move across
until you have a number 8 in cell I1.
Excel Note
• Auto fill can do different types of series, for example:
•
•
•
•
•
•
Even Numbers: 2, 4, 6, 8 …
Numbers by 5: 5, 10, 15, …
Calendar Days: Monday, Tuesday, Wednesday, …
Quarters: Qtr 1, Qtr 2, …
Months: January, February, March, …
You would be surprised the different types of series you can do.
• On the Home ribbon there is a Fill button. It is recommended that
you play around with it to see what it can do.
In-Class Activity: Absolute vs
Relative Reference
• Highlight cells A1 through I9, and copy those cells to A11, A21, A31, A41, A51,
A61, A71, A81, A91, A101, A111, A121, A131, A141, A151.
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
In cell B2 type: =A2+B1 and copy that from B2 to I9.
In cell B12 type: =$A12+B11 and copy that from B12 to I19.
In cell B22 type: =A$22+B21 and copy that from B22 to I29.
In cell B32 type: =A32+$B31 and copy that from B32 to I39.
In cell B42 type: =A42+B$41 and copy that from B42 to I49.
In cell B52 type: =$A$52+B51 and copy that from B52 to I59.
In cell B62 type: =$A62+$B61 and copy that from B62 to I69.
In cell B72 type: =$A72+B$71 and copy that from B72 to I79.
In cell B82 type: =A$82+$B81 and copy that from B82 to I89.
In cell B92 type: =A$92+B$91 and copy that from B92 to I99.
In cell B102 type: =A102+$B$101 and copy that from B102 to I109.
In cell B112 type: =$A$112+$B111 and copy that from B112 to I119.
In cell B122 type: =$A$122+B$121 and copy that from B122 to I129.
In cell B132 type: =$A132+$B$131 and copy that from B132 to I139.
In cell B142 type: =A$142+$B$141 and copy that from B142 to I149.
In cell B152 type: =$A$152+$B$151 and copy that from B152 to I159.
Which of these is/are your standard table of addition?
In-Class Activity: Copying,
Pasting, and Moving Data
• Rename Sheet3 to Demo 3.
• While in the Demo 2 worksheet and when your A1 cell is
active, press CTRL+A (what happened?), press CTRL+A again
(what happened?), and then CTRL+C.
• What do you think would have happened if you were not in a cell
that had any data in it when you pressed CTRL+A?
• What do you think happens when you click on the triangle in the upper
left corner of the worksheet that is left to the A column and above row 1?
• Next go to cell A1 in your Demo 3 worksheet and press
CTRL+V.
• What happened (look carefully at the formulas)?
• What do you think would have happened if you were not in cell
A1?
In-Class Activity: Copying,
Pasting, and Moving Data Cont.
• In the Demo 3 worksheet, go to cell A41.
• Select all the data that is below that row.
• Note that the cells will be indicated as selected when they are
encompassed in a black outlined box where the cells are blue
inside the box.
• There are several ways you can do it, what are they?
• Using your mouse, go to the upper end of the outlined box
until your cursor changes to show
.
• When you right click your mouse, you are able to drag the
highlighted box to some other part of the worksheet.
• In this case drag the information to cell K1.
In-Class Activity: Copying,
Pasting, and Moving Data Cont.
• In the Demo 3 worksheet, go to cell K41.
• Select all the data that is below that row.
• Press CTRL+X, go to cell U1, and press CTRL+V.
• What happened to the formulas in the cells?
• In the Demo 3 worksheet, go to cell U41.
• Select all the data that is below that row.
• Press CTRL+X or CTRL+C, go to cell AE1, and click on the Paste
button on the Home ribbon.
• What happened to the formulas in the cells?
• Add a new worksheet known as Demo 4.
• This can be done a couple different ways; what are they?
In-Class Activity: Copying,
Pasting, and Moving Data Cont.
• Copy all the cells in Demo 3.
• In Demo 4 worksheet make cell A1 the active cell.
• Go to the Home ribbon and click on the triangle below the
word paste on the paste button.
• Notice that several options come up for you to choose.
• Choose the first icon under the paste values.
• What happened?
Formatting Cells in Excel
• There are two main ways you can format a cell in Excel:
• You can use the preprogrammed buttons that are on the Home
ribbon that are categorized by Font, Alignment, and Cells.
• Notice that there is a small box in the lower right-hand corner of
these categorizes.
• These boxes will take you to the dialog box for other things that you can
do to the cell.
• The other way to format a cell is to use the formatting dialog box
which you can get by right clicking your mouse on the cell and
choosing the Format Cells option or you can use CTRL+1 to get
the dialog box.
Formatting Cells in Excel Cont.
• The Format Cells Dialog box allows you to format the cell
based on:
• Number: This tab allows you to format the cell depending on
what is in the cell, e.g., Date, Time, Text, a number in scientific
notation, a number with decimal places, etc.
• Alignment: This tab allows you to format the cell based on how
the information will show up in the cell.
• This allows you to control the orientation of the information in the
cell, the alignment, how much information will fit in a cell, etc.
• Font: This tab allows you to control the color of the information in
the cell, the style and size of the information in the cell, and
certain effects, etc.
Formatting Cells in Excel Cont.
• Border: This tab allows you to control what goes around the
information in the cell.
• You can control the look of the line that goes around the cell (e.g.,
dotted, solid, thick, etc.), the color of the lines that outline the cell, etc.
• You can highlight multiple cells and format them at a single time in
unison.
• Fill: This tab allows you to control the background of the cell
including fill effects, pattern styles, etc.
• One of the best ways of learning about what any formatting option
does is to play around with it.
• A useful tool when dealing with formatting is the Format Painter on
the Home ribbon.
• This tool allows you to take a format that is done in one cell and copy
it to another cell or group of cells with just a click of the mouse.
In-Class Activity: Formatting
• In the Demo 4 worksheet, attempt the following utilizing the
given formatting style for a set of numbers, e.g., cells A1 to
cells I9:
• Make all the numbers in one group bold and italicized.
• What shortcut keys allow you to do this quickly?
• Make all the numbers in one group red and 14 point Times New
Roman Font.
• Make all the cells in one group have an orange background with a
1 .
6.25% gray pattern style, e.g.
• Make all the numbers in one group centered and read from top to
bottom, e.g., a cell with a number like 11 should look like 11 .
• Make all the numbers in one group formatted in currency with
the decimal to three significant digits, e.g., $44.000 .
• You should notice the increase and decrease decimal buttons on the
Home ribbon in the number section.
Useful Aspects of the View
Ribbon
• What happens when you uncheck the Formula Bar?
• What happens when you uncheck Gridlines?
• What happens when you uncheck Headings?
• Why do you think these options would be useful to you?
•
•
•
•
What happens when you Freeze Top Row?
What happens when you Freeze First Column?
What happens when you Freeze Panes?
What happens when you Split the screen?
• What is the difference between Split and Freeze panes?
Conditional Formatting
• Excel allows you to format a cell or group of cells based on
whether a certain condition or sets of conditions are met.
• The conditional formatting button is available on the Home
ribbon.
• Conditional formatting can highlight important aspects of your
data very quickly, e.g.:
•
•
•
•
It can show you what numbers are between a certain range.
It can show you the top ten numbers in a group of numbers.
It can highlight values that are duplicates.
It can give you a relative picture of the numbers in terms of other
numbers in the group.
• Like regular formatting, the best way to learn conditional
formatting is to play around with it.
In-Class Activity: Conditional
Formatting
• In the Demo 4 worksheet, attempt the following utilizing the
given formatting style for a set of numbers:
• For cells L12 to S19, use conditional formatting to highlight the
cells where the numbers are duplicates. Use the suggested
formatting for now even though you have the ability to change it.
• For cells L22 to S29, use conditional formatting to highlight the
cells that have numbers between 4 and 11. Use the suggested
formatting for now even though you have the ability to change it.
• For cells V2 to AC16, use conditional formatting to highlight the
cells using Color Scales. Use the first one for now even though
you could choose any of them.
Naming Cells
• Excel has the ability to give a cell a name rather than just cell
designation, e.g., A1.
• This can be a very handy tool when you are building formulas.
• There are a few ways you can name a cell.
• One way is to click on the Name Box that gives the cell
designation, and type in a name with no spaces.
• Another way is to go to the Formulas ribbon and click on Define
Name.
• The advantage of naming a cell in this manner is that you can define
the scope of the name, i.e., is the name valid for just the particular
worksheet or does it work for the whole workbook.
• A third way is to click on Name Manager.
• The advantage of Name Manager is that it can show you all the
named cells in the workbook.
In-Class Activity: Naming a Cell
• In the Demo 4 tab where the conditional formatting was used
to see if there was any duplicates, name the smallest non
duplicate cell LowNonDup.
• In the Demo 4 tab where the conditional formatting was used
to see if there was any duplicates, name the largest non
duplicate cell HighNonDup.
• In cell A42 type: =LowNonDup.
• What do you get?
• In cell A43 type: =HighNonDup.
• What do you get?
• Copy and paste cells A42 and A43 across to F42 and F43.
• What do you get?
• A range name is like what in terms of relative and absolute
references?
One Final Note
• Under the File ribbon is Options.
• This allows you to change aspects regarding how Excel operates.
• Typically you will not need to change anything here.
• Under the Formulas option you want to make sure that your
workbook calculations are set to automatic.
• Every once in awhile this setting gets changed and can cause you
problems.
• The other option that you may have interest in is under the Save
option.
• Usually you want to have the Save AutoRecover checked so Excel
automatically saves a back-up of your work as you are working on it.