Spreadsheet — NCERT Solutions

An active worksheet is the worksheet that is currently selected and available for the user to work on.

Only one worksheet can be active at a time. Its name appears in bold on the sheet tab at the bottom left of the screen.

To fill the series 120, 320, ... 2300 in Column A using Fill: Series:

1. Enter 120 in A1.
2. Enter 320 in A2.
3. Select the range A1:A2.
4. On the Home tab, in the Editing group, click Fill and then click Series.
5. In the Series dialog box, choose Columns.
6. Set Step value to the difference between the terms: $320-120=200$.
7. Set Stop value to 2300.
8. Click OK.

Excel will fill the series by adding 200 each time until 2300 is reached.

A data range is a range of cells used to store data in the worksheet.

A name range is a descriptive name given to a cell or range of cells so that the name can be used in formulas instead of the cell reference.

Creation of name range:

1. Select the cell(s) or range to be named.
2. Click the Formula tab.
3. Choose Define Name.
4. Type the name in the Name box.
5. Click OK.

Selection/use of name range:

• Use the name in a formula in place of the cell range, or
• Use Apply Names to apply the named range in formulas.

The chapter does not give separate steps for Custom Lists. It explains Fill and Series and how Excel can automatically continue data like numbers, dates, or time periods.

If the question intends the use of the fill series/custom list feature, the idea is:

1. Enter the initial items or list.
2. Select the cells.
3. Drag the fill handle or use Home → Fill → Series.
4. Excel extends the pattern automatically.

But the exact detailed procedure for custom lists is not provided in the chapter text.

Format is used to change the appearance of data in cells, such as number format, currency, percentage, date, font, border, colour, alignment, and table style. It helps make data easy to read.

Conditional Formatting changes the appearance of cells only when a condition is true. It is used to highlight important values, unusual values, trends, and comparisons using colour scales, data bars, or icon sets.

AutoFormat is a quick way to apply a ready-made combination of formatting features to a spreadsheet so that it looks professional without formatting each part separately.

Wrap Text is a feature that displays long text within a cell on multiple lines so that the full content fits inside the cell width.

Merging of cells means combining two or more adjacent cells into one larger cell. The contents appear centered in the merged cell.

The cell address of a merged cell is the upper-left cell of the original selected range.

Print Preview is used to see how the worksheet will look when printed before actually printing it. It helps us check the layout, margins, page breaks, and overall appearance, so that mistakes can be corrected before printing.

A relative reference changes when a formula is copied or pasted to another cell. By default, cell references are relative. For example, if a formula containing A1 is copied one cell down, the reference changes to A2.

An absolute reference stays fixed when a formula is copied. It uses dollar signs before the column and row, for example \C\4. If this formula is copied anywhere else, the reference still remains \C\4.

So, the difference is:

• Relative reference: changes with location.
• Absolute reference: remains constant.

The IF function is a logical function that returns one value if a condition is TRUE and another value if it is FALSE. Its format is:

=IF(logical_test, value_if_true, value_if_false)

Here:

• logical_test is the condition to be checked.
• value_if_true is returned when the condition is true.
• value_if_false is returned when the condition is false.

Example:
=IF(A1<20,"Yes","No")
This returns Yes if A1<20, otherwise No.

A nested IF function means one IF function is placed inside another IF function. It is used when more than two conditions are to be tested.

Example from the chapter:
=IF(E2<96, IF(E2<91, IF(E2<55,"Fail","C Grade"), "B Grade"), "A Grade")

This checks several conditions step by step and returns different grades depending on the value in E2.

Two financial functions from the chapter are:

1. PV — present value of an investment.

• Syntax: PV(rate, nper, pmt, fv, type)
• It returns the present value of a series of future payments.

2. FV — future value of an investment.

• Syntax: FV(rate, nper, pmt, pv, type)
• It returns the future value of an investment based on periodic constant payments and a constant interest rate.

Other financial functions mentioned in the chapter include PMT, RATE, NPV, ACCRINT, and CUMIPMT.

The PMT function calculates the periodic payment for an annuity, assuming equal payments and a constant rate of interest. It is often used to calculate the payment for mortgage loans or other loans with fixed interest.

Its syntax is:

=PMT(rate, nper, pv, [fv], [type])

Where:

• rate = interest rate per period
• nper = number of periods
• pv = present value or loan amount
• fv = future value, optional
• type = 0 if payment is at the end of the period, 1 if at the beginning

The chapter states that data entry in a spreadsheet is possible in more than one way. The methods described are:

1. Typing directly into a cell from the keyboard.
2. Using Fill / Fill Series / fill handle to enter repeated or series data automatically.
3. Importing or copying data from other sources such as text files.
4. Using Data Validation Form / data input form for entering records.

So, data entry is possible in four ways as discussed in the chapter.

A one-variable data table is a data table that shows the results of substituting different values into one input cell. The formula used must refer to one input cell. It may be column-oriented or row-oriented.

Example from the chapter: a table to calculate budget increase corresponding to different cost values.

A two-variable data table uses two input cells and one formula with two lists of input values. It shows how two sets of values affect the result.

Example from the chapter: a multiplication table where one list of numbers is entered in a column and another list in a row, and the formula multiplies the two input cells.

So:

• One-variable table → one input cell
• Two-variable table → two input cells

A Pivot Table is a way to present information in a report format. It creates a cross-tabulation summary of data, and the headings can be moved to show different views of the same data.

Usage / advantages mentioned in the chapter:

• summarising large amounts of data
• sub-totaling and aggregating numeric data
• expanding and collapsing levels of data
• moving rows to columns or columns to rows
• filtering, sorting, grouping, and conditional formatting data
• presenting concise and attractive reports

It is useful when we need to analyse related totals and compare several facts about figures.

A formula is a mathematical calculation on a set of cells. It must start with an equal sign (=).

Examples:

• =D1+E1/F1*G1
• =SUM(B1:F1)

Formulas may contain cell references, operators, and functions. The chapter says that the left-hand side of the equal sign is what is calculated and displayed in the cell.

To create a formula:

1. Decide which values will be entered in which cells.
2. Decide the cells that will contain relationships.
3. Enter the data and write the formula beginning with =.
4. Excel calculates and displays the result.

The difference between a formula and a function is:

• A formula is a user-written calculation using operators and cell references, and it must begin with =. Example: =D1+E1/F1*G1.
• A function is a built-in formula already provided by Excel. It also begins with = and uses a function name and arguments inside brackets. Example: =SUM(B1:F1) or =IF(A1<20,"Yes","No").

In short:

• Formula = custom calculation written by the user
• Function = predefined calculation provided by Excel

The chapter says the Now function gives the current date and time, and the Days360 function can be used to work out how many days are left before the next birthday.

If the current date is in A2 as =NOW() and the birthday is in B2, then use the Days360 function to calculate the number of days between them. The exact syntax of DAYS360 is not printed in the chapter, but the required idea is to calculate the days remaining by comparing today’s date with the birthday date.

A suitable spreadsheet formula is:

=DAYS360(A2,B2)

This gives the number of days between the two dates. If needed, you can adjust the year so that B2 refers to the next birthday date.