Session 1:
Use AUTOSUM in Cells Autosum is used for adding the values given in cells automatically without writing the formula to perform the operation. Auto Sum Option is presented in Editing Subgroup at HOME tab
Note: Autosum automatically selects the values around the cells either horizontally or vertically. In the above example you want to perform addition of numbers listed from left to right, after finding the total of two persons when you try to perform autosum for another person the values on top of the current cell get selected. In that case, you have to select the cell values that you want to perform addition.
SESSION 2 :
CONDITIONAL FORMATTING
Conditional formatting allows you to change the formatting (font color, border, shading) of the cells based on the values in it.
Specify a condition for the values in the cell, and if the condition is satisfied then automatically the formatting of the cell changes.
You can select one or more cells, and create rules (conditions) for when and how those cells are formatted. The conditions can be, based on the selected cell’s contents, or based on the contents of another cell.
Conditional Formatting controls the following formats:
• Number format
• Font, font style, and font colour (but not font size)
• Fill colour and fill pattern
• Border colour and border style (but not border thickness)
Applying Conditional Formatting:-
Select all the cells you want to format conditionally.
Click on Conditional Formatting option available under Styles group in the Home tab. a drop down list appears. Click on Highlight Cells Rules option, a submenu appears. A dialog box appears.
Specify the value in the box and click on the down arrow next to the with field and select a predefined format for the condition from the drop down list. If you want to highlight the cell contents with your own formatting style click Custom Format option in the dropdown list.
SESSION 3:
HIDE/UNHIDE/FREEZ ROWS AND COLUMNS Sometimes when we have data in rows and columns which are needed for formulae or chart, but don’t want to show to the user, we use Hide or Unhide row or column.
To hide a row or column:-
In order to hide any row/column, select particular row or column that is to hidden
Click on FORMAT option available under Cells group in Home tab
A drop down list appears Click on Hide & Unhide > Hide Rows
Selected row will be hidden
Note: In the same way you can hide a column or even a sheet also.
To unhide a row or column:-
Select particular data from which you want to unhide a row/column
Go to the Format option available under Cells group in Home tab
Select Hide & Unhide option from Visibility section in the drop down list
Click on Unhide Row or Unhide Column
Hidden row or column will be unhide.
Note: In the same way you can hide or unhide a sheet also.
Freezing:- When you are dealing with a huge amount of data that spans several rows or columns, you may want the headers to remain constant (frozen) while you scroll through the data so that you can see the identifying names of the rows/columns.
To Freeze the row or column:-
In Order to freeze rows or columns click on Freeze Panes option under Windows group in View Tab
A drop down list appears
It has three options
1. Freeze panes: Doing this all rows above and all the columns to the left of the selected cell will be freeze
2. Freeze Top Row: It will freeze the top row visible while scrolling through the rest of the worksheet.
3. Freeze First Column: It will freeze the first column. Select appropriate option as per choice.
Session 4:
SET PAGE BREAKS
To print a worksheet with the exact number of pages that you want, you can adjust the page breaks in the worksheet before printing it.
This feature is very useful especially when printing huge sheets.
You can use the Page Break view to see how other changes (e.g. changing row height / column width) affect the automatic page breaks.
Inserting Page Break:-
Click the worksheet that you want to print.
On the View tab, in the Workbook Views group, click Page Break Preview.
Do one of the following: ...
1. To insert a vertical page break, select the row below where you want to insert the page break.
2. To insert a horizontal page break, select the column to the right of where you want to insert the page break.
On the Page Layout tab, in the Page Setup group, click Breaks.
Click Insert Page Break
SESSION 5:
SET PAGE LAYOUT
Spreadsheet allows layout option through Page Layout option. This option enables you to view and control the way Excel sheets will look like on a printed page. It includes page orientation, margin size, and others.
The Page Layout option can be used to:-
Set Margins
Set Page Header and Footer
Set Size of the page
Specify the background
Set Orientation
Hide or displays gridlines
Define the Print Area
Setting Page Margins
A Margin is the space between your content and the edge of the page. By, Default NORMAL margin settings are applied to an Excel sheet. In NORMAL margin “one-inch” white space between the content and edge of the page is applied.
Steps:-
1. Switch to Page Layout view under View Tab
2. On the Page layout tab, click on the Margins command
3. A drop-down list appears with various options
4. Select the desired margine size from the list
OR
4. Click on Custom Margin and set it as per your requirements. Header and Footer A Header is the information which appears on the top of every page and Footer will displays at Bottom side on every page.
To Insert Header and Footer:-
1. Switch to the Page Layout view under View tab.
2. Click on the Header or footer area
3. Write page numbers or any information in it.
4. It has 3 sections in which different elements can be placed, like Date, Page Number, File Name etc.
5. Design tab will open when any information will be added in Header or Footer section. Setting The
Paper Size: The paper size option lets you select the appropriate paper size according to the printing need.
Set Paper size:-
1. Click on the SIZE option on the Page Layout tab
2. Select the appropriate page size from the drop down list.
3. The new page size will be applied.
Applying Background :-
This option on the Page Setup group will add a background image to the excel worksheet.
Steps:-
1. Switch to the Page Layout view
2. Click on the Background option on the Page Layout tab
3. Sheet Background window allows to select the picture which you want to insert
4. Click on the Insert button, Selected image will be added to the sheet background
5. To remove background, click on Delete Background option from the Page Setup group on the Page Layout Tab.
NOTE: Sheet Background is applied for the Display purpose only and it does not get printed.
Page Orientation:- Excel offers two types of page orientation
1. Landscape
2. Portrait
Landscape orients the page horizontally while Portrait orients the page vertically
Steps :-
1. Click on the orientation option on the Page Layout tab in Page Setup group.
2. Select either the Portrait or Landscape option from the drop down menu
3. The page orientation of the worksheet will change as per your selection.
NOTE: Portrait will show more rows and less columns where as Landscape shows less rows and more columns.
Gridlines:- 1. Go to the sheet options group on the Page Layout tab and select the VIEW check boxes under Gridlines and Headings.
2. If it will be unchecked then gridlines will not work on excel sheet and will not be visible. PRINT AREA:- Once you select a print area, only that part of the worksheet will be printed.
Steps:- 1. Select Page Layout view button on the Status Bar
2. Click on the Print area option on the page layout tab and select the Set Print Area option from the drop down list 3. A dashed line appears around the selected cells indicating that the area has been sets as the Print Area.
SESSION 6: MANAGE WORKBOOK VIEWS
Excel provides 5 different views to see the spreadsheet differently.
1. Normal View
2. Page Layout View
3. Page Break Preview View
4. Custom View
5. Full Screen View
These options are in WORKBOOK VIEWS sub group on View Tab. Normal View:-
This is the default view of the Excel worksheet and it is commonly used to build the spreadsheet.
Switching to Normal View :-
1. Click on Normal option present in the Workbook Views group on the View tab.
Page Layout View:- This view gives the exact image of the worksheet as it would appear on printing.
Switching to Page Layout View :-
1. Click on Page layout View present in Workbook Views group on the View tab.
Page Break View :- This view is useful in viewing the positioning of all the pages. It also gives you the control in determining the page break positions.
Switching to Page Break View:-
1. Click on Page Break View present in Workbook Views group on the View tab.
Full Screen View:- This view eliminates the Ribbon and the Status bar and allows you to view the spreadsheet in full screen.
Switching to Full Screen View:- 1. Click on Full Screen View present in Workbook Views group on the View tab. Custom View Excel also allows you to customize different display settings and print settings as per the requirements for a worksheet. To create custom view click on custom view and click on Add button to create your own view.
SESSION 7: APPLY CELL AND RANGE NAMES
1. Naming of cell or range of cells is a much more user friendly technique than working with cell coordinates.
2. We can use it for quickly locating the specific cells by entering their names.
3. This could be useful when working with large spreadsheets.
Defining 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
New Name dialog box appears.
In the New Name field, enter the name for range.
In the Scope combo box, specify the availability of the range name by choosing Workbook
Click OK button, Defined name range is ready to use.
Managing Range Names:-
Click on the Name Manager option on the Formulas tab in the Defined Name group.
Name Manager dialog box appears.
Now change the name scope, or cell range of the named range, by clicking on the Edit button.
Click OK to make modifications.
Delete a Range Name:-
Click on Name Manager option on the Formulas tab in the Defined Names group.
Select the range name and click on Delete button, Range name will be deleted.
IMP : Range Names are not case sensitive. The first character must be a letter, an underscore or a backslash. The range name must not be the same as a cell address.
SESSION 8: Create, Modify and format Charts
A chart is a graphical representation of data in a worksheet. It helps to provide a better understanding of large quantities of date. It make it easier to draw comparison, see growth, and relationship amoung the values and trends in data.
Creating Chart
Select the range of cells from the worksheet that contains the source data for the chart.
Click on Insert tab and select Charts group.
Click on any chart type, select the desired layout from the Chart Layout group on the Design tab.
All the titles, data labels, legend details mentioned in the selected data range will be displayed in the chart.
Chart Elements:
1. CHART AREA: It includes all the area and objects in the chart
2. CATEGORY AXIS: It is also called X-Axis is the horizontal axis of the chart.
3. VALUE AXIS: It is also called Y-axis is the vertical axis used to plot the value,located at left side.
4. DATA Series: These are the bars, slices or other elements that show the date value.
5. CATEGORY NAME: These are labels, which are displayed on the X & Y-axis
6. PLOT AREA: It is a window within the Chart area. It contains the actual chart itself and includes plotted data,data series, category and value axis.
7. LEGEND: It depicts colours, patterns or symbols assigned to the data series. It helps to differentiate the data.
8. CHART TITLE: It describe the aim and contents of the chart.
9. GRIDLINES: These can either Horizontal or Vertical lines depending on the selected chart type.
Types of Charts:-
1. COULMNS CHART: This chart is used for displaying data that shows different trends over time.It is default chart.
2. Bar Chart: It illustrates the data comparison between individual items. It displays data in the form of long rectangular rods called bars.
3. LINE CHART: It is in the form of lines and is used to illustrate trends in data at equal intervals.
4. PIE CHART: This chart displays data in the form of a circle that is divided into a series of segments.
5. AREA CHART: It is a unique version of line or column graph. This type of chart has a greater visual impact than a Line chart.
6. XY Chart: This chart shows the relationship in several data series or plots two group of numbers as one series of XY co-ordinate.
Moving, Resizing and Deleting Charts in Excel:-
1. First select the chart by clicking on it.
2. To Move a Chart place the cursor on the Chart Area. Four headed arrow appears, press and drag it in order to move the chart in any direction.
3. To Resize the Chart , position the mouse pointer on any of the corner border of the chart. Pointer will change to a double headed arrow. Drag the handle in desired direction to change the size accordingly.
4. To Delete a Chart from the worksheet, click anywhere on the chart to select it and press the Delete button.
Formatting a Chart:-
1. Changing the PLOT area background:-
i. Right click on the Plot area and select FORMAT PLOT AREA option.
ii. Select “Border Styles”, “Border Colour”, “shadow” etc.
iii. By default the FILL tab is selected. Select the Solid Fill option from the right pane.
iv. Click on Color button and select the desired colour from the drop-down menu.
2. Inserting the Chart Title:-
i. Select the chart.
ii. Click on Chart Title button in the Labels group on the Layout tab.
iii. Select the desired option from the drop-down list of options
iv. If you select Above Chart option, A text box will appear right above the chart with Chart Title, Click inside the box and write the chart title.
Session 9 : SORT AND FILTER DATA
Sorting means arranging the given data according to a specific field wither in the increasing or decreasing order in a worksheet
Steps for sorting Data:-
1. Click on the particular Field Name
2. Now click on SORT button in the Sort & Filter group on the Data tab.
3. Select the Largest To Smallest option in the Order section. By default Smallest To Largest option is selected and My data has headers checkbox is tick marked.
4. Click OK, Data will be arranged in the descending order on the basis of selected field.
Filter option allows you to see only those records that you want to see.
We can filter list of records that meet specific criteria by using Auto Filter option
Steps to use Filter
1. Select a particular field, on which you want to do filter.
2. Click on Filter button in the Sort & Filter group on the Data tab.
3. Click on the down arrow available next to the field name, Select the filter by specifying the value from the list and click OK.
4. The list gets filtered and displays only those records that contain the specified range.
Spreadsheet Notes Class 10 IT 402 Code | IT 402 CODE | IT Notes class 10
0 Comments