Excel Functions and Print Setup Quiz

Spreadsheet: Using Advanced Features

• Aim: To use advanced features of spreadsheet software.

• Learning Objectives:

  • Use auto-filter for selecting specific data.
  • Use functions (maximum, minimum, count) to analyze data.
  • Modify page setup.
  • Print a worksheet.

Filtering Data

• Activity 1: Filtering Data
  • Step 1: Open an Excel spreadsheet.
  • Step 2: Enter sample data (e.g., student names, genders, and marks in different subjects).
  • Step 3: Select the relevant data range (e.g., names and marks).
  • Step 4: Use the Sort & Filter tool on the Home ribbon.
  • Step 5: Filter the data using the dropdown menu (e.g., filter by gender).
  • Step 6:

Uncheck unwanted entries to show filtered result.

- **Step 7:** Click OK to see the filtered data.
- **Step 8:** Filter by selecting a single category(e.g., filter by gender to only show "boys"). A tick will appear.
- **Result:**

Data is displayed only with matching entries (e.g., only boys).

• Removing Filters:
  • Step 1: Go to the "Sort & Filter" tool in the Home ribbon.
  • Step 2: Select the filter option from the menu.
  • Step 3: Save the work.

MAX, MIN, and COUNT Functions

• Activity 3
  • Step 1: Open the spreadsheet file.
  • Step 2: In a cell, enter a formula for highest and lowest marks(e.g., "Highest marks (English)").
  • Step 3: In the adjacent cell, apply the maximum function (e.g. =MAX(C3:C8)).
  • Step 4: Apply the minimum function (e.g., =MIN(C3:C8))
  • Step 5: Close the brackets.
  • Step 6: Press Enter to display results.
  • Steps 7-11: Repeat the above process for other columns by selecting the desired range.
• Steps 12-14: Follow steps 1 to 6 to find highest and lowest marks. Repeat for different subjects. Save the file.

Modifying Page Setup

• Activity 5 (Method 1):

  • Step 1: Open the spreadsheet.
  • Step 2: Go to the Page Layout tab.
  • Step 3: Click on the Page Setup arrow.
  • Step 4: The Page Setup Dialog box opens.
  • Step 5: Choose the Page Orientation (Portrait).
  • Step 6: Select Printing Options.
  • Step 7: Choose Paper Size (e.g., A4) to fit the whole worksheet in one page for printing.
  • Step 8: Click OK.

• Activity 6 (Method 2):

  • Step 1: Open the spreadsheet.
  • Step 2: Go to the File tab.
  • Step 3: Click Print .
  • Step 4: Open the Page Setup option.
  • Step 5-8: Repeat steps for Page Setup from Method 1. Save final file.

Printing a Worksheet

• Activity 7:
  • Step 1: Open the spreadsheet.
  • Step 2: Click the File tab.
  • Step 3: Select Print.
  • Step 4: Check the copies you want to print.
  • Step 5: Click Print.

Printing Only Part of a Worksheet

• Activity 8:
  • Step 1: Open the spreadsheet file.
  • Step 2: Select the cells to be printed.
  • Step 3: On the Page Layout tab, use "Print Area" to select the range you want to print.
  • Step 4: Click Set Print Area.
  • Step 5: Click the File tab and select Print.
  • Step 6: Your spreadsheet will be printed only with the range selected.

Removing Print Area

• Activity 9:
  • Step 1: Open the spreadsheet file.
  • Step 2: Go to the "Page Layout" Tab.
    • Step 3: Click on "Print Area."
    • Step 4: Click "Clear Print Area."

Extra Challenge

• Instructions:
  • Open a blank spreadsheet
  • Enter provided student data.
  • Adjust column width (e.g., adjust the column width of the name column).
  • Center-align the text.
  • Calculate totals, averages, highest & lowest using spreadsheet formulas.
  • Adjust page orientation/scaling (Landscape/85%).
  • Set a print area(e.g., A1:E10).
  • Save with the file name "Total Sales".
  • Print the file.