Transcript Introduction to Excel

Introduction to Excel
EC 151 Principles of Microeconomics
Block 3, 2012-2013
Tutorial Outline
•
•
•
•
•
•
•
•
•
How to use Excel
Inputting Data
Basic Formulas
Built in functions
Basic Graphing
XY plots
Creating Supply and Demand Tables
Graphing Supply and Demand
Labeling and formatting plots
Uses of Excel
•
•
•
•
•
•
Budget data
Accounting data
Graphing
Organizing information
Finding information
Creating data
DATA Management
• Tab menus
• Formula Bar
• Cell formats
• Editing Cells
• Inserting and deleting rows or columns
Inputting Data
1. Keyboard Entry
2. Import from text file
3. Importing from the internet
Keyboard Entry
• Open a new workbook by going to fileNewblank
workbook
• Type in the new data
• Save workbook using the File Menu
• Copy and Paste from other Excel Sheets
• Copy (ctrl C)
• Paste (ctrl V)
Import Text File
• Open Excel
• Click on FileOpenOn the drop down menu select “Text
Files” open the text file.
Open the Micro Data
• Open File “Micro Data”
• Go to the Colorado College
Economics and Business
webpage.
• Go to resources for
students
• Click on Labs and Tutorials
• Download and Open “Micro
Data”
• You should have a data set
similar to the one at the
right.
Reference Cells
• Reference Cells
• A cell reference refers to a cell or a range of cells on a worksheet
and can be used in a formula so that Excel can find the values or
data that you want that formula to calculate
• The importance of “=“ in formulas—necessary to direct excel to
find your data
• Absolute reference cells ($)
• Basic data and reporting of statistics
• Formulas
Basic Formulas
• Addition
• You can add across cells, columns, or rows by using an addition
formula
• Subtraction
• You can subtract between cells, columns, or rows by using a
subtraction formula
• Multiplication
• You can multiply across cells, columns, or rows by using a
multiplication formula
• Division
• You can divide among cells, rows, columns by using a division symbol
Built in Functions
•
•
•
•
•
•
•
•
•
•
•
AVERAGE—gives the average of a set of reference cells
ABS—gives the absolute values of cells
LCM—returns the least common multiple of a number
STDEV(P)A-returns the standard deviation of sample or entire
population
LOG-returns the log of a value
MEDIAN—returns the median of a sample
SUM—returns the sum of a sample
COUNT(A)—counts the number of cells that have numbers/not
empty
NPV—returns the net present value given a rate, and original value
IF—checks whether a condition is met and returns a value based on
the test.
PMT—calculates the payment for a loan based on constant payments
and an interest rate.
Excel Keystrokes
• Shortcut keys work in excel as well as word
• Using the keystrokes will save you a lot of time
• Use the following two shortcuts while working in excel
• Ctrl+Shift+Arrow==allows you to select all the data in a row,
column or matrix
• Ctrl+Arrow==allows you to move to the end of a row or column.
Basic Graphing
• To make a basic bar graph
• Select your data (highlight column A)
• Click on Insert
• Click on Column
XY Plots
• Click on Insert
• Select Scatter
• You should now have a blank Plot. Up on the toolbar, click
“Select Data”
• In the “chart data range” select Column A
XY Plots Continued
• Click on Price-then “edit”
• In the Edit Series Window, Select Column B as the “Series X
values”
• Click ok
Formatting you Plots
• To Insert a line of best fit, go up to “Chart Tools”
• Under design, select the chart with a linear fit
Labeling Axes
• Under Chart Tools select “layout”
• Click on “Chart Title” and Label your graph
• Next Click on “Axis Titles” and edit your axes
Generating Supply and
Demand Tables
• Open a new worksheet (click sheet2 on the bottom left
corner)
• Type “price” into cell A2 and “Qd” in cell B2
• Generate the price series ranging from 0 to 20 by increments
of 1. Rather than type each number in manually, use Excel’s
automatic fill function. Type “0” and “1” into rows two and
three. Highlight the cells and then drag the bottom right
corner down to row 22.
Generate the Qd Column
• The demand function we’ll use
today is Qd=20-P
• In cell B2 write the formula “=20A2”
• Hit enter
• Click on the cell B2, and double
click on the black square in the
lower right corner.
• This will autofill the remainder of
the demand column.
Generate a Qs Column
• Using the Quantity
Supply Function Qs=10+2P generate a
column of quantity
supply
• Can you find the
equilibrium price and
quantity?
Graphing Supply and Demand
• Scatter plot of Supply and Demand
• Highlight Columns A-C
• Click InsertScatterselect a scatter plot with lines
Formatting your Plot
• Under Chart tools, select Layout
• Click on Axis Titles—select the title type you prefer and label
your graph
• Click on Axis Titles—Label your vertical axis “price” and the
horizontal axis “quantity”
Questions?
Thank you for your time. Additional questions may also be emailed to
[email protected]
This presentation along with the data are available in the student
resource section of the Economics and Business webpage.