Excel Spreadsheet

Week 4 Hands On 02

Instructions

Exercise 4:

Course Objectives:

· CO8: Import and manipulate data from various sources, use templates and text functions, paste and transpose data, and utilize quick analysis tools for streamlined data handling and presentation.

Estimated time to Complete:  20 minutes

Description:

Student will learn about:

· Creating and using templates.

Required Resources:

· Access to Microsoft Excel.

Deliverable:

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

Setup:

Access to Microsoft Excel.

Lab Steps:

Creating and Using a Template

1. Create a new spreadsheet with the following data:

Meyor Insurance Sales Analysis
Branch	Category	2020 In-Office Premiums in $	2021 In-Office Premiums in $	2020 Online Premiums in $	2021 Online Premiums in $	Percentage Increase or Decrease		Sum of Categories	2021 Totals
	a	1	2	3	4	50.00%		a	6

 

Save the Template​

1. Click the File tab and click Save As to display the Save As screen​.

2. Click the Browse button to display the Save As dialog box​.

3. Type the desired file name in the File name box​.

4. Click the “Save as type” arrow and then click Excel Template in the list to specify that this workbook should be saved as a template​.

5. Navigate to the desired save location​.

6. Click Save to save the template.

Opening a Template-Based File and Save It as a Workbook​

1. With Excel active, click File Explorer button on the taskbar to start the File Explorer app​.

2. Navigate to the location of the file to be opened​.

3. Double-click the template file to open a new file based on the template​.

4. Click the File tab and click Save As to display the Save As screen​.

5. Click the Browse button to display the Save As dialog box​.

6. Type the file name in the File name box and then navigate to your storage location​.

Click Save to save the file

Uploading completed worksheet

1. Save the workbook to your local drive.

2. Upload the saved spreadsheet to Canvas.

Part 5

Course Objectives:

· CO8: Import and manipulate data from various sources, use templates and text functions, paste and transpose data, and utilize quick analysis tools for streamlined data handling and presentation.

Estimated time to Complete:  20 minutes 

Description:

Student will learn about:

· Creating and using templates.

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:

Creating and Using a Template

1. Create a tab delimited text file with the following data:

Branch Category         2020 In-Office Premiums in $ 2021 In-Office Premiums in $            2020 Online Premiums in $    2021 Online Premiums in $   

82410  Auto    131654            136748            71165  72618

82410  Health 411298            473779            281852            287604

82410  Home  152500            160477            35680  36408

82410  Life      144100            147335            67556  68935

82410  Other  26137  27907  11271  11501

Importing Data from a Text File​

1. Click the first cell on the worksheet to contain the imported data​.

2. Click Data on the ribbon to display the Data tab​.

3. Click the “From Text/CSV” button to display the Import Data dialog box​.

4. If necessary, navigate to the location of the Data files to display the files​.

5. Double-click the name of the file to display the preview window​.

6. Click the Load arrow, then click Load To to display the Import Data dialog box.​

7. Click the Exiting worksheet option button to place the data in the current worksheet rather than on a new sheet​.

8. Click OK to import the data.

9. Capture a screen shot of the spreadsheet and upload it to Canvas.

Using the Trim Function​

1. Select the desired cell, type =trim(cell#) and then click Enter button to trim the spaces from the data in the (cell) and display it in the desired cell.

Pasting Values Only​

1. With the desired range selected press CTRL+C to copy the data​.

2. Right-click one of the cells to display the shortcut menu​.

3. In the Paste Options area, click the Values icon to paste only the values​.

4. Click the save button on the Quick Access toolbar to save the file with the new data.

5. Capture a screen shot of the spreadsheet and upload it to Canvas.

Deleting a Column Using Power Query​

1. In the Navigator dialog box, click the Transform Data button to display the Power Query Editor window​.

2. Click the desired column heading to delete​.

3. Click the Remove Columns button to remove the dollar value columns from the import​.

4. Click the “Close & Load” arrow to display the “Close & Load” menu​.

5. Click the “Close & Load To” command to close the Power Query Editor Window and to display the Import Data dialog box​.

6. Click OK to import the data.

7. Capture a screen shot of the spreadsheet and upload it to Canvas.

To Transpose Columns and Rows​

1. With the range to be copied selected, press CTRL+C to copy the selection to the Office Clipboard​.

2. Click a cell to prepare for pasting data into that location​.

3. Click the Paste arrow to display the Paste gallery​.

4. Click the Transpose button in the Paste gallery to transpose and paste the copied cells.

5. Capture a screen shot of the spreadsheet and upload it to Canvas.

Uploading completed worksheet

1. Save the workbook to your local drive.

2. Upload the saved spreadsheet to Canvas.

 

Part 6

Course Objectives:

· CO8: Import and manipulate data from various sources, use templates and text functions, paste and transpose data, and utilize quick analysis tools for streamlined data handling and presentation.

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:

Formatting Using the Quick Analysis Gallery​

1. Select the 2020 In-Office Premiums range, then click the Quick Analysis button to display the Quick Analysis gallery​.

2. If necessary, click the Formatting tab to display the Quick Analysis gallery formatting options​.

3. Select the color formatting option.

4. Click a desired cell and type a legend for the formatting.

Totaling Data​

1. Click the cell below the last entry for 2020 In-Office Premiums.

2. Type =sum(cell#:cell#) and then click the ENTER button​.

3. Drag the fill handle to the right to replicate the totals for remaining columns

Uploading completed worksheet

1. Save the workbook to your local drive.