Kenlyn Services Limited

Spreadsheets are a powerful way to perform calculations, create charts and organize data. This course will walk you through usage of spreadsheets. We will use Microsoft Office Excel as the basis for this course, although many of the principles in this course would be applicable to other spreadsheet applications.

Lesson 1: Introduction to Spreadsheets

Before discussing a spreadsheet one should understand; What is a spreadsheet?

A spreadsheet is simply a sheet of paper on which horizontal and vertical lines are drawn to generate a rectangular grid, similar to a mathematical notebook. Each blank square or block is for writing the number or text or data as required.

An electronic spreadsheet is an electronic counterpart of the paper worksheet. The main difference is that electronic spreadsheet is far bigger than paper spreadsheets and is far more powerful, faster and versatile to use than the paper counterpart. So an electronic spreadsheet (or worksheet) is used to store information in the memory of the computer, asks the computer to calculate results, and display the information and results on the computer screen.

There are many spreadsheet packages such as Lotus, Tally, Microsoft Excel etc. The most popular program included in the MS OFFICE suite is MS Excel. It is an integrated electronic worksheet (spreadsheet) program developed by Microsoft Corporation, USA.

As you may realize, Microsoft Excel's primary purpose is to help you create lists using readily available cells spread on a sheet that resembles a piece of paper. The group of cells that constitute a document is referred to as a sheet and those cells are spread all over it. For this reason, a document whose main purpose is to present a list as a group of cells is referred to as spreadsheet. Microsoft Excel is referred to as a spreadsheet application. A list created on a sheet is called, in Microsoft Excel, a worksheet. By default, a MS Excel workbook (when you open the Excel program) starts with three worksheets. You can add or delete the worksheets. A single worksheet can be treated as independent entities, although it is more suitable to treat it as part of a unit.

Advantages of Excel

• The worksheet can be quite big in size but any part of it can be viewed or edited.
• The worksheet is saved in an electronic file. This file can consist of many worksheets .This file can be retrieved and modified later, if required.
• Several mathematical, trigonometric, financial and statistical functions are built-in.
• The calculations are very fast and results are accurate.
• Any repetitive calculations may be done with formulas.
• Data can be sorted either in ascending or descending order very easily.
• Data entered in a worksheet can be formatted in several ways to give it a professional look.
• Data can be viewed in various forms of graphs (charts).

Applications of MS Excel

• Preparation of statistical and financial reports of business firm.
• It is used in various banking related functions.
• It is a very good tool for preparation of projections and forecasting statements.
• Can be used to keep track of daily household expenses.
• It can be used to do a comparative analysis of the budget and actual over a period of time.
• Analysis of data can be depicted in a graphical format in the form of pie-charts, histograms, etc.
• It can be used for results tabulation in schools.

Basic Concept of Worksheet

Worksheet

Worksheets are the most important feature of MS Excel. A worksheet consists of rows and columns. All actions and operations of MS Excel take place in the Worksheet. You can enter and edit several worksheets simultaneously and perform calculations based on data from multiple worksheets. Worksheets are the pillars to create and analyze a document in MS Excel.

Workbook

Excel files are known as workbooks. A workbook is a collection of worksheets. These are the files in which you work and store your data. Workbooks are made up of worksheets or spreadsheets. By default, all new workbooks consist of three worksheets.

Cell

A Cell is the basic unit of the spreadsheet. The cell is an intersection of row and column. Each cell is referred to with a cell address for example; D4 refers to the cell at the intersection of column D and row 4. A cell can contain a value, a formula, or a text entry.

Rows

Rows are the boxes placed adjacent to each other in a horizontal manner. They are numbered from top to bottom in an ascending order. There is a maximum of 65,536 rows.

Column

Columns are the rectangles placed on top of each other vertically. Column headings are referred to with letters. They are named starting from A,B,C,D,…..AA,AB,AC,.. and called labels. There is a maximum of 256 columns.

The Active Cell

An active cell is the selected cell, it has a dark and thick border which sets it apart from the other cells. This is important because you always need to know where the changes you are making are being applied.

Block or Range

A group of adjacent cells forming a rectangle is called a block or range. A block of cells can be marked by using the mouse (click in one cell and drag through the cells you want to include)or by holding down the shift key and using the arrow keys (click in the cell you want to start with, use arrow keys to include more adjacent cells.) Once a block is defined, you can perform many functions with that block such as moving it, copying it, or deleting it. As well as aesthetically changing the look of just that block of cells.

Name Box

The name box is at the left end of the formula bar (at the top of the screen.) It identifies the selected cell. You can go to a cell directly by typing its cell address in the name box.

Worksheet Tabs

Each new Excel workbook contains three identical worksheets named, Sheet1, Sheet2 and Sheet3. Each worksheet is recognized by a worksheet tab at the bottom of the screen. To switch between worksheets, you click the worksheet tab you want to display and that worksheet then appears as the active worksheet. A worksheet can be renamed at any time by double-clicking the worksheet tab to select the title and then simply typing a new name.

The Status Bar

Status bar displays error messages, and status of special keys such as numeric lock and quick calculations. By right clicking status bar, formulas such as Average, Sum, Count, Max and Min are displayed. If the Status bar is turned on, it appears at the very bottom of the screen. Before proceeding, make sure the Status bar is turned on. To turn on, click on View menu (top of the screen) and select Status bar from Toolbar option.

Notice the word "Ready" on the Status bar at the lower left side of your screen. The word "Ready" tells you that it is awaiting your next command. Other indicators appear on the Status bar in the lower right corner of the screen.

More on Excel Window

1. Under the toolbars there are white boxes displaying a name like A1 (it may not display A1...), that small box is called the Name Box. Position your mouse on it and observe the tool tip.
2. On the right side of the Name box, there is a gray box with an = button; that = button is not the = sign, it is indeed a button and it is called the Edit Formula button. Position your mouse on it and observe the tool tip.
3. On the right side of the Edit Formula button is a long empty white box or section called the Formula Bar. Position your mouse on it and observe the tool tip.
4. Under the Name Box and the Formula bar, you see the columns. The columns are labeled A, B, C, etc. There are 256 of them. On the left side of the main window, there are gray boxes called rows. Each row is labeled with a number, starting at 1 on top, then 2, and so on. The main area of Microsoft Excel is made of cells. A cell is the intersection of a column and a row. On the right side of the cells area, there is a vertical scroll bar that allows you to scroll up and down in case your document cannot display everything at one time.
   Click and hold the down pointing arrow of the vertical scroll bar for a few seconds, then release it to view more rows on your scroll bar.

Lesson 2: Creating and Saving a Worksheet

By the end of this lesson, you should be able to open MS Excel and create a worksheet, select cell, enter data and save and edit the worksheet.

Excel allows you to create spreadsheets much like paper ledgers that can perform automatic calculations.

Starting MS Excel

• Click on Start button.
• Click on Program.
• Click on Microsoft Office.
• Click on MS Excel.

Or Alternate Method: Opening by Double-Clicking
Double click on the Microsoft Excel icon on the desktop:

The opening Screen appears as:

Location of Cell

Each cell on the spreadsheet has a cell address, say C5, that is the column C and the row 5.It is also called the location of the cell. Whenever working on a cell, it is always important to know the location of the cell you are working on. This address or location also serves as the cell's primary name. When you click a cell, its column header receives a border thicker than the other column headers. In the same way, the row header of a selected cell is thicker than the other row headers.

Before doing anything in a cell or a group of cells, you must first select it (click it.) Selecting cells is almost equivalent to highlighting a word in a text document.

Entering Data

In a spreadsheet there are three basic types of data that can be entered.

• Labels - (text with no numerical value) e.g. name, address or any text.
• Constants or numbers- (just a number -- constant value) e.g. 9, 4.75, -12.
• Formulas - (a mathematical equation used to calculate) e.g. 4+5/2, 6*2-3.

Formulas are entered beginning with an equal sign (=) or a plus sign (+).

The numbers appear in cells as a numeric value. You can perform mathematical calculations using this cell entry. Note that by default the number is right-aligned and text is left-aligned.

Entering Data In Worksheet

Click on A1 and start typing the data. To move to next cell you can use Tab key or arrow key or by clicking the mouse button. You may input the data in different cells to prepare a worksheet containing Household Budget.

Save the File

It is important to save your worksheet before you close the computer. If you are working on a large worksheet you should periodically save the worksheet.

• Select File> Save.
• It displays the “Save As” dialog box.
• Specify a name (Budget) for the file in file name text box
• Click on save button.
• The file is saved with “.xls” extension i.e. “Budget.xls”

Lesson 3: Editing a Worksheet

You may open an existing file in MS Excel and edit the worksheet, copy or move data in the worksheet and again save it.

Open an Existing File

To open a file in MS Excel follow these steps:

• Click on File in the menu.
• Select Open option (When file is selected, there is a list of most recently saved files, if budget.xls is not there select Open option)
• Select the file “Budget.xls”. (This file must already be in Excel to find and open it in the files saved on your computer from previous lessons.)

Editing a Cell

After you enter data into a cell, you can edit it by pressing F2 while you are in the cell you wish to edit.

• Move the cursor to cell A3.
• Change "Grocery " to "Food." by retyping in this cell
• Press Enter.

You can also edit a cell by using the Formula bar. Click in the formula area of the Formula bar and edit the data. Cell can also be edited by double clicking it. It brings the cursor in that location. You can use F2 also for editing.

Deleting a Cell Entry

To delete an entry in a cell or a group of cells, you bring the cursor in the cell or select the group of cells and press Delete key.

• Place the cursor in cell A2.
• Press the Delete key.

Copying Cell Contents

Ms-Excel provides two ways to copy data:

• Drag and Drop method
• Copy and Paste method

Drag and Drop Method

The steps are given below:

• Select the range of data to be copied.
• Position the mouse pointer at the lower border of the selected range.
• Hold down the Ctrl key. You will notice that the mouse pointer changes to an arrow with a plus sign.
• Drag the border to target location keeping the left mouse button pressed.

Data from the selected cell is copied to the new location.

Copy and Paste Method

The steps are as follows :

• Select the range of data to be copied.
• Choose Copy from Edit menu.
• Click on the cell to which you want to copy data.
• Select Paste from the Edit menu.

The data from the selected cells is copied to the new location.

You can also use the shortcuts keys Ctrl+C and Ctrl+V to copy and paste.

One more way to Copy and Paste is to use Copy ( ) and Paste ( ) buttons from Standard toolbar.

Moving Cell Contents

Drag and Drop Method

• Select the range of cells to be moved.
• Take the mouse pointer to any place at the boundary of the border. When the cursor changes to an arrow sign, click and hold the left mouse button pressed, drag the data to the new location.

Data from the selected cell is moved to the new location.

Cut and Paste Method

• Select the range of data to be moved.
• Choose Cut from Edit menu.
• Click on the cell to which you want to move data.
• Select Paste from the Edit menu.

The data from the selected cells is moved to the new location.

You can also use the shortcuts keys Ctrl+X and Ctrl+V to cut and paste.

One more way to Cut and Paste is to use Cut ( ) and Paste ( ) buttons from Standard toolbar.

Inserting a New Row or Column

Sometimes we need to insert a new row or column in between the rows or columns. It happens when you forget to enter something by mistake or things change. If you have a spreadsheet designed and you want to include some important information, you can insert a column into an existing spreadsheet. For example, in the ‘Budget” file, you may need to add a column for more subjects or a row for more items.

You just have to click on the column label (letter) and choose Insert Columns from the Insert menu (top of screen.) A new column will be inserted immediately left of the selected column.

If you want to insert a new column between 3rd and 4th column, just select Column heading ( D ) and select Column from Insert menu. A new column is inserted as shown below. Notice that the column heading is also changed.

Similarly, we can also insert rows. With the row label (number 4) selected select Row from the Insert menu. Again this will insert a row before the row you have selected.

Deleting a Row or Column

Similarly, sometimes you may need to delete a row or column in the worksheet.

What you have to do is to click on the column label (say D) and then select Delete option from Edit. The entire column D will be deleted.

Notice that the column label also changes.

Similarly, you can also delete rows.

Select Edit> Delete > Entire Row

Resizing Rows and Columns

There are two ways to resize rows and columns.

• Resize a row by dragging the line below the label of the row you would like to resize. Resize a column in a similar manner by dragging the line to the right of the label corresponding to the column you want to resize.
  OR
• Click the row or column label and select Format ->Row -> Height or Format -> Column -> Width from the menu bar to enter a numerical value for the height of the row or width of the column.

Practice

Open the Budget file and (1) Increase width of Column A (2) Enter new Item Electricity Bill in cell A5 and some assumed data in B5, C5, D5. and (3) save the file with new name Revised Budget.

Lesson 4: Formatting a Worksheet

The general arrangement of data is known as formatting. The contents of selected cells can be formatted in different ways. The quick way to format the data is to use the format toolbar . Font and cell attributes can be added from shortcut buttons on the formatting tool bar. If this toolbar is not already visible on the screen, select View→Toolbars→Formatting from the menu bar.

Format Cells Dialog Box

For a complete list of formatting options, right-click on the highlighted cells and choose Format Cells from the shortcut menu or select Format→Cells from the menu bar.

• Number tab – Numbers are formatted to change their appearance. Excel has many predefined numeric and date/time formats. The data type can be selected from the options on this tab. By default Excel's cell format for cells is general except for date and time and number entered as percentage (8%). Primary reason to change is for display and printing.
• Alignment tab - In an unformatted cell, you will notice that text aligns against the left edge of the column, and numbers align against the right edge.
• Font tab - All of the font attributes are displayed in this tab including font face, size, style and effects.
• Border and Pattern tabs - These tabs allow you to add borders, shading, and background colors to a cell.

Formatting Numbers

Excel has many predefined numeric and date/time formats. In addition you can design your own custom formats. These custom formats can contain characters and symbols that you specify. You can designate the decimal precision you desire and apply different colors. The format and color can be made to change according to the ranges of values in a cell. By default, Excel uses general numeric format, which means a number is displayed to the greatest precision possible. If the number is too large or small, the display appears in scientific format, such as 6.478E+05.The Number tab contains the Category box which determines the type of number format you want. This list has the following options:

General

This is the default format.

Number, Currency, Accounting, Scientific, Percentage

Decides the number of decimal places you want to appear. Enter the number of decimal places you want to appear or use the scroll keys to scroll value you want or select the currency format.

Dates and Times

If you enter the date "March 1, 2008" into a cell on the worksheet, Excel will automatically recognize the text as a date and change the format to "1-Mar-08". To change this date format, select the Number tab from the Format --( Cells window. Select "Date" from the Category box and choose the format for the date from the Type box. If the field is a time, select "Time" from the Category box and select the type in the right box. Date and time combinations are also listed. Press OK when finished.

Alignment Tab

Besides using the alignment buttons on the Formatting toolbar, to be more precise or to perform various actions in one step, you can use the Alignment property page of the Format Cells property sheet:

To provide the same options as the Formatting toolbar, the Alignment property page is equipped with the Horizontal combo box. Besides the left, center, and right alignments, this combo box goes further and allows text to be justified. This can be useful especially if the text is significantly long. If you select to indent text, you can use the Indent spin button to specify the number of units to indent by.

The Vertical combo box provides options not available on the Formatting toolbar. It allows you to align the contents of a cell towards the top, the middle or the bottom area of a cell.

In this example, the numbers are center aligned and text is left aligned.

However, you can change the position and alignment of the data with the cell by alignment tab. The different types of alignments in Horizontal box are –

The Horizontal box includes these options:

• General: To align text to the left and numbers to the right. This is the default setting
• Left: Aligns cell contents at the left edge.
• Center: Center aligns to the cell contents; within the cell.
• Right: To align cell contents at the right edge.
• Fill: To repeat the text to fill the cell.
• Justify: To wrap the cell contents into multiple lines within the cell and also align cell contents to both edges.
• Center Across Selection: To align cell contents in the center of a selected group of cells.

The Vertical box has the following options:

• Top: To align entry with the top of the cell.
• Center: To center the entry between the top and bottom edges of the cell.
• Bottom: To align the entry with the bottom of the cell.
• Justify: To align cell contents to both edges.

The Text Control section provides three options: Wrap Text, Shrink To Fit, and Merge Cells.

Wrapping Text

When you enter text that is too long to fit in a cell into a cell, it overlaps the next cell. If you do not want it to overlap the next cell you can wrap the text.

I am Learning Excel

• Move to cell A2.
• Type “I am learning Excel”.
• Press Enter.
• Click to cell A2.
• Click on Format > Cells from the menu.
• Select the Alignment tab.
• Select Wrap Text.

Click on OK. The text wraps.

I am Learning Excel

Shrink to Fit

When cell entry is larger than the size of the cell then you can use The Shrink To Fit option to fit within the cell. In this option, the contents are reduced in size so as to fit within the cell.

• Move to cell B2.
• Type “I am learning Excel”.
• Press Enter.
• Click to cell B2.
• Click on Format > Cells from the menu.
• Select the Alignment tab.
• Select Shrink to fit Text.

Merging Cells

The Merge cells option enables you to select a range of cells and include them into one cell.

• Select the range of cells you want to merge.
• Click the Format menu and choose Cells… . Select the Alignment tab in the Format cell dialog box.
• Select the Merge Cells check box.
• Click OK.

Orientation

The Orientation section allows you to "bend" text by a fixed angle. There are two main ways you can set an angle. If you drag the small red diamond, you can use it to specify the desired angle. You can also specify a value in the Degrees: box between 99 and -99 degrees, use one of the arrows of the Degrees spin button to rotate the text.

Formatting Text

• Select the cell or range of cell which you want to format.
• Click on Format Menu and select Cells option.
• Select Font from Format Cells dialog box.
• Select font style from the font style list.
• Select font size from the font size list.
• Select underline style from the underline list.
• Select color from the color list.
• Select effect from the effect list.
• Preview box displays the result whatever option you have selected.
• Click OK.

Lesson 5: Designing Layout

Creating Borders

You can use borders to make entries on your spreadsheet stand out. Accountants usually place a single underline above a final number and a double underline below. The following illustrates:

• Select the cell A1.
• Choose Format → Cells from the menu.
• Choose the Border tab.

• Click on the single underline from the Style box

• Click the top of the Border box.
• In the Style box, click on the double underline.
• Click the bottom of the Border box.
• Click OK. Cell A1 now has a border on the top and bottom.

Alternate Method: Creating Borders by Using the Icon

• Go to cell B1. Click the down arrow beside the Borders icon.

• Select the Top and Double Bottom Border. Cell B1 now has borders.

Adding Background Color

You can add background color to a cell or group of cells:

• Go to cell B1.
• Choose Format → Cells from the menu.
• Choose the Patterns tab.

• Choose Sky Blue.
• Click OK. The background of cell B1 is now Sky Blue.

Adding Pattern

• Select the cell or range of cells which you want to set the pattern.
• Click on Format menu and select Cells option.
• Click on Pattern from Format cells dialog box.
• Select the base color from the color list.
• Select a pattern and a color for the pattern from the pattern list.
• Click OK.

Merge and Center

You will sometimes want to center a piece of text over several columns. When reviewing cells, we found out that a cell doesn't have dimensions of its own. Its width is imposed by its parent column and its height is set on its parent row. All of the cells we have used so far were considered individually. Microsoft Excel allows you to combine various cells in a group. This is called merging cells.

The following steps shows how to put heading with the help of Merge and Center option.

• Go to cell A1.
• Type Sample of Merging Spreadsheet.
• Click the check mark on the Formula bar.
• Select columns A1 to H1.

Click the Merge and Center icon on the formatting toolbar. Cells B1, C1, and D1 are merged and centered.

Styles

The use of styles in Excel allow you to quickly format your worksheet, provide consistency, and create a professional look. Select the Styles drop-down box from the formatting toolbar (it can be added by customizing the toolbar). Excel provides several preset styles:

• Comma - Adds commas to the number and two digits beyond a decimal point.
• Comma [0] - Comma style that displays a whole number.
• Currency - Formats the number as currency with a dollar sign, commas, and two digits beyond the decimal point.
• Currency [0] - Currency style that displays a whole number.
• Normal - Reverts any changes to general format.
• Percent - Changes the number to a percent and adds a percent sign.

Style Dialog Box

Create your own styles from the Style Dialog Box.

• Highlight the cell(s) you want to add a style to.
• Select Format→Style... from the menu bar.

• Modify the attributes by clicking the Modify button.
• Check all the items under Style includes that the style should format.
• Click Add to preview the formatting changes on the worksheet.
• Highlight the style you want to apply to the cells and click Apply.

Create a New Style

• Select the cell on the worksheet containing the formatting you would like to set as a new style.
• Click the Style box on the Formatting toolbar so the style name is highlighted.

• Delete the text in the Style box and type the name of the new style.
• Press ENTER when finished.

Format Painter

A handy and very convenient feature on the standard toolbar for formatting text is the Format Painter. If you have formatted a cell with a certain font style, date format, border, and other formatting options, and you want to apply the same format on another cell or group of cells, then highlight the cell and place the cursor within the cell containing the formatting you want to copy. Click the Format Painter button in the standard toolbar (notice that your pointer now has a paintbrush beside it). Highlight the cells you want to apply the same formatting to.

To copy the formatting to many groups of cells, double-click the Format Painter button. The format painter remains active until you press the ESC key to turn it off.

Auto Format

Excel has many preset table formatting options. With the help of these options you can format your data quickly.

• Highlight the cells to be formatted.

• Select Format→AutoFormat from the menu bar.
• On the AutoFormat dialog box, selet the type of format you want to apply to the table. Use the scroll bar to view all of the format options available.

• Click the Options button to select the elements that the formatting will apply to.

Lesson 6: Cell References

A cell in a worksheet is identified as C5, D8 etc. When a cell is referred to in a formula, this is called cell referencing. There are three types of referencing.

Relative References

When you create a formula, references to cells or ranges are usually based on their position relative to the cell that contains the formula. In the following example, cell B6 contains the formula =A5; Microsoft Excel finds the value of one cell above and one cell to the left of B6. This is known as a relative reference.

When you copy a formula that uses relative references, Excel automatically adjusts the references in the pasted formula to refer to different cells relative to the position of the formula. In the following example, the formula in cell B6, = A5, which is one cell above and to the left of B6, has been copied to B7. Excel has adjusted the formula in cell B7 to =A6, which refers to the cell that is one cell above and to the left of cell B7. Therefore in relative referencing, cells are referenced to by their position in the worksheet on the basis of their position relative to the cell containing formula.

Absolute Referencing

If you do not want Excel to adjust references when you copy a formula to a different cell, use an absolute reference. For example, if your formula multiplies cell A5 with cell (=A5*C1) and you copy the formula to another cell, Excel will adjust both references. You can create an absolute reference to cell C1 by placing a dollar sign ($) before the parts of the references that do not change. To create an absolute reference to cell C1, for example, add dollar sign to the formula as follows :

=A5*$C$1

Therefore in absolute referencing the cell referencing is copied for a different cell. Cells are referenced by their fixed positions in the worksheet.

Mixed Referencing

Combines the features of absolute and cell references. In absolute addressing Row and Column both are fixed. In mixed addressing only row or column is fixed, example $B9 means “B” column fixed. B$9 means “9” row is fixed.

Lesson 7: Formulas and Functions in MS Excel

The distinguishing feature of a spreadsheet program such as Excel is that it allows you to create mathematical formulas and execute functions.

Microsoft Excel has a set of prewritten formulas called Functions. A function is a small assignment that is performed to produce a result that can be reliably used without caring as to how the function works or how it was created.

Functions differ from regular formulas in that you supply the value but not the operators, such as +,-,* or /.

Formulas are entered in the worksheet cell and must begin with an equal sign "=". The formula then includes the addresses of the cells whose values will be manipulated with appropriate operands placed in between. After the formula is typed into the cell, the calculation executes immediately and the formula itself is visible only in the formula bar.

While using a function, remember the following rules:

• Use an Equal (=) sign to begin a function.
• Specify the function name.
• Enclose arguments within parentheses.
• Use a comma to separate arguments

Function Wizard

MS Excel provides different function categories as follows:

• Maths & Trig
• Statistical
• Logical
• Text
• Financial
• Date and Time
• Database

You can view all functions available in Excel by using the Function Wizard. Activate the cell where the function will be placed and click the Function Wizard button on the standard toolbar.

From the Paste Function dialog box, browse through the functions by clicking in the Function category menu on the left and select the function from the Function name choices on the right. As each function name is highlighted a description and example of it's use is provided below the two boxes.

• Click OK to select a function.
• The next window allows you to choose the cells that will be included in the function. In the example below, cells B4 and C4 were automatically selected for the sum function by Excel. The cell values {2, 3} are located to the right of the Number 1 field where the cell addresses are listed. If another set of cells, such as B5 and C5, needed to be added to the function, those cells would be added in the format "B5:C5" to the Number 2 field.

• Click OK when all the cells for the function have been selected.

Auto Sum

One of the most frequently used functions is the Sum() function that calculates the total of a set of numeric values. Thus, a toolbar button has been provided to invoke the Sum() function. You can use this button to calculate the total of a group of cells without typing the formula in the destination cells.

Select the cell that the sum will appear in that is outside the cluster of cells whose values will be added. Cell C2 was used in this example.

Click the Autosum button (Greek letter sigma) on the standard toolbar.

Making Numeric Entries

A formula is nothing more than an equation that you write up. In Excel a typical formula might contain cells, constants, and even functions. Here is an example Excel formula that we have labeled for your understanding.

=C3 * 4 / SUM(C4:C7)

cell(s): C3 and the range of cells from C4:C7
constant(s): 4
function(s): SUM()

In Microsoft Excel, you can enter numbers and mathematical formulas into cells. When a number is entered into a cell, you can perform mathematical calculations such as addition, subtraction, multiplication, and division. When entering a mathematical formula, precede the formula with an equal sign. Use the following to indicate the type of calculation you wish to perform:

• + Addition
• - Subtraction
• * Multiplication
• / Division
• ^ Exponential

Performing Mathematical Calculations

The following exercises demonstrate how to perform mathematical calculations.

Addition, Subtraction, Division and Multiplication

• Click on cell A1.
• Type 5.
• Press Enter.
• Type 5 in cell A2.
• Press Enter.
• Type =A1+A2 in cell A3.
• Press Enter. Cell A1 has been added to cell A2, and the result is shown in cell A3.

Place the cursor in cell A3 and look at the Formula bar.

Now in the same way perform subtraction, multiplication and division as given below.

Place the cursor in cell D3 and look at the Formula bar.

The most powerful aspect of Excel is not the simple calculator abilities we describe in our first formula example, but rather the ability to take values from cells to be used in your formulas.

Let's set up a basic sales spreadsheet to help explain this topic.

In cells A1-D4 enter the following information:

Notice: that cell D2 and D3 are blank, but should contain the amount of money from selling 150 tea items and 3 sugar. By referencing the Quantity and Price cells we will be able to do this! Let's start with Tea.

Note:It is very important to follow these steps exactly without interruptions!

• Select cell D2, tea "revenue", and type the equal sign "=" to begin your formula.
• Left-click on cell B2, Tea's Quantity and notice your formula is now "=B2"

We want to multiply Quanity(B2) by Price(B3) so enter an asterisk (*)

Now left-click on Tea's Price (C2)to complete your formula!

If your formula looks like this then press Enter, otherwise you can manually enter the formula "=B2*C2". However, we really think it is easier and preferred to click on cells to reference them, instead of entering that information manually.

After you pressed Enter your Tea Revenue cell should be functioning properly and contain the value 2500.

Using your newly gained knowledge please complete Sugar's Revenue by repeating the above steps.

Your spreadsheet should now look like this:

Hint: If you are having trouble creating the formula for Sugar's Revenue it is "=B3*C3"

Statistical Functions

Example Table:

MAX() : This function is used to return the maximum value in the set of values.
Syntax : Max(number1,number2,…..)
Example : =Max(D3:D12) , Max(A1,A2,10800)
Result : 10700 10800

MIN() : This function is used to return the minimum value in the set of values.
Syntax : Min(number1,number2,…..)
Example : =Min(D3:A12) , Min(D1,D3,1000)
Result : 10000 1000

Average() : This function is used to return the average of the arguments.
Syntax : Average(number1,number2,…..)
Example : =Average(D3:D12) , Average(D3,D4)
Result : 10137 10600

Sum() : This function is used to return the sum of the arguments.
Syntax : Sum(number1,number2,…..)
Example : =Sum(D3:D12) , Sum(D3,D4,1000)
Result : 101370 22200

Count() : This function is used to count the number of cells that contain numbers and numbers within the list of arguments.
Syntax : Count(number1,number2,…..)
Example : =Count(D3:D12) , Count(D3,E12,1000)
Result : 10 20

The following worksheet is created for salary slip. The basic pay and HRA(house rent allowance) is given. The DA(dearness allowance) is 25% of the basic pay. The gross pay is basic+HRA+DA.

Lesson 8: Some More Functions

Mathematical & Trigonometric Functions

Abs() :This function is used to return the absolute value of a number.
Syntax : Abs(number)
Example : =Abs(A1) , Abs(-54)
Result : 5 54

Sqrt() : This function is used to return the square root of a number.
Syntax : Sqrt(number)
Example : =Sqrt(A5) , Sqrt(D1*5)
Result : 5 10

Round(): This function is used to round a number to a specified number of digits.
Syntax : Round(number, number of digits)
Example : =Round(5.1583,2) , round(B2,1)
Result : 5.16 5.5

MOD() : This function is used to the remainder after a number is divided by a divisor.
Syntax : Mod(number, divisor)
Mod(9/4) Mod(A2,A3) Mod(A2,6)
Result : 1 5 4

Power() : This function is used to return the result of a number raised to a power.
Syntax : Power(number1,number2)
Example : =Power(D3,D4) , Power(A1,3)
Result : 64 125

Int() : This function is used to return the round a number down to the nearest integer.
Syntax : Int(number)
Example : =Int(A1) , Int(14.4324)
Result : 5 14
Note : It does not round the number.

Exp() : This function is used to return e raised to the power of a given number.
Syntax : Exp(number)
Example : =Exp(A1) , Exp(4)
Result : 148.4132 54.59815

Practice Problem

The following example calculates the gross salary which is the total of basic+HRA+25% of basic as DA(C3*25/100). Income tax is 30% ((F3*30/100)of gross pay which is deducted from gross pay to get net pay(F3-G3).

Logical Functions

If() : This function is used to return one value if a condition is true then it returns TRUE otherwise returns FALSE.
Syntax : If(condition, value1, value2)
Example : =If(A1>A2,”Pass”,”Fail”> =If(A1>75,Grade A”,” Grade B”)
Result : Fail Grade B

AND() : This function is used to return True if all the arguments are True, otherwise returns false if any argument is false.
Syntax : And(Logical 1, Logical 2)
Example : =AND(A1,A2) AND(5,5)
Result : false True

Or() : This function is used to return true if any argument is true otherwise returns false. If all arguments are false.
Syntax : OR(Logical 1, Logical 2)
Example : =OR(A1, A2) =OR(5,6)
Result : True True

NOT() : This function is used to reverse the logic of its argument, returns False for a True argument and True for a False argument.
Syntax: NOT(Logical)
Example: =NOT(A1) =NOT(6)
See the following example, here Grade is added based on the following condition
=IF ((H4>10000),"Grade A”,” Grade B")

Text Functions

LEFT() : This function is used to return the leftmost characters from the text.
Syntax : Left(text, number characters)
Example : =Left(A1,3) =Left(“education”,4)
(if the value of A1 is computer)
Result : com educ

Len() : This function is used to return the number of characters in a text string.
Syntax : Len(text)
Example : =Len(A1) =Len(“educa”)
(if the value of A1 is computer)
Result : 8 5

Lower() : This function is used to convert all uppercase characters in a text to lower case.
Syntax : Lower(text)
Example : =Lower(A1) =Lower(“EDUCATION”)
(if the value of A1 is COMPUTER)
Computer education

Upper() : This function is used to convert all lowercase characters in a text to upper case.
Syntax : Upper(text)
Example : =Upper(A1) =Upper(“education”)
(if the value of A1 is computer)
COMPUTER EDUCATION

Rept() : This function is used to repeat text a given number of times.
Syntax : Rept(text, number-times)
Example : =Rept(A1,20) =Rept(“E”,5)
(if the value of A1 is c)
cccccccccccccccccccc EEEEE

Right() : This function is used to return the rightmost character from a text value.
Syntax : Right(text, num-chars)
Example : =Right(A1,3) =right(“EDUCATION”,4)
(if the value of A1 is computer)
Ter TION

Concatenation() : This function is used to join several text strings into one text strings.
Syntax : Concatenation(text1,text2)
Example : =concatenation(A1,A2) Concatenation(“edu”,”cation”)
(if the value of A1 is computer and B1 is science)
Computerscience education

Auto Fill

Ms Excel provides you a unique feature that is AutoFill. The Autofill function fills the cells automatically. It is used to quickly fill cells with repetitive or sequential data such as sequential numbers, chronological dates or repeated text. Excel automatically fills in times, the days of the week or months of the year, years, and other types of series. Days of the week and months of the year fill in a similar fashion. The following demonstrates filling the days of the week:

• Click on cell A1.
• Type Sun
• Click on cell B1.
• Type Sunday.
• Highlight cells A1 to B1.
• Notice the small black square in the lower right corner of the highlighted area. This is called the Fill Handle.
• Grab the Fill Handle and drag with your mouse to fill cell A1 to B24. Note how the days of the week fill the cells in a series. Also, notice that the Auto Fill Options icon appears.

• Click the Auto Fill Options icon.
• Choose the Copy Cells radio button. The entry in cells A1 and B1 are copied to all the cells highlighted.
• Click the Auto Fill Options icon again.
• Choose the Fill Series radio button. The cells fill as a series from Sunday to Saturday again.
• Click the Auto Fill Options icon again.
• Choose the Fill without Formatting radio button. The cells fill as a series from Sunday to Saturday, but the entries are not bolded.
• Click the Auto Fill Options icon again.
• Choose the Fill Weekdays radio button. The cells fill as a series from Monday to Friday.

If you want to autofill a column with cells displaying the same number or date you must enter identical data to two adjacent cells in a column. Highlight the two cells and drag the handle of the selection with the mouse.

Auto Filling Functions

Autofill can also be used to copy functions. In the example below, column A and column B each contain lists of numbers and column C contains the sums of columns A and B for each row. The function in cell C2 would be "=SUM(A2:B2)". This function can then be copied to the remaining cells of column C by activating cell C2 and dragging the handle down to fill in the remaining cells. The autofill feature will automatically update the row numbers as shown below if the cells are reference relatively.

Series Dialog Box

The series dialog box offers options for generating a series. The steps to generate a series are:

• Enter the initial values.
• Select the range of cells that need to be filled.
• Select the Fill option from the Edit menu.
• Select the Series option from the Fill submenu. The Series dialog box is displayed.
• Select the appropriate options and click the OK button.

The Series dialog box offers the following options:

Series in: Rows or Columns
Excel will guess the correct orientation, depending upon the range selected. If you have selected a single cell, you must indicate in which direction you want the series to be placed across a row or down a column.

Type Linear
This option creates a straight linear progression, starting at the current value of the cell and incrementing each cell in the range by the number entered in the step value. For example, if you have entered 3 in the first cell and the step value is 3, the numbers will incremented by 3, i.e., 3, 6, 9, and so on.

Type growth
This option grow the data by the number entered in the step value box using simple multiplication. If you start with a value of 1 and grow the data by a step value of 3, the series will be 1, 3 , 9, 27, and so on.

Type Date
This allows you to enter a series of dates a specific number of days apart. For example, if you enter 9/30/99 in the first cell and choose Monthly, the data series will be read 9/30/99, 10/30/99, 11/30/99, and so on.

Type AutoFill
This option allows you to enter a series of numbers and have Excel determine the relationship between them in order to fill the remaining cells in the series. For example, if you enter 10 in the first cell and 20 in the next, Excel will fill the remaining cell with 30, 40, 50, and so on.

Step Value
The Step Value is used to determine the increment Value for a linear series or date series and multiplication rate for a growth series.

Stop Value
Stop value is the maximum value for the series.

Trend
It is available only in conjunction with Linear or Growth series. In this case Excel analyzes the data selected and determines what the trend line for the data should be, ignoring any step value and replacing the current values of the cells with the calculated series.

Note : Both step value and stop value are grayed out when you choose the Trend option

Auto Sum Icon

The AutoSum icon on the Standard toolbar automatically adds a column of numbers. The following illustrates the SUM function:

• Go to cell F1.
• Type 6. Press Enter.
• Type 6. Press Enter.
• Type 6. Press Enter.
• Click the AutoSum button, which is located on the Standard toolbar.
  F1 to F3 should now be highlighted.
• Press Enter. Cells F1 through F3 are added.

Automatic Calculation

If you have automatic calculation turned on, Microsoft Excel recalculates the worksheet as you change cell entries. You can check to make sure automatic calculation is turned on.

Lesson 9: Graphs and Charts

Charts are pictorial representation of worksheet data and make it easy for users to make comparisons, patterns, and trends in data. Charts present worksheet data in graphical or pictorial form, which is easier to read and understand. For instance, rather than having to analyze several columns of worksheet numbers, you can see at a glance whether sales are falling or rising over quarterly periods, or how the actual sales compare to the projected sales.

Charts can be created in two ways: Embedded and Chart Sheet.

An embedded chart is a chart object that has been placed on a worksheet and that is saved on that worksheet when the workbook is saved. When it is selected you can move and size it.
A chart sheet is a sheet in a workbook containing a chart. When a chart sheet is created, it is automatically inserted.

Chart Types in MS Excel

• Area Chart
  An area chart emphasizes the magnitude of change over time. By displaying the sum of plotted values, an area chart also shows the relationship of parts to a whole.

• Column Chart
  A column chart shows data-changes over a period of time or illustrates comparisons among items. Categories are organized horizontally, values vertically, to emphasize variation over time. One variation of column chart is a stacked column chart, and another is 3-D chart. The Stacked column chart shows the relationship of individual items to the whole. The 3-D chart shows the three dimensional column.

• Bar Chart
  A bar chart illustrates comparisons among individual items. Categories are organized vertically, values horizontally, to focus on comparing values and to place less emphasis on time. One variation of a bar chart is a stacked bar chart. The stacked bar chart shows the relationship of individual items to the whole.

• Line Chart
  A line chart shows trends in data at equal intervals. Line charts are useful for depicting the change in a value over a period of time. Line graphs emphasize time flow and rate of change, rather than amount of change.

• Pie Chart
  A pie chart shows the proportional size of items that make up a data series to the sum of the items. It always shows only one data series and is useful when you want to emphasize a significant element. Pie charts highlight how each value in a data series relates proportionally to the whole i.e., the sum of the values in the series.
  There is a variation of pie chart available in Ms Excel and this variation is a grouped pie chart. To make small slices easier to see, these can be grouped together as one item in a pie chart and then broken down into a smaller pie or bar chart next to the main chart.

• XY (Scatter ) Chart
  An XY (scatter)chart shows the relationship among the numeric values in several data series. Like a line chart, an XY chart plots each point with a marker and and connects the points in each series with a line. The XY chart is sometimes referred to as a scattered chart because it is often used without any lines connecting the data markers.

• Doughnut chart
  Like a pie chart, a doughnut chart shows the relationship of parts to a whole, but it can contain more than one data series. Each ring of the doughnut chart represents a data series.

• 3-D Surface Chart
  The 3-D surface chart is a unique chart type that seems to drape a sheet over the data to create a three-dimensional view of it.

• Radar Chart
  A radar chart plots data on concentric circles. In a radar chart, each category has its own value axis radiating from the centre point. A line connects all the values in the same series. A radar chart compares the aggregate values of a number of data series.

• Cone, Cylinder, and pyramid Charts
  The cone, cylinder, and pyramid data markers give a dramatic effect to 3-d column and bar charts.

Components of a Chart

Lesson 10: Chart Wizard

The Chart Wizard brings you through the process of creating a chart by displaying a series of dialog boxes.

• Enter the data into the worksheet and highlight the range for which you want to make a chart including headers.

• Click the Chart Wizard icon on the standard toolbar to view the first Chart Wizard dialog box or choose Chart option from the Insert menu.
• Chart Type – The Chart Wizard dialog box is displayed. Choose the Chart type and the Chart subtype if necessary. Click Next.

• Chart Source Data - Select the data range (if different from the area highlighted in step 1) and click Next. If you want to change the data and labels, click in the Data Range box and select the source data range on the worksheet.

• Chart Options - Enter the name of the chart and titles for the X- and Y-axes. Other options for the axes, grid lines, legend, data labels, and data table can be changed by clicking on the tabs. Press Next to move to the next set of options.

• Chart Location - Click As new sheet if the chart is to be placed on a new, blank worksheet or select As object in if the chart is be embedded in an existing sheet and select the worksheet from the drop-down menu.

• Click Finish to create the chart.

Resizing The Chart

To resize the chart, click on its border and drag any of the nine black handles to change the size. Handles on the corners will resize the chart proportionally while handles along the lines will stretch the chart.

Moving The Chart

Select the border of the chart, hold down the left mouse button, and drag the chart to a new location. Elements within the chart such as the title and labels may also be moved within the chart. Click on the element to activate it, and use the mouse to drag the element to move it.

Chart Formatting Toolbar

Chart Objects List - To select an object on the chart to format, click the object on the chart or select the object from the Chart Objects List and click the Format button. A window containing the properties of that object will then appear to make formatting changes.

Chart Type - Click the arrowhead on the chart type button to select a different type of chart.

Legend Toggle - Show or hide the chart legend by clicking this toggle button.

Data Table view - Display the data table instead of the chart by clicking the Data Table toggle button.

Display Data by Column or Row - Charts the data by columns or rows according to the data sheet.

Angle Text - Select the category or value axis and click the Angle Downward or Angle Upward button to angle the the selected by +/- 45 degrees.