PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I

This PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I will help you in revision during exams.

PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I

Excel is Product of Microsoft:

An Excel Workbook consists of many worksheets to perform these calculations. A worksheet is made up of Rows and columns. Intersection of a Row and Column generate a cell.

PSEB 9th Class Computer Notes Chapter 2 MS Excel Part-I

Formatting Cells:

Each cell in a worksheet can be formatted. Changing the format of a cell doesn’t affect the cell value.
There are six tabs in the “Format Cells” window. All formatting options may be found on these tabs. Multiple cells can be formatted in one step by first selecting the cells and applying formatting.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 1The “Format Cells” window can be opened in from the right-click menu. Formatting options are available on the Home Tab on the Font, Alignment, and Number groups.

Merge and Centre

Merging cells is used when a text is to be centered over a particular section of a spreadsheet. When a group of cells is merged, then the text of this cell is merged as per selection and aligned center.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 2
Following are the steps:

  1. Type your data in your worksheet.
  2. Highlight or select a range of cells.
  3. Right-click on the highlighted cells and select Format Cells. Format Cells dialog box will open.
  4. Click the Alignment tab of Format the checkbox labeled Merge cells as

To merge a group of cells and center the text, we can also use the Merge and Center button on the Excel tool bar.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 3
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 4
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 5

Steps:
1. Highlight or select a range of cells. Click the Merge and Center button on the toolbar.
Clicking this button will automatically merge our highlighted cells and center the cell value.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 6

PSEB 9th Class Computer Notes Chapter 2 MS Excel Part-I

Numbers Group

A number format does not affect the actual cell value that Excel uses to perform calculations. The actual value is displayed in the formula bar. By applying different number formats, we can display numbers as percentages, dates, currency, and so on.

Number Formats available in MS Excel:

PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 7

Styles in MS Excel:

A style is just a set of cell formatting settings. All cells to which a style has been applied look the same according to formatting. When we change a part of a style, all cells to which that style has been applied also change their formatting accordingly to new style.

Conditional Formatting

Conditional Formatting is a tool in MS Excel that allows applying formats to a cell or range of cells. It also allows formatting change depending on the value of the cell or the value of a formula.

PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 8

Formatting as Table

Tables can help to organize our content and make it easier for us to find the information we need.
To format information as a table:
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 9

1. Type the data in worksheet:

A B C D E
1. Code Name Colour Unit Price Unit Cost
2. ABC123 Widget Red 10.15 7.18
3. ABC124 Widget Green 10.9 6.981
4. ABC125 Widget Blue 10.56 7.31
5. ABC 126 Gadget Red 12.45 8.22
6. ABC 127 Gadget Green 13.61 8.91

2. Select the cells we want to format as a table.
3. Click the Format as Table command in the Styles group on the Home tab.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 10
4. A list of predefined table styles will appear. Click a table style to select it.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 11
5. A dialog box will appear confirming the range of cells we have selected for our table. The cells will appear selected in the spreadsheet and the range will appear in the dialog box.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 12
6. If necessary, change the range by selecting a new range of cells directly on your spreadsheet.
7. If our table has headers check the box next to My table has headers.
8. Click OK. The data will be formatted as a table.

PSEB 9th Class Computer Notes Chapter 2 MS Excel Part-I

Cell Styles:

When we want to format cells in Microsoft Excel, we can do it manually either by selecting fonts, font color and size, background colors and borders, or we can do the formatting quickly and automatically using styles.

Microsoft Office Excel has several built-in cell styles that we can apply or modify. We can also modify or duplicate a cell style to create our own such as custom cell style.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 13
Cell styles are based on the document theme that is applied to the whole workbook.

Applying a cell style:

1. Type the data in our worksheet
2. Select the cells that we want to format.
3. On the Home tab, in the Styles group, click Cell Styles.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 14
Click the cell style that we want to apply. Our data will be changed according to our selected style.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 15

Cell Group:

To insert new cells, rows, or columns in an Excel worksheet, follow these steps:
1. Select the cells, rows, or columns where we want the new blank cells to appear.
2. Click the drop-down arrow attached to the Insert button in the Cells group of the Home tab.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 16
4. Click Delete Cells on the drop-down menu.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 17
The Delete dialog box opens, showing these options for filling in the gaps:

How to Insert New Worksheets?

As we can add new cells/row/columns in our existing worksheet, we can also add a new worksheet in our current workbook.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 18

How To Delete Worksheets/worksheet?

A Single Worksheet or Worksheets can be deleted from a workbook, including those containing data.
1. Select the worksheet/worksheets we want to delete.
2. Right-click one of the selected worksheets. (The worksheet menu appears)
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 19
3. Select Delete. The selected worksheets will be deleted from our workbook.

PSEB 9th Class Computer Notes Chapter 2 MS Excel Part-I

Cell Size:

We can modify size of cells according to our requirement. We will learn how to change row height and column width.

How to modify column width?

1. Place our mouse over the column line in the column heading so the white cross becomes a double arrow.
2. Click and drag the column to the right to increase column width or to the left to decrease column width.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 20
3. Release the mouse. The column width will be changed in your spreadsheet.

How to set column width with a specific measurement?

1. Select the columns we want to modify.
2. Click the Format command on the Home tab. The format drop-down menu appears.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 21
3. Select Column Width.
4. The Column Width dialog box appears. Enter our specific measurement.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 22
5. Click OK. The width of each selected column will be changed in our worksheet.

How to modify row height?

1. Place the cursor over the row line so the white cross becomes a double arrow.
2. Release the mouse. The height of each selected row will be changed in our worksheet.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 23

How to set row height with a specific measurement?

1. Select the rows we want to modify.
2. Click the Format command on the Home tab. The format drop-down menu appears.
3. Select Row Height.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 24
4. The Row Height dialog box appears. Enter a specific measurement.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 25
5. Click OK.

PSEB 9th Class Computer Notes Chapter 2 MS Excel Part-I

Formulas & Functions:

To maximize the capabilities of Excel, it is important to understand how to create simple formulas.
Creating simple formulas:
Excel uses standard operators for equations, such as a plus sign for addition (+), minus sign for subtraction (-), asterisk for multiplication (*), forward slash for division (/), and caret (A) for exponents. All formulas must begin with an equals sign (=).

To create a simple formula in Excel:
1. Select the cell where the answer will appear.
2. Type the equals sign (=).
3. Type in the formula we want Excel to calculate.
4. Press Enter. The formula will be calculated, and the value will be displayed in the cell.

Creating formulas with cell references:

When a formula contains a cell address, it is called a cell reference. Creating a formula with cell references is useful because you can update data in our worksheet without having to rewrite the values in the formula.

To create a formula using cell references :

  1. Select the cell where the answer will appear.
  2. Type the equals sign (=).
  3. Type the cell address that contains the first number in the equation.
  4. Type the operator we need for our formula. For example, type the addition sign (+).
  5. Type the cell address that contains the second number in the equation.
  6. Press Enter. The formula will be calculated, and the value will be displayed in the cell.

Edit a Formula:

A formula in excel can be edited as per requirement.

  1. Click the cell we want to edit.
  2. Insert the cursor in the formula bar and edit the formula as desired. We can also double-click the cell to view and edit the formula directly from the cell or press F2 key.
  3. When we’re done, press Enter or select the Enter command.

Cell Reference:

Cell Reference is termed to calculate important calculations by using a cell or a range of cells for a formula to calculate the result of the formula in a worksheet. We can use a cell reference for a single formula or for multiple formulas.

PSEB 9th Class Computer Notes Chapter 2 MS Excel Part-I

Types of Cell Reference

  1. Relative Reference.
  2. Absolute Reference.
  3. Mixed Reference.

1. Relative Reference:

In Excel Relative reference is used by default. When it is copied to multiple cells then it changes according to cell position.
1. Type data in a worksheet.
2. Now type our formula in cell B1 = A1 * 10.
3. Drag the fill handle of cell Bl, we will see that the formula becomes in celi B2 = A2 * 10.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 26

2. Absolute Reference:

Sometimes we want that during copying a formula from, one cell to another, its cell reference should not be changed. In this case Absolute Reference is used. Dollar($) sign is used during typing a formula using Absolute Reference. Dollar($) sign can be used either for a row or a column. We can also use it for both together.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 27
1. Type data in a worksheet.
2. Now type our formula in cell B1=$A$1 + 5
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 28
3. Drag the fill handle of cell C1, we will see that the formula becomes in cell B2 = $A$1 + 5.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 29

3. Mixed Reference:

Mixed Reference is the combination of both Relative and Absolute Reference. In Mixed Reference a Dollar($) sign is used either to a Row or Column.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 30

Basic functions:

A function is a predefined formula that performs calculations using specific values in a particular order. They can save our time because we do not have to write the formula yourself. Excel has hundreds of functions to assist with our calculations.

The parts of a function

The order in which we insert a function is important. Each function has a specific order – called syntax – which must be followed in order for the function to work correctly.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 31
1. First of all equal to (=) sign is written.
2. After this the function name is written.
3. After this argument is written. Arguments contain the information we want the formula to calculate, such as a range of cell references.

PSEB 9th Class Computer Notes Chapter 2 MS Excel Part-I

Working with arguments:

Arguments are a vital-part of a Function.
1. Arguments must be enclosed in parentheses.
2. If there are Individual values or cell references inside the parentheses are separated by either colons or commas. Commas separate individual values, cell references, and cell ranges in parentheses.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 32
3. If there is a ceil range in argument then it is written with colon in braces. Colons create a reference to a range of cells.

To create a basic function in Excel

1. Select the cell where the answer will appear (J3, for example).
2. Type the equals sign (=), then enter the function name (SUM, for example).
3. Enter the cells for the argument inside the parentheses.
4. Press Enter, and the result will appear.

Using AutoSum to select common functions

The AutoSum command allows us to automatically return the results for a range of cells for common functions like SUM and AVERAGE.
1. Select the cell where the answer will appear.
2. Click the Home tab.
3. In the Editing group, click the AutoSum drop-down arrow and select the function we want.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 33
4. A formula will appear the selected cell.
5. Press Enter, and the result will appear.

1. Text Functions:

  • Clean: This Function removes all non-printable characters from asupplied text string.
  • Trim: This Function removes duplicate spaces, and spaces at thestart and end of a text string
  • Concatenate: This Function Joins together two or more text strings
  • Left: This Function returns a specified number of characters fromthe start of a supplied text string
  • Mid: This Function Returns a specified numberfrom the middle of a supplied text string
  • Right: This Function Returns a specified numberfrom the end of a supplied text string

2. Logical Functions:

IF: This Function tests a user-defined condition and returns one result if the condition is TRUE, and another result if the condition is FALSE

3. Date and Time Functions:

  • Date: This Function returns a date, from a user-supplied year,month and day
  • Time: This Function returns a time, from a user-supplied hour, minute and second
  • Now: This Function returns the current date & time
  • Today: This Function returns today’s date

The Function Library:

To insert a function from the Function Library:
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 34
1. Type data in our worksheet.
2. Select the cell where the answer will appear.
3. Click the Formulas tab.
4. From the Function Library group, select the function category we want. In this example, we’ll choose Date & Time.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 35
5. Select the desired function from the Date & Time drop-down menu
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 36
6. The Function Arguments dialog box will appear.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 37
7. Click OK, and the result will appear.

Date Ordered Date Received
12-Sep 17-Sep 5

PSEB 9th Class Computer Notes Chapter 2 MS Excel Part-I

Sorting:

Sorting data in Excel basically means that we can arrange the data according to some specific criteria. We can even arrange data alphabetically:
To sort in alphabetical order:
1. Type data in our worksheet.
2. Select a cell in the column we want to sort by.
3. Select the Data tab, then locate the Sort and Filter group.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 38
4. Click the ascending command to Sort A to Z or the descending command to Sort Z to A.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 39
5. The data in the spreadsheet will be organized alphabetically.
Filtering data: Filter is a tool in MS Excel that is used to get the information according to our requirement. When we need to find special information from a list, then we use Filter. Filters can be applied in different ways to improve the performance of our worksheet. We can filter text, dates, and numbers. We can even use more than one filter to further narrow our results.

Steps:

1. Type the data in a worksheet.
2. Select the Data tab, and then locate the Sort & Filter group.
3. Click the Filter command.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 40
4. Drop-down arrows will appear in the header of each column.
5. Click the drop-down arrow for the column we want to filter.
6. The Filter menu appears.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 41
7. Uncheck the boxes next to the data we don’t want to view, or uncheck the box next to Select All to quickly uncheck all.
8. Check the boxes next to the data we do want to view.
9. Click OK.
To clear a filter: We can clear a filter very easily.
1. Click the drop-down arrow in the column from which we want to clear the filter.
2. Choose Clear Filter From.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 42
3. The filter will be cleared from the column. The data that was previously hidden will be on display once again.

PSEB 9th Class Computer Notes Chapter 2 MS Excel Part-I

Find and Replace

Excel Find and Replace feature are powerful tools that we can use for special criteria such as to Find a text and to Replace it with our new text.

How to use Find Option:

Following are the steps to locate data in a worksheet:
1. Choose Find & Select in the Editing group on the Home tab, and then select Find (or press Ctrl+F).The Find and Replace dialog box appears with the Find tab on top.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 43
2. In the Find What box, enter the data we want to locate.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 44
3. Click the Options button to expand the dialog box.
1. Within: It searches just the current worksheet or the entire workbook.
2. Search: It selects whether to search first across the rows or down the columns.
3. Look In: It selects whether we want to search through the values or formula results, through the actual formulas, or if we want to look in the comments.
4. Match Case: It checks this box if we want our search to be case-specific.
List only the items that exactly match our search criteria.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 45
3. Click Find Next.
Excel jumps to the first occurrence of the match.
4. Click Close when we’ve located the entry we want.
Using Replacing data Option: Replace option is used to change data according to our requirement. We can change each entry of a cell while typing on, but it require more time and labour so we can do it easily using Replace option.
1. Choose Find & Select in the Editing group on the Home tab, and then select Replace (or press Ctrl+H).The Find and Replace dialog box appears with the Replace tab on top.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 46
2. In the Find What box, enter the data we want to locate.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 47
3. In the Replace With box, enter the data with which we want to replace the found data.
4. Click the Options button and specify any desired options.
PSEB 9th Class Computer Notes Chapter 1 MS Excel Part-I 48
Click Find Next to locate the first occurrence or click Find All to display a list of all occurrences.
Click OK in the alert box and then click Close.