Unit 8: MS Excel

I. Excel Foundations — The Spreadsheet Environment

Microsoft Excel is a spreadsheet application used to organize, calculate, analyze, visualize, and print data. A workbook contains one or more worksheets, while each worksheet consists of cells arranged in numbered rows and lettered columns.

• Workbook: An Excel file, commonly saved with the .xlsx extension; for example, Sales.xlsx.
• Worksheet: A spreadsheet page within a workbook, identified by a sheet tab such as Sheet1.
• Cell: The intersection of a row and a column; B4 means column B, row 4.
• Range: A group of cells; A1:A10 is a vertical range, while A1:C5 is a rectangular range.
• Active cell: The currently selected cell, shown with a highlighted border.
• Data types: Cells may contain text, numbers, dates, times, logical values, or formulas.
• Ribbon: The command area containing tabs such as Home, Insert, Formulas, Page Layout, and View.
• Formula bar: Displays and permits editing of the active cell’s contents.
• Recalculation: Excel automatically recalculates dependent formulas when source values change.
• Cell referencing: Formulas identify data through relative, absolute, or mixed references.

II. Formulas — Calculating and Analyzing Worksheet Data

A. Formulas

A formula is an expression that begins with an equals sign (=) and calculates a result from values, cell references, operators, or functions.

• Basic structure: A formula may combine references and arithmetic operators.

=A2+B2

• A2 and B2 are cell references.
• + is the addition operator.
• The result appears in the cell containing the formula.

• Arithmetic operators: Excel uses + for addition, - for subtraction, * for multiplication, / for division, % for percentage, and ^ for exponentiation.
• Order of operations: Parentheses are evaluated first, followed by powers, multiplication or division, and addition or subtraction.

=(B2+C2)*D2

• B2+C2 is calculated first.
• Its result is multiplied by the value in D2.

• Functions: A function is a predefined formula with arguments placed inside parentheses.

=SUM(B2:B6)
=AVERAGE(B2:B6)
=MAX(B2:B6)
=MIN(B2:B6)
=COUNT(B2:B6)

• B2:B6 means all cells from B2 through B6.
• SUM adds values; AVERAGE finds their arithmetic mean.
• MAX and MIN return the largest and smallest values.
• COUNT counts cells containing numbers.

• Relative reference: A2 changes when a formula is copied; copying =A2*B2 down one row changes it to =A3*B3.
• Absolute reference: $A$2 remains fixed when copied; this is useful for a constant tax or interest rate.
• Mixed reference: $A2 fixes the column, while A$2 fixes the row.
• Logical calculation: IF returns different results according to a condition.

=IF(C2>=50,"Pass","Fail")

• C2>=50 is the logical test.
• "Pass" appears if the condition is true; otherwise, "Fail" appears.

• Common errors: #DIV/0! indicates division by zero, #NAME? often indicates an unrecognized function or name, and #REF! indicates an invalid reference.
• Worked example: If B2 contains 5, C2 contains 80, and D2 contains 10%, the net amount is:

=B2*C2*(1-D2)

• Quantity 5 × price 80 = 400.
• A 10% reduction produces 360.

III. Graphs — Visual Representation of Numerical Relationships

A. Graphs

A graph presents numerical data visually so that changes, patterns, and relationships can be recognized more quickly than in a table alone.

• Axes: The horizontal x-axis commonly shows categories or time, while the vertical y-axis shows measured values.
• Data series: A series is a related set of values, such as monthly sales from January to December.
• Data points: Each plotted value is one data point; the value in B3 may become a marker, bar, or column.
• Scale: Axis intervals must be suitable and consistent; a vertical scale of 0, 10, 20, 30 represents equal steps.
• Trend identification: An upward line may indicate growth, while a downward line may indicate decline.
• Relationship analysis: An XY scatter graph can show whether two numerical variables move together; for example, advertising expenditure may be compared with sales.
• Accuracy requirement: A graph should use correct source data, readable labels, and a non-misleading scale.
• Limitation: Graphs simplify information and may hide exact values unless data labels or the underlying table are available.

IV. Charts — Creating and Editing Data Visualizations

A. Charts

A chart is an Excel object that graphically displays selected worksheet data and remains linked to its source cells.

• Creation process:

  1. Arrange data in labelled rows or columns.
  2. Select the required range, such as A1:B6.
  3. Open the Insert tab.
  4. Choose a chart category and subtype.
  5. Use the chart commands to edit its design and formatting.

• Chart title: States the subject precisely, such as “Monthly Sales for 2025.”

• Legend: Identifies series through names and colors; “Product A” and “Product B” may appear as separate entries.

• Plot area: Contains the visual data markers, lines, columns, or bars.

• Chart area: Includes the entire chart object, including its title, legend, and plot area.

• Axis titles: Explain measurements; for example, the x-axis may be “Month” and the y-axis “Sales in Rupees.”

• Gridlines: Help users estimate values by connecting axis scale positions across the plot area.

• Data labels: Display exact values, percentages, or category names beside data points.

• Linked behavior: If a source value changes from 120 to 150, the corresponding chart element normally updates automatically.

• Placement: A chart may be embedded in a worksheet or placed on a separate chart sheet.

• Selection principle: The chart should communicate the data honestly; unnecessary colors, 3-D effects, and decorations can reduce clarity.

V. Chart Types — Selecting the Appropriate Visual Form

A. Chart types

Different chart types emphasize comparison, trend, composition, distribution, or relationship, so selection must match the nature of the data.

• Column chart: Uses vertical columns to compare categories, such as sales across four branches.
• Bar chart: Uses horizontal bars and is effective when category names are long or many categories must be compared.
• Line chart: Connects values to show trends over ordered periods, such as monthly temperature or annual income.
• Pie chart: Shows parts of one whole as slices and works best with a small number of positive categories totaling 100%.
• Doughnut chart: Resembles a pie chart but can represent more than one data series through concentric rings.
• Area chart: Emphasizes the magnitude of change over time by filling the area below a line.
• XY scatter chart: Plots paired numerical values on two value axes; it is suitable for investigating correlation between height and weight.
• Bubble chart: Extends a scatter chart by using bubble size to represent a third numerical variable.
• Radar chart: Displays several measurements around a circular axis and can compare profiles, such as employee skill ratings.
• Stock chart: Represents financial values such as opening, closing, high, and low prices; its source data must follow the required order.
• Combo chart: Combines types, such as columns for revenue and a line for profit percentage.
• Selection limitation: Pie charts are unsuitable for trends, while line charts are generally unsuitable for unrelated categories.

VI. Chart Toolbar — Commands for Chart Modification

A. Chart toolbar

The chart toolbar provides commands for changing a selected chart’s data, layout, type, and appearance.

• Version distinction: Older Excel versions display a Chart toolbar, whereas newer versions provide contextual Chart Design and Format tabs when a chart is selected.
• Change Chart Type: Converts a chart, for example, from a column chart to a line chart without changing the source values.
• Select Data: Adds, removes, renames, or reorders data series and changes category-axis labels.
• Switch Row/Column: Interchanges the way worksheet rows and columns are plotted as series.
• Add Chart Element: Inserts or modifies chart titles, axis titles, legends, data labels, gridlines, and trendlines.
• Quick Layout: Applies a predefined arrangement of titles, legends, and labels.
• Chart Styles: Changes coordinated colors and visual formatting while retaining the underlying data.
• Format Selection: Adjusts properties of a selected element, including fill, outline, font, axis scale, and number format.
• Move Chart: Places the chart on another worksheet or on a separate chart sheet.
• Responsible formatting: Changing style should improve readability rather than distort comparisons through excessive effects or inappropriate scaling.

VII. Printing — Producing a Worksheet on Paper or PDF

A. Printing

Printing converts worksheet content into a fixed page output, so the print range, scaling, and page breaks must be checked beforehand.

• Print preview: File → Print displays the pages as they will appear and reveals clipped columns, blank pages, or poor scaling.
• Printer selection: The user chooses an installed printer or a virtual option such as Microsoft Print to PDF.
• Copies: The Copies box determines how many complete sets are printed.
• Print scope: Excel can print the active sheets, the entire workbook, or only the current selection.
• Print area: Page Layout → Print Area → Set Print Area restricts output to a selected range such as A1:F40.
• Orientation: Portrait is taller than it is wide; landscape is wider and often suits worksheets with many columns.
• Collation: For multiple copies of several pages, collated output prints complete sets in order.
• Page breaks: Automatic or manual page breaks control where one printed page ends and another begins.
• Print titles: Selected rows or columns, such as row 1, can repeat on every page.
• Scaling caution: “Fit Sheet on One Page” may make a large worksheet unreadably small; fitting all columns on one page is often more practical.

VIII. Page Layout — Controlling Printed Page Appearance

A. Page layout

Page layout settings determine how worksheet content is arranged and scaled on printed pages.

• Margins: Normal, wide, narrow, or custom margins control the blank space around content.
• Orientation: Portrait suits narrow lists, while landscape suits wide tables and charts.
• Paper size: Common choices include A4 and Letter; the selected size should match the printer’s paper.
• Scale to Fit: Width, height, and scale settings reduce or enlarge output; setting width to 1 page fits all columns across one page.
• Breaks: Manual page breaks can be inserted, moved, or removed to keep related records together.
• Background: A worksheet background is primarily for on-screen display and is not printed like ordinary cell content.
• Sheet options: Gridlines and row or column headings can be displayed on screen or included in printing.
• Page Setup dialog: Provides detailed controls under Page, Margins, Header/Footer, and Sheet tabs.
• Layout verification: Page Layout view and print preview should be used to check page boundaries before final output.

IX. Header and Footer Tab — Repeating Page Information

A. Header and footer tab

The Header/Footer tab adds information that repeats in the top or bottom margin of each printed page.

• Header: Appears above worksheet content and may contain a report title, department name, date, or filename.
• Footer: Appears below worksheet content and commonly contains page numbers, confidentiality notices, or document details.
• Access: Open Page Setup → Header/Footer, or use Insert → Header & Footer in newer Excel versions.
• Preset entries: Built-in options may insert page numbers, workbook names, worksheet names, dates, and times.
• Custom sections: Headers and footers are divided into left, center, and right sections.
• Dynamic fields: Commands can insert the current page number, total pages, current date, current time, file path, filename, or sheet name.
• Page-number format: A footer may display “Page 2 of 6” by combining the current-page and total-pages fields.
• Different pages: Options can create a different first-page header or separate headers for odd and even pages.
• Design principle: Header and footer text should be brief, relevant, and placed so that it does not compete with the worksheet’s main data.