Week 1
Hands On – 02 – Instructions
Part 4:
Course Objectives:
· CO2: Utilize advanced tools and features, such as Flash Fill, AutoCalculate, and chart creation, to analyze and present data effectively, troubleshoot errors, and optimize print and display settings.
Estimated time to Complete: 20 minutes
Description:
Adding a pie chart to the Frangold Real Estate Budget.
Formatting the worksheet.
Changing the document properties.
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:
Add a pie chart to the Frangold Real Estate Budget spreadsheet
1. Select the range of cells that contain the income for January, starting from Commission to Interest.
2. Click the “insert Pie” button on the Insert tab to display the Insert Pie gallery.
3. Click the Insert Pie to create a pie chart of the expenses for January.
4. Change the chart title to “January Income”.
5. Move the pie chart so that it is displayed below the expenses on the spreadsheet.
6. Now create a pie chart for the expenses by selecting the range of cells that contain the expenses for January, starting from Rent to Miscellaneous.
7. Click the “insert Pie” button on the Insert tab to display the Insert Pie gallery.
8. Click the Insert Pie to create a pie chart of the expenses for January.
9. Change the chart title to “January Expenses”.
10. Move the pie chart so that it is displayed below the expenses on the spreadsheet.
Move the Chart to a new worksheet
1. Right-mouse click on the Income chart to open the Design menu options.
2. Click the New Sheet to select it. For the title, type “Income” for the worksheet that will contain the chart.
3. Click the OK button to move the chart to a new sheet with a new sheet tab name.
4. Right-mouse click on the Expense chart to open the Design menu options.
5. Click the New Sheet to select it. For the title, type “Expense” for the worksheet that will contain the chart.
6. Click the OK button to move the chart to a new sheet with a new sheet tab name.
Change the worksheet tab names and colors
1. On the Monthly Expenses spreadsheet, double-click the sheet tab in the lower-left corner of the window.
2. Type the name “Monthly Expenses” as the worksheet tab name.
3. Right-click the sheet tab in the lower-left corner of the window to display a shortcut menu.
4. Select a blue color for the tab.
5. Change the color of the “Income” tab to green.
6. Change the color of the “Expense” tab to red.
Changing document properties
1. Click on the File menu.
2. Select the Info menu or the left-hand side.
3. Select the Properties drop down menu on the right-hand side. Select Advance Properties. A popup menu will appear.
4. In the Summary Tab of the Advanced Properties pop-up menu, Enter the following:
Title: Frangold Real Estate Budget
Subject: Expenses
Author: <your name>
5. Capture a screenshot to upload to Canvas.
6. Click the OK button to save the changes.
Uploading completed worksheet
1. Save the workbook to your local drive.
2. Upload the saved spreadsheet to Canvas.
Part 5:
Course Objectives:
· CO2: Utilize advanced tools and features, such as Flash Fill, AutoCalculate, and chart creation, to analyze and present data effectively, troubleshoot errors, and optimize print and display settings.
Estimated time to Complete: 20 minutes
Description:
Formatting a worksheet for printing.
Using the AutoCalculate Area to determine a Maximum
Using Microsoft Office Help
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:
Printing a Worksheet
1. Click the File menu, select the Print tab to display the Print screen.
2. Click the Portrait Orientation button in the Settings area and then select Landscape Orientation to change the orientation of the page.
3. Click the No scaling button and select the “Fit Sheet on One Page” to print the entire worksheet on one page.
4. Print the worksheet as a Microsoft PDF on your local drive.
5. Click the Print button.
6. Save this PDF to upload to Canvas.
Calculating Maximum Value
1. Select the range of cells in the Rent row from January to December.
2. Right-click the status bar to display the Customized Status Bar shortcut menu.
3. Click the Maximum on the shortcut menu to display the Maximum value in the range.
Using Microsoft Office Help
1. Click on the Help on the ribbon to display the Help tab.
2. Click the Help button to display the Help pane.
3. Type the word “Sum” in the search box and hit enter. An explanation of how to use the Sum function will appear.
4. Capture a screen shot of this panel to upload to Canvas.
Uploading completed worksheet
1. Save the workbook to your local drive. Name the spreadsheet “Frangold Real Estate Budget Lab 4”.
2. Upload the saved spreadsheet to Canvas.
Part 6:
Course Objectives:
· CO2: Utilize advanced tools and features, such as Flash Fill, AutoCalculate, and chart creation, to analyze and present data effectively, troubleshoot errors, and optimize print and display settings.
Estimated time to Complete: 20 minutes
Description:
Students will learn to use Flash fill, enter formulas, and use MAX, MIN, and AVERAGE functions
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:
Using Flash Fill
1. Create a new spreadsheet.
2. In the first column, enter a header “Name”. Then type the following names:
|
John |
|
Amy |
|
Omar |
|
Jay |
|
Rahul |
3. In the second column, enter a header “Email”. Then type the following emails:
|
John@ms.com |
|
Amy@ms.com |
4. Select the cells in the second column next to the names that were entered.
5. From the Data menu, select the Flash Fill button. Notice the Omar’s name is automatically entered into this cell. Notice the email address were automatically filled in based on the existing email addresses.
6. Change the first-row header to a theme with a blue background.
7. Change the sheet tab name to “Employees”. Change the sheet tab color to orange.
Entering Formulas
1. In the fourth column, enter a header “Hours Worked”. Then type the following hours:
|
10 |
|
12 |
|
5 |
|
6 |
|
20 |
2. In the fifth column, enter a header “Hourly Pay Rate”. Then type the following rates:
|
16.75 |
|
20 |
|
15.95 |
|
18.25 |
|
20 |
3. In the fifth column, enter the header “Gross Pay”.
4. In the gross pay column, in the first row, type the formula to calculate the gross pay. The formula is the hours worked times the hourly pay rate. The gross pay will be calculated.
5. Copy the formula to the other cells to calculate the pay for employees without entering the formula manually. The gross pay should be calculated for each employee.
6. Change the theme on the newly added headers to be the same as the ones for the Name and Email.
Using Min, Max, and Average Function
1. In the Hourly Pay Rate column, after the last cell entry, type the following:
Max
Min
Average
2. Select the cell in the Gross Pay column next to the Max entry.
3. Select all six Gross Pays.
4. Select the Formulas menu, then select the Insert Function button.
5. From the pop-up menu, select the Max function, and click OK. A pop-up menu will appear.
6. From the Function Arguments menu, ensure that the correct cells are selected. You can edit the cells range from this menu. Then click OK. The Maximum cell value will be inserted next to the Max cell.
7. Repeat the same steps to calculate the minimum gross pay.
8. Repeat the same steps to calculate the average gross pay.
9. Select a theme the calculated cells so that they stand out from the other data on the spreadsheet.