Skills checklist for Excel by user002



and knowledge

Created by Debbie Wolfe Technology Training Editor 1-800-333-7505, 2919 St. Petersburg Times, St. Petersburg, Fla. USA Web: FREE tip sheets, TECH TIPS archive and consulting info

Skills checklist for Excel

revised March 2004

launching the application (software) from a desktop icon and via "Start Programs" creating a new file via File New terminology (10 new terms for Excel): 1. workbook 6. row/record Windows review: 2. worksheet 7. column/field 1. menu bar 3. cell 8. standard toolbar 2. title bar 4. cell address 9. formatting toolbar 3. vertical scrollbar 5. active cell 10. formula bar 4. horizontal scrollbar Nerd notes: total number of columns in a worksheet total number of rows in a worksheet total number of characters per cell total number of cells in a worksheet size of one entire worksheet, if printed data entry techniques based on a small data set:
Example: Reporter Salaries (fictitious data) A Last name Frakes Smith Smith Appleton Davies Epps Williams Williams Benton VanHorst TOTAL MEAN MEDIAN B First name Michelle David Dominique Delores Michael Stephen Barbara Chris Sally William C Beat City Council City Council City Council County County GA GA GA Police Police D Level Reporter 2 Reporter 3 Reporter 2 Reporter 2 Reporter 2 Reporter 3 Reporter 1 Reporter 3 Reporter 1 Reporter 1 E 1998 salary 28,450.70 29,730.40 27,930.00 27,485.20 27,300.40 29,854.30 23,267.60 31,385.00 24,670.90 26,670.40 F 1999 salary 31,680.40 32,300.60 30,250.00 29,732.80 28,620.00 32,648.20 24,840.30 32,790.50 27,360.00 28,360.50 G % change H 1999 % of total

= 256 = 65,536 = 32,000 = 16,777,216 = approx. 21 feet wide by 1,365 feet long

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

1. 2. 3. 4. 5.

using the mouse to move the character insertion mark for data entry Tab key to "lock data" and move across the worksheet Enter key to "lock data" and move down the worksheet naming and saving a workbook for the first time via File Save as after the first time you save a workbook, save changes to permanent memory via File Save or Ctrl s or by clicking the disk icon on the standard toolbar 6. data entry in teams of two 7. data entry form via Data Form

data formatting techniques: 1. click on a row number to highlight (define) a whole row to make formatting changes: • make all column headers bold (click on the "B" button on the formatting toolbar) • make the column headers wrap as shown Format Cells Alignment … click in the "Wrap text" box • make the salary amounts look like dollars ... Format Cells Number Currency ... make sure the "$" symbol and two decimal places are selected 2. change the number of decimal places by using the icons on the formatting toolbar 3. resize the width of a column by clicking and dragging on the vertical bar between column letters 4. AutoFit the width of a column by double-clicking the vertical bar between column letters 5. AutoFit the width of a column by highlighting the column (click on the letter) then, Format Column AutoFit 6. highlight blocks of cells to make formatting changes: • define cells next to one another (contiguous) -- hint: use the Shift key • define cells not next to one another (noncontiguous) -- hint: use the Ctrl key • highlight the blocks of cells you will create then shade their backgrounds light gray to differentiate them from data you have obtained from public officials -- hint: use the drop-down arrow on the paint can icon on the formatting toolbar to locate a color pallet (gray works on color and black and white printers) 7. change the font style of the column headers (pick a condensed font) data design and manipulation techniques: 1. insert a row by highlighting a row and then Insert Row 2. insert multiple rows 3. insert a column by highlighting a column and then Insert Column 4. insert multiple columns 5. delete a row by highlighting and Edit Delete 6. delete a column by highlighting and Edit Delete 7. delete the contents of a cell by highlighting and using the Del key 8. define the whole data area: start in the upper left corner of the worksheet (Ctrl Home), then, click in the cell in the upper left corner of the data area, then, Ctrl down-arrow, Ctrl right-arrow 9. define the whole worksheet using the "secret" button (good for use only with Format Column AutoFit) 10. Hide and unhide a column or columns using the mouse method 11. Hide and unhide a column or columns using the menu bar method -- Unhide hint: define the column letters on either side of the hidden area 12. Hide and unhide a row or rows using the mouse method 13. Hide and unhide a row or rows using the menu bar method 14. undo 15. redo 16. zoom 17. large icons


data calculation -- the basics: 1. AutoSum (total), Tab or Enter to lock in the formula 2. viewing the formula behind the number -- all formula's start with an "=" sign 3. using function wizard to do a mean 4. adjusting a cell range within a formula 5. using function wizard to do a median 6. why is it good to calculate both the mean and median to determine the average? 7. entering in your own custom formula part one: percent change (difference) -- formula hint: (new-old)/old then click on the icon on the formatting toolbar to multiply the decimal value by 100 8. copy the percent change formula down the column using the "special" fill handle technique -- no dragging! 9. entering in your own custom formula part two: percent of total 1999 -hint: you'll need to use the "$" symbol to anchor the appropriate cell address of the total value (think of the anchor as an electronic thumb tack) -- Potential trap: What happens if you omit the anchors in the cell address? formula hint: divide each % change value by the total … think of this as a mathematical way to determine how big each slice of pie is in relation to the whole 10. increasing and decreasing decimal places via the formatting toolbar or from the menu bar via Format Cells (number/currency/percentage) simple sort (alphabetize the last names) via Data Sort ascending vs. descending with characters and numbers sort the salary amounts from high to low multiple sorts (alphabetize "Beat" A to Z, then "Last name" A to Z, then "First name" A to Z) simple filter via Data Filter the only sure fire way to turn off a filter and be 100 percent certain you are working with all your data and not a subset -- Tip: Turn the filter process off and start over each time to make sure you are always analyzing all of your data! custom filter via Data Filter, drop-down arrow in selected column, choose custom custom filter wildcard searches -- filter so only those records associated with "City Council" are visible -- review of mathematical operators as Excel understands them! (=, >, <, >=, <=, <>) filter so only those records associated with "City Council" and "Police" are visible custom filter "and" vs. "or" wildcard use in custom filters copying and pasting a filtered data set onto a new worksheet via Edit Copy; Edit Paste Ctrl c and Ctrl v keyboard shortcuts using "paste special" to paste just the data values -- no formulas! renaming a worksheet quiz questions: Which level(s) of reporters received the highest mean and median pay increase? What was the total amount spent on raises? leave the reporter salary file open while you open an existing file to practice using a large data set based on actual campaign finance records freeze and unfreeze panes; horizontal only; horizontal and vertical review quiz questions: How much money came from out-of-state contributions? Overall, which contributor gave the most? How much money came from "legal" sources? What is the influence of money from legal sources?

data clean-up tips: • filter • sort • search and replace • separating data using the "text to columns" function • combining data using the "concatenate (&)" function • other useful text functions: proper, replace, substitute, find display only those contributions that are $100 or more -- instead of using a custom filter, solve this problem by creating a new column and using an IF statement in a formula so the results are part of the permanent data area -- use Help to locate the tip sheet for IF formulas adding comments to cells editing comments how many contributions were over $100? -- use the COUNTIF function subtotals based on city names collapsing and expanding a subtotal outline in terms of total contributions, what are the top five cities? while a subtotal or filter is turned on...copying only visable cells via Edit Go To... Special... Visable cells only simple charts -- Trick: column order, sort order and highlighting make a difference; then, F11 switching between workbooks via Window on the menu bar open a third file which contains fictitious data based on police records calculating a per capita murder rate formatting cells and/or columns for data entry of date values calculating date values -- how the serial numbers work and why they are helpful how Excel handles the year 2000 issue for dates (what you really need to know about birthdays!) formatting cells and/or columns for data entry of time values calculating time values formatting cells and/or columns for data entry of ZIP codes, telephone numbers, etc. print preview: • portrait vs. landscape • percent reduction • headers (sheet tab labels) and footers (Pg. 1 of ?) • gridlines • column headers • printing comments closing workbooks Windows95 directory management -- creating new directories file downloading techniques: • downloading data from the Web as an Excel file (Florida campaign finance data) • downloading data from the Web as an Acrobat Reader (.pdf) file (Census data) • downloading data from the Web as a text (ASCII) file (Census data) converting .pdf data to text files using Word importing text files into Excel using the text import wizard -- working with delimited data copying and pasting Acrobat Reader (.pdf) narrative into Excel


troubleshooting tips: • to avoid large file sizes that may not fit on a standard floppy disk, turn off subtotals before saving the file to permanent memory • if your file size is larger than you expected, check to make sure any formatting changes you made regarding shading do not extend beyond the actual data area • if you see a series of ##### marks, your column width is not wide enough to display the data storage devices: CD-ROMs, DVDs, ZIP disks, 3-1/2 floppy disks, etc. Excel via e-mail: • e-mailing an Excel file as an attachment • downloading an Excel file delivered as an e-mail attachment BONUS topic: VLOOKUP review -- identify the key icons on the standard toolbar:

review -- identify the key icons on the formatting toolbar:

resource books you may want to purchase:
1. Teach Yourself Visually Excel 2002 By Ruth Maran maranGraphics ( $20.99 via: ISBN: 0764535943 (paperback) 320pp
“A great step-by-step narrative married with clear visuals and screen shots that make sense. Dip into the index and refer to only the page(s) you need at the moment. This is truly a cookbook for journalists at the beginning and intermediate levels.” – Deb Wolfe

2. Excel 2002 Formulas (Supercharging Your Spreadsheets with
Powerful Formulas)

by John Walkenbach $27.99 via: ISBN: 076454800X (paperback with CD-ROM) 760 pp.
“Chapter 6 – Working with Dates and Times, is worth the total cost of this book. Walkenbach’s other helpful formula examples are icing on the cake. Save yourself and your technology mentor hours of work reading Excel’s “help” files – get this book! Great for advanced intermediate and expert-level users.” – Deb Wolfe


To top