8th Class Computers Science Microsoft Excel Microsoft Excel 2013

Microsoft Excel 2013

Introduction

Microsoft Excel is known as a program that deals with spreadsheet based application. Spreadsheet is an application software used for calculation based applications. In this application software, a user can edit and modify data and also calculate average, sum, difference, etc. A spreadsheet contains rows and columns which forms many cells in a spreadsheet. The Microsoft Excel is open with name. Book 1. By default Book 1 contain one sheet that can be renamed, and additional worksheet can also be added in a work book 1. In this chapter, we will study about working with Microsoft Excel application software.

Cell Reference

There are three types of cell references that you can use in Excel 2013.

• Relative cell reference
• Absolute cell reference
• Mixed cell reference
• Relative Cell Reference: The most commonly used cell reference type is the relative cell reference. When you type a cell reference using just the column letter and row number, you are using a relative reference. Relative references are important when you need to repeat the same calculation in multiple rows and column.

For example:

     The formula = A1 + B1 is typed into cell C1

            Result of formula Type in C1 figure 5.1 is shown in figure 5.2

• Absolute Cell Reference: Absolute cell references are used with fixed cell reference in a formula. A dollar sign is typed before the column letter and before row number to indicate that the cell reference is an absolute reference (for example: $b$2)

For Example:     

In the given figure the formula =$B$2+$C$2 is entered into cell D2 and = $B$3+$C$3 is entered into cell ds. Regarding Absolute References, the value do not change, when they are copied or filled and are used when you want the values to stay the same.

• Mixed Cell Reference: A Mixed cell reference is a cell reference that contains both absolute and relative reference. When the formula is copied from one place to another, the relative reference in the mixed reference changes, while the absolute reference does not change. Mixed references contain either an absolute column and relative row or an absolute row and relative column.

Press F4 key on the formula bar two or three times depending on whether you want to refer to row or column.

            Difference between Cell References Types

            Difference between Cell References Types

Sorting the Data

Sort command is used to arrange the rows of data list alphabetically or numerically in ascending or descending order, based on the contents of the fields or columns.

The sort order options depends on the data type of the column you are sorting on.

• For text values, select A to Z or Z to A.
• For number values, select smallest to largest or largest to smallest.
• For date or time values, select oldest to newest or newest to oldest.

You can define your own sorting order by creating a custom list.

Steps to apply sorting in custom list

• Select a cell within the list.
• Go to Home tab and go for Editing Group.
• Under the Editing group, select Custom List option under Sort & Filter
• The custom lists dialog box will appear.
• From the Order Drop Down list select the option smallest to largest and largest to smallest.
• Click on Ok.

Creating Charts

MS Excel allows you to create different types of charts. Any table data in a worksheet can be depicted graphically in the form of a chart.

Steps to create chart

1. Enter the data in the cells of an excel spreadsheet.
2. Select the cell range from the worksheet.
3. Click the Insert
4. Select the chart type from Charts
5. Now format the chart as you desire.

Using Formulas and Functions in Excel

Formula

In MS-Excel, formula can be defined as an expression which calculate the value of a cell. In Excel, you can perform, addition, substraction, Multiplication and division.

To create a formula

1. Select the cell in which formula is to be applied.
2. Type the equals sign (=).
3. Type the cell address of the cell you want to reference first in the formula.
4. Type the mathematical operator that you want to use.
5. Type the cell address of the cell you want to reference second in the formula.
6. Press the Enter key.
7. The formula will be calculated and the value will be displayed in the cell.

Autosum

The Autosum feature of MS-Excel perform addition on a selected column or row of uninterrupted (no blank) cells.

Follow the steps given below to perform Autosum

1. Click the cell where you want to show the result.
2. Click on the Formulas
3. Click the Autosum
4. Click the Enter button on the formula bar, or press enter key.

Functions

A function is a predefined formula that takes a value or values, performs an operation on a range of cells you select, and returns a value or values.

Some of the built in function in MS-Excel 2013 are

• Mathematical functions
• Statistical functions
• Text functions
• Date & time functions

Mathematical Function

The most commonly used Mathematical functions in excel are ABS, Sum, Round, MOD, SQRT, Power, Trunk etc....

• ABS: =ABS (A1) will return 8 if a cell Al have 8 or 8. This function mainly removes the sign.
• Sum: =Sum (A1, B5, C5) will return the sum of the values in cells A1, B5, C5. =Sum (A1: A20) will return the sum of the values in A 1 to A20. =Sum (A1: A20, B5: B20) will return the sum of the values in cells A1 to A20 plus the sum the values in cells B5 to B20.
• Round: =round (A1, 2) this function removes decimals rounding up the last decimal if the next one is 5 or over. So if you have 4.126 in the cell Al and use the formula =Round (A1, 2) the result will be 4.13 if the value in Al is 4.123 the result will be 4.12
• Mod: =mod (32, 5) the modulous is the remainder left after a division. So the remainder is 2 after dividing 32 by 5.
• Sqrt: =sqrt (16) that will result in 4 because 4 multiplied by 4 is 16
• Power: =power (4, 2) will result in 16 because it work like 2* or 2*2*2*2 (2 multiply 4 times)
• Trunk: =trunk (8.9) or = trunk (0.45) will truncates a number by removing any decimal or fractional portion of the number.

Statistical Functions

The most commonly used statistical functions in excel are Average, Count, Max, Min etc.

• Average: =Average (A1: A9) will calculate the average of the values in A1 to A9.
• Count =Count (A1: A9) will count the cells containing only numerical values.
• Max =Max (A1: A9) will return the maximum or greatest numerical value in A1 to A9.
• Min =Min (Al: A9) will return the minimum or least numerical value in Al to A9.

Text Function

Text function is used to manipulate or create strings. Some text function list are the following:

• Concatenate: = Concatenate (As, B2) will used to merge or join the cell values of A2 and B2 into one cell.
• Left: =Left (B2, 5) returns a specified number of characters from the start of a supplied text string.
• Right: =Right (B2, 5) returns a specified number of characters form the end of a supplied text string.
• Len: =Len (B2) returns the length of a supplied text string.

Date and Time Function

• Today = Today ( ) returns the current system date.
• Now = Now ( ) return the current system date and time. It can be continuously updated changed or opened.