class7 lab Lab Activities Term2

Microsoft Excel Ex. No.: 1 Using Formula in Excel Step 1: Invoke Excel, type the following data in sheet1. Step 2: In ...

13 downloads 341 Views 573KB Size
Microsoft Excel Ex. No.: 1

Using Formula in Excel Step 1: Invoke Excel, type the following data in sheet1.

Step 2: In the cell G2, type the following formula =B2+C2+D2+E2+F2 and press .

Step 3: In the cell G3, type the following formula =B3+C3+D3+E3+F3 and press . Step 4: In the cell G4, type the following formula =B4+C4+D4+E4+F4 and press . Step 5: In the cell G5, type the following formula =B5+C5+D5+E5+F5 and press . Step 6: In the cell G6, type the following formula =B6+C6+D6+E6+F6 and press . Step 7: Now save the file and exit Excel. Output: (to be drawn on the left page)

Class 7

Ex. No.: 2

Calculating Total with AutoSum Step 1: Invoke Excel, type the following data in sheet1.

Step 2: Select the range of cells (B2:F2) and click AutoSum in the Editing group under the HOME tab.

Step 3: Using AutoFill feature, fill a range of cells. Select cell G2. Step 4: Point the cursor to the fill handle (the small black square at the lower-right corner) of cell G2. Step 5: It will change to a small black cross. Keeping the left mouse button pressed, drag the fill handle to cell G6. Step 6: Release the left mouse button to view the result. Step 7: Now save the file and exit Excel. Output: (to be drawn on the left page)

Ex. No.: 3

Calculating Salary A Company needs to calculate salary components and total pay using these formulas: HRA : 30% of Basic CCA : 15% of Basic DA : 35% of Basic Total Pay : Basic+HRA+CCA+DA Enter proper formula in this worksheet and calculate total pay. Solution: Step 1: Invoke Excel, type the following data in sheet1.

Step 2: To calculate HRA, in the cell C2 type =B2*30% and press . Step 3: Select cell C2. Point the cursor to the lower right corner of the cell C2. Keeping the left mouse button pressed, drag the fill handle to cell C6. Release the left mouse button to view the result. Step 4: To calculate CCA, in the cell D2 type =B2*15% and press . Step 5: Select cell D2. Point the cursor to the lower right corner of the cell D2. Keeping the left mouse button pressed, drag the fill handle to cell D6. Release the left mouse button to view the result. Step 6: To calculate DA, in the cell E2 type =B2*35% and press . Step 7: Select cell E2. Point the cursor to the lower right corner of the cell E2. Keeping the left mouse button pressed, drag the fill handle to cell E6. Release the left mouse button to view the result. Step 8: To calculate Total, in the cell F2 type =SUM(B2:E2) and press . Step 9: Select cell F2. Point the cursor to the lower right corner of the cell F2. Keeping the left mouse button pressed, drag the fill handle to cell F6. Release the left mouse button to view the result. Step 10: Now save the file and exit Excel. Output: (to be drawn on the left page)

Ex. No.: 4

Conditional Formatting Can you help Anis display all marks less than or equal to 50 in red and all marks greater than or equal to 90 in the given worksheet ? Solution: Step 1: Invoke Excel, type the following data in sheet1.

Step 2: Select the range of cells C2:D6 Step 3: Click Conditional Formatting in the Styles group of the Home tab. The drop-down menu appears. Point to Highlight Cells Rules and click Greater than in the submenu. Step 4: The Greater than dialog box appears. Type 89 on the left and select Custom Format on the right. Step 5: The Format Cells dialog box appears. Select Bold in Font Style and blue in Color. All marks greater than 89 will be displayed in bold blue. Step 6: Click Conditional Formatting again. Point to Highlight Cells Rules and click Less than in the submenu. Step 7: The Less than dialog box appears. Type 51 on the left and choose Custom Format on the right. The Format Cells dialog box appears. Select Bold in Font Style and red in Color. All marks below 51 will appear in bold red. Step 8: Now Save the file and Exit Excel. Output: (to be drawn on the left page)

Ex. No.: 5

3-D Pie Chart The School counselor conducted a test to evaluate students on 5 mental skills: memory, flexibility, problem solving, speed and attention. The test has maximum marks of 200, with 40 marks for each section. Ritu scored 100 marks out of 200. These are her results for the different sections of the test. Help the counselor create a pie chart for these results, format it, and emphasize Ritu’s Strongest point. Section

Marks (out of 40)

Memory

35

Flexibility

10

Problem Solving

19

Speed

20

Attention

16

Total (out of 200)

100

Solution: Step 1 : Enter the data in a worksheet. Do not enter the row with the total marks. Step 2 : Select the cells with data. Step 3 : Click Pie in the Charts group of the Insert Tab. Step 4 : A drop-down list appears. Click the first option in 3-D Pie. A Pie chart appears on the worksheet. Step 5 : Click Layout 6 in the Chart Layouts group of the Design tab. [Note : If the tabs do not appear on the ribbon, it means the chart is not selected. Click on the chart to select it.] Step 6 : Click the legend to select it. Click Shape Outline in the Shape styles group of the Format tab and choose light blue from the color palette. This adds a thin, light blue border around the legend. Step 7 : Right-click the chart title. Use the buttons on the context menu to change the font to Arial, font size to 14, and font color to red. Step 8 : Right-click the chart title again. Click Format Chart Title in the context menu. Click Solid fill in the dialog box and choose yellow as the fill color. Step 9 : Double-click the chart title and change the text. Step 10: The percentage data labels are inside the pie. Click the 10% label and drag it to the outside. Do the same for the other labels. Step 11: We need to emphasize that Ritu’s best results were in the memory section. Click on the blue slice that represents Memory on the pie chart. All the the slices will be selected. Click on the blue slice again, and it will be the only one selected. Drag it out. The chart will look as shown below.

Output: (to be drawn on the left page)

Ex. No.: 6

Column Chart Create a Mark list with the following details.

Solution: Step 1 : Enter the data in the worksheet. Select the range of cells (A1:C5). Click Column in the Charts group of the Insert Tab. A drop-down list appears. Click the first option in 2-D column. A Column chart appears on the worksheet. Step 2 : Click Layout 9 in the Chart Layouts group of the Design tab. [Note : If the tabs do not appear on the ribbon, it means the chart is not selected. Click on the chart to select it.] Step 3 : Click the legend to select it. Click Shape Outline in the Shape styles group of the Format tab and choose light blue from the color palette. This adds a thin, light blue border around the legend. Step 4 : Right-click the chart title. Use the buttons on the context menu to change the font to Arial, font size to 14, and font color to red. Step 5 : Right-click the chart title again. Click Format Chart Title in the context menu. Click Solid fill in the dialog box and choose yellow as the fill color. Double-click the chart title and change the text.

Step 6 : Double Click on the X-axis title box and change the title to Name. Double Click on the Y-axis title box and change the title to Marks. Step 7 : Click Data Labels from the Layout tab and choose the label location to Outside End. It displays the value of each column on the chart. The chart will now look as shown below. Output: (to be drawn on the left page)