table of contents - university of akron · 3. drag the mouse pointer to the left until the black...

Excel 2011-2013 for Food Systems Management II The University of Akron

Ver. 2014.11.21 . [email protected]

These materials may not be reproduced in whole or in part without the express written permission of The University of Akron

TABLE OF CONTENTS

OVERVIEW ...................................................................................................................... 1

DISCUSSION ..................................................................................................................... 2 OBJECTIVES ..................................................................................................................... 2

LESSON 1: THE EXCEL WINDOWS........................................................................... 3

LESSON 2: PLANNING A WORKBOOK WITH MULTIPLE SHEETS ................. 4

LESSON 3: INSERTING, MOVING AND RENAMING SHEETS ............................ 7

PART A: INSERTING A SHEET ........................................................................................... 7 PART B: RENAME A SHEET .............................................................................................. 8 PART C: MOVE A SHEET .................................................................................................. 8

LESSON 4: TYPING SIMULTANEOUSLY ON MULTIPLE SHEETS ................... 9

PART A: MAKING MULTIPLE SHEETS ACTIVE.................................................................. 9 PART B: TYPING AT ONCE ON ALL ACTIVE SHEETS ......................................................... 9 PART C: FORMATTING AT ONCE ON ALL ACTIVE SHEETS .............................................. 11 PART D: UNGROUP THE SHEETS ..................................................................................... 13

LESSON 5: USING COPY AND PASTE ..................................................................... 14

LESSON 6: MOVING DATA ........................................................................................ 15

PART A: USING CUT AND PASTE .................................................................................... 15 PART B: USING DRAG AND DROP .................................................................................. 16

LESSON 7: USE PAGE SETUP BEFORE PRINTING ............................................. 17

APPENDIX A: TIPS FOR WORKING WITH ROWS AND COLUMNS ............... 20

APPENDIX B: THE EDIT, CLEAR COMMANDS ................................................... 21

APPENDIX C: USE ATOMIC LEARNING TO GET HELP ................................... 22

These materials were developed and are owned by The University of Akron. All rights reserved.

©2014 The University of Akron




Overview Discussion You are asked to use Microsoft Excel to complete menu projects. This session will build on the computer skills introduced in Food Systems Management I to further your knowledge of Excel and help you to complete the menu project. The menus used in this Excel course are for illustration purposes to teach you how to use Excel. The menus may not meet the requirements of the project as assigned by your instructor. Objectives In this course, you will learn to:

Plan a workbook with multiple sheets

Insert sheets

Move sheets

Rename sheets Type on more than one sheet at a time

Use copy and paste

Use autofill

Move data with cut and paste

Move data with drag and drop

Use borders

Use shading

Use the Page Setup dialog box




Lesson 1: The Excel Windows Open Excel to review its windows. To do that, click on the Start Button or Office globe and trace to (All) Programs > Microsoft Office > Excel and click on Excel.

This screenshot shows the basic elements of the Excel windows.

When you begin a new workbook, Excel will give you one sheet. You can create additional sheets and delete any sheet. The workbook must have at least one sheet. When you save your work, all the sheets in a workbook are saved in one file, under one file name. Each sheet in the workbook consists of a series of columns identified by lettered column headings and a series of rows identified by numbered row headings. Columns are assigned alphabetic labels from A to XFD (16,384 columns). Rows are assigned numeric labels from 1 to 1,048,576 (1,048,576 rows).

A cell is the rectangular area where a column and a row intersect. Each cell is identified by a cell reference, such as A1, which is its column and row location. The active cell is the cell in which you currently are working. Excel identifies the active cell with a dark border that outlines one cell. In the example above, cell A1 is the active cell.

Formula Bar

Ribbon

Name Box

Sheet

Active Cell

Scroll bars

Control buttons for window Quick Access toolbar




Lesson 2: Planning a Workbook with Multiple Sheets Planning the menu project before beginning to type will save you some time and effort. The overall appearance of one day’s menus is the same as any other day’s menus, so planning a form to use for each day will make the work in Excel easier. The Plan What happens 1. In the menu project that you are

assigned, you need to have one week of menus. Each day of the week is to appear on one sheet of paper. One way to do this is to place each day’s menus on its own sheet, so that there are seven sheets in the workbook.

2. The overall appearance of each of the seven sheets will be the same. • The day of the week will appear in

cell A1. Row 2 will be used for the diet labels.

• The meal names, Breakfast, Lunch and Dinner, will appear in the first column on the left.

• For each meal, ten rows are allotted for menu items.




3. There are five (5) diets to include.

Each menu item in a diet needs to have its portion size (PS) indicated. These labels will be in row 2.

4. Formatting the labels for the columns and rows will make the menus easier to read.




5. When the form is typed and formatted for one day, the form will be typed and formatted for all the days of the week at the same time.

With one set of typing and formatting, the form is created on all seven days.




Lesson 3: Inserting, Moving, and Renaming Sheets When a new workbook is opened in Excel, it will have one sheet by default. In the plan for the menu project, seven sheets are required. There will be a sheet for each day of the week. Part A: Inserting a Sheet What you do What happens 1. Open Excel.

The application Excel is started and a new workbook is opened. Note that there is one sheet in the new workbook. The active sheet, with its tab highlighted in white, is Sheet1.

2. At the bottom of the work window,

click on the New Sheet tool , which is located after the Sheet1 tab.

A sheet, named Sheet2, is inserted after Sheet1.

3. Repeat step 2 five more times. The workbook has seven sheets.




Part B: Rename a Sheet Renaming a sheet from a default name, such as Sheet1, to Monday will make it easier to locate the data. What you do What happens 1. Double click on the sheet tab named

Sheet1. The word “Sheet1” is selected.

2. Type Monday. 3. Press the ENTER key.

The edit is complete. Pressing ENTER lets Excel know that you are done typing.

4. Follow this procedure to rename the

remaining sheets for the days of the week, Tuesday through Sunday.

The sheets are renamed for the days of the week.

Part C: Move a Sheet If the sheets are not in the order that you would like, use drag and drop to move them. In this exercise, you move the Sunday sheet to be the first sheet. What you do What happens 1. Place the mouse pointer on the Sunday

sheet tab. 2. Hold down the mouse button.

The mouse pointer will look like an arrow

with a sheet of paper attached to it . A small black triangle will appear above and to the left of the sheet tab.

3. Drag the mouse pointer to the left until the black triangle is in front of the Monday tab.

4. Release the mouse button.

The Sunday sheet is moved from the last position to the first position in the sheets.

5. Click on File > Save As > Computer. 6. Select Browse to navigate to the drive

on which to save, which will be listed under Computer in the left column of the Save As dialog box.

7. In the File name box, type menu project.

8. In the Save As dialog box, click on the Save button.

The workbook is saved.




Lesson 4: Typing Simultaneously on Multiple Sheets Usually, when you type or format cells, only one sheet is active. If several sheets require the same typing or format, you can select the appropriate sheets, so all those sheets are active. Then, type or format at once on the active sheets Part A: Making Multiple Sheets Active What you do What happens 1. Note that the Sunday sheet is the active

sheet. Active sheets are indicated by a bright white tab.

2. To select all the sheets, a quick method is to place the mouse pointer over the sheet tab that is to be on top. In this lesson, it will be Sunday.

3. Right-click on the Sunday sheet tab and choose Select All Sheets from the shortcut menu.

All the sheets are active.

Part B: Typing at Once on All Active Sheets What you do What happens 1. Be certain that all the sheets are active.

(All the tabs will be bright white.) If all the sheets are not active, follow steps 2 and 3 above.





What you do What happens

2. Type the following: a. In cell A3, Breakfast. b. In cell A13, Lunch. c. In cell A23, Dinner.

The entries are made to every sheet.

3. Excel can recognize patterns. This can save typing time. a. In cell B2, type PS. b. In cell D2, type PS. c. With the mouse pointer as the white

cross, select cells B2:E2. This is the pattern. (PS, space, PS, space)

4. Use AutoFill to fill in cells F2:K2, if 5 diets are assigned. Use AutoFill to fill in cells F2:Q2, if 8 diets are assigned. a. Place the mouse pointer on the

small black rectangle in the lower left corner of the selected cells. The mouse pointer will look like a thin, black cross .

b. With the mouse pointer as the thin, black cross, drag through cells F2:K2 or cells F2:Q2.

In step 3c, the pattern is selected.

In step 4 b, the pattern is repeated.

Be sure that the Sunday sheet is active and (on top.)




What you do What happens 5. To complete the data entry for row 2,

type the following: a. In cell C2, type HOUSE. b. In cell E2, type SOFT. c. In cell G2, type DL1 for

Dysphagia Level 1. d. In cell I2, type CCHO for

Consistent Carbohydrate Diet. e. In cell K2, type NAS for No Added

Salt. If additional diets are included, they may be:

• 2GM NA for 2 Gram Sodium • 1800 CAL for 1800 Calorie

Diabetic Diet • RED FAT for Restricted

Fat/Cholesterol

The sheets look like this.

Part C: Formatting at Once on All Active Sheets What you do What happens 1. Be certain that all the sheets are active.

(All the tabs will be bright white.) If all the sheets are not active, right-click on the Sunday sheet tab and choose Select All Sheets from the shortcut menu.





What you do What happens 2. Apply shading to the cells with the

labels in row 2. a. With the mouse pointer as the white

cross , select cells B2:K2 or B2:Q2.

b. On the Home tab, click on the

down arrow of the Fill tool .

c. Select a color from the list that

displays.

The list of colors on the Fill tool is displayed in step b.

3. Apply a border to the cells with labels in row 2. a. With the mouse pointer as the white

cross , select cells B2:K2 or B2:Q2.

b. On the Home tab, click on the down arrow of the Border tool

.

c. Select a border from the list that

displays. In this example, Thick Bottom Border is selected.

The list of borders on the Border tool is displayed in step b.

4. With the cells B2:K2 or B2:Q2

selected, click on the Bold tool in the Home tab.

The text in the selected cells is in bold type.

5. In the Quick Access toolbar, click on

the Save tool to save the changes. The file, with the changes, is saved.




Part D: Ungroup the Sheets Ungroup the sheets, when you have completed the work that you want to do simultaneously on multiple sheets. What you do What happens 1. Place the mouse pointer over the

Sunday sheet tab. 2. Right-click.

The shortcut menu appears.

3. From the shortcut menu, click on Ungroup Sheets.

Only one sheet is now active. It will be the sheet on whose tab you right-clicked. The active sheet’s tab is a brighter white than the inactive sheet tabs.

4. Review the sheets to see how they all contain the same labels and formatting.




Lesson 5: Using Copy and Paste Frequently, menu items are the same for several of the diets. You can use copy and paste to quickly enter the menu items in other diets. What you do What happens 1. Type the following data in cells B3:C5.

The entries are completed. TIP: In cell B4, while holding down the Ctrl key, press the “ (quotation mark) key. Ctrl + “ copies the entry from the cell above the active cell.

2. Place the mouse pointer over cell B3. 3. With the mouse pointer as the wide,

white cross , hold down the mouse button and drag to cell C5.

Cells B3:C5 are selected.

4. On the Home tab, click on the Copy tool.

On the Mac, select the Copy tool from the Toolbox above the Ribbon.

The entries are copied to the Windows program called The Clipboard. In Excel, the copied cells are surrounded by the marquee (the moving, dashed lines).

5. Click in cell H3 to make it the active cell.

6. In the Home tab, click on the top half

of the Paste tool . On the Mac, click on the left half of the Paste tool.

The copied entries are pasted into the cells, beginning with cell H3.

Until you create another copy (or a cut) or press the ESCape key to erase the marquee, this copy also will remain on the Clipboard.

Select cells B3:C5 with these steps.




Lesson 6: Moving Data To avoid retyping data that was entered into an incorrect cell, move the data to the correct cell with cut and paste or drag and drop. Part A: Using Cut and Paste What you do What happens 1. Place the mouse pointer over cell H3. 2. With the mouse point appearing as a

white cross , hold down the mouse button and drag to cell I5 to select the data to move. On the Mac, the mouse pointer is a hand.

Cells H3:I5 are selected.

3. On the Home tab, click on the

Cut tool . On the Mac, the Cut tool is in the Toolbox above the Ribbon.

The entries are copied. In Excel, the cut cells are surrounded by the marquee (the moving, dashed lines).

4. Click in cell D3 to make it the active cell.

5. Click on the top half of the

Paste tool . On the Mac, click on the left side of the Paste tool.

The cut entries are pasted into the cells, beginning with cell D3. The entries are deleted from the cells H3:I5. The move is completed.




Part B: Using Drag and Drop What you do What happens 1. If necessary, select cells D3:E5. Cells D3:E5 are highlighted.

2. Place the tip of the mouse pointer on

the border of the selected area. The mouse pointer displays as a four-pronged arrow with the selection arrow

. This is the pointer shape that is necessary to move the data by dragging it to a new location. On the Mac, the mouse pointer displays as a hand.

3. Hold down the mouse button and drag to cell H3. An outline of the selected cells is displayed.

4. Release the mouse button to drop the entries.




Lesson 7: Use Page Setup before Printing Before you print your sheet(s), review the options in the Page Setup dialog box. Page Setup is accessed from the Home tab of the Ribbon. IMPORTANT: When selections are made in Page Setup, only the active sheet(s) is affected. If the selections are to be applied to multiple sheets, CTRL + click on each of the sheet tabs to select them. Then, make the choices in the Page Setup dialog box. What you do What happens

1. Click on the File tab of the Ribbon or File in the menu.

2. Click on Print. On the PC, Microsoft refers to this window as the Backstage.

You can see how your work will look when you print it.

3. Use the Page tools , at the bottom, center of the window, to preview other pages.




What you do What happens 4. Click on the Page Setup link (on the

Mac, Page Setup button) at the bottom of the window.

5. In the Page Setup dialog box, click on the Page tab.

6. Note that on the Page tab, you can: a. Change the page orientation

from portrait to landscape. b. Scale the active sheet. c. Change the paper size.

On the Mac, click on the Options button to select a paper size.

d. For your project, you may want to landscape the paper and change the paper size to legal (8 ½ by 14).

The Page Setup dialog box is displayed. The landscape page orientation and legal paper size are selected.

7. Click on . 8. Note that on the Margins tab, you

can: a. Change the margins. b. Center the printing horizontally

and/or vertically on the paper. 9. For your project, you may want to

center horizontally. Click in its checkbox to make that selection.

The Margins tab of the Page Setup box is displayed.




What you do What happens 10. If all the worksheets need to have

the same header or footer, return to the workbook by using the back button, if necessary. Then: a. Right click on the active sheet

tab. b. Choose Select all sheets.

11. To display the Page Setup dialog box, select File > Print > Page Setup.

12. In the Page Setup dialog box, click on the Header/Footer tab.

13. At the down arrow for Header (top of page) or the down arrow for Footer (bottom of page), select the data that you want to print.

The Page 1 of ? choice will print the word Page, the page number and the total number of pages. Example: Page 1 or 7 Page 2 of 7

14. Click on OK to preview the

changes. Optional: On the Mac, a full screen (print) Preview is available on the Layout tab.

15. Click on the Back button to return to the Normal View. On the Mac, exit from the Preview window, if you opened it.

Print Preview is closed. The Normal view of the sheet is displayed.

16. Click on the Save tool . The changes to the workbook are saved.




Appendix A: Tips for Working with Rows and Columns

On the Mac, control+click is the same as right click on the PC.

Type of edit What to do Column, change width Place the mouse pointer on the dividing

line between the column headers. With the mouse pointer appearing as a double-headed arrow , drag the dividing line.

Column, delete Right-click on the column header (where

the letter for the column is given, such as ) and choose Delete from the

shortcut menu. Column, insert new Right-click on the column header (where

the letter for the column is given, such as ) and choose Insert from the

shortcut menu. Row, change height Place the mouse pointer on the dividing

line between the row headers. With the mouse pointer appearing as a double-

headed arrow , drag the dividing line.

Row, delete Right-click on the row header (where the

number for the row is given, such as ) and choose Delete from the shortcut menu.

Row, insert new Right-click on the row header (where the number for the row is given, such as ) and choose Insert from the shortcut menu.

Column headers Row headers




Appendix B: The Edit, Clear Commands A cell can contain three things: contents (the typing), formatting, and comments. Using the delete or backspace key only erases the contents of a cell.

To clear the formatting or comments, select the cell or cells. Click on the down arrow of

the Clear tool on the Home tab. From the list that displays, select the appropriate option.

From the Mac’s menu, select Edit > Clear.




Appendix C: Use Atomic Learning to Get Help Atomic Learning is a website with thousands of short 1-3 minute tutorials to quickly answer your questions about how to perform a specific computer-based task. There are hundreds of tutorials for Excel 2013, organized by level (Introduction, Intermediate, Advanced, Charts). Tutorials are listed by topic within each level.

To use Atomic Learning, you first login to My Akron. When the Atomic Learning link is used in My Akron, it identifies you as a student at The University of Akron and allows access to the tutorials.

To access Atomic Learning tutorials:

1. Open a browser, such as the Internet Explorer or Firefox. 2. Go to The University of Akron’s home page at http://www.uakron.edu. 3. Click on the My Akron link at the top of the web page. 4. Login with your UAnet ID and password. 5. Click on the Technology Support tab.

6. Click on the Atomic Learning link.

(If you cannot locate the Atomic Learning link, click on the My Experience tab. Select Student Center > Main Menu > Help. The link should display.)

7. On the Atomic Learning web site, select the desired filters, such as: • Enter a keyword for the topic that you need. • At the down arrow for Advanced Filters, select Excel 2013. • Click to select from the search results.

http://www.uakron.edu/

table of contents - university of akron · 3. drag the mouse pointer to the left until the black...

Documents