CBSE-SOLUTIONS
CLASS-10
PART-B
UNIT-II ELECTRONIC SPREADSHEET(ADVANCED)
A. Multiple Choice Questions:
1. _________ is a tool to test “what-if” questions.
(a) Scenario
(b) Solver
(c) Macro
(d) Average
Answer: (a) Scenario
2. Rohit scored 25 out of 30 in English, 22 out of 30 in Maths. He wants to calculate the score in IT he needs to achieve 85 percent in aggregate. Suggest him the suitable option out of the following to do so.
(a) Macro
(b) Solver
(c) Goal Seek
(d) Subtotal
Answer: (c) Goal Seek
3. In a Subtotal function what are the functions that can be performed on the group of cells?
(a) Sum
(b) Count
(c) Average
(d) All of the above
Answer: (d) All of the above
4. A __________ is a What-if Analysis tool that allows you to substitute set of values automatically in a worksheet.
(a) Navigator
(b) Scenario
(c) Subtotal
(d) Goal Seek
Answer: (b) Scenario
5. Solver command is found in __________ tab.
(a) Data
(b) Tools
(c) Format
(d) Edit
Answer: (b) Tools
6. In Data Consolidate what are the operations we can perform __________.
(a) Sum
(b) Average
(c) Max
(d) All of the above
Answer: (d) All of the above
7. __________ hyperlink contains a full URL.
(a) Absolute
(b) Relative
(c) Both (a) and (b)
(d) None of the above
Answer: (a) Absolute
8. __________ can be used in a spreadsheet software to jump to a different location from within a spreadsheet and can lead to other parts of the current file, to different files or even to websites.
(a) Illustrations
(b) Hyperlinks
(c) Links
(d) Filter
Answer: (b) Hyperlinks
9. Shortcut to open the list of recorded Macros in MS-Excel are:
(a) Alt+F8
(b) Alt+F5
(c) Alt+F4
(d) Alt+F7
Answer: (a) Alt+F8
10. A __________ is a What-if Analysis tool that allows you to substitute set of values automatically in a worksheet.
(a) Navigator
(b) Scenario
(c) Subtotal
(d) Goal Seek
Answer: (b) Scenario
11. What does it mean by Goal Seek in spread sheet?
(a) To draw the curve for given cell value
(b) To determine the value of cell for given result
(c) To omit the cell data for given graph
(d) None of the above
Answer: (b) To determine the value of cell for given result
12. Which function isn't available in consolidating data?
(a) Sum and Count
(b) Max and Min
(c) Average and Product
(d) Percentage
Answer: (d) Percentage
13. Shortcut to open the list of recorded Macros in MS-Excel are:
(a) Alt+F8
(b) Alt+F5
(c) Alt+F4
(d) Alt+F7
Answer: (a) Alt+F8
14. Ramesh wants to solve one variable problem. Suggest him which of the data analysis tools is best suited for him.
(a) Solver
(b) Goal Seek
(c) Scenario
(d) Data table
Answer: (d) Data table
15. Pankaj wants to combine and find the sum/average of marks obtained by the students in the previous three periodic tests. The data is stored in various sheets of a workbook. Which of the following tools is best suited for him?
(a) Data Range
(b) Data Consolidation
(c) Data Review
(d) Data Merge
Answer: (b) Data Consolidation
16. Rakesh wants to apply a formula in the entire column of the spreadsheet, with respect to only one cell. What referencing he will use to get the correct result?
(a) Relative Referencing
(b) Absolute Referencing
(c) Mixed Referencing
(d) Hyperlink
Answer: (b) Absolute Referencing
17. Kartik and Kritika have done a survey of age wise literacy rates of their locality as a school project, which they have created in a Spreadsheet. They both want to work simultaneously to complete it on time. Which option they should use to access the same Spreadsheet to speed up their work?
(a) Consolidate Worksheet
(b) Shared Worksheet
(c) Link Worksheet
(d) Lock Worksheet
Answer: (b) Shared Worksheet
18. Pratham wants to do the same set of tasks to be done repeatedly like formatting or applying a similar formula in a similar range of data. Suggest him a suitable tool for that.
(a) Goal Seek
(b) Solver
(c) Scenario
(d) Macros
Answer: (d) Macros
19. Which language is used to create macros in MS-Excel?
(a) Java
(b) Visual Basic
(c) Access
(d) Visual C++
Answer: (b) Visual Basic
B. True (T) or False (F)
1. Data can be consolidated from two worksheets only.
Answer: F
2. If the contents of selected columns are changed, the Subtotals are automatically recalculated.
Answer: T
3. Subtotals can be arranged either in ascending or descending order.
Answer: T
4. We cannot insert page break between groups in subtotals.
Answer: F
5. Scenario does not have any name.
Answer: F
6. We can create only two scenarios for a given range of cells.
Answer: F
(We can create many scenarios.)
7. Scenario is more advanced form of Solver.
Answer: F
8. Excel files can be shared only between two users.
Answer: F
9. While sharing an Excel file, password is optional.
Answer: T
10. When you have shared a file, you have to accept all changes done by the other user.
Answer: F
11. Macro is written in Visual C.
Answer: F
12. Macro can be used to sort data in a worksheet.
Answer: T
C. Short Answer Type Questions
1. What is consolidating of data?
Answer: Consolidating of data means combining data from many worksheets into one master worksheet.
We can use functions like Sum, Average, Count etc. while consolidating.
It helps us to see total or summary of data at one place.
2. Write the steps to create subtotals in a worksheet.
Answer: First sort the data on the column by which you want to group.
Then select the data and go to Data → Subtotal.
Choose the column, function (like Sum) and columns to total, then click OK.
Excel will insert subtotal rows after every group.
3. What is the use of what-if scenario?
Answer: What-if scenario is used to test different sets of values in a worksheet.
We can save many situations and switch between them easily.
It helps us to see how results change when input values change.
4. What is the use of Goal Seek?
Answer: Goal Seek is used to find the input value needed for a desired result.
We give the target value and Excel finds the required input.
It is useful when we know the answer but do not know the input.
5. What is the use of Solver?
Answer: Solver is used to find the best possible solution for a problem.
It can maximize or minimize a value by changing many cells.
We can also add limits (constraints) while using Solver.
6. Write the steps of using Goal Seek.
Answer: Go to Data → What-If Analysis → Goal Seek.
Select the formula cell in “Set cell” and type the desired value.
Select the input cell in “By changing cell” and click OK.
Excel will find and show the required input value.
7. Write the steps of using Solver.
Answer: First make sure Solver Add-in is installed.
Go to Data → Solver and select the target cell.
Choose Max, Min or Value and select the cells that can change.
Add any limits if needed and click Solve.
8. What is a hyperlink?
Answer: A hyperlink is a clickable link in a worksheet.
When we click on it, it takes us to another cell, sheet, file or website.
It makes navigation easy and fast.
9. What are the types of hyperlinks?
Answer: There are four main types of hyperlinks.
They are: Existing File or Web Page, Place in This Document, Create New Document and E-mail Address.
We can create any of these using Insert → Link.
10. What is the difference between absolute and relative hyperlinks?
Answer: Absolute hyperlink has the full path of the file or website.
Relative hyperlink has path related to the current file location.
Absolute link always points to the same place, relative link works even if files are moved together.
11. How can you link a worksheet to external data?
Answer: We can link a worksheet to external data using Data tab options.
We can use Get Data, From File, From Web or write formulas that refer to other workbooks.
The linked data can also be refreshed when source data changes.
12. What is sharing of a worksheet?
Answer: Sharing of a worksheet means allowing many users to work on the same file at the same time.
Users can open the file from a shared location and make changes.
All changes can be tracked and reviewed later.
13. Write the steps of sharing a spreadsheet.
Answer: Save the file on a shared network location or OneDrive.
Go to Review → Share Workbook and allow changes by more than one user.
Optionally set a password and then save the file.
Now other users can open and edit the same file.
14. What is the meaning of track changes?
Answer: Track Changes means recording all the changes made by different users.
It shows who made the change and when it was made.
Later we can accept or reject each change one by one.
15. How can you insert comments in a spreadsheet?
Answer: Select the cell where you want to add a comment.
Go to Review → New Comment and type your message.
A small red mark appears on the cell and the comment is shown when we move the mouse over it.
16. What is merging of worksheets?
Answer: Merging of worksheets means combining data or changes from two or more worksheets into one.
It is useful when different people have worked on separate copies.
After merging we get one complete updated worksheet.
17. How can you merge two worksheets in a single worksheet?
Answer: We can copy data from one sheet and paste into another.
We can also use Consolidate feature or Compare and Merge Workbooks option.
Power Query can also be used to append data from two sheets.
18. How can you compare two worksheets?
Answer: We can open both worksheets and use View → View Side by Side.
We can also use Compare and Merge Workbooks feature.
This helps us to see the differences between the two sheets easily.
19. What is a macro?
Answer: A macro is a set of recorded actions that we can run again and again.
It saves time when we have to do the same work many times.
Macros are written in VBA (Visual Basic for Applications).
20. How can you record a macro?
Answer: Go to Developer tab and click Record Macro.
Give a name to the macro and start doing the actions.
When finished, click Stop Recording.
The macro is now saved and can be run anytime.
21. How can you pass arguments to a macro?
Answer: We write the macro as a procedure that accepts values (parameters).
These values are called arguments.
When we run the macro, we can give different values to it.
22. How can you sort a single column using macro?
Answer: We can record a macro while sorting the column.
Or we can write VBA code using the Sort command.
When we run the macro, the selected column gets sorted automatically.
D. Long Answer Type Questions
1. Explain the process of consolidating data using subtotals with the help of a worksheet.**
- First sort the data on the column you want to group (for example Region).
- Then select Data → Subtotal.
- Choose the grouping column, the function (usually Sum), and the columns to total.
- Click OK.
Excel inserts subtotal rows after each group and a Grand Total at the end.
You can also choose “Page break between groups” if needed.
2. Explain the use of What-If Scenario with the help of example.**
What-If Scenario is used to store different sets of values and quickly see the results.
Example: Loan calculation
- Scenario 1 (Low Interest): Principal = 5,00,000, Rate = 7%
- Scenario 2 (High Interest): Principal = 5,00,000, Rate = 11%
Using Scenario Manager we can switch between these scenarios and instantly see the change in EMI without changing the worksheet again and again.
3. What is hyperlink? Explain the types of hyperlinks with the help of a worksheet example.**
A hyperlink is a link that takes you to another place when clicked.
Types:
1. Existing File or Web Page – links to a file or website.
2. Place in This Document – links to another sheet or cell.
3. Create New Document – creates and links a new file.
4. E-mail Address – opens email.
Example on Dashboard sheet:
- “Sales Report” → links to Sales sheet
- “Company Website” → links to www.company.com
- “Contact Us” → links to email address
4. Steps of sharing worksheets to other users and reviewing changes in a shared document
Sharing a worksheet (Microsoft Excel):
- Open the workbook you want to share.
- Go to the Review tab → click Share Workbook (or in newer versions: File → Share → Share with People / OneDrive/SharePoint).
- In the Share Workbook dialog, check Allow changes by more than one user at the same time.
- (Optional) Set advanced options such as tracking changes, update frequency, etc.
- Click OK, then save the workbook to a shared network location, OneDrive, or SharePoint so other users can access it.
- Invite other users by entering their email addresses or by sharing the link with edit permissions.
Reviewing changes in a shared document:
- Open the shared workbook.
- Go to the Review tab → click Track Changes → Highlight Changes.
- In the dialog box, select When, Who, and Where options as needed, and check Highlight changes on screen.
- Click OK. Changed cells will be highlighted with a border/comment indicator.
- To accept or reject changes: Review → Track Changes → Accept/Reject Changes. Review each change and choose Accept or Reject.
- You can also view the History sheet (if enabled) that lists all changes with user name, date/time, and details.
.
5. Steps of recording and running a macro
Recording a macro:
Go to the Developer tab (if not visible: File → Options → Customize Ribbon → check Developer).
Click Record Macro.
In the Record Macro dialog:
- Enter a Macro name (no spaces). (Optional) Assign a shortcut key.
- Choose where to store the macro (This Workbook / New Workbook / Personal Macro Workbook).
- Add a description if desired.
- Click OK. The macro recorder starts.
- Perform the actions you want to automate (e.g., formatting, calculations, navigation).
- When finished, go to Developer → Stop Recording.
Running a macro:
- Go to the Developer tab → click Macros (or press Alt + F8).
- Select the desired macro from the list.
- Click Run.
- Alternatively, use the assigned shortcut key, or assign the macro to a button/shape and click it.
6. Sample data and Subtotals (Item-wise and City-wise)
Enter the given data in a worksheet (columns: ITEM NAME, CITY, PRICE, QTY, TOTAL AMOUNT). The TOTAL AMOUNT column already contains the calculated values (PRICE × QTY).
To display Subtotals:
Item-wise Subtotals:
- Select any cell in the data range.
- Sort the data by ITEM NAME (Data → Sort).
- Go to Data → Subtotal.
- At each change in: ITEM NAME.
- Use function: Sum.
- Add subtotal to: check TOTAL AMOUNT (and PRICE/QTY if needed).
- Click OK.
City-wise Subtotals:
- 1. First remove existing subtotals if any (Data → Subtotal → Remove All).
- 2. Sort the data by CITY.
- 3. Go to Data → Subtotal.
- 4. At each change in: CITY.
- 5. Use function: Sum.
- 6. Add subtotal to: TOTAL AMOUNT.
- 7. Click OK.
You can collapse/expand the outline levels using the +/– buttons on the left to view only the subtotals or the detailed data.
7. Displaying the result for the student wanting 95%
Given:
- Total Marks obtained = 430
- Number of Subjects = 5
- Current Score = 86%
The student wants to achieve 95%.
Using Goal Seek (recommended method):
1. In a worksheet, set up the cells as follows (example):
Cell Content Value/Formula
A1 Marks Obtained 430
A2 Maximum Marks (assume) =430/0.86 (≈ 500)
A3 Percentage =A1/A2
A4 Desired Percentage 95%
2. Go to Data → What-If Analysis → Goal Seek.
3. Set cell: the percentage cell (A3).
4. To value: 0.95 (or 95%).
5. By changing cell: the Marks Obtained cell (A1).
6. Click OK.
Goal Seek will calculate the marks the student needs to obtain to reach exactly 95%.
Manual calculation (if maximum marks = 500):
Required marks for 95% = 500 × 0.95 = 475
Extra marks needed = 475 – 430 = 45
Display the result clearly in the worksheet (e.g., “Marks required for 95% = 475”).
No comments:
Post a Comment