Unit 7: MS Excel - Subjective Questions

The MS Excel environment is the user interface through which users create, edit, calculate, analyze, and present data in spreadsheets.

Major components include:

• Title Bar: Displays the name of the current workbook and Excel application.
• Quick Access Toolbar: Contains frequently used commands such as Save, Undo, and Redo.
• File Tab: Provides commands for creating, opening, saving, printing, and closing workbooks.
• Ribbon: Organizes Excel commands into tabs such as Home, Insert, Formulas, Data, and View.
• Name Box: Shows the address or name of the active cell or selected range.
• Formula Bar: Displays and allows editing of the contents or formula of the active cell.
• Worksheet Area: Consists of rows, columns, and cells used to enter data.
• Sheet Tabs: Allow users to move between worksheets in a workbook.
• Status Bar: Displays information such as calculation results, page status, and zoom level.
• Scroll Bars and Zoom Controls: Help users navigate and adjust the worksheet view.

A workbook is an Excel file that can contain one or more worksheets.

Steps to create a new workbook are:

1. Open Microsoft Excel.
2. Click the File tab.
3. Select New.
4. Choose Blank workbook to create an empty workbook.
5. Alternatively, select a suitable template, such as a budget or calendar template.
6. Enter data into the cells of the worksheet.
7. Use the Add Worksheet button if additional worksheets are required.
8. Save the workbook by selecting File > Save or pressing Ctrl+S.

A new blank workbook normally opens with a worksheet containing columns labeled with letters and rows labeled with numbers.

An existing workbook can be opened using any of the following methods:

• Using the File tab:

  1. Open Excel.
  2. Select File > Open.
  3. Choose a recent file or click Browse.
  4. Locate the workbook, select it, and click Open.

• Using a keyboard shortcut: Press Ctrl+O, browse to the required file, and open it.

• From File Explorer: Locate the Excel file and double-click it. The workbook opens in Excel.

• From the Recent list: Select a workbook from the list of recently used files displayed on Excel's start screen.

• From cloud storage: Choose a file saved on OneDrive or another connected online location.

If a workbook is opened in Protected View, editing may be disabled until the user selects Enable Editing, provided the file is trusted.

Save and Save As are used to store workbook data, but they serve different purposes.

• Save: Updates the current workbook using its existing file name, location, and format. Its shortcut is Ctrl+S.
• Save As: Creates a new copy or allows the user to choose a different name, location, or file format. The original file can remain unchanged.

Steps for saving a new workbook:

1. Select File > Save or press Ctrl+S.
2. Choose a storage location, such as a computer folder or OneDrive.
3. Enter a meaningful file name.
4. Select a file type, such as *Excel Workbook (.xlsx)**.
5. Click Save.

Common file formats include:

• .xlsx: Standard Excel workbook without macros.
• .xlsm: Macro-enabled workbook.
• .csv: Comma-separated text file that normally stores only the active worksheet and does not retain most formatting.
• .pdf: Used to share a fixed-layout version of worksheet content.

The terms have the following meanings:

• Workbook: The complete Excel file. It may contain multiple worksheets.
• Worksheet: A spreadsheet page inside a workbook where data is entered and processed.
• Row: A horizontal group of cells identified by numbers such as 1, 2, and 3.
• Column: A vertical group of cells identified by letters such as A, B, and C.
• Cell: The intersection of a row and a column. It is the basic unit for entering data.

For example, C5 identifies the cell at the intersection of column C and row 5. If a workbook contains separate sheets named Sales, Expenses, and Summary, each sheet is a worksheet within the same workbook.

A range is a group of one or more cells selected or referenced together.

• A contiguous range contains adjacent cells and is written using a colon. For example, A1:C5 includes every cell from A1 through C5.
• A non-contiguous range contains separate cells or ranges. It can be selected by holding Ctrl while choosing the required areas. For example, A1:A5,C1:C5 represents two separate ranges.
• A single-cell range may consist of only one cell, such as B4.
• An entire row range can be represented as 2:5.
• An entire column range can be represented as A:C.

Ranges are used in formulas, functions, formatting, chart creation, sorting, and data analysis. For example, =SUM(B2:B10) adds all numeric values in the range B2 through B10.

Ranges can be selected in several ways:

• Mouse selection: Click and drag across the required cells.
• Shift selection: Click the first cell, hold Shift, and click the last cell.
• Ctrl selection: Hold Ctrl to select separate cells or ranges.
• Name Box: Type a range such as A1:D10 in the Name Box and press Enter.
• Keyboard selection: Hold Shift while using the arrow keys.

To name a range:

1. Select the required cells.
2. Click the Name Box.
3. Type a valid name, such as SalesData.
4. Press Enter.

Benefits of named ranges include:

• Formulas become easier to read, for example, =SUM(SalesData).
• Important data areas can be located quickly.
• Named ranges reduce confusion in complex formulas.
• A valid range name must not contain spaces and should not look like a cell address.

Cell formatting changes the appearance or display of data without necessarily changing its stored value.

Common formatting options include:

• Font formatting: Changes font type, size, color, bold, italic, or underline.
• Alignment: Positions data to the left, center, or right and controls vertical alignment.
• Wrap Text: Displays long content on multiple lines within the same cell.
• Merge and Center: Combines selected cells and centers the content, often for headings.
• Borders: Adds lines around cells to organize tables.
• Fill Color: Adds a background color to highlight cells.
• Number Format: Displays values as currency, percentage, date, time, or other formats.
• Row Height and Column Width: Adjust the size of rows and columns.
• Format Painter: Copies formatting from one cell or range to another.

Effective formatting improves readability, highlights important information, and gives a worksheet a professional appearance.

A number format controls how a stored value appears in a cell. It usually does not change the underlying value used in calculations.

• General: Displays data using Excel's default format.
• Number: Displays numeric values with a chosen number of decimal places and optional thousands separators.
• Currency: Displays a currency symbol beside the value, such as $1,250.00.
• Accounting: Aligns currency symbols and decimal points in a column.
• Percentage: Multiplies the displayed value by 100 and adds %. For example, the stored value 0.25 appears as 25%.
• Date: Displays a date serial value in a form such as 15/03/2025.
• Time: Displays part of a day as hours, minutes, and seconds.

For example, the same stored value may appear differently when formats are changed, but formulas continue to use the stored numeric value. Users can apply formats from the Number group on the Home tab or through the Format Cells dialog box.

Cell references determine whether row and column addresses change when a formula is copied.

• Relative reference: Both the row and column can change. Example: A1. If =A1*B1 is copied one row downward, it becomes =A2*B2.
• Absolute reference: Both the row and column remain fixed. Example: $A$1. If =B2*$E$1 is copied, $E$1 remains unchanged.
• Mixed reference: Either the row or the column is fixed.
  • $A1 fixes column A but allows the row to change.
  • A$1 fixes row 1 but allows the column to change.

Absolute references are useful when every formula must use one constant value, such as a tax rate stored in E1. The F4 key can be used while editing a formula to cycle through relative, absolute, and mixed reference forms.

Mathematical functions are predefined formulas that perform arithmetic or numerical operations.

• SUM: Adds values. Example: =SUM(A1:A5).
• PRODUCT: Multiplies values. Example: =PRODUCT(B2:B4).
• POWER: Raises a number to a given power. =POWER(5,2) calculates .
• SQRT: Finds the positive square root. =SQRT(81) returns .
• ABS: Returns the absolute value. =ABS(-15) returns .
• MOD: Returns the remainder after division. =MOD(17,5) returns because .
• ROUND: Rounds a number to a specified number of digits. =ROUND(12.456,2) returns .

Every formula begins with an equals sign. Function arguments are placed inside parentheses and are normally separated by commas, although the separator may vary with regional settings.

Assume the headings are in row 1 and item data occupies rows 2 through 10.

1. In cell D1, enter the heading Amount.
2. In D2, enter the multiplication formula =B2*C2.
3. Copy the formula from D2 down to D10 using the fill handle. Because the references are relative, Excel changes them to =B3*C3, =B4*C4, and so on.
4. In D11, calculate the grand total with =SUM(D2:D10).
5. To round the grand total to two decimal places, use =ROUND(SUM(D2:D10),2).

The mathematical relationship for each row is:

The Currency or Accounting number format can then be applied to columns C and D. This formatting changes the display but not the underlying calculated values.

The IF function is a logical function that tests a condition and returns one value if the condition is true and another value if it is false.

Its syntax is:

=IF(logical_test, value_if_true, value_if_false)

Suppose a student's mark is stored in B2 and the pass mark is 40. The formula is:

=IF(B2>=40,"Pass","Fail")

Explanation:

• B2>=40 is the logical test.
• "Pass" is returned when the test is true.
• "Fail" is returned when the test is false.

The formula can be copied down to evaluate multiple students. Text results must be enclosed in double quotation marks, while numeric results normally do not require quotation marks.

Logical functions evaluate conditions and return either TRUE or FALSE.

• AND: Returns TRUE only when all conditions are true. Example: =AND(B2>=40,C2>=40).
• OR: Returns TRUE when at least one condition is true. Example: =OR(B2>=80,C2>=80).
• NOT: Reverses a logical result. Example: =NOT(B2>=40).

They can be combined with IF:

• =IF(AND(B2>=40,C2>=40),"Pass","Fail") returns Pass only if both marks are at least 40.
• =IF(OR(B2>=80,C2>=80),"Award","No Award") returns Award if either mark is at least 80.
• =IF(NOT(D2="Paid"),"Pending","Complete") returns Pending when D2 does not contain Paid.

These combinations allow Excel to make decisions based on multiple conditions and are useful in grading, attendance, payroll, and stock-management worksheets.

Excel provides date and time functions for creating, displaying, and extracting date or time information.

• TODAY(): Returns the current date. Example: =TODAY().
• NOW(): Returns the current date and current time. Example: =NOW().
• DATE(year,month,day): Creates a valid date from separate values. Example: =DATE(2025,3,15).
• TIME(hour,minute,second): Creates a time value. Example: =TIME(14,30,0).
• DAY: Extracts the day number from a date. Example: =DAY(A2).
• MONTH: Extracts the month number. Example: =MONTH(A2).
• YEAR: Extracts the year. Example: =YEAR(A2).

Excel stores dates as serial numbers and times as fractions of a day. Therefore, date and time values can be used in arithmetic calculations. TODAY() and NOW() are dynamic functions and are normally updated when the workbook recalculates.

Excel stores valid dates as serial numbers, so one date can be subtracted from another.

Assume:

• Start date is in A2.
• Due date is in B2.
• Completion date is in C2.

The planned duration can be calculated with:

=B2-A2

The relationship is:

To determine whether an unfinished task is overdue, use:

=IF(AND(C2="",TODAY()>B2),"Overdue","Not Overdue")

This formula checks that the completion-date cell is blank and that today's date is later than the due date.

If a task has been completed, the actual duration can be calculated using =C2-A2. The result cells should normally use the General or Number format to show a count of days rather than another date.

AutoSum is a feature that quickly inserts the SUM function for a nearby row or column of numeric data.

Steps to use AutoSum are:

1. Select the cell below a column of numbers or to the right of a row of numbers.
2. Click AutoSum on the Home or Formulas tab, or press Alt+=.
3. Excel proposes a nearby range, such as =SUM(B2:B10).
4. Check the highlighted range.
5. If necessary, select the correct range manually.
6. Press Enter to confirm the formula.

Excel usually searches upward first when AutoSum is placed below a column and searches left when it is placed beside a row. Blank cells or text within the data may cause Excel to suggest an incomplete range, so the user should verify the selected cells before confirming.

All three methods can add values, but they differ in convenience and flexibility.

• Manual addition formula: A formula such as =A1+A2+A3+A4 lists each cell separately. It is suitable for a small number of non-adjacent cells but becomes lengthy for large ranges.
• SUM function: A formula such as =SUM(A1:A100) adds an entire range efficiently. It is easier to read, edit, and maintain than a long addition expression.
• AutoSum: Automatically inserts a SUM formula and suggests a nearby range. It is the fastest method for totaling a regular row or column.

For a large continuous range, =SUM(A1:A100) is preferable to writing every cell with the + operator. AutoSum creates a SUM function; it is not a different calculation method. The suggested range should always be checked, particularly when the worksheet contains blank rows, text, or separate data sections.

A monthly sales workbook can be created as follows:

1. Create the workbook: Open Excel and choose Blank workbook.
2. Enter headings: Use headings such as Date, Product, Quantity, Unit Price, Amount, Target, and Status.
3. Enter records: Place each sales transaction in a separate row.
4. Calculate amount: If Quantity is in C2 and Unit Price is in D2, enter =C2*D2 in E2 and copy it downward.
5. Calculate total sales: At the bottom of column E, use AutoSum or =SUM(E2:E31).
6. Assign status: If the target is in F2, use =IF(E2>=F2,"Target Met","Below Target") in G2.
7. Format the worksheet:
   • Make headings bold and apply a fill color.
   • Apply borders to the table.
   • Format dates using a Date format.
   • Format prices and amounts as Currency or Accounting.
   • Adjust column widths and use Wrap Text where necessary.
8. Rename the sheet: Use a meaningful name such as January Sales.
9. Save the workbook: Select File > Save As, choose a location, enter a file name, and save it in .xlsx format.

This design combines workbook creation, cell ranges, formulas, logical functions, AutoSum, formatting, and file management.

Common Excel mistakes and preventive measures include:

• Missing equals sign: Writing SUM(A1:A5) as text instead of =SUM(A1:A5). Begin every formula with =.
• Incorrect range: Using A1:A4 when the data extends to A5. Verify the highlighted range before confirming.
• Wrong reference type: A constant changes when a formula is copied. Use an absolute reference such as $E$1 where required.
• Numbers stored as text: Calculations may ignore such entries. Convert them to numeric values and apply an appropriate number format.
• Incorrect date entry: Excel may interpret ambiguous dates incorrectly. Use consistent date formats or the DATE function.
• Confusing display with value: Rounding through formatting only changes the display. Use ROUND when the calculated value itself must be rounded.
• Excessive merged cells: They can interfere with sorting and selection. Use merging mainly for simple headings.
• Overwriting the original file: Use Save As to create a separate copy before major changes.
• Saving in an unsuitable format: CSV may remove formulas, formatting, and additional worksheets. Use .xlsx when these features must be preserved.
• Failing to save regularly: Press Ctrl+S frequently and use cloud storage or backups where possible.

Checking formulas, formats, references, ranges, and file types improves the reliability of a workbook.