Transcript An Introduction to Microsoft Excel

An Introduction to
Microsoft Excel
Excel Workshop
Presented September 10, 2012
3 p.m. Palmer 02
Workshop Outline
•
•
•
•
•
•
•
•
•
•
•
Where Excel truly excels (in terms of what it’s useful for)
How Excel thinks and manages data
Creating data
Using formulas to manipulate data
Excel keystrokes
Basic data Management
Graphing data
If statements
Conditional formatting
Goal seek
Excel efficiency
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
Creating Data
• 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.
Exercise 1
• Download the Excel workshop data from the economics and
business page.
• On sheet 3 generate a new series
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
Using Formulas
• Important formulas
•
•
•
•
•
•
•
•
•
•
•
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.
Exercise 2
• Switch to sheet 1—Accounting data
• Using the Sum function, calculate the following
•
•
•
•
•
Total assets
Total liabilities and stockholder’s equity
Gross margin
Income before taxes
Income after taxes
• Using a formula project sales into 2002 where sales increases
by a rate of 10% each year.
• Use similar rates for other key values in the spreadsheet.
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.
Exercise 3
• Practice highlighting and selecting data using the two
keystrokes just mentioned
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.
Exercise 4
• Practice changing the format style of your cells to general
numbers, and different currency styles
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
Graphing Data
• First Select your graph
• Insert select from the chart section
• Next click select data from the top tab
• Select the cells you want in the chart
• Click ok—your new graph will appear
Exercise 5
• Graph sales between 1999 and 2002
• Make a pie chart of current liabilities in 1999
Editing Your Graphs and Charts
• To add titles and axes labels: click the layout tab chart title
or axis titles
• Add labels as you like
Current Assets 1999
Sales and Net Income in $
Sales and Net Income 19992000
$60,000.00
Cash
Short Term
Investments
Accounts Receivable
$40,000.00
net income
$20,000.00
sales
$0.00
1999
2000
2001
Year
Inventories
other current assets
2002
Property plant and
equipment
Exercise 6
• Add titles to both your graph and chart
• Add axis labels to your line graph
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 7
• 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 9
• 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.
Goal Seek
• You can use excel to find the answer to specific questions. You
can do this by using the goal seek function.
• Goal seek is located under the data tab, in the “What-ifAnalysis”
• You could use a goal seek to determine how long it would take
to save $25,000 with a 2% annual interest rate making
monthly deposits of $500.00.
For more Excel Techniques
• Visit http://www.free-training-tutorial.com for free tutorials in
Microsoft Excel.
THANK YOU FOR YOUR TIME
Questions???
If you have additional questions, please email
[email protected] or visit Palmer 101F.