Unit 8: MS Excel - Subjective Questions

A formula is an expression used to calculate or process data in an Excel worksheet. Every formula begins with an equal sign (=).

Basic structure:

• Equal sign: Indicates the beginning of a formula.
• Cell references: Identify the cells whose values are used, such as A1 or B5.
• Operators: Specify the calculation, such as +, -, *, /, and ^.
• Functions: Perform predefined calculations, such as SUM or AVERAGE.

Examples:

• =A1+B1 adds the values in cells A1 and B1.
• =C2*10 multiplies the value in C2 by 10.
• =SUM(A1:A5) calculates the total of cells A1 through A5.

After entering a formula, Excel displays the result in the cell while the formula remains visible in the Formula Bar.

Excel uses three main types of cell references:

• Relative reference: Written as A1. Both the row and column change when the formula is copied. For example, if =A1+B1 is copied one row down, it becomes =A2+B2.
• Absolute reference: Written as $A$1. Both the row and column remain fixed when the formula is copied. It is useful for fixed values such as tax rates or conversion factors.
• Mixed reference: Written as $A1 or A$1. In $A1, the column is fixed but the row can change. In A$1, the row is fixed but the column can change.

The dollar sign ($) is used to lock a row, a column, or both. Pressing F4 while editing a reference cycles through the available reference types.

These functions are commonly used to summarize numerical data:

• SUM: Adds values. Syntax: =SUM(A1:A10).
• AVERAGE: Calculates the arithmetic mean. Syntax: =AVERAGE(A1:A10).
• MAX: Returns the largest value. Syntax: =MAX(A1:A10).
• MIN: Returns the smallest value. Syntax: =MIN(A1:A10).
• COUNT: Counts cells containing numbers. Syntax: =COUNT(A1:A10).

For example, if cells A1:A5 contain 10, 20, 30, 40, 50, then:

• =SUM(A1:A5) returns 150.
• =AVERAGE(A1:A5) returns 30.
• =MAX(A1:A5) returns 50.
• =MIN(A1:A5) returns 10.
• =COUNT(A1:A5) returns 5.

Assume that the first item is entered in row 2.

Step 1: Calculate gross amount

• Gross amount is quantity multiplied by price per unit.
• In cell E2, enter =B2*C2.

Step 2: Calculate discount amount

• Mathematically,
• If D2 contains a number such as 10, enter =E2*D2/100 in F2.
• If D2 is already formatted as a percentage, enter =E2*D2.

Step 3: Calculate net amount

• Net amount is gross amount minus discount amount.
• In G2, enter =E2-F2.

Step 4: Copy the formulas

• Select E2:G2 and drag the fill handle downward.
• Relative references automatically adjust for each row.

The formulas should be checked for correct references and the amount columns should be formatted as currency or numbers with suitable decimal places.

Common Excel formula errors include:

• #DIV/0!: Occurs when a number is divided by zero or by an empty cell. Check the denominator or use an IF or IFERROR function.
• #VALUE!: Occurs when a formula uses an incorrect data type, such as multiplying text. Replace text with valid numerical data.
• #NAME?: Appears when Excel does not recognize a function name or named range. Correct spelling mistakes and check quotation marks or range names.
• #REF!: Indicates an invalid cell reference, often caused by deleting a referenced cell or range. Restore the deleted cells or correct the formula references.
• #N/A: Means that a required value is not available, commonly in lookup formulas. Check the lookup value and lookup range.
• #####: Usually means that the column is too narrow to display the result. Increase the column width or change the number format.

The Formula Bar, error indicator, and Evaluate Formula feature can help locate and correct errors.

Excel follows a standard order of operations when calculating a formula:

1. Parentheses: Expressions inside parentheses are calculated first.
2. Reference operators: Range and intersection operations are evaluated.
3. Negation: Negative values such as -5 are processed.
4. Percentage: Percentage values are evaluated.
5. Exponentiation: Powers are calculated using ^.
6. Multiplication and division: * and / are evaluated from left to right.
7. Addition and subtraction: + and - are evaluated from left to right.
8. Comparison operators: Operators such as =, >, and < are evaluated.

For example, =2+3*4 gives 14 because multiplication is performed first. However, =(2+3)*4 gives 20 because the expression inside parentheses is calculated first.

A chart is a visual representation of worksheet data using elements such as bars, lines, columns, points, or sectors. The term graph is often used for charts that display relationships or trends between numerical values.

Importance of charts and graphs:

• They convert large amounts of numerical data into an understandable visual form.
• They make comparisons between categories easier.
• They help identify trends, patterns, increases, and decreases.
• They show relationships between variables.
• They support reports, presentations, and decision-making.
• They make a worksheet more attractive and informative.

A chart is generally linked to its source data. Therefore, when the worksheet data changes, the chart usually updates automatically.

The general procedure for creating a chart is:

1. Arrange the data in rows or columns with meaningful headings.
2. Select the complete data range, including category and series headings.
3. Open the Insert tab.
4. Choose a suitable chart category, such as Column, Line, Pie, or Bar.
5. Select the required chart subtype.
6. Excel inserts the chart into the worksheet.
7. Add or edit the Chart Title, Axis Titles, Legend, and Data Labels.
8. Use the chart formatting tools to change colors, styles, fonts, and layout.
9. Resize or move the chart as required.
10. Verify that the chart represents the correct source data.

A chart can be kept as an embedded object on the worksheet or moved to a separate chart sheet.

The major chart types serve different purposes:

• Column chart: Uses vertical columns to compare values across categories. It is suitable for comparing sales by product or marks by subject.
• Bar chart: Uses horizontal bars. It is useful when category names are long or when many categories must be compared.
• Line chart: Displays values as points connected by lines. It is best for showing trends over time, such as monthly sales.
• Pie chart: Shows parts of a whole as sectors of a circle. It is suitable for a single data series with a small number of positive categories.
• Area chart: Similar to a line chart, but the area below the line is filled. It emphasizes the magnitude of change over time.

The appropriate chart should be selected according to the nature of the data and the message that needs to be communicated.

Suitable chart choices are:

• Monthly sales trends — Line chart: Months represent a continuous time sequence, and the line clearly shows increases, decreases, and overall trends.
• Product-wise market share — Pie or doughnut chart: Market share represents parts of a total. A pie chart is suitable when there are only a few categories and their values add up to a meaningful whole.
• Student marks by subject — Column or bar chart: Columns or bars make it easy to compare marks across different subjects.
• Relationship between height and weight — Scatter chart: A scatter chart plots paired numerical values on two value axes and helps reveal correlation or patterns.

Chart selection must depend on the data type, number of series, number of categories, and purpose of the presentation. Decorative effects should not reduce readability.

The main elements of an Excel chart are:

• Chart area: The entire chart object, including all chart components.
• Plot area: The region where the actual data series are displayed.
• Chart title: Describes the subject or purpose of the chart.
• Category axis: Displays categories, commonly along the horizontal axis.
• Value axis: Displays the numerical scale, commonly along the vertical axis.
• Axis titles: Explain what each axis represents and may include units.
• Data series: A related set of values shown by columns, lines, points, or sectors.
• Legend: Identifies the color or pattern used for each data series.
• Data labels: Display values, percentages, or category names near data points.
• Gridlines: Help users estimate and compare values against the axis scale.

Using these elements correctly makes a chart clear, accurate, and easy to interpret.

The Chart toolbar provides commands for creating, editing, and formatting charts. In older Excel versions, it appears as a separate toolbar when a chart is selected. In newer versions, similar commands appear under contextual tabs such as Chart Design and Format.

Common functions include:

• Selecting a chart object or chart element.
• Changing the chart type.
• Adding or removing chart elements.
• Editing the source data range.
• Switching data between rows and columns.
• Choosing a chart layout or style.
• Formatting the chart area, plot area, axes, and data series.
• Adding trendlines, data labels, legends, and titles.
• Moving the chart to another worksheet or a separate chart sheet.

These tools allow the user to improve both the meaning and appearance of a chart.

To modify and format an existing chart:

1. Select the chart to display the chart-related tools.
2. Use Change Chart Type if another chart type communicates the data more effectively.
3. Use Select Data to add, remove, or edit data series and category labels.
4. Add chart elements such as titles, data labels, legends, gridlines, and axis titles.
5. Select an individual element and open its formatting options.
6. Change fill color, border, font, effects, number format, or axis scale as required.
7. Apply a chart style or color scheme, ensuring that it remains readable.
8. Drag the sizing handles to resize the chart, or drag its border to move it.

Formatting should improve clarity. Excessive colors, three-dimensional effects, or decorative features may make the data difficult to understand.

A data series is a group of related values plotted in a chart. For example, monthly sales for one product may form one data series.

Category labels identify the groups into which values are organized, such as months, product names, departments, or subjects. They normally appear on the category axis.

The Switch Row/Column command changes the way Excel interprets the selected data:

• Data previously treated as separate series may become categories.
• Data previously treated as categories may become separate series.
• The underlying worksheet data is not changed.

This command is useful when a chart is technically correct but presents the information from the wrong perspective. The chart should be checked after switching to ensure that headings, legends, and categories are meaningful.

The steps for previewing and printing a worksheet are:

1. Check the worksheet for errors and apply suitable number formatting.
2. Select the worksheet or required range.
3. Open File > Print to display Print Preview and printing options.
4. Select the required printer.
5. Specify the number of copies.
6. Choose whether to print active sheets, the entire workbook, or the selected range.
7. Set orientation, paper size, margins, scaling, and page order.
8. Review every page in Print Preview.
9. Correct problems such as clipped columns, unwanted blank pages, or poor scaling.
10. Click Print after confirming all settings.

Print Preview is important because it shows how the worksheet will appear on paper before printing, which saves paper and prevents formatting errors.

These features control how worksheet content is divided and printed:

• Print Area: Defines the specific range of cells to be printed. After selecting the range, the user can choose Page Layout > Print Area > Set Print Area. It can later be cleared or changed.
• Page breaks: Determine where one printed page ends and another begins. Excel inserts automatic page breaks, but users can add, move, or remove manual page breaks.
• Scaling: Reduces or enlarges worksheet content to fit a required number of pages. Options may include fitting all columns on one page, fitting the entire sheet on one page, or specifying page width and height.

Scaling should be used carefully because fitting too much content onto one page may make the printed text too small to read.

Page Layout controls the appearance and arrangement of a worksheet on printed pages.

Major settings include:

• Margins: Set the blank space around the page edges.
• Orientation: Chooses Portrait for vertical printing or Landscape for horizontal printing.
• Paper size: Selects a size such as A4, Letter, or Legal.
• Print area: Restricts printing to a selected cell range.
• Page breaks: Control where pages begin and end.
• Background: Adds an on-screen sheet background, although it is generally not printed.
• Print titles: Repeats selected rows or columns on every printed page.
• Scale to Fit: Adjusts the worksheet to a specified page width, height, or percentage.
• Gridlines and headings: Determine whether cell gridlines and row or column headings are printed.

Proper page layout ensures that the printed worksheet is organized, readable, and professional.

A header is information printed at the top of each page, while a footer appears at the bottom. The Header/Footer tab in the Page Setup dialog box is used to create and manage this information.

Procedure:

1. Open the Page Setup dialog box or use the Header and Footer option from the relevant ribbon tab.
2. Select the Header/Footer tab.
3. Choose a built-in header or footer, or select a custom option.
4. Enter information in the left, center, or right section.
5. Insert automatic fields such as page number, total pages, current date, time, file name, file path, or sheet name.
6. Format the text if necessary.
7. Check the result in Print Preview.

Headers and footers are useful for report titles, page numbering, dates, organization names, confidentiality notices, and document identification.

To repeat headings on every printed page:

1. Open the Page Layout tab.
2. Select Print Titles.
3. In the Page Setup dialog box, specify the required rows in Rows to repeat at top.
4. Specify the required columns in Columns to repeat at left, if necessary.
5. Confirm the settings and review them in Print Preview.

Importance of other print settings:

• Margins create space around the worksheet and prevent content from being cut off. They also provide room for headers and footers.
• Gridlines can be printed to make cell boundaries visible, especially in tables without borders.
• Row and column headings print labels such as A, B, C and 1, 2, 3. They are useful for reference but are not always required in formal reports.

Repeating meaningful table headings makes multi-page reports much easier to understand.

A complete procedure may include the following stages:

1. Prepare the worksheet

• Enter headings such as product, quantity, price, sales, and month.
• Arrange data consistently and apply suitable number, currency, and date formats.

2. Calculate results

• Use formulas such as =B2*C2 to calculate sales.
• Use functions such as =SUM(D2:D20), =AVERAGE(D2:D20), =MAX(D2:D20), and =MIN(D2:D20).
• Use absolute references for fixed rates, if required.
• Copy formulas with the fill handle and check for errors.

3. Create the chart

• Select the relevant categories and values.
• Insert a suitable chart, such as a column chart for product comparison or a line chart for monthly trends.
• Add a clear title, axis titles, legend, and necessary data labels.
• Format the chart without using distracting effects.

4. Set up the printed report

• Define the print area.
• Select Portrait or Landscape orientation according to the width of the report.
• Set paper size, margins, page breaks, and scaling.
• Repeat table headings on each page.
• Add a header containing the report title and a footer containing page numbers and date.

5. Preview and print

• Inspect all pages in Print Preview.
• Ensure that no columns are cut off and that text remains readable.
• Correct any layout issues and then print the final report.