Spreadsheet Assignments: Assistance
This page provides help with the following for Excel 2016:
· Naming sections of the spreadsheet
· Creating macros to
If you use a different version, you will need to look at the help files when these suggestions do not match your version.
Naming sections of the spreadsheet:
This is optional and sometimes not a good idea if you have many areas to be named, because it gets confusing to have a lot of named areas. It is used much less than people thought.
1. Highlight the area of the spreadsheet you wish to name (using either the mouse, or the <Shift> button with the arrow keys).
2. look for a box below the "Clipboard" area on the ribbon. Type in the name you want for the area and click "Enter". (For a real old-school experience try this: highlight the area, then type: /IND (forward slash I N D) and hit enter. Then click "edit". what you just did was use the old Excel commands from the early 1990s. For reasons known only to them, the Microsoft programmers still maintain backwards compatibility to early versions.)
To create a macro to go to a particular section of the spreadsheet:
The first macro you create will take a very, very long time. After about 3 or 4 macros you will be able to create one in 1 minute or less.
1. Name the section of the spreadsheet you wish to "Go To" if you wish, otherwise you can just GOTO a specific cell. Make sure that section is not highlighted after you finish naming it (you do this by clicking anywhere else on the spreadsheet).
2. Then create the macro to go to that section (section DATA in our example)Click VIEW | MACROS | Record Macro. In the Record Macro dialogue box, type in a name such as "gotodata" and select "store macro in this workbook. press Enter. Every step you take after this will be recorded in the macro.
Click Home | Editing | Find & Select | Go To. [Or, cntl G] When the Go To dialogue box opens, scroll down to the name of the section you wish to go to (DATA in our example) or type in the cell number and click OK. This area is instantly highlighted.
Stop recording (View | Macros | Stop Recording). You now have a macro rrecorded.
3. Insert a button in the cell in the index area in
the appropriate cell (for our example, at the intersection of the column that
says Go To, and the row for the data section). This is the beginner way to get
a button. Inserting an activex control is more
advanced, but a bit out of scope for this class. Activate the Developer tab by
File | Options | Customize Ribbon, then click the Developer
selection on the right column | Enter. When you return
to the sheet you will see a whole new tab at the top called Developer. Lots to
play with under this tab including Insert | Form Controls but that is
another class....
4. Assign the macro you just created (gotodata) to the button by right-clicking on the button to get a little pop-up menu. Scroll down to Assign Macro... and left-click it. Scroll down to the macro you want in the Assign Macro dialogue box, click it, then click OK. Then click anywhere on the worksheet to deselect the button. Check to see if the button works by clicking on it (the cursor should turn into a pointing finger the moment it's on the button).
To create a macro to print a section:
Making a macro to print sections of the worksheet works basically the same way. At this point you should have all your ranges named (index, data, computations, etc.). If not, you will need to highlight the print rrange inside the macro)
1. Record the macro (View | Macros | Record Macro). Give it a name such as "Printdata".
Then, here are the steps you'd follow:
a. Page Layout | Print Area | Clear Print Area [this clears any existing print definitions]
b. Home | Editing | Find & Select | Go To. [Or, cntl G], then click the area you wish to go to, such as DATA. (I'm assuming this will highlight the entire data area, which should be the case if you've named the section correctly), or type in the print range you want.
c. Page Layout | Print Area | Set Print Area
d. File | Print [make any selections needed for your print job] | OK
e. Stop recording (View | Macros | Stop Recording)
2. Insert the button in the appropriate cell.
3. Assign the macro Printdata to the button as you did in (4) above.
To protect a cell or cells in the worksheet:
In Excel, protecting cells is a two-step process: 1. Select cells to
be Unprotected; 2. Protect all remaining cells.
1. Highlight all the cells you DON'T want protected.
2. Right click | Format Cells | Protection
3. Uncheck Locked (i.e., click to remove the check mark). Click OK.
4. View | Protect Sheet Do not add password in this class. select [click on] Select locked cells, and select unlocked cells..
This is the easy way. Insert | Shapes Choose any simple shape and insert.
Take the cursor (which is now in the shape of a crosshair) to the cell where you want the button to be located. From the top left corner of the cell, hold down of the left mouse button and drag it to the right bottom corner. Release the mouse button.
For the spreadsheet assignments, the buttons should be inserted in a cell in
the index area in the appropriate position (in our example, at the intersection
of the column that says Go To, and the row for the Data section). Click out of
the shape and then hover and right click |
Assign Macro. Back to the top