An Introduction to Microsoft Excel

Presented to EC 303 Research Methods Block 7 March 25, 2013 1:30-2:30 p.m. Palmer 02

Workshop Outline

• • • • • • • • • • • • • • Uses of Excel How to use Excel Basic Data Management Inputting Data Basic Formulas Built in functions Basic Graphing XY plots Creating Supply and Demand Tables Graphing Supply and Demand Labeling and formatting plots Graphing data If statements Conditional formatting

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.

2.

3.

Keyboard Entry Import from text file Importing from the internet

Keyboard Entry--Creating Data

• • • Open a new workbook by going to file  New  blank workbook Type in the new data Save workbook using the File Menu • Creating a series • Type a value into cell A1 (no parentheses) • • Type another value into cell A2 Select both cells and click and drag • • Dragging the bottom right corner down, will create your series following the pattern you created in cells 1 and 2. You can use this technique to create a time series. • 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 Methods Data

• Open File “Methods Data” • Go to the Colorado College Economics and Business webpage. • Go to resources for students • • Click on Labs and Tutorials Download and Open “Methods 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

Basic Data Management

• • • • Sorting data Formatting cells Moving data across sheets Moving Data • Paste Special • Transpose • Values and Formulas • Find and Replace

Sorting and Formatting Cells

• Sorting Data • Formatting Cells—click on the arrow under format  go to the bottom option, the format your cells for decimals, dollar signs, and other preferences.

Moving Data and Pasting

• You can copy and paste data into a new spreadsheet by moving over to a new sheet. • • Pasting Special • Values—keeps the values and not the formulas you used to get the value • Transpose—changes the layout of your data (columns and rows switch) • Formulas—keeps your formulas in place, not the values Find and Replace • Use to change your data quickly • Ex. Ctrl+F  click replace, then fill in your data

Cleaning up your data

• • • • Not all data is in ready to analyze condition when you get it— the next few slides will present a few ways to clean up your data • Text to columns Using “If” statements Concatenate Conditional Formatting

Exercise

• • Switch to sheet 2—BLS Data This data set contains the average price data for a pound of bacon and a pound of bananas for all US cities, Northeast Urban areas, Midwest Urban areas, and South Urban areas.

• • Notice that the data is not in a very friendly manner in terms of recognizing the regions and substance. The next exercises will show you how to format this data.

Text-to-Columns

• • Notice that column A—the data for region and commodity is coded in a moderately confusing way. We will use text to columns to recode these two categories. • • • Step 1—add additional columns to the right of the column you are expanding (this will ensure you don’t overwrite your cells) Step 2—select the data you want to split (in this case A11:A18) Step 3—select the text to column button under the data tab

Text-to-Columns, cont

• • In the pop up window, select “Fixed Width” Next, select the areas where you want to break the data into separate columns.

• In this case we want to separate the code APU from the regional codes (000,0100,0200,0300) and from the commodity code (704111, 711211) • • Hit next, then finish. Your data will fill the two open columns you had created. Click where you want the break

Exercise 8

• Follow the text to column steps to separate the commodity and regional codes. • You should have something like this…

Using “If” Statements

• • We can use “if” statements to further clean up this data set. The syntax of the “if” statement is • If([logical test], [value if TRUE], [value if FALSE]) • • Logical Test—This is the test you want excel to use to determine the output (use cell references). An example would be “B4=25” Value if True—This is the output value that excel will place in the cell if the logical test is true • Value if False—This is the output value that excel will place in the cell if the logical test is not true.

Recoding Using IF

• • • • To recode using If, insert two new columns between the commodity codes and data Next, use your reference cells from above to make your if statements • Remember to use absolute cell references ($G$3) Click and drag down your formula You can have multiple if statements in one line. Separate these by a comma. • =IF(B11=0,$G$3,IF(B11=100,$G$4, IF(B11=200, $G$5, IF(B11=300, $G$6, 0))))

Exercise

• Recode the data for commodities using the if statement

Combining Cells--Concatonate

• • • After recoding cells you can recombine them using Concatonate First make a new column between the commodity name and the data. Next, use the formula concatonate • Syntax “=concatonate(cell 1, cell2,…)

Exercise--Transpose

• • Finally, if we want our data in columns rather than rows we should copy and paste special into a new sheet. Use the shortcut keystrokes to copy your new data, paste special with values and transpose into sheet 4.

Conditional Formatting

• • You may wish to use conditional formatting at some point during your research. Conditional formatting can be found under the home tab, styles tab. • • You can use conditional highlighting to draw attention to specific cells. Select the data you are interested in, then click on conditional formatting, next select the highlighting criteria that fits your needs.

For more Excel Techniques

• Visit http://www.free-training-tutorial.com

Microsoft Excel. for free tutorials in

THANK YOU FOR YOUR TIME Questions???

If you have additional questions, please email [email protected]

or visit Palmer 101F.