Excel Spreadsheet

Week 2 – Hands On 02

Instructions

Use absolute and mixed cell references in a formula

1. In the Manola Department Stores Expense Chart Sheet, enter the data “Bonus + Salary” in the row below “Total Expenses”. Enter the formula for the first column, then copy and paste the formula into the other columns. Notice that the formula does not adjust to the column.  Update the formula so it is using the correct column.

2. Use the absolute cell reference to calculate the sum of the two expenses for each month.

3. In the same spreadsheet, enter the data “Site Rental + Marketing” in the row below the “Bonus + Salary” row.

4. Use the mixed cell reference to calculate the sum of the two expenses for each month. Use the absolute column and relative row as the mixed cell reference. Enter the formula for the first column, then copy and paste the formula into the other columns.  Notice that the formula adjust to the column while keeping the rows the same.

5. Use the “ctrl” + “`” to view the formulas in the spreadsheet.

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

Using the IF function

1. Click the cell to contain under the first column of the Gross Margin (B7). Click the Insert Function button in the formula bar to display the Insert Function dialog box.

2. Click the “Or select a category” arrow and then select Logical in the list to populate the “Select a function list” with logic functions.

3. Click IF in the “Select a function” list to select the required function.

4. Click the OK button to display the Function Arguments dialog box.

5. Enter the logical test in the Logical test box to enter a logical test for the IF function. The logical test to enter is “B7>100000”.

6. Type the result of the IF function if the logical test is true in the Value if true box. The value to enter is “Over Budget”.

7. Type the result of the IF function if the logical test is false in the Value if false box. The value to enter is “Under Budget”.

8. Click the OK button to insert the IF function in the selected cell.

9. Copy and paste this function for all the other columns.

10. Use the “ctrl” + “`” to view the formulas in the spreadsheet.‘

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

Uploading completed worksheet

12. Save the workbook to your local drive.

 

Part 5:

Course Objectives:

· CO4: Apply and manage advanced data visualization and analysis tools, such as sparklines, charts, conditional logic with the IF function, and various worksheet views and layout options for streamlined presentation and printing.

Estimated time to Complete:  30 minutes 

Description:

Students will learn to:

· Create sparkline charts.

· Using the format painter button to format cells.

Required Resources:

· Access to Microsoft Excel.

Setup:

Access to Microsoft Excel.

Lab Steps:

Create and format sparkline charts

1. In the Manola Department Store spreadsheet, select the cell below the calculations where you want to insert a sparkline chart​.

2. Display the Insert tab and then click the Line button to display the Create Sparklines dialog box​.

3. Drag through the Revenue range of cells from January through June, and then lift your finger or release the mouse button to insert the selected range in the Data Range box​. The Data Range will be displayed in the Create Sparkline dialog box.

4. Click the OK button to insert a line sparkline chart in the selected cell and display the Sparkline tab.

Formatting Sparkline Chart​s

1. Click the More button on the Sparkline tab to display the Sparkline Style gallery​.

2. Click a desired style in the Sparkline Style gallery to apply the style to the sparkline chart in the selected cell​.

3. Point to the fill handle in the cell and then drag through the desired range to copy the line sparkline chart.

Changing the Sparkline Type​

1. Select the range of the sparkline charts and, if necessary, click the Sparkline tab to make it the active tab​.

2. Click the Column button to change the sparkline charts in the selected range to the column type.

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

Formatting the Worksheet - Assign Formats to Nonadjacent Ranges​

1. Select the first cell to the right of the Total Revenue.​

2. While holding down the CTRL key, select the nonadjacent ranges and then release the CTRL key to select nonadjacent ranges​.

3. Click the Number group on the Home tab to display the Format Cells dialog box​. Select the Currency format. Then click the OK button.

4. Select a cell and click Fill Color button on the Home tab select a fill color for the non-adjacent cells.

5. Click the Font Color button on the Home tab and select a font to apply to the non-adjacent cells.

6. Now enter data into these non-adjacent cells. Notice that the font selected are used for the data in these cells.

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

Formatting the Worksheet – Using the Format Paint​

1. Double-click the Format Painter button on the Home tab and then move the pointer onto the worksheet to cause the pointer to change to a block plus sign with a paintbrush​.

2. Click the cell(s) to receive the format to assign the format of the source cell to the destination cell(s)​.

3. Click the Format Painter button or press the ESC key to stop the format painter.

Uploading completed worksheet

  4. Save the workbook to your local drive.

Part 6: 

Course Objectives:

· CO4: Apply and manage advanced data visualization and analysis tools, such as sparklines, charts, conditional logic with the IF function, and various worksheet views and layout options for streamlined presentation and printing.

Estimated time to Complete:  20 minutes

Description:

Student will learn to:

· Create sparkline charts.

Required Resources:

· Access to Microsoft Excel.

Deliverable:

After completing the assignment, upload the spreadsheet for Manola Department Stores to Canvas.

Setup:

· Access to Microsoft Excel.

Lab Steps:

Adding a Clustered Column Chart to the Workbook

1. Select the cell range containing the revenue for January through June.

2. Click the Recommended Charts button on the Insert tab to display the Insert Chart dialog box with the Recommended Charts tab active​.

3. Select the Bar Chart and then click the OK button​.

4. When Excel draws the chart, click the Move Chart button on the Chart Design tab to display the Move Chart dialog box​.

5. Click New sheet and then enter the name “Revenue” in the New sheet text box to enter a sheet tab name for the chart sheet​.

6. Click the OK button to move the chart to a new chart sheet with a new sheet tab name “Revenue”.

Insert a Chart Title

1. Click anywhere in the chart title placeholder to select it​.

2. Select the text in the chart title placeholder and then type a new chart title “Manola Department Stores Revenue”.

Adding Data Labels​

1. Click the Chart Elements button to display the Chart Elements gallery​.

2. Point to Data Labels to display an arrow and then click the arrow to display the Data Labels fly-out menu​.

3. Select the “Outside End” from the Data Labels fly-out menu so that data labels are displayed on the outside of the bar chart.

4. Click the Chart Elements button to close the gallery

Adding an Axis Title to the Chart​

1. Click anywhere in the chart area outside the chart to select the chart, and then click the Chart Elements button to display the Chart Elements gallery​.

2. Point to Axis Titles to display an arrow and then click the arrow to display the Axis Titles fly-out menu​.

3. Click an axis on the Axis Titles fly-out menu to add an axis title, and then click the Chart Elements button to remove the Chart Elements gallery​.

4. Select the placeholder text in the axis title and replace it with the desired text​. On the vertical axis, enter the text “Revenue”. On the horizontal axis, enter the text “Months”.

5. Right-click the axis title to display a shortcut menu, and then click “Format Axis Title” on the shortcut menu to open the Format Axis Title pane​.

6. Click the Title Options tab, click the “Size & Properties” button, and then, if necessary, click the Alignment arrow to expand the Alignment section​

Modify the Chart Data Labels​

1. Select the Data Labels for the columns. The Format Data Labels fly out menu appears on the right.

2. Click the Number arrow to expand the Number section and then scroll to display options related to formatting numbers​.

3. Change the number to Currency and select zero decimal places. The data labels should have the decimal places removed.

4. Close the Format Data Labels fly out menu.

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

Uploading completed worksheet

  6. Save the workbook to your local drive.