Presentation Transcript

1. Excel Basics Tutorial

   Lauren Ottaviano Fall 2012
2. Rows and Columns Rows: horizontal lines on the grid Numbered, total of 220 = 1,048,576 Columns: vertical lines on the grid Lettered from A . . . Z, AA, AB, . . . To ZZ, AAA to XFD Total of 214 = 16,384 columns.
3. Row & Column Operations Insert, Delete, Cut, Copy, Paste Move cursor over to row or column heading (number or letter on the border of the spreadsheet) Cursor turns into an arrow Right click to see menu Also Hide or Unhide rows/columns
4. Resizing a row or column Move cursor over to edge of column or row label (the number or letter) Cursor turns into an “adjustor” Click and drag to resize
5. Insert & Delete with Cells When you insert or delete a cell, you need to state how the other cells should be rearranged. To simply empty the contents, press the delete or backspace button
6. Multiple Worksheets Multiple “worksheets” are associated with a single file or “workbook” Click on tab at bottom to move from one to another. –far right tab inserts a new sheet.
7. Multiple Worksheets Right click on sheet name to move, copy, etc. If you select more than one worksheet & then edit some cells, the same edits will appear on all of the selected sheets
8. Cursor Movements Ctrl + arrow Move to the next or last area with content Shift + arrow Select this cell and cell in the next direction Further use of arrows: expand size of selected rectangle. Shift + ctrl + arrow Select all cells/rows/columns until next cell or last cell with content
9. Cursor Movements From bottom right: shift + ctrl + up – select last column of data Shift + ctrl + left – select remaining columns of data
10. Cursor Movements Move cursor to edge of selection and it becomes arrows in four directions Drag to move entire selection
11. Cell Properties Right-click on cell or group of cells to see drop-down menu. Click on “Format Cells” to adjust cell properties
12. Formatting Cells Number tab: determine the data type Number Currency Date Percentage Text Custom Each with its own options
13. Formatting Cells Number options in toolbar Change number of decimal places Presence of comma Dollar or other currency Convert data type
14. Date Formats Excel has multiple options for presenting dates Dates are stored as numbers Allows for addition, subtraction of days Date number 1 is 1/1/1900. Earlier dates are allowed, but some problems arise with algebra
15. Formatting Cells Other tabs allow for changing text alignment, font, borders, fill, & rights for changing the cell.
16. Merging Cells Cells can be joined horizontally, vertically, or both into larger rectangles Mainly for creating titles
17. Wrapping text Click the wrap text button to show all of the text on multiple lines (resize to fit)
18. Freezing Panes For large spreadsheets, the header row or column can be frozen so that it always displays when scrolling.
19. Paste Special Option when right-clicking a cell or can be dragged and selected from the paste button. Can paste formulas, values (what the formulas equal), formats (font, etc.), transpose of cell
20. Sorting Data Can sort data in ascending or descending order by any variable. Can also do nested sorting First by column A then by column B. Excel will use column names if you ask. Go to data tab  sort for dialogue box with sorting options
21. Sorting Data Can also sort by cell or font color Changed with buttons in the home menu.
22. Pasting Text Tab delimited text pastes directly into Excel as different columns. For other text (comma-delimited, other delimiters, or block text), first paste, then select your text and click on “text to columns” in the Data tab
23. Text to Columns After clicking text to columns, a dialogue box opens with a variety of options. This same box opens when you open a text file directly in Excel
24. Text to Columns If you pick “delimited,” you can select the delimiter (i.e., what indicates the start of the next column) Tab, comma, semicolon, etc. If you pick “fixed width,” then you select the places where each old column ends and new one begins
25. Formulas To calculate a value based upon values in other cells, begin the entry with = Refer to other cells by their locations (C3, B3, etc.) Formula appears in the formula bar above the column heading. Cells are highlighted when selected
26. Expanding formulas Move your cursor to the lower-right of the cell, and it turns into a plus sign. Drag in any direction to fill in the same formula in those additional cells Double click to repeat formula for all values until next blank space in column
27. Expanding Formulas By default, the locations of cells change as you drag or paste. As you move from B3 to E7, = C17/B12 would change to = F21/E16 To keep a cell, column, or row constant, use a dollar sign before the number or letter in the formula. As you move from B3 to E7, = C$17/$B12 would change to = F$17/$B16
28. Formulas Arithmetic Operators +, -, *, /, ^ (plus, minus, times, divide, exponent) Parentheses (for order of operations) Mathematical Functions SQRT, LN, LOG, EXP, MOD ROUNDDOWN, ROUNDUP, ROUND
29. Formulas Logical Operators IF(condition,value if true,value if false) uses >, >=, =, <=, < AND(condition a, condition b) or & OR(condition a, condition b) nested ifs: IF(condition a ,IF(condition b,0,1),1)
30. Formulas Statistical Functions SUM(A1,A2,B1,B2) or SUM(A1:B2) SUMIF(A1:A13, "<=20") –sum of all elements ≤ 20 AVERAGE, MIN, MAX, MEDIAN, STDEV, COUNT SUMPRODUCT(A1:A13,B1:B13) COVAR(A1:A13,B1:B13) NORMDIST(x,mean,sd,cumulative) NORMINV(probability,mean,sd) other distributions as well (F, t, beta, binomial, etc.) RAND() –random number between zero and one Use norminv to make it random normal
31. Formulas Text functions UPPER, LOWER, PROPER –change case LEN –length (number of characters) SUBSTITUTE(text, string to remove, string to replace it with) FIND(text to find, text to search) finds position of first instance of a string in text MID(text, starting position, number of characters) picks out a portion of the text
32. Formulas Cell positions ADDRESS(1,2) = $B$1 useful if you need the 5th row from the 36th column, or if the location depends on another formula. COLUMN(B1) is 2 (because 2 is the second column) ROW(B36) is 36.
33. Formulas INDIRECT(cell with location of other cell in it) Here, INDIRECT(B7) gives 99.495
34. Formulas VLOOKUP(value in leftmost column, table of data, column from data to return) Note: data must be sorted by first column.
35. Formulas HLOOKUP(value in top row, table of data, row from data to return) VLOOKUP and HLOOKUP are useful for values such as “ID of top earner,” Note: data must be sorted from left to right
36. Formulas Financial functions PMT(interest rate, number of periods, present value) –the number of payments for a loan NPV(interest rate, payment after 1 year, payment after 2 years, . . . , payment after n years) NPER(interest rate, payment per period, present value of sum being paid off) IRR(cash flow in year 0, cash flow in year 1, . . . , cash flow in year n, initial guess for irr)
37. Naming Values or Ranges Useful for keeping track of items in your spreadsheet. –right click, then select name a range to get a dialogue box
38. Naming Values or Ranges Cell or group of cells can now be referred to by name in formulas.