NOTES FOR USING EXCEL 2007 WHEN PERFORMING LAB #6 Copyright ©2007 Dr. William T. Verts GENERAL NOTES ------------- Anywhere throughout any Excel 2007 assignment where it says to click on Edit-Cut, Edit-Copy, or Edit-Paste in the menu (non-existant in Office 2007 products), you can accomplish the same three tasks by clicking the Home tab (the leftmost tab) and then clicking the cut, copy, or paste icon from the Clipboard panel. Also, in any Windows program that supports cut, copy, and paste you can use keyboard shortcuts for these actions: Edit-Cut Control-X Edit-Copy Control-C Edit-Paste Control-V PAGE 855, STARTING UP --------------------- When starting up Excel, click first on the View tab. In the Workbook Views panel, insure that the Normal button is selected (not Page Layout). In the Show/Hide panel insure that the following items have a check mark next to them: Formula Bar Gridlines Headings PAGE 856, STARTING UP --------------------- In the paragraph that asks you to save the spreadsheet file with File-Save As, click instead on the round "Office" button in the upper left corner of the screen and roll the mouse over Save As in the menu. The options that appear include Excel-Workbook and Excel-Macro-Enabled-Workbook. For all normal spreadsheets that do not include macros, saving as a simple Excel-Workbook (with a .xlsx extension) is sufficient. In lab #6, however, we will be using macros, so save the spreadsheet as an Excel-Macro-Enabled-Workbook (with a .xlsm extension). Note that when you open this file in the future, there will be a message saying "Security Warning: Macros have been disabled" next to an Options button. Click on Options immediately and select the "Enable this content" radio button from the dialog. If you do not do this action now, it will become unavailable after you make changes to the spreadsheet, forcing you to close and re-open the spreadsheet to make the Options button visible once more. PAGE 857, THE SECOND WINDOW --------------------------- In the fourth paragraph the instructions ask that you use the Window menu to select the lab6.xls file (from now on called lab6.xlsm). There is no Window menu in Excel 2007, so you must use the task bar buttons at the bottom of the screen to switch between views of lab6.xlsm and world.xls (the data file is stored in an older format, so it will still have a .xls extension). PAGE 857, BUILDING THE INPUT/OUTPUT TABLE ----------------------------------------- In the third paragraph it says to click on the Patterns tab. In Excel 2007 this is now called the Fill tab. PAGE 858, BUILDING THE INPUT/OUTPUT TABLE ----------------------------------------- What was called Insert-Name-Define in Excel 2003 is now accomplished by clicking the Formulas tab and then clicking the Define Name entry in the Defined Names panel. Now that Excel 2007 allows one, two, and three character column names (column A through column XFD, or 16384 columns), the range names ASK1, ASK2, ASK3, ASK4, LOW1, LOW2, LOW3, and LOW4 are now no longer valid because there are actual cell addresses with the same names. You can create slightly different names using the underscore or question mark that will satisfy Excel, such as ASK_1 or _ASK1 or ASK?1. Any of these are OK to use, but please be consistent (i.e., if you use the ASK_1 form, also use LOW_1 and HIGH_1, even though HIGH1 is acceptable because it isn't a valid cell address). PAGE 864, EXPLAINING QUERIES ---------------------------- The Autofilter is now activated by clicking the Data tab and selecting Filter from the Sort & Filter panel. Clicking on a drop-down button gives a list of all possible options for that column, but now you can select any or all of them for the search criteria. Unfortunately, all options are selected by default. Clicking on the Select All entry "toggles" the check marks each time between all-on to all-off. Once all the marks are off, you can then select the desired options. To clear a search, you either click the drop-down button and then click Select All until all marks are set, or you can click the Clear button in the Sort & Filter panel of the Data tab. PAGE 866, CREATING THE UPDATE MACRO ----------------------------------- To record a macro, click on the View tab, then on Macros, and finally on Record Macro. Unlike Excel 2003, NO button will appear to stop recording the macro. You have to remember that macro steps are now being records, and to stop you must click on Macros and then Stop Recording (still in the View tab). PAGE 867, THE MACRO STEPS ------------------------- In step 5, you can only pick "1" from the drop-down button. Unfortunately, this records the wrong item in the body of the macro itself. We will fix this in the next section. In step 7, you can click Control-C to copy. In step 10, you can click Control-V to paste. PAGE 868, THE MACRO BODY REALITY CHECK -------------------------------------- The body of the macro in the Excel 2007 Visual Basic editor is very similar to what you see on page 868, but there are a couple of differences, one minor and one major. When you complete the macro, the body of the macro will probably look like the following: Sheets("Input Output").Select Range("B12:B100").Select Selection.ClearContents Sheets("Data").Select ActiveSheet.Range("$A$5:$J$85").AutoFilter Field:=10 Range("A6:A86").Select Selection.Copy Sheets("Input Output").Select Range("B12").Select ActiveSheet.Paste This macro as it is won't run correctly unless you make one correction, and that is to add a small amount of text to the end of the AutoFilter line. That line is missing the criterion that searches for all items in that column which have the value "1". Add text to the end to make that line look like this: ActiveSheet.Range("$A$5:$J$85").AutoFilter Field:=10, Criteria1:="1" Notice that the new text requires a comma, the word Criteria followed by a "one" (not a lowercase "L"), a colon, an equal sign, and the number 1 in quotes. You have to get this typed in correctly or the macro won't work. PAGE 869, THE FINAL MODIFICATION -------------------------------- In the third paragraph the instructions say to click on Insert-Name-Paste to create the list of range names. In Excel 2007 this is done by clicking on the Formulas tab, then on the Use In Formula entry of the Defined Names panel. You will see a drop-down list containing all of the defined range names (ASK_1, ASK_2, etc.). At the end of that list is an entry called Paste Names. Click on Paste Names, and in the pop-up dialog click on Paste List. This will paste the data dictionary into the spreadsheet at the current cell.