Excel for Media Specialists; Terms, Tools, and Tips

Excel for Media Specialists; Terms, Tools, and Tips Lakeshore Northeast Ohio Computer Association April 5, 2005 Nancy Vogt Assistant Director/Curriculum I: 1 Housekeeping Cell phones and pagers Restrooms Questions I: 2 Electronic Data in EXCEL I: 3 What is a database?  A place to hold data or information  Also known as a databank or dataset  It is sometimes called a spreadsheet when the data appear in rows and columns Examples of print databases: •Telephone book •Dictionary •Reports from testing companies •Library card catalog I: 4 A phonebook It is sorted alphabetically The fields are: last name, first name, address, sometimes area code, and phone number. You need to know the last name (and how to spell it) in order to find the phone number. What if the phonebook was in an electronic form? I: 5 What could you do with an electronic phone book that would be very difficult to do with a print phone book? ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ I: 6 Excel Excel is a spreadsheet program found in Microsoft Office. Because it is readily available, we will use Excel for demonstration. The concepts used in Excel are similar to those used in other spreadsheet programs. OPTIONAL Open an Excel worksheet: Double click on the if it appears on the desktop (screen). If you don’t see it on the screen, click the Start button, click on Programs, click on Microsoft Excel I: 7 Here is a blank Excel worksheet … I: 8 Pre-test: Can you find these items on your worksheet? 1 2 Cell C22 Chart Wizard icon 10 11 Range C3:D10 Row 22 3 4 5 6 7 8 9 Close button Cut icon Formula bar Horizontal scroll bar 12 13 14 15 Save icon Shortcut to alphabetize Toolbar Icon to alter appearance of data Location of workbook name Worksheet name tab Where to get answers to Excel I: 9 questions (extra credit) Icon for More buttons 16 Icon to center data Name bar 17 18 Excel terms to know… Worksheets and Workbooks Excel is made up of multiple sheets within a single workbook – you can have up to 255 sheets in a workbook. The name of the workbook appears at the very top of the screen. You must name the workbook when you save it. Worksheet names appear as tabs at the bottom of the screen. I: 10 At the top of a worksheet… The toolbar allows you to choose a tool to work with your data. i.e. File, Edit, View, Insert, Format, Tools, Data, Window, Help I: 11 Each tool has a cascade that “drops down,” with a menu to offer you more options. Edit offers you Undo, Cut, Copy, Paste, Paste Special, Fill, Clear… Clear offers you All, Formats, Contents, Comments. I: 12  These buttons are the reason people “lose” work in Excel. Use with caution!  If you close before you SAVE, you lose your work. Danger! Minimize Enlarge/Reduce Close The left and center buttons refer to the size of I: 13 the window on your screen. The next row includes icons or pictures that are shortcuts. This is the “standard toolbar.” New, Open, Save, Email, Print, Print, Spell, Cut, Copy Preview Redo, Sum, Function, Sorts, Chart, Zoom , More… Explore…keep looking for more shortcuts I: 14 If you forget what an icon means, let the cursor hover over the picture and you will get a text box telling you what the button does. I: 15 Cell A cell is the location where a single piece of data is stored. Cells are arranged in columns and rows and have addresses to identify their positions There are 16,777,216 cells on each worksheet - 256 columns (A-IV) and 65,536 rows. The cell address is the column and row that intersects at that cell location, i.e. A6 is Column A, Row 6 I: 16 AC345 is Column AC, Row 345 Columns are vertical Columns hold many fields for a single record (like the phone book, with fields of last name, first name, address for each person’s record) Column B A B C D E F Last Name First Name Address Smith Jones John Bob 123 Main St 456 Main St Area Code 216 216 Phone Number 123-4567 234-5678 I: 17 Rows are horizontal, like a row of beans. Each row holds an entire record. (a single listing in the phone book or all the proficiency information for a single student). Smith John 123 Main St 216 123-4567 Row 3 I: 18 The formula bar tells what data is in the selected cell. This information changes as you move the cursor and select other cells. You can edit or change the data in the formula bar or in the cell I: 19 The cell address shows up in the name box – this cell is named “B1” Column B, Row 1 I: 20 The Range of cells selected (highlighted) here is A1:E12 (: indicates to, A1 to E12) I: 21 Active cell To select a cell or make it active, highlight it by clicking on the cell. Cell E6 is now selected and outlined with a heavy black line. I: 22 Active Range of cells To select a range of cells, click on the cell in the upper left corner, hold and drag the cursor to the cell in the lower right corner (A1:C7), release mouse button. Or click on the cell in the upper left corner, hold the shift button down on the keyboard and, at the same time, click on the cell in the lower right corner. I: 23 To select a column, click on the alpha column indicator (A) and the entire column will be highlighted To select a row, click on the numeric row indicator (3) and the entire row will be highlighted I: 24 “Select all” button To select the entire dataset, click on the solid button in the upper left corner, to the left of Column A and above Row 1. The entire dataset will change color and look like this I: 25 Some other Excel functions: Edit, Undo This lets you take back a mistake! (It can be your best friend.) I: 26 You can change the way your data look On the toolbar, click Format, Cells, Font or Alignment - large or small - font (type style) - color - placement in a cell (ex. centered) I: 27 Header This is the first row of a column that identifies the field This tells you what data are in the column – “Rd Score” (reading score) I: 28 Scroll Bars: Horizontal and Vertical Click and drag these bars to move the visible part of your screen up or down, left or right. It’s a quick way to move around the worksheet. I: 29 More ways to move around…. Click on the empty part of the scroll bar to move one screen at a time. Or click and hold on the arrow at the end of the scroll bar to move larger distances quickly. I: 30 Window, Freeze panes When you freeze a pane, you can move around inside the spreadsheet and still see all the items ABOVE and to the LEFT of the selected cell. In this example, if you wanted to see the headers in the columns and the students’ names in the rows while scrolling through the rest of the data, select cell C2, click on Window, Freeze Panes I: 31 Cut removes selected data (and holds it in a buffer for future use) Copy duplicates selected data (and leaves the original intact) I: 32 And Paste puts what you copy or cut into your new location I: 33 Data, Sort lets you arrange selected numeric or alphabetic data in ascending or descending order. I: 34 Data, Filter Lets you look at selected or filtered data All data Filtered data I: 35 Filter reading scores so that you view only “failed” scores I: 36 Post-test: Find these items on your worksheet? 1 2 Cell C22 Chart Wizard icon 10 11 Range C3:D10 Row 22 3 4 5 6 7 8 9 Close button Cut icon Formula bar Horizontal scroll bar 12 13 14 15 Save icon Shortcut to alphabetize Toolbar Icon to alter appearance of data Location of workbook name Worksheet name tab Where to get answers to Excel I: 37 questions (extra credit) Icon for More buttons 16 Icon to center data Name bar 17 18 16 12 9 15 14 4 5 18 13 3 2 8 7 10 11 1 17 6 I: 38 Just think of what you could do with huge amounts of data …. thousands of records and hundreds of fields? I: 39 To use a spreadsheet Collect data – remember, you must know what you’re looking for before you collect! From 10 people*, collect the following pieces of data – code responses so they are all numeric, i.e. January = 1, Female = 1, Morning = 1 * students, workshop participants, family, friends, imaginary friends          Number of siblings Number of pets Favorite color Gender Number of times moved Birth month Favorite season Favorite part of the day Do you enjoy working/playing on computers?  Do you have a automatic dishwasher in your home?  Number of years in school  Number of vacations in the last 5 years  Number of languages spoken or understood  Do you think it is more important to exercise daily or eat healthy foods?  How many times a year do you go to the museum?  On average, how much TV do you watch per day?  How much TV is too much (hours per day)? I: 40 Enter your data on an Excel worksheet Begin by entering headers in Row 1. I: 41 Notice that the word “dishwasher” does not seem to fit in the column… Place the cursor on the line between J and K, wait to see the double sided arrow, click and drag that line to the right so the column is large enough to hold the entire word. I: 42 Another way to re-size columns… • Select the whole data set by clicking on the solid colored cell to the left of column A and above row 1. • Then double click between any two column letters (A and B, F and G, etc.) and all the columns will be automatically sized to accommodate the largest piece of data. I: 43 Look at your spreadsheet... If you have started your columns in A with “Siblings,” insert a new column for identification numbers or names. Click on Column A to highlight it. Click on Insert, Click on Column. Notice “Siblings” is now Column B I: 44 Before you begin to enter data, change the font, size, and color in the headers and name column and freeze your panes so you can see the header and name as you enter data. Select Column A, Click Format, Cells, Font. Change the font, size, and color. Select Row 1 and do the same Click on B2, click on Window, Click on Freeze Panes * You may have to re-size your columns again. I: 45 Your spreadsheet may look like this… Notice the heavier dark line identifying the area that is “frozen” I: 46 Questions???? I: 47 SAVE your work File, Save As, File Name: (i.e. Joe’s data) I: 48 When you have entered all your data, it’s time to look at it. This is data analysis. Analysis simply means: examine, inspect, investigate, review. *It should generate questions. *It does not necessarily require you to come up with answers or solutions. *Tables and charts are efficient ways to look at data. I: 49 Importing your own data Locate file/report to be imported i.e. Circulation statistics for February: HHS Circ Stat.txt Open a new Excel workbook I: 50 Data Get External Data Import Text File I: 51 Locate file, Click IMPORT I: 52 Follow the Text Import Wizard I: 53 Don’t panic – the data is here, scroll down and across I: 54 Click FINISH I: 55 Where do you want to put the data? The existing worksheet is the one you just opened, Click OK I: 56 Clean up You will see lots of information that you do not want as part of your spreadsheet I: 57 Select data to eliminate Edit, Clear, All Edit, Delete Data, Sort, to remove blanks Add headers I: 58 Data, Sort, Click OK I: 59 You’re ready to go… Add the data for February, March, etc. I: 60 Now let’s look at CHARTS I: 61 The Chart Wizard will help you create a powerful visual of your data I: 62 With the click of an icon you can go from this to this. Which tells the story best? I: 63 In Good Charts  Words are spelled out, abbreviations are avoided  Words run from left to right  Elaborate colors and patterns are avoided, labels are placed on the chart so no legend is required  Messages are included to explain data if necessary  Curiosity is provoked  Colors, if used, are chosen so color-deficient viewers can understand the chart  Type is clear, using upper and lower case, with serifs. Tufte, Edward R., The Visual Display of Quantitative Information, p. 183, Graphics Press, 1983. I: 64 In Bad Charts Abbreviations must be decoded Text is scattered and it is necessary to search to understand the graphic Uses “chart junk” Type is difficult to read TYPE IS ALL CAPITALS AND SAN SERIF Tufte, Edward R., The Visual Display of Quantitative Information, p. 183, Graphics Press, 1983. I: 65 Warning: Excel gives you the tools to make “Bad Charts.” Don’t be tempted. I: 66 Import your data then make a chart from your data Click on any cell in the database Click on the Chart Wizard icon Select a chart type and click Finish I: 67 Explore your Chart Options and Chart Types I: 68 I: 69 Chart Types I: 70 Chart Options I: 71 Examples Titles Color, font, background, gridlines Changing data I: 72 Experiment, and remember the rule of thumb use the least ink possible I: 73 Questions I: 74 Spreadsheets, tables, and charts are powerful tools. They can make your life easier…take the time to learn more about them. I: 75 Excel Workshop for Media Specialists Evaluation I: 76