Class 10 IT Spreadsheet Notes
1. Which are the 2 tabs where you can find the Auto sum option ?
- Home tab.
- Formulas tab.
2. Which function is displayed in the formula bar when we use Auto sum to find the total of cells?
The sum function gets displayed in the Formula Bar, along with the range of cells.
3.What is Conditional Formatting ?
Conditional Formatting is a way change the look of (Background Colour, Border) of a cell based on value contained in it .
4. Which formatting can we apply using Conditional formatting ?
- Number Format
- Font, Font style and font colour.
- Border colour and Border Style
- Fill colour and fill Pattern.
5. Name some of the criteria options that the conditional formatting offers ?
- Greater Than
- Less Than
- Between
- Equal to
- Text that contains
- A date Occurring
- Duplicate Values.
6. What is the advantage of Freeze in Excel ?
By using this feature of Excel, we can make first row or first column fixed in an Excel sheet. We can also divide Excel worksheet in such a way so that we can view content from different parts of the worksheet at the same time.
7. What does the page break preview view show ?
Using this feature we can see the range of cells that will be printed. It also shows different page breaks and page numbers.
8. Which page orientation is better?
- The portrait is useful for worksheets with a lot of rows,
- Landscape is best for worksheets with a large number of columns.
9. What is print area ?
Print area is that portion of the worksheet that will be printed.
10. What is the advantage of Page Breaks Preview ?
Page break preview is useful to view the position of all the pages. We can also insert, move or delete page breaks as per our requirement.
11. What is the use of custom view in Excel ?
Custom view helps us to specify different settings like cell selections, filter settings, print settings, page settings, margins etc. as per the requirement.
12. The nature club in a school wants to calculate the average of the monthly sales of the recycled products they had prepared for a school project. How can they calculate this figure in the Excel sheet in which they have recorded the sale in the range E2 to E12 ?
Click at cell G13 and select the Average function from the drop down of the Auto Sum (Ʃ) on the Home Tab.
13. Sunita wants to highlight scores of students who got 90 and above. Please suggest the best way to do it.
- She should select the cell range which contain the scores .
- Select Home > Conditional Formatting option.
- From the Highlight Cells Rules menu, she should select Greater Than,
- Enter the criteria as score of 90
- Select the formatting style to highlight all cells containing score of 90 and above.
14. Raman wants to show an excel sheet to his boss in his office. He want to hide some columns temporarily. How can he do it?
- Select the columns he wants to hide
- Click on Home > Cells > Format > Hide & Unhide > Hide Columns option.
15. On opening an Excel sheet sheet Suman finds that the columns between B and E are not visible. What should she do to view them?
- To make the columns visible, Suman should select the columns A and F,
- Click on Home > Cells > Format > Hide & Unhide > Unhide Columns .
16. Mohan is analyzing data in a worksheet with a large number of rows. While they are scrolling down the rows of data, the first row which has headings scrolls off the screen, causing them inconvenience. Which option of Excel will be useful in this case ?
He should freeze the row header by clicking on the View tab > Freeze Panes > Freeze Top Row.
17. Sohil wants to take printout of a worksheet on his company letter head which already has company address and logo printed on the top of the paper. When he prints the worksheet, top rows of the worksheet get printed over some part of the letter head logo. What can he do to ensure that the worksheet starts getting printed after leaving more space at the top ?
He can increase the top margin by selecting the page layout > Margins option. He can then select the Custom Margins option from the drop down list and adjust the margins according to his requirement .
18. Rani wants to view a large spreadsheet, some of the lower rows are not fitting on the screen. Which view can be used to see the entire worksheet on the screen ?
She can click on the Full Screen option present on the View tab
19. What is the advantage of using a cell name over a cell range ?
- It can be used to quickly locate specific cells.
- It is useful in large spreadsheets.
20. What are the steps to delete a range name ?
- Click on the Name Manager option in Formulas tab.
- In the Name Manager dialog box, select the range name
- Click the on delete button.
- Click Ok
21. What is a chart ? What are the advantage of charts ?
Chart is a graphical representation of data in a worksheet. It helps to provide a better understanding of the values and trends in data. They can be used to analyze the information.
22. Which are the two axes of a chart ?
The two axes of a chart are :
- CATEGORY AXIS :It is the horizontal axis of a chart and also called X axis.
- VALUE AXIS :It is the vertical axis used to plot values and also called Y axis.
23. How will you delete an excel chart
To delete a chart from the worksheet:
- Click anywhere on the chart to select it
- Press the delete Key.
24. How can you quickly format a chart title ?
- Right click on the chart title
- Select the Format Chart Title option from the shortcut menu.
- The Format Chart Title dialog box appears where you can modify the chart title by using the various formatting options.
25 .What are the pre- conditions for specifying the criteria before you use advance filter ?
- The criteria range must have column labels.
- There should be one blank row between the criteria values and the range.
26. Write the formula to find out the minimum value of cell B2 in each range of 4 worksheets from Sheet1 to Sheet4
Max (Sheet1:Sheet4!B2).
27. What is the use of sharing a workbook ?
By using this feature, multiple users can work on a workbook simultaneously and changes made by them will also be reflected otherwise multiple separate copies of the worksheet will be needed and it will be difficult to keep track of the changes made by all the users.
28. Simran uses cell ranges in various functions with category wise sales of Furniture but finds it difficult to remember the cell ranges. Can you suggest an easy way to remember the cell ranges ?
She can define meaningful range names for the cell ranges by selecting the cell ranges in each category and then define a name as Formula-> Define Name.
29. Akshay has done a survey of age wise literacy rates of a village as a school project ,which they want to show in a graphical form. They want to show the proportional number of literate age groups from the total of literate population. Which chart can they use and what options of Excel should they use ?
He can represent their data through a Pie chart.
30. Raman has a worksheet of student data which has field names: Name of student, class and section. He wants to display students of class 12 only. Kindly suggest the option to display the specific records ?
He can use the Autofilter feature of Excel. He can click on any cell in the worksheet, choose Data > Filter and select the value Veg from the drop- down list
31. Harish is working on an Excel worksheet in collaboration with 2 other colleagues on a report. Colleagues are working on a separate copy of the workbook and it is getting difficult to keep track of the changes being made. Please suggest a suitable solution to their problem.
He can share the workbook by taking following steps
- Click Review > Share workbook
- Changes in the Share workbook dialog box opens up
- Select the Editing tab
- Click the check box: Allow changes by more than one user at the same time. This also allows merging
- Click OK.
32 What is conditional formatting?
Using this feature, we can apply formatting to one or more cells depending upon some condition. Using conditional formatting, we can apply the following formatting effects :
- Number format, text, and Dates.
- Font, font style, and font colour.
- Border colour and border style.
- Fill colour and fill pattern.
33 Write steps to apply conditional formatting,
- Select the range of cells.
- From the Home Click on the Conditional formatting .
- A submenu appears which displays options like Greater than, Less than, Text that contains etc.
- We can select the criteria as per our requirement .
- Specify the value in the Criteria box
- Click on the down arrow next to the with field.
- A drop- down list containing predefined format appears.
- Select the suitable format.
34 Write steps to follow the steps below to Hide/Unhide the data in Excel :
- Select the rows that you want to hide.
- Click on the Home >Cells > Format option.
- A drop- down list will appears.
- Click on the Hide & Unhide > Hide Rows.
- To unhide the hidden rows, select the rows before and after the hidden rows and select the format> hide & unhide > Unhide Rows.
- Similarly, you can hide/unhide columns.
35 Write steps to Freeze the row / columns :
- Select the top row or the left column.
- Click on the freeze panes option in the window group on the view tab.
- A drop- down menu will appears .
- Clicking on the Freeze Panes option will freeze multiple rows/columns.
- To lock one row only, choose the Freeze Top Row option.
- To lock one column only, choose the Freeze First Column option.
36 Write steps to insert a page break in an Excel sheet.
- To add a horizontal page break, select the row below where the marking line will appear.
- Right- click on it and select the Insert page break option from the shortcut menu.
- To add vertical page break, select the column to the right where you want to insert the page break.
- Right-click on it, and select the insert page break option from the shortcut menu.
37 Write steps to delete a page break in an Excel sheet.
- Open the worksheet in the page break preview view.
- To delete the horizontal page break
- Select the row below where you want to delete the inserted page break.
- Cick on the breaks button present on the page layout tab and select the remove page break option from the drop-down menu.
- To delete the vertical page break :
- Select the column on the right where you want to delete the inserted page break.
- Click on the Breaks button present in the page setup group on the page layout tab and select the remove page break option from the drop-down menu.
- You can also right-click on the column on the right where the inserted page break is to be deleted and then select the remove page break option from the shortcut menu.
38. Write names of various page layout options in Excel.
The page layout option can be used to :
- Set Margins
- Set Orientation
- Set page headers and footers
- Hide or display gridlines
- Set size of the page
- Define the print area
- Specify the background
39. What is page margin? How can we set it?
Margin is the space between content and the edge of the page. To set margins we can take following steps:
- Switch to page layout view under the view tab.
- On the page layout tab, click on the margins command.
- A drop-down list with various options will be visible.
- Select the desired margin size from the drop-down menu. As you browser the menu, you are able to see the preview of how the page will look different margin settings after printing.
40. What are types of page orientation provided by Excel?
There are two types of page orientation in Excel:
Landscape orients the page horizontally. It is best for worksheets with a large number of columns.
Portrait orients the page vertically. It is helpful for worksheets with a large number of rows.
41. Write steps to change the page orientation :
- Click on the Orientation option on the Page layout tab.
- Choose either the Portrait or landscape option from the drop-down menu.
41 How can we set the Print Area in Excel?
- Select the range of cells that we want to print.
- Click on the Print area option on the page layout tab
- Select the set print area option from the drop-down list.
42 How can we clear the Print Area in Excel?
- Click on the Print area option on the page layout tab
- Clicking on the clear print area option from the drop-down menu of the Print area option in the page setup group.
43 How can we apply background colour to Excel worksheet?
- Switch to the Page layout view
- Click on the background option on the page layout tab.
- It will open a sheet background window.
- Select the background file .
- Click on the Insert button .
- The picture will be applied to the entire worksheet.
44 Write steps to Insert Header and Footer in Excel Worksheet.
- Open Page Layout view .
- Select ‘Click to add header’ for inserting a header at the top of the worksheet
- Select ‘Click to add Footer’ for Inserting a footer at the bottom of the worksheet.
- Click in the Header or Footer area.
- We can insert date, time and other information.
45. What are different page views in Excel and how can we set them?
i. NORMAL VIEW
This is the default view of the Excel worksheet commonly used to build the spreadsheet.
You can switch to Normal view :
- By clicking on the Normal option present in the workbook views group on the View tab. OR
- By clicking on the Normal button present on the Status bar.
ii. PAGE LAYOUT VIEW
This view gives the exact image of the worksheet as it appears on printing. We can open page layout view as:
- Click on the page layout option from the view tab.
OR
- Clicking on the page layout button present on the status bar.
iii. PAGE BREAK PREVIEW VIEW
This view is useful to view the positioning of all the pages. It also gives you the control in determining the page break positions. We can open page break preview view as:
- Click on the Page break Preview option from the view tab. OR
- Click on the page break preview button present on the status bar.
iv. FULL SCREEN VIEW
This view eliminates the Ribbon and the status bar and allows you to view the spreadsheet in full screen mode. We can open full screen view as: :
- Click on the full screen option from the view tab.
46 What do you mean by custom view in Excel? How can we create it?
Using Custom view feature of Excel we can customize different display settings (cell selections, filter settings etc.) and print settings (page settings, margins etc.). We can also include a specific print area in a custom view.
To create custom view :
- Set the display and print settings that you want to save in the custom view.
- Click on the custom views option present in the workbook views group on the view tab.
- The custom views dialog box will appear. Click on the
- Add button.
- The add view dialog box will appear.
- Specify the name for the view in the name text box.
- Click OK button .
47. How can we apply custom view?
- Click on the custom views option present in the workbook views group on the view tab.
- The custom views dialog box will appear.
- Select the desired custom view from the views list box
- Click on the show button.
48. How can we define a name for a range ?
- Select the range of cells that you want to name.
- On the formulas tab, in the defined names group.
- Click on the define name option.
- The new name dialog box appears.
- In the name field, enter the name that you want to specify for the range.
- Click on the OK button.
48. How can we use name range ?
- Click on a cell
- Write any excel function like sum(
- Click on the drop-down arrow of the Use in Formula option in the defined names group on the Formulas tab
- Select any range name already defined from the list.
- The name gets pasted in the formula.
- Closing the bracket to complete the formulas definition.
- Press the Enter Key to get the result in the cell.
49. How can we delete a Range Name?
- Click on the Name Manager option on the Formulas tab in the Defined Names group.
- The Name Manager dialog box appears showing all the named ranges in the worksheet.
- Select the range name that you wish to edit and click on the Delete button.
- Click on OK.