1



Some Notes on EXCEL

TABLE OF CONTENTS

In Excel a file is called a work book and each page is worksheet.

If you click on office button, you can see the files that you recently worked.

1. Formatting

2. Entering Mathematical Operations in Cells 4

3. Selecting Ranges 4

To select a range that fits on a screen 4

To select a range doesn’t fit on a screen 5

Selecting more than one range 5

4. Using Range Names 5

To create a range name 6

Applying Formulas to Ranges 6

5. Inserting or Deleting Rows or Columns 7

To insert one or more blank rows 7

To delete one or more rows 7

6. Filling a Series 7

7. Copying and Pasting 7

To copy and paste using keyboard shortcuts 7

Using Absolute/relative references in Formulas when Copying/Pasting 8

Copying And Paste A Formula Into Multiple Cells At Once (Using Ctrl-Enter) 9

Copying and Pasting with the Special/Values option 9

Moving (cutting and pasting) 10

8. Some Useful Functions 10

Using the summation button 11

To use the COUNT and COUNTIF function 11

To use the AVERAGE function 12

To use MAX and MIN functions 12

Using IF functions 12

Using nested IF functions 13

Using an AND condition in an IF function 13

To use an OR condition in an IF function 14

Using lookup functions 14

9. Using the paste function (fx) Button in the Top Toolbar 15

10. Using Data Tables 17

11. Split Screen

12. Insert Charts

Formatting

Formula window, bold borders, adding notes, merging cells

1.1 Changing font size or font size

In the Font box on the Formatting toolbar, click the font you want.

[pic]

In the Font Size box, click the font size you want.

1.2 Formatting cells

You can format an entire group of cells using the Format cells page. First select the group of cells that you wish to format (section 3 details how to select cells). Then, click Home>Format>Cells. Using this page you can format cells as general numbers, monetary values, a date, text, etc…

[pic]

1.3 Formatting Font

Excel allows you to format font in a spreadsheet similar to excel. Simply highlight the font (or cells) that you wish to format and click on the appropriate box on the Formatting toolbar. Below are some of the most commonly used formatting boxes:

[pic] Makes font within selected cells bold

[pic] Makes font within selected cells italicized

[pic] Changes the color of the font within selected cells. Click on the arrow to specify

the color.

[pic] Shades the background of the selected cells. Click on the arrow to specify the

color.

[pic] Places a border around the selected cells. Click on the arrow to specify the desired

border type.

1.4 Other Formatting activities

• Sizing the cells, grab the line (horizontal or vertical and drag). If you want to specify a precise column width, use the Column Width dialog box.

• Alignment: ribbon > home > alignment group

• Text Orientation: To change text orientation, select the cells whose contents you want to rotate. Click the Orientation button in the Alignment group on the Home tab of the Ribbon. A menu of orientation options appears, with commands for angling the text at 45-degree angles clockwise or counterclockwise, stacking the text vertically, or rotating the text up or down.

• Searching for data: The Find command locates data in a worksheet, which is particularly helpful when a worksheet contains a large amount of data; The Replace command is an extension of the Find command. Replacing data substitutes new data for the data found. In the Editing group on the Home tab of the Ribbon, click the Find & Select button, and then click either Find or Replace: Ribbon > home > editing group > find & Select.

• Zoom either using the bar on the bottom of the sheet or ribbon > view > zoom

• Print and preview: on print menu you can select number of copies, the sections you want to print etc.

• Print area: Select the text that you want to print: Ribbon > Page Layout > in page setup group select Print area

• Format cells: right click > format cells > different options (wrap text, merge, changing text orientation, borders, number formats,

• Format Painter: select the cell that has the format > Click on format painter on home, clipboard, brush > click on another cell or area that you want to carry that format

• Freezing Pane: Often a worksheet includes too much data to view on the screen at one time. In order to keep the label line fixed when you scroll down or keep the first row frozen: View > Freeze Panes

• To clear all the formatting, select the cell or range, click Home > Editing group > Clear Format

• AutoFit determines the best width for a column or the best height for a row, based on its contents. Place the pointer on the right edge of the column heading (or below the row heading) until the pointer changes to a double-headed arrow. Then, double-click to resize the column or row to the best fit.

• Spelling: Ribbon > Review > Proofing group > spelling

• Header and footer: Ribbon > Insert > text grouping > header and footer. When you type it will take you to layout view of the document. Click on spreadsheet to get the header placed.

U

Performed

Entering Mathematical Operations in Cells

Excel allows you to enter any number, letter and/or character into a cell. You must let excel know whether it needs to mathematically compute values opposed to listing items exactly as you enter them. You must first enter = in a cell if you want Excel to compute a value.

Enter 2 into cell A2 and 5 in cell A3. Then enter =A2+A3 in cell B2. Notice cell B2 has the value 7.

Selecting Ranges

In Excel ranges are often selected for the purpose of copying/pasting, entering formulas, deleting items, and so on. It’s easy to select a range when the entire range appears on the screen. However, it is more frustrating to use traditional techniques (such as dragging) when you can’t see the entire range. Below we review several efficient techniques for completing this task.

3.1 To select a range that fits on a screen

Method 1: Click on one corner of the range and drag to the opposite corner.

Or:

Method 2: Click on one corner, hold down the Shift key, and click on the opposite corner.

Try it! Create the spreadsheet below and select the range B2:D7

[pic]

3.2 To select a range doesn’t fit on a screen

Click on one corner of the range, say, the upper left corner. Then, holding the Shift key down, use the End-arrow combinations (End and right arrow, then, if necessary, End and down arrow) to get to the opposite corner. [This is method 2 above]

3.3 Selecting more than one range

You can format more than one range at a time in Excel. This is particularly useful when formatting cells with similar information across a spreadsheet.

Select the first range, press the Ctrl key, select the second range, and press the Ctrl key, select the third range, and so on.

For example, to select the ranges A2:B4 and D2:E6, click on A2, hold down the Shift key and click on B4 (so now the first range is selected), hold down the Ctrl key and click on D2, and finally hold down the Shift key and click on E6.

Try it! Create the spreadsheet below, and select the two ranges below.

[pic]

Using Range Names

Using range names

Range names are extremely useful for making your formulas more understandable. Efficient use of range names takes some experience, but here are a few useful tips.

4.1 To create a range name

Select a range that you want to name. Then type the desired range name in the upper left “name box” on the screen. (This box is just above the column “A” heading. It usually shows the cell address, such as E13, where the cursor is.)

You could right click and choose “Define name,” and type the name. By the way, range names are not case sensitive. For example, Revenue, revenue, and REVENUE can be used interchangeably.

4.2 Applying Formulas to Ranges

Suppose you have a business and you want to determine the profits over the past 12 months. Each month you entered the monthly Revenues and Costs into separate columns on a spreadsheet, and you now need to compute the profits.

Naturally, Profits = Revenues – Costs.

Try it! Create a spreadsheet below by entering the monthly revenues in range B3:B14. Then name this range REVENUE. Next enter the monthly costs in range C3:C14 and name this range COST. For each month you want that month’s revenue minus cost in the appropriate cell in column D. You will get it correct if you select the range D3:D14, type the formula =Revenues-Costs, and press Ctrl-Enter. If you click on any cell in this range, you’ll see the formula =Revenues-Costs.

If this confuses you, you can always enter =B3-C3 and copy it down. Then you’re safe, but you’ve lost the advantage of range names!

[pic]

Inserting or Deleting Rows or Columns

Often you want to insert or delete rows or columns. Note that deleting a row or column is not the same as clearing the contents of a row or column. Deleting means erasing the cells of all formulas, formatting, etc.

5.1 To insert one or more blank rows

Click on a row number and drag down as many rows as you want to insert, and then press Alt-i and then r .

The rows you insert are inserted above the first row you selected. For example, if you select rows 8 through 11 and then insert, four blank rows will be inserted between the old rows 7 and 8.

Columns are inserted in the same way, except that the key sequence is Alt-i and then c.

Alternatively, a single row or column can be inserted by clicking on the menu items Insert/Rows or Insert/Columns.

5.2 To delete one or more rows

Click on a row number and drag down as many rows as you want to delete, and then press Alt-e and then d (the menu equivalent of Edit/Delete). Columns are deleted in exactly the same way.

6 Filling a Series

Say you want to fill column A, starting in cell A2, with the values 1, 2, and so on up to 1000. Excel has a function that will allow you to fill in columns (or rows) with a series. You can select the start value, end value, and the step value.

Enter the first value in the first cell (1 in cell A2). With the cursor in the starting cell (A2), use the menu item Home/Editing/Fill/Series in to obtain a dialog box. Change the Row setting to Column, make sure the Type setting is Linear, set the Step Value to 1 (since you want values to be incremented by 1), enter the final value (1000) in the Stop Value box, and click on OK.

Copying and Pasting

Copying and pasting (usually formulas) is one of the most frequent tasks done in Excel. Below we review several techniques for copying/pasting values, formulas, or specific values created by formulas.

7.1 To copy and paste using keyboard shortcuts

Select the copy range (using one of the efficient selection methods described above), press Ctrl-c (for copy), select the paste range (again, efficiently), and press Ctrl-v (for paste).

The copy range will still have a dotted line around it. Press the Esc key to get rid of it.

7.2 Using Absolute/relative references in Formulas when Copying/Pasting

A relative cell reference adjusts to its new location when copied or moved. Absolute cell references do not change when moved or copied to anew cell. To create an absolute reference, you insert a dollar sign ($) before the column letter and/or the row number of the cell reference you want to stay the same.

Absolute and references are indicated in formulas by dollar signs or the lack of them, and they dictate what happens when you copy or move a formula to a range. This is a crucial concept for efficiency in spreadsheet operations, so you should take some time to understand it thoroughly. Let’s say you want to multiply the contents of cell A2 by 2 and enter the product in cell B2. Traditionally you would enter =A2*2 in cell B2. Alternatively you could use absolute referencing and enter =$A$2*2 in cell B2.

Here are two things to remember: (1) The dollar signs are relevant only for the purpose of copying or moving; they have no inherent effect on a formula. For example, the formulas =A2*2 and =$A$2*2 in cell C3, produce exactly the same result. Their difference is relevant only if cell C3 is copied or moved to some range. (2) There is never any need to type the dollar signs. This can be done with the F4 key.

Create the spreadsheet below.

[pic]

For any month the total cost = (fixed cost) + (units produced) * (Variable cost)

In cell B7 enter = B2 + B3*B6 and press ENTER. The value 498 will appear in cell B7. Now copy cell B7, and go to cell C7 and paste its contents. Let’s discuss the results.

• Excel displays the value 0 in cell C7.

• Click on cell C7 and review the formula in this cell.

➢ C7 contains the formula C2 + C3 *C6.

➢ Excel changed the formula = B2 + B3*B6 into =C2 + C3 *C6.

LET UNDERSTAND WHY………………………….

Excel automatically adjusts formulas relative to the cell position where you are pasting it. Since we want the fixed and variable costs to always reference the cells B2 and B3 respectively, we must use absolute referencing to keep these cell locations constant when copying and pasting formulas (i.e., use dollar symbols when referencing cells B2 and B3).

Now go back to cell B7 and enter = $B$2 + $B$3*B6 and press ENTER. The value 498 will still appear in cell B7. Now copy cell B7, and go to cell C7 and paste its contents.

• Notice Excel now displays the value 438 in cell C7.

• Click on cell C7 to review the formula in this cell. Notice that Excel changed the formula = $B$2 + $B$3*B6 into =$B$2 + $B$3*C6. The fixed and variable cost references remained constant. However, the cell referencing the units produced adequately adjusted to the units produced for February instead of January (which was originally referenced when copied).

YOU CAN ALWAYS HIGHLIGHT A PORTION OF A FORMULA AND PRESS F4 KEY AND $ SYMBOLS WILL BE AUTOMATICALLY PLACED IN ALL CELL REFERENCES.

In a nutshell, you should always remember to use absolute referencing when copying and pasting formulas that contain cell references that you want to remain constant. I STRONGLY SUGGEST THAT YOU PLAY WITH COPYING AND PASTING USING ABSOLUTE REFERENCES UNTIL YOU COMPLETELY UNDERSTAND THIS CONCEPT.

7.3 Copying And Paste A Formula Into Multiple Cells At Once (Using Ctrl-Enter)

Below there are values entered in column A, and values entered in column B. Suppose you want to enter the product of the value in column A and column B into column C, for each row. You can always enter the formula manually into cell C2. Then copy cell C2 and paste its contents into cells C3:C8 (i.e., C3 through C8). However there is a more efficient way to complete this task.

Select the range C3:C8. With the entire paste range selected, type the formula =A2*B2, and press Ctrl-Enter instead of Enter. The Entire range will fill up with the appropriate formulas.

[pic]

Pressing Ctrl-Enter enters what you typed in all of the selected cells (adjusted for relative addresses), so in general, it can be a real time saver. For example, it could be used to enter the number 10 in a whole range of cells. Just select the range, type 10, and press Ctrl-Enter.

7.4 Copying and Pasting with the Special/Values option

Often you have a range of cells that contains formulas. It is possible to paste the exact values generated by the formulas in an alternate location on the spreadsheet, or even into another worksheet altogether. Traditional cutting and pasting techniques will either alter the precise values generated by the formulas or produce errors (when pasting into a new worksheet).

Select the range with formulas, press Ctrl-c to copy, and select the range where you want to paste the values (which could be the same as the copy range). Then select the Edit/Paste Special menu item, and select the Values option.

Try it! Use the spreadsheet that you just created above. First try to use traditional method (i.e., select the copy range, press ctrl + c, then select the paste range and press ctrl + v) to paste the values in cells C2:C8 into cells E2:E8. Excel will display all zeros in the paste range instead of the values 12, 9, 36, 42, 6, 40, 27.

Now select the copy range, and press ctrl + c. Next select the copy range and select the Edit/Paste Special menu item, and select the Values option. The appropriate values appear.

[pic]

When you use the Paste Special function only values are pasted. The formulas that were used to generate the values are not available in the pasted cells.

You might want to experiment with the other options on the Edit/Paste Special dialog box. For example, if you have a set of labels entered as a row and you want this same set of labels entered somewhere else as a column, try copying and pasting special with the Transpose option.

7.5 Moving (cutting and pasting)

Select the range to be cut, press Ctrl-x (for cutting), select the upper left corner of the paste range, and press Ctrl-v.

Some Useful Functions

A function formula contains three components: an equal sign, a function name, and an argument. If a function has more than one argument, comma separates the arguments. An example is =SUM(D5:D10)

The function can be types in the cell or can be inserted. When you type “=” in the cell, you will see the drop down menu on the north corner of your screen. Choose the function from there and the arguments are presented there

EXCEL has more than 300 function formula.

Functions:

• FV(rate,nper,pmt,pv,type): Displays the future value of a series of equal payments (third argument), at a fixed rate (first argument), for a specified number of periods (second argument), the fourth and fifth argument are optional. FV(.8,5,100) displays the future value of five 100 payments at the end of five years if you earn a rate of 8%.

• PMT(rate,nper,pv,fv,type) displays the payment per period needed to repay the loan (3rd arg), at a specified rate (1st arg), for a specified number of periods (2nd arg). Fourth and fifth arg are optional.

• PV(rate, nper, pmt, fv, type) displays the present value of a series of equal payments (3rd arg), at a fixed rate (1st arg), for a specified number of periods(2nd arg). Fourth and fifth arg are optional. PV(.1,5,500) shows the current value of five payment of $500 at a 10% rate. When someone wants to pay your debt by installment.

• NOW( ), displays the current date or time based on the computer’s clock. Just type it in a cell

8.1 Using the summation button

The SUM function is used to sum across rows or columns that a toolbar button (the Σ button) is available to automate the procedure. To illustrate its use, suppose you have a table of numbers in the range A2:D6. We can easily compute the sum of each row and place results in range E2:E6, and compute the column sums and place results in the range A7:D7.

Select the range(s) where you want the sums (E2:E6 and A7:D7–remember how to select multiple ranges), and click on the summation button.

Note that if you select multiple cells, you get the sums automatically. If you select a single cell (such as when you have a single column of numbers to sum), you’ll be shown the sum formula “for your approval” and you’ll have to press Enter to actually enter it. Why does Excel do it this way–your guess is as good as ours!

Try it! Use the summation button to fill in the row and column sums.

[pic]

Alternatively you could enter the formula =SUM(range) into any cell where you want to place results. You can manually enter a range [i.e., type =SUM (A2:D2)] or you may enter =SUM( , then select the range, and then enter the closing parenthesis ).

There are many useful functions in Excel. You should become familiar with the ones most useful to you (for example, financial analysts should learn the financial functions), but here are a few everyone should know. (By the way, we capitalize the names of these functions just for emphasis. However, they are not case sensitive. You can enter SUM or sum, for example, with the same result.)

8.2 To use the COUNT (range) and COUNTIF (range, criteria) function

Enter the formula =COUNT(range), where range is any range. This produces the number of numerical values in the range.

There is a similar function, COUNTA, which counts all of the cells, numerical or otherwise, in the range(s). For example, if cells A1, A2, and A3 contain Month, 1, and 2, respectively, then =COUNT(A1:A3) yields 2, whereas =COUNTA(A1:A3) yields 3.

COUNTIF (range, criteria) finds the number of cells within arrange that meet specified criteria

Try it! Use the COUNT and COUNTA functions to fill in cells E1 and E2.

[pic]

8.3 To use the AVERAGE function

Enter the formula =AVERAGE (range) where range is any range. This produces the average of the numerical values in the range.

Be aware that the AVERAGE function ignores labels and blank cells in the average. So, for example, if the range C3:C50 includes scores for students on a test, but cells C6 and C32 are blank because these students haven’t yet taken the test, then =AVERAGE (C3:C50) averages only the scores for the students who took the test. (It doesn’t automatically average in zeroes for the two who didn’t take the test.)

8.4 To use MAX and MIN functions

Enter the formula =MAX (range) or =MIN (range) where range is any range. These produce the obvious results: the maximum (or minimum) value in a range of cells.

8.5 Using IF functions

IF functions are very useful, and they vary from simple to very complex. We’ll provide a few examples.

Enter the formula =IF(condition,expression1,expression2), where condition is any condition that is either true or false, expression1 is the value of the formula if the condition is true, and expression2 is the value of the formula if the condition is false.

A simple example is =IF(A1 window group > split

12. Insert Chart:

Type the data that you want to include in your chart and then Ribbon > insert > in charts group select the type of chart you want. When you click on chart you will see additional toolbars for charts that will give you three additional menus on tool bar: layout, design and format that can be used for formatting the chart. Selecting Chart Data: Chart data, called the data source, is stored in a range of cells in the worksheet. When you select the data source, include the text you want to use as labels. You can chart more than one series of data. A data series is a group of related information in a column or row of a worksheet that is plotted on the chart.

Selecting a Chart Type: The next step is to select the type of chart you want to create, such as a column chart, a pie chart, or a line chart. Each type of chart has a variety of subtypes you can choose from. The chart types are available on the Insert tab in the Charts group. Choosing the Chart Location: After you select a chart type and style, the chart is inserted as an embedded chart in the center of the worksheet. You can also choose to move the chart to a chart sheet, which is a separate sheet in the workbook that stores a chart. If you need to change the data in the worksheet, the chart is automatically updated to reflect the new data.

You switch between a chart sheet and a worksheet by clicking the appropriate sheet tab.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download