Week 5
Hands On – 01 Instructions
Lab Steps:
To Find and Replace Data
- Click the Find & Select button to display the Find & Select menu.
- Click Replace on the Find & Select menu to display the Find and Replace dialog box.
- 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.
- If desired, click Match case and then “Match entire cell contents” to place check marks in those check boxes.
- Click the Replace All button to replace the string.
- Click OK, click Close, and click Save
To Insert a Chart Using the Quick Analysis Gallery
- Select the Category and 2020 In-Office Premium data to include in the chart.
- Click the Quick Analysis button to display the Quick Analysis gallery.
- Click the Charts tab to display the buttons related to working with charts in the gallery.
- Select the bar chart. The bar chart of the data will be created.
- Click the Move Chart button to display the Move Chart dialog box.
- Click the New sheet option button, and then type 2020 In-Office Premiums as the sheet name in the New sheet box.
- Click OK to move the chart to the new sheet.
Formatting the Chart
- Click the desired style button to change the style of the chart.
- Right-click any of the data bars on the chart to display the shortcut menu.
- Click “Format Data Series” on the shortcut menu to display the Format Data Series pane.
- Select the different options to make changes to the chart.
- Select the Chart Elements menu and add Data Labes, Chart Title, and Axis Title.
- Change the Chart Title to 2020 In-Office Premiums.
- Change the Vertical Axis Title to Dollar Values.
- Change the Horizontal Axis Title to Category.
Uploading completed worksheet
- Save the workbook to your local drive.
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
- Create a new spreadsheet in the workbook.
- Click the Illustrations button in the Insert menu, and then click the SmartArt Graphic button to display the Choose a SmartArt Graphic dialog box.
- Select the Accented Pictor type from the Picture menu of SmartArt in the left pane.
- Click the desired layout to see a preview of the chart in the preview area.
- Click OK to insert the desired type of SmartArt graphic in the worksheet.
Color and Resize the SmartArt Graphic
- Click the Change Colors button to display the Change Colors gallery.
- Click the desired color.
- Drag the sizing handles to resize the SmartArt graphic to the desired size.
To Add Text to a SmartArt Graphic
- Click the Text Pane button to display the Text Pane.
- Click the first bulleted item in the Text Pane and then type Meyor Insurance.
- For each text circle, type the textbelow, one in each box.
Auto
Health
Home
Life
Adding a Picture to a SmartArt Graphic
- In the Text Pane, click the first Insert Picture icon to display the Insert Pictures dialog box.
- Click the From a File button to display the Insert Picture dialog box and then browse to the desired file.
- Double-click the desired file to place it in the SmartArt graphic.
- Add a main image for Meyor Insurance, then add images for each of the four categories.
Uploading completed worksheet
- Save the workbook to your local drive.
- 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
- Click the Text button to display a gallery with additional buttons related to inserting text objects.
- Click the Text Box button and then move the pointer into the worksheet.
- Drag to create a text box above the Meyor Insurance image.
- Type the text “Meyor Insurance Logo”.
- Click the Shape Outline arrow and then click desired effect.
Linking to website
- Select a cell below the logo and right mouse-click to display the menu.
- Select the Link menu item.
- In the Insert Hyperlink menu, select E-mail Address option.
- In the Text to display bar, type “Definition of Insurance”.
- 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.
- Click OK button to save the changes.
Embedding Objects
- Select a cell below the logo and right mouse-click to display the menu.
- Select the Object button in the Text menu.
- Select the tab Create from file.
- Use the Browse button to locate the text file containing the tab delimited data for the spreadsheet.
- Click the OK button to save the changes.
Uploading completed worksheet
- Save the workbook to your local drive.