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.

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.
The “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.

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.

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.

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:

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.

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:

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.

4. A list of predefined table styles will appear. Click a table style to select it.

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.

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.

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.

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.

Click the cell style that we want to apply. Our data will be changed according to our selected style.

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.

4. Click Delete Cells on the drop-down menu.

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.

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)

3. Select Delete. The selected worksheets will be deleted from our workbook.

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.

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.

3. Select Column Width.
4. The Column Width dialog box appears. Enter our specific measurement.

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.

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.

4. The Row Height dialog box appears. Enter a specific measurement.

5. Click OK.

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.

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.

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.

1. Type data in a worksheet.
2. Now type our formula in cell B1=$A$1 + 5

3. Drag the fill handle of cell C1, we will see that the formula becomes in cell B2 = $A$1 + 5.

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.

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.

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.

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.

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.

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:

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.

5. Select the desired function from the Date & Time drop-down menu

6. The Function Arguments dialog box will appear.

7. Click OK, and the result will appear.

Date Ordered	Date Received
12-Sep	17-Sep	5

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.

4. Click the ascending command to Sort A to Z or the descending command to Sort Z to A.

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.

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.

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.

3. The filter will be cleared from the column. The data that was previously hidden will be on display once again.

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.

2. In the Find What box, enter the data we want to locate.

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.

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.

2. In the Find What box, enter the data we want to locate.

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.

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.