bishop scott boys’ schoolbishopscottboysschool.com/en/wp-content/uploads/... · page 1 of 21...

of 21

BISHOP SCOTT BOYS’ SCHOOL (Affiliated to CBSE, New Delhi) Affiliation No.: 330726, School Campus: Chainpur, Jaganpura,

By-Pass, Patna 804453. Phone Number: 7061717782, 9798903550. , Web: www.bishopscottboysschool.com Email: [email protected]

STUDY COURSE MATERIAL - 6

COMPUTER SESSION-2020-21

CLASS- VI

TOPIC: CHAPTER – 6 [MICROSOFT EXCEL 2010]

DAY-1

TEACHING MATERIAL: -

INTRODUCTION

Spreadsheets that have not been formatted can be difficult to read. Formatted text

and cells can draw attention to specific parts of the spreadsheet and make the

spreadsheet more visually appealing and easier to understand.

In Excel, there are many tools you can use to format text and cells. In this lesson, you

will learn how to change the color and style of text and cells, align text, and apply special formatting to numbers and dates.

http://www.bishopscottboysschool.com/

mailto:[email protected]

of 21

Formatting text

Many of the commands you will use to format text can be found in the Font,

Alignment, and Number groups on the Ribbon. Font commands let you change the

style, size, and color of text. You can also use them to add borders and fill colors to

cells. Alignment commands let you format how text is displayed across cells both

horizontally and vertically. Number commands let you change how selected cells

display numbers and dates.

To change the font:

1. Select the cells you want to modify.

2. Click the drop-down arrow next to the Font command on the Home tab.

The font drop-down menu appears.

3. Move your mouse over the various fonts. A live preview of the font will

appear in the worksheet.

4. Select the font you want to use.

of 21

To change the font size:


2. Click the drop-down arrow next to the font size command on the Home

tab. The font size drop-down menu appears.

3. Move your mouse over the various font sizes. A live preview of the font size

will appear in the worksheet.

4. Select the font size you want to use.

You can also use the Grow Font and Shrink Font commands to change the size.

To use the bold, italic, and underline commands:


2. Click the Bold, Italic, or Underline command on the Home tab.

of 21

To add a border:


2. Click the drop-down arrow next to the Borders command on the Home tab.

The border drop-down menu appears.

3. Select the border style you want to use.

You can draw borders and change the line style and color of borders with the Draw

Borders tools at the bottom of the Borders drop-down menu.

To change font color:


2. Click the drop-down arrow next to the font color command on the Home

tab. The color menu appears.

3. Move your mouse over the various font colors. A live preview of the color


of 21

4. Select the font color you want to use.

Your color choices are not limited to the drop-down menu that appears. Select More

Colors at the bottom of the menu to access additional color options.

To add a fill color:


2. Click the drop-down arrow next to the fill color command on the Home

tab. The color menu appears.

3. Move your cursor over the various fill colors. A live preview of the color


of 21

4. Select the fill color you want to use.

To change horizontal text alignment:


2. Select one of the three horizontal Alignment commands on the Home tab.

o Align Text Left: Aligns text to the left of the cell

o Center: Aligns text to the center of the cell

o Align Text Right: Aligns text to the right of the cell

To change vertical text alignment:


2. Select one of the three vertical Alignment commands on the Home tab.

o Top Align: Aligns text to the top of the cell

o Middle Align: Aligns text to the middle of the cell

o Bottom Align: Aligns text to the bottom of the cell

of 21

By default, numbers align to the bottom-right of cells, while words and letters align to

the bottom-left of cells.

Using the Format Painter

If you want to copy formatting from one cell to another, you can use the Format

Painter command on the Home tab. When you click the Format Painter, it will copy all

of the formatting from the selected cell. You can then click and drag over any cells you

want to paste the formatting to.

of 21

DAY-2


Formatting numbers and dates

One of Excel's most useful features is its ability to format numbers and dates in a

variety of ways. For example, you might need to format numbers with decimal places,

currency symbols ($), or percent symbols (%).

To format numbers and dates:


2. Click the drop-down arrow next to the Number Format command on the

Home tab.

3. Select the number format you want. For some number formats, you can then

use the Increase Decimal and Decrease Decimal commands (below the

Number Format command) to change the number of decimal places that are

displayed.

Click the buttons in the interactive below to learn about the different number formats.

of 21

Challenge! 1. Open an existing Excel 2010 document. If you want, you can use

this example.

2. Select a cell and change the font, size, or color of the text. If you are using

the example, change the title in cell A1 to the Verdana font, size 16, with a

font color of green.

3. Select a portion of the content in one cell, and make only that

portion bold. For example, if the words Weight (pounds) are in a cell, make

only the word Weight bold.

4. Add a border to a group of cells. If you are using the example, add a border

to the descriptions in row 3.

5. Change the fill color of a group of cells. If you are using the example, add a

fill color to row 3.

https://media.gcflearnfree.org/ctassets/topics/175/Excel2010_FormatCells_Practice.xlsx

of 21

6. Try changing the vertical and horizontal text alignment for some cells.

7. Try changing the formatting of a number. If you are using the example,

change the date format in column A.

MS Excel Cell can hold different types of data like Numbers, Currency, Dates, etc. You

can set the cell type in various ways as shown below −

Right Click on the cell » Format cells » Number.

Click on the Ribbon from the ribbon.

Various Cell Formats

Below are the various cell formats.

General − This is the default cell format of Cell.

Number − This displays cell as number with separator.

Currency − This displays cell as currency i.e. with currency sign.

Accounting − Similar to Currency, used for accounting purpose.

Date − Various date formats are available under this like 17-09-2013, 17th-Sep-2013, etc.

Time − Various Time formats are available under this, like 1.30PM, 13.30, etc.

Percentage − This displays cell as percentage with decimal places like 50.00%.

of 21

Fraction − This displays cell as fraction like 1/4, 1/2 etc.

Scientific − This displays cell as exponential like 5.6E+01.

Text − This displays cell as normal text.

Special − Special formats of cell like Zip code, Phone Number.

Custom − You can use custom format by using this.

DAY-3


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 (^) for exponents.

The key thing to remember when writing formulas for Excel is that all formulas must

begin with an equals sign (=). This is because the cell contains—or is equal to—the formula and its value.

of 21

To create a simple formula in Excel:

1. Select the cell where the answer will appear (B4, for example).

2. Type the equals sign (=).

3. Type in the formula you want Excel to calculate (75/250, for example).

4. Press Enter. The formula will be calculated, and the value will be displayed

in the cell.

If the result of a formula is too large to be displayed in a cell, it may appear as pound

signs (#######) instead of a value. This means the column is not wide enough to

display the cell content. Simply increase the column width to show the cell content.

of 21

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 your worksheet without

having to rewrite the values in the formula.

To create a formula using cell references:



3. Type the cell address that contains the first number in the equation (B1, for

example).

4. Type the operator you need for your formula. For example, type the addition

sign (+).

5. Type the cell address that contains the second number in the equation

(B2, for example).


in the cell.

of 21

If you change a value in either B1 or B2, the total will automatically recalculate.

Excel will not always tell you if your formula contains an error, so it's up to you to

check all of your formulas. To learn how to do this, you can read the Double-Check

Your Formulas lesson from our Excel Formulas tutorial.

To create a formula using the point-and-click method:



3. Click the first cell to be included in the formula (A3, for example).

http://www.gcflearnfree.org/excelformulas/doublecheck-your-formulas/1/

http://www.gcflearnfree.org/excelformulas/doublecheck-your-formulas/1/

http://www.gcflearnfree.org/excelformulas

of 21

4. Type the operator you need for the formula. For example, type

the multiplication sign (*).

5. Click the next cell in the formula (B3, for example).


in the cell.

To edit a formula:

1. Click the cell you want to edit.

2. Insert the cursor in the formula bar, and edit the formula as desired. You

can also double-click the cell to view and edit the formula directly from

the cell.

3. When you're done, press Enter or select the Enter command .

of 21

4. The new value will be displayed in the cell.

If you change your mind, use the Cancel command in the formula bar to avoid accidentally making changes to your formula.

DAY-4


The following example demonstrates how to use the order of

operations to calculate a formula:

of 21

The parts of a function

The order in which you 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. The basic syntax to create a formula with a function is to insert an equals

sign (=), function name (SUM, for example, is the function name for addition),

and argument. Arguments contain the information you want the formula to calculate,

such as a range of cell references.

of 21

Built In Functions

MS Excel has many built in functions, which we can use in our formula. To see all the

functions by category, choose Formulas Tab » Insert Function. Then Insert function

Dialog appears from which we can choose the function.

Functions by Categories

Let us see some of the built in functions in MS Excel.

Text Functions

o LOWER − Converts all characters in a supplied text string to lower case

o UPPER − Converts all characters in a supplied text string to upper case

o TRIM − Removes duplicate spaces, and spaces at the start and end of a text string

o CONCATENATE − Joins together two or more text strings.

o LEFT − Returns a specified number of characters from the start of a

supplied text string.

o MID − Returns a specified number of characters from the middle of a

supplied text string

o RIGHT − Returns a specified number of characters from the end of a

supplied text string.

of 21

o LEN − Returns the length of a supplied text string

o FIND − Returns the position of a supplied character or text string from

within a supplied text string (case-sensitive).

Date & Time

o DATE − Returns a date, from a user-supplied year, month and day.

o TIME − Returns a time, from a user-supplied hour, minute and second.

o DATEVALUE − Converts a text string showing a date, to an integer that

represents the date in Excel's date-time code.

o TIMEVALUE − Converts a text string showing a time, to a decimal that represents the time in Excel.

o NOW − Returns the current date & time.

o TODAY − Returns today's date.

Statistical

o MAX − Returns the largest value from a list of supplied numbers.

o MIN − Returns the smallest value from a list of supplied numbers.

o AVERAGE − Returns the Average of a list of supplied numbers.

o COUNT − Returns the number of numerical values in a supplied set of cells

or values.

o COUNTIF − Returns the number of cells (of a supplied range), that satisfies

a given criteria.

o SUM − Returns the sum of a supplied list of numbers

Logical

o AND − Tests a number of user-defined conditions and returns TRUE if ALL

of the conditions evaluate to TRUE, or FALSE otherwise

o OR − Tests a number of user-defined conditions and returns TRUE if ANY of

the conditions evaluate to TRUE, or FALSE otherwise.

o NOT − Returns a logical value that is the opposite of a user supplied logical

value or expression i.e. returns FALSE if the supplied argument is TRUE and

returns TRUE if the supplied argument is FAL

Math & Trig

o ABS − Returns the absolute value (i.e. the modulus) of a supplied number.

o SIGN − Returns the sign (+1, -1 or 0) of a supplied number.

of 21

o SQRT − Returns the positive square root of a given number.

o MOD − Returns the remainder from a division between two supplied

numbers.

DAY-5


Using AutoSum to select common functions

The AutoSum command allows you 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 (E24, for example).

2. Click the Home tab.

3. In the Editing group, click the AutoSum drop-down arrow and select the

function you want (Average, for example).

of 21

4. A formula will appear in E24, the selected cell. If logically placed, AutoSum

will select your cells for you. Otherwise, you will need to click the cells to

choose the argument you want.

5. Press Enter, and the result will appear.

The AutoSum command can also be accessed from the Formulas tab.

You can also use the Alt+= keyboard shortcut instead of the AutoSum command. To

use this shortcut, hold down the Alt key and then press the equals sign.

bishop scott boys’ schoolbishopscottboysschool.com/en/wp-content/uploads/... · page 1 of 21...

Documents