Excel Spreadsheet

Week 5 – Hands On 01

Instructions

Part 1

Course Objectives:

· CO9: Enhance worksheet and chart presentations by formatting bar charts, using Find feature, SmartArt graphics, applying text effects, hyperlinks, and utilizing linking and embedding features.  

Estimated time to Complete:  20 minutes 

Description:

Students will learn about:

· Using the Quick Analysis gallery

· Finding and replacing data

Required Resources:

· Access to Microsoft Excel.

Deliverable:

After completing the assignment, upload the spreadsheet to the Canvas module.

Setup:

Access to Microsoft Excel.

Lab Steps:

To Find and Replace​ Data

1. Click the Find & Select button to display the Find & Select menu​.

2. Click Replace on the Find & Select menu to display the Find and Replace dialog box​.

3. Type the branch value “82410” in the Find what box, and the new branch value “91110” in the Replace with box to specify the text to find and to replace​.

4. If desired, click Match case and then “Match entire cell contents” to place check marks in those check boxes​.

5. Click the Replace All button to replace the string​.

6. Click OK, click Close, and click Save

To Insert a Chart Using the Quick Analysis Gallery​

1. Select the Category and 2020 In-Office Premium data to include in the chart​.

2. Click the Quick Analysis button to display the Quick Analysis gallery​.

3. Click the Charts tab to display the buttons related to working with charts in the gallery​.

4. Select the bar chart​. The bar chart of the data will be created.

5. Click the Move Chart button to display the Move Chart dialog box​.

6. Click the New sheet option button, and then type 2020 In-Office Premiums as the sheet name in the New sheet box​.

7. Click OK to move the chart to the new sheet.

Formatting the Chart​

1. Click the desired style button to change the style of the chart​.

2. Right-click any of the data bars on the chart to display the shortcut menu​.

3. Click “Format Data Series” on the shortcut menu to display the Format Data Series pane​.

4. Select the different options to make changes to the chart.

5. Select the Chart Elements menu and add Data Labes, Chart Title, and Axis Title.

6. Change the Chart Title to 2020 In-Office Premiums.

7. Change the Vertical Axis Title to Dollar Values.

8. Change the Horizontal Axis Title to Category.

Uploading completed worksheet

1. Save the workbook to your local drive.

2. Upload the saved spreadsheet to Canvas.

Part 2

Course Objectives:

· CO9: Enhance worksheet and chart presentations by formatting bar charts, using Find feature, SmartArt graphics, applying text effects, hyperlinks, and utilizing linking and embedding features.

Estimated time to Complete:  20 minutes 

Description:

Students will learn about:

· Using SmartArt graphics

· Adding pictures to SmartArt graphics

Required Resources:

· Access to Microsoft Excel.

Deliverable:

After completing the assignment, upload the spreadsheet to the Canvas module.

Setup:

Access to Microsoft Excel.

Lab Steps:

Inserting a SmartArt Graphic​

1. Create a new spreadsheet in the workbook.

2. Click the Illustrations button in the Insert menu, and then click the SmartArt Graphic button to display the Choose a SmartArt Graphic dialog box​.

3. Select the Accented Pictor type from the Picture menu of SmartArt in the left pane.​

4. Click the desired layout to see a preview of the chart in the preview area​.

5. Click OK to insert the desired type of SmartArt graphic in the worksheet.

Color and Resize the SmartArt Graphic​

1. Click the Change Colors button to display the Change Colors gallery​.

2. Click the desired color​.

3. Drag the sizing handles to resize the SmartArt graphic to the desired size​.

To Add Text to a SmartArt Graphic​

1. Click the Text Pane button to display the Text Pane​.

2. Click the first bulleted item in the Text Pane and then type Meyor Insurance.

3. For each text circle, type the text​ below, one in each box.

Auto

Health

Home

Life

Adding a Picture to a SmartArt Graphic​

1. In the Text Pane, click the first Insert Picture icon to display the Insert Pictures dialog box​.

2. Click the From a File button to display the Insert Picture dialog box and then browse to the desired file​.

3. Double-click the desired file to place it in the SmartArt graphic​.

4. Add a main image for Meyor Insurance, then add images for each of the four categories.  

Uploading completed worksheet

1. Save the workbook to your local drive.

2. Upload the saved spreadsheet to Canvas.

Part 3

Course Objectives:

· CO9: Enhance worksheet and chart presentations by formatting bar charts, using Find feature, SmartArt graphics, applying text effects, hyperlinks, and utilizing linking and embedding features.

Estimated time to Complete:  20 minutes 

Description:

Students will learn about:

· Applying text effects.

· Including hyperlinked screenshots.

· Using the ALT text.

Required Resources:

· Access to Microsoft Excel.

Deliverable:

After completing the assignment, upload the spreadsheet to the Canvas module.  

Setup:

Access to Microsoft Excel.

Lab Steps:

Drawing a Text Box​

1. Click the Text button to display a gallery with additional buttons related to inserting text objects​.

2. Click the Text Box button and then move the pointer into the worksheet​.

3. Drag to create a text box above the Meyor Insurance image.

4. Type the text “Meyor Insurance Logo”.

5. Click the Shape Outline arrow and then click desired effect.

Linking to website

1. Select a cell below the logo and right mouse-click to display the menu.

2. Select the Link menu item.

3. In the Insert Hyperlink menu, select E-mail Address option.

4. In the Text to display bar, type “Definition of Insurance”.

5. In the address bar at the bottom, enter the link to a website with the definition of insurance. For example:   Insurance Definition & Meaning - Merriam-WebsterLinks to an external site.   ​

6. Click OK button to save the changes.

Embedding Objects

1. Select a cell below the logo and right mouse-click to display the menu.

2. Select the Object button in the Text menu.

3. Select the tab Create from file.

4. Use the Browse button to locate the text file containing the tab delimited data for the spreadsheet.

5. Click the OK button to save the changes.

Week 5 – Hands On 01

Instructions

Part 1

Course Objectives:

· CO9: Enhance worksheet and chart presentations by formatting bar charts, using Find feature, SmartArt graphics, applying text effects, hyperlinks, and utilizing linking and embedding features.  

Estimated time to Complete:  20 minutes 

Description:

Students will learn about:

· Using the Quick Analysis gallery

· Finding and replacing data

Required Resources:

· Access to Microsoft Excel.

Deliverable:

After completing the assignment, upload the spreadsheet to the Canvas module.

Setup:

Access to Microsoft Excel.

Lab Steps:

To Find and Replace​ Data

1. Click the Find & Select button to display the Find & Select menu​.

2. Click Replace on the Find & Select menu to display the Find and Replace dialog box​.

3. Type the branch value “82410” in the Find what box, and the new branch value “91110” in the Replace with box to specify the text to find and to replace​.

4. If desired, click Match case and then “Match entire cell contents” to place check marks in those check boxes​.

5. Click the Replace All button to replace the string​.

6. Click OK, click Close, and click Save

To Insert a Chart Using the Quick Analysis Gallery​

1. Select the Category and 2020 In-Office Premium data to include in the chart​.

2. Click the Quick Analysis button to display the Quick Analysis gallery​.

3. Click the Charts tab to display the buttons related to working with charts in the gallery​.

4. Select the bar chart​. The bar chart of the data will be created.

5. Click the Move Chart button to display the Move Chart dialog box​.

6. Click the New sheet option button, and then type 2020 In-Office Premiums as the sheet name in the New sheet box​.

7. Click OK to move the chart to the new sheet.

Formatting the Chart​

1. Click the desired style button to change the style of the chart​.

2. Right-click any of the data bars on the chart to display the shortcut menu​.

3. Click “Format Data Series” on the shortcut menu to display the Format Data Series pane​.

4. Select the different options to make changes to the chart.

5. Select the Chart Elements menu and add Data Labes, Chart Title, and Axis Title.

6. Change the Chart Title to 2020 In-Office Premiums.

7. Change the Vertical Axis Title to Dollar Values.

8. Change the Horizontal Axis Title to Category.

Uploading completed worksheet

1. Save the workbook to your local drive.

2. Upload the saved spreadsheet to Canvas.

Part 2

Course Objectives:

· CO9: Enhance worksheet and chart presentations by formatting bar charts, using Find feature, SmartArt graphics, applying text effects, hyperlinks, and utilizing linking and embedding features.

Estimated time to Complete:  20 minutes 

Description:

Students will learn about:

· Using SmartArt graphics

· Adding pictures to SmartArt graphics

Required Resources:

· Access to Microsoft Excel.

Deliverable:

After completing the assignment, upload the spreadsheet to the Canvas module.

Setup:

Access to Microsoft Excel.

Lab Steps:

Inserting a SmartArt Graphic​

1. Create a new spreadsheet in the workbook.

2. Click the Illustrations button in the Insert menu, and then click the SmartArt Graphic button to display the Choose a SmartArt Graphic dialog box​.

3. Select the Accented Pictor type from the Picture menu of SmartArt in the left pane.​

4. Click the desired layout to see a preview of the chart in the preview area​.

5. Click OK to insert the desired type of SmartArt graphic in the worksheet.

Color and Resize the SmartArt Graphic​

1. Click the Change Colors button to display the Change Colors gallery​.

2. Click the desired color​.

3. Drag the sizing handles to resize the SmartArt graphic to the desired size​.

To Add Text to a SmartArt Graphic​

1. Click the Text Pane button to display the Text Pane​.

2. Click the first bulleted item in the Text Pane and then type Meyor Insurance.

3. For each text circle, type the text​ below, one in each box.

Auto

Health

Home

Life

Adding a Picture to a SmartArt Graphic​

1. In the Text Pane, click the first Insert Picture icon to display the Insert Pictures dialog box​.

2. Click the From a File button to display the Insert Picture dialog box and then browse to the desired file​.

3. Double-click the desired file to place it in the SmartArt graphic​.

4. Add a main image for Meyor Insurance, then add images for each of the four categories.  

Uploading completed worksheet

1. Save the workbook to your local drive.

2. Upload the saved spreadsheet to Canvas.

Part 3

Course Objectives:

· CO9: Enhance worksheet and chart presentations by formatting bar charts, using Find feature, SmartArt graphics, applying text effects, hyperlinks, and utilizing linking and embedding features.

Estimated time to Complete:  20 minutes 

Description:

Students will learn about:

· Applying text effects.

· Including hyperlinked screenshots.

· Using the ALT text.

Required Resources:

· Access to Microsoft Excel.

Deliverable:

After completing the assignment, upload the spreadsheet to the Canvas module.  

Setup:

Access to Microsoft Excel.

Lab Steps:

Drawing a Text Box​

1. Click the Text button to display a gallery with additional buttons related to inserting text objects​.

2. Click the Text Box button and then move the pointer into the worksheet​.

3. Drag to create a text box above the Meyor Insurance image.

4. Type the text “Meyor Insurance Logo”.

5. Click the Shape Outline arrow and then click desired effect.

Linking to website

1. Select a cell below the logo and right mouse-click to display the menu.

2. Select the Link menu item.

3. In the Insert Hyperlink menu, select E-mail Address option.

4. In the Text to display bar, type “Definition of Insurance”.

5. In the address bar at the bottom, enter the link to a website with the definition of insurance. For example:   Insurance Definition & Meaning - Merriam-WebsterLinks to an external site.   ​

6. Click OK button to save the changes.

Embedding Objects

1. Select a cell below the logo and right mouse-click to display the menu.

2. Select the Object button in the Text menu.

3. Select the tab Create from file.

4. Use the Browse button to locate the text file containing the tab delimited data for the spreadsheet.

5. Click the OK button to save the changes.