To perform the tasks outlined in Assignment using Excel, follow these step-by-step instructions:
Step 1: Create a Table and Enter Data
- Open Excel: Launch Microsoft Excel on your computer.
- Create a New Workbook:
- Click on File > New > Blank Workbook to start with a new worksheet.
- Enter Sample Data:
- Enter the following sample data into Excel, starting from cell A1:
- Format as Table:
- Select the range of data (A1).
- Go to Home tab > Format as Table > Choose a table style (confirm the range if asked).
Step 2: Calculate Average Score
- Add Average Score Column:
- Click on cell F1 (next to "English Score" column header).
- Enter the formula =AVERAGE(B2:D2) and press Enter.
- This calculates the average score for John Doe.
- Copy Formula Down:
- Click on cell F2 (next to "Jane Smith").
- Use the fill handle (small square at the bottom right corner of the cell) to drag down to F4.
- This copies the formula and calculates average scores for all students.
Step 3: Apply Conditional Formatting
- Highlight Scores Above 85:
- Select the range of average scores (F2).
- Go to Home tab > Conditional Formatting > Highlight Cells Rules > Greater Than....
- Enter 85 in the dialog box and choose a formatting style (e.g., light green fill).
Step 4: Sort Data
- Sort by Math Score:
- Click on any cell within the Math Score column (C2).
- Go to Data tab > Sort > Choose Sort Largest to Smallest (descending order).
Step 5: Format Scores
- Format Scores to One Decimal Place:
- Select the range of scores (C2).
- Go to Home tab > Number group > Click on Decrease Decimal to decrease decimals to one place.
Final Check and Save
- Review and Save:
- Review your table to ensure all steps are correctly applied.
- Save your Excel workbook by clicking File > Save As and choose a location and filename.
