MS-EXCEL - Weebly



MS-EXCEL

: Cell reference

In Excel, a cell reference identifies the location a cell or group of cells in the spreadsheet. Sometimes referred to as a cell address, a cell reference consists of the column letter and row number that intersect at the cell's location. Note that when listing a cell reference, the column letter is always listed first.

Cell references are used in formulas, functions, charts, and other Excel commands.

Such as: D47, R87, E45:E77

Relative cell reference:

In Excel and other spreadsheets, a relative cell reference identifies the location of a cell or group of cells.

Cell references are used in formulas, functions, charts , and other Excel commands.

By default, a spreadsheet cell reference is relative. What this means is that as a formula or function is copied and pasted to other cells, the cell references in the formula or function change to reflect the function's new location.

In contrast, an absolute cell reference does not change when it a formula is copied and pasted to other cells.

A relative cell reference consists of the column letter and row number that intersect at the cell's location.

5An example of a relative cell reference would be C

454, G15, or Z

Absolute cell reference

In Excel and other spreadsheets, an absolute cell reference identifies the location a cell or group of cells.

Cell references are used in formulas, functions, charts , and other Excel commands.

An absolute cell reference consists of the column letter and row number surrounded by dollar signs ( $ ).

An example of an absolute cell reference would be $C$4, $G$15, or $A$345.

Note: An easy way to add the dollar signs to a cell reference is to click on a cell reference and then press the F4 key on the keyboard.

An absolute cell reference is used when you want a cell reference to stay fixed on a specific cell.

This means that as a formula or function is copied and pasted to other cells, the cell references in the formula or function do not change.

By contrast, most cell references in a spreadsheet are relative cell references, which change when copied and pasted to other cells.

FUNCTION WIZARD PROJECT

In this section you’ll learn how to activate the FUNCTION WIZARD, use the FUNCTION WIZARD to find Excel’s inbuilt functions, and how they can help you.

You can avoid having to remember the complex syntax for each function, and to find out more about functions you come across in spreadsheets.

FUNCTION WIZARD is Excel’s ways of helping developers and users understand and use functions.

To activate

❖ you can either click on this fx button or

❖ you can say Insert

❖ Function,

❖ when you click on that a menu similar to this will appear, depending on the version of Excel you have,

❖ now what you’ll have here is a couple of categories, and all the functions that sit in there.

❖ So for example if you click on Look up &Reference all the functions that exist in that section will pop up and you can look through this and actually by looking at the description see if it meets what you need,

I highly recommend you have a look at these every now and again, or look at our website and get some training material on some of the more useful functions.

How this helps as well is, if you have a spreadsheet with a function in it that you don’t fully understand, you can actually backtrack into the FUNCTION WIZARD to find out what is actually happening. So for example

❖ if we click on this cell, we’ll see the function is a simple IF function,

❖ if you click on the FUNCTION WIZARD in there, up pops up the IF function arguments and it explains that that is the logical tests happening,

❖ if its true that’s what’s happening

❖ and if it false that’s what’s happens and it’ll explain what the If function does,

Now lets say you understand the If function, but you don’t know what this ABS does.

❖ Click in the ABS itself

❖ click on the FUNCTION WIZARD

❖ and up will pop that function argument.

So there’s the cells being used, but it tells you that ABS returns the absolute value of a number - that is a number without its sign. So you can quickly understand what is happening and look into it.

Now different versions of Excel handle that slightly differently. What you might find is that on your version the way it’ll do it is

❖ once you’ve clicked in it

❖ you’ll be able to click over there

❖ and there’ll be a little fx next door to it ,and you can click it again, all depends on the version

But this allows you understand any function that is used in a spreadsheet, and so you don’t have to worry about running back to the developer to learn what functions they have actually used

For saving your file from file select save as:**

Save: for save your file then select location your file on the hard disk then press saves.

You can change type of your file from the save as type on the drop down list at the same screen(save as)

**header and footer:

To add header or footer for any excel sheet:

From file select page setup, then from the sub menu for page setup select header or footer. then press custom header button then screen will be appear which allow you to insert any thing like text, image, page number, date, time, path and file name.

You can do the same thing if you want to add a footer.

**to select type of your page portrait or landscape:

From main menu then file then page setup then page then portrait/landscape

**to define margins

Main menu then file then page setup then margins

**insert chart:

Main menu then insert then chart then select type of your chart then next then select data range then finish

-now you can move the chart to any spreadsheet by cut and then paste

**insert picture:

Main menu then insert picture from file then go to location, the picture on the hard disk then open

**format cells:

Highlight the cells right click then format cells or direct from formatting toolbar.

**page break:

Main menu then insert then page break

**change cell name:

Highlight the cell then main menu then insert then name then define then write new name then press OK

**hyperlink:

Highlight the text then insert then hyperlink then write the address then ok

**protection for sheets or book:

Main menu then tools then protection then protect then sheet or book then type your password.

**insert diagram:

Insert then diagram then select diagram type then ok

**hide (cells, rows, columns, sheets):

From format then select one of them then hide, but you have to highlight it first.

Verify your data entry

*validation rules in excel:

1. Highlight cells which you want to add rules for them

2. Select the data menu then validation …this will open the data validation window.

3. The rule is set in the setting tab

4. Use drop down menu in the allow: how to select the type of data to allow. for example: if you are going to enter numbers between 0.8 and 2.5 select the decimal from this menu

5. Select between from data: drop down menu if it is not already visible.

6. Set the minimum value to 0.8 by typing this value

7. Set the maximum value to 2.5 by typing this value

8. If you wish to give the user information about the data to be entered in this cell, use the input message.

9. To enter an error alert, if the user enters the wrong data, select the error alert tab.

10.

Enter stop as a validation style this will prevent invalid data being entered into these cells.

11. Eneter the title for this error message.

12. Enter the contents of the error message

13. Click on ok to enter these setting for validation

**how to protect parts of the spreadsheet:

1. Highlight this part (cells)

2. Select the format menu then cells …this will open the format cells window

3. Select protection tab.

4. Tick the locked check box then press ok

**how to protect worksheet

Select the tools menu followed by protection then protect sheet…then tick the check box to add any votes.

Spreadsheet - Excel

Functions In MS-Excel:

1. Sum function: finds the total for a numeric range of cells.

Data in these cells should be numeric. EX:- =sum(A1:A10)

2. Average function:- finds the average for a numeric range of cells. Data should be numeric. EX:- =average(A1:A10)

3. Maximum function: finds the maximum value between a numeric range of cells. EX:- =MAX(A1:A10)

4. Minimum function: finds the minimum value between a numeric range of cells. EX:- =MIN(A1:A10)

5. Count function: counts a numeric range of cells.

Suppose we want to count the number of numeric entries in the range C3 to C30. we can use =COUNT(C3:C30). Any blank lines or text entries in the range are not counted.

6. Counta function: to count a number of items or names of people

(not numeric entries as the count function) we need to be able to count text entries. To do this we can use =COUNTA(C3:C30) you need to make sure that heading are not included in the range so that they are not counted as well. Again blank lines are not included.

7. Countif function:

Counts the number of cells within a range that meet the given criteria. COUNTIF(range,criteria) Range is the range of cells from which you want to count cells.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be counted.

For example, criteria can be expressed as 32, "32", ">32", "apples".

[pic]

[pic] [pic]

[pic][pic] [pic]

❖ [pic] [pic]

Function Wizard

In excel there is a help tool for functions called the function wizard.

There are two ways to get the function wizard. If you look at the standard toolbar, the function wizard icon looks like the icon on the right.

The other way to get to the function wizard is to go to the menu INSERT -- down to FUNCTION.

How to Use Excel Filtering to Find Data Fast:

Learn how to use Excel’s AutoFilter and free yourself up for other tasks.

Many of us find Microsoft Office Excel spreadsheets very useful for compiling information about customers, products, sales revenues and other types of data. But when the volume of data in a single worksheet grows to fill dozens of columns or rows, sorting through it can be a challenge. If you want to isolate, for example, your top 10 customers in a particular region over the last six months, you might spend a long time reviewing your data entries.

Fortunately, Excel includes an easy-to-use AutoFilter to show just what you want to see and hide the rest. Filtering doesn't change your data in any way. As soon as you remove the filter, all your data reappears, exactly the same as it was before.

Here's how to use the AutoFilter tool in Excel.

1. Make Sure the Data Type Is the Same in Each Column

In your worksheet, the top row of each column should have a heading that describes the contents of the column, such as "Product Number" or "Customer."

[pic]

The data in each column should all be the same type. For instance, do not mix text in a column with numbers, or numbers in a column with dates.

2. Activate the AutoFilter

Now click inside any data cell and activate the AutoFilter by doing the following:

|• |In Office Excel 2003, click the Data menu, point to Filter, and then click AutoFilter. |

|• |In Office Excel 2007, click the Data tab and in the Sort & Filter area click Filter. |

The AutoFilter arrows now appear to the right of each column heading.

[pic]

Note: If you select an entire column instead of a single cell before clicking the AutoFilter command, an AutoFilter arrow will appear only on the selected column, not on all columns of the data.

3. Start Filtering Data

Suppose your worksheet contains customer sales data. Each customer entry includes information about the customer's location, products they purchase, purchase dates, and revenues and profits from each purchase. Perhaps you want to view sales activity only for those customers located in the West region. Excel can help you do this.

To view only the sales activity from customers in the West region, you click the AutoFilter arrow in the column with the Region heading. When you click an AutoFilter arrow, a list is displayed. The list contains each of the items in the column, in alphabetical or numeric order, so that you can quickly find the item you want. In this instance, you scroll to West, and click it.

[pic]

When you click West, Excel hides all the rows on the worksheet except for those that contain that text in this column.

[pic]

4. Apply Additional Filters

If you want to focus on even more specific information, you can filter again on another column, and then again on another column, and so on. You can click the arrow next to any heading in any column to apply a filter.

After filtering by Region, for example, you can click the AutoFilter arrow on the Product Number column and filter that column to see only the West region customers who purchased product number 12-100.

[pic]

You can filter columns in any order you choose. The filters are applied progressively, in the order you apply them. Each filter limits the data to which you can apply the next filter.

5. Use Advanced Filtering Techniques

Excel also enables you to perform more intricate types of filtering. Two particularly useful types are the Top 10 filter and custom filtering.

Finding the Top (or Bottom) 10 in a column

You can use the Top 10 filter on columns of numbers or dates. With Top 10 you can find either the top items or the bottom items (the smallest or largest numbers or dates). And you're not limited to finding the top 10 items or the bottom 10 items. You can choose how many items you want to see: only 1 or as many as 500. You can use Top 10 to find the highest-priced or lowest-priced products, to identify employees with the most recent hire dates, or to see the top or bottom student grades.

To use Top 10 on a column of data in Excel 2003, click on a data cell in the column and then click the column's AutoFilter arrow.

|• |In Excel 2003, click (Top 10…) near the top of the drop down list. |

|• |In Excel 2007, click Numbered Filters and then select Top 10. |

The Top 10 AutoFilter dialog box opens. In the dialog box, select either Top or Bottom. Then select a number. Finally, select either Items or Percent.

You can filter columns in any order you choose. The filters are applied progressively, in the order you apply them. Each filter limits the data to which you can apply the next filter.

Using custom filters

When you filter by choosing from the AutoFilter drop-down list, you hide everything except your single choice. If you want to see more than one selection in a column, you can create custom filters.

To create a custom filter,

|• |In Excel 2003, click (Custom…) near the top of the drop down list. |

|• |In Excel 2007, click Numbered Filters and then click Custom Filter. |

The Custom AutoFilter dialog box opens. You can now enter two filtering requirements for the column of data. For example, you could see customers who purchased product numbers 12-100 and 12-500.

Note: Be sure to select the Or button or you will see no results at all.

[pic]

6. Turn Off Filtering

How you remove filters depends on how many filters you have applied, and from how many columns you wish to remove filters.

|• |To remove a filter from one column, click the AutoFilter arrow next to that column, and then click All. That command will |

| |display the rows hidden by that filter. |

|• |To remove all filters at once, point to Filter on the Data menu, and then click Show All. That command will display all |

| |rows hidden by all filters on the worksheet but leave AutoFilter turned on. |

|• |To turn off AutoFilter, point to Filter on the Data menu, and then click AutoFilter. |

|• |To remove the filters from your spreadsheet, simply deselect AutoFilter (Excel 2003) or Filter (Excel 2007) on the Excel |

| |menu. All the data in the spreadsheet is revealed again. |

The IF Function - Step by Step

1 of 8

[pic]

How the IF function works

Note: This tutorial can be used with all versions of Excel up to and including Excel 2007. There is also a tutorial specific to Excel 2007.

The Excel IF function checks to see if a certain condition is true or false. If the condition is true, the function will do one thing, if the condition is false, the function will do something else.

The IF function we are using in this tutorial asks if the value in column A is greater than the value in column B. If it is, the IF function will place the statement "A is larger" in column D. If it is not, the IF function will place the statement "B is larger" in column D.

Our IF function will be entered into cell D1 and it looks like this:

=IF(A3 > B3,"A is larger","B is larger")

Note: the two text statements "A is larger" and "B is larger" are enclosed in quotations. In order to add text to an Excel IF Function, it must be enclosed in quotation marks.

2 of 8

Enter the Data for the IF Function

[pic]

Enter the data for the IF function

It is always a good idea to enter all your data into a spreadsheet before you begin writing functions or formulas. This way you will know if there are any layout problems and it is less likely that you will need to make corrections to your functions later.

To begin this tutorial, add the following data to a worksheet so it resembles the example above:

Cell Data

A3 -- 23

A4 -- 30

A5 -- 45

A6 -- 57

B3 -- 20

B4 -- 45

B5 -- 40

B6 – 60

3 of 8

Starting the IF Function

[pic]

Starting the IF function

When you write an IF function in Excel, you always start by typing the equal sign in the cell where you want the answer to appear. Following this, for our tutorial, is the IF keyword and the opening round bracket.

For our tutorial:

Click on cell D1 with your mouse pointer and type the equal sign followed by the “IF” keyword and a left or opening round bracket. So far in cell D1 you should have:

=IF(

4 of 8

Parts of the IF Function

[pic]

Parts of the Excel IF function

Inside the round brackets there are three parts or arguments to an IF Function.

The Arguments

1. the logic test - in this tutorial it is A3 > B3

2. the value if true - in this tutorial it is "A is larger"

3. the value if false - in this tutorial it is "B is larger"

Between each argument of the IF function, the comma is used as a separator. The comma is what tells Excel when each argument of IF function ends and the next section begins.

Adding Separators

Therefore, as we complete the IF function, we will add two separators

1. one between the logical test and the “value if true” arguments

2. one between the “value if true” and the “value if false” arguments.

5 of 8

Writing the IF Function Logic Test

[pic]

Writing the IF Function Logic Test

The logic test is always a comparison between two amounts. In this tutorial we are comparing the values in cells A3 and B3. Between the two values we place a comparison operator. Since we want to know if A3 is greater than B3, we will use the Greater Than operator " > " between the two cell references.

Comparison Operators

The comparison operators that can be used in the logical test portion of an Excel IF function are:

Equals (=)

Less than (=)

Not equal to ()

For our tutorial:

type A3 > B3 followed by a comma ","

In cell D1 you should now see:

=IF(A3 > B3,

6 of 8

Adding the "Value if True" Argument to the IF Function

[pic]

Adding the "Value if True" argument to the IF Function

Following our comma separator, we add in the “value if true” argument of the IF function followed by another comma separator.

In this case, if the value in cell A3 is greater than the value in cell B3, we want the function to place a "A is larger" in cell D1.

For our tutorial

type "A is larger" followed by a second comma ","

In cell D1 you should see:

=IF(A3 > B3,"A is larger",

7of 8

Adding the "Value if False" Argument to the IF Function

[pic]

Adding the "Value if False" argument to the IF Function

The last section to add to the IF function is the “value if false" argument of the function followed by the right or closing round bracket.

In this case, if the value in cell A3 is not greater than the value in cell B3, we want the function to place a "B is larger" in cell D1.

For our example

type "B is larger" followed by a right or closing round bracket ")".

In cell D1 you should see the completed IF function:

=IF(A3 > B3,"A is larger","B is larger")

7of 8

Copying the IF Function to Other Cells

[pic]

Copying the IF Function to Other Cells

To complete the worksheet, we need to add the IF function to cells D2, D3, and D4. Since our data is laid out in a regular pattern, we can copy the IF function in cell D1 to the other three cells. As the function copied, Excel will update the cell references to reflect the functions new location.

To copy down our function we will use the Fill Handle.

For our tutorial

1. Click on cell D1.

2. Place the mouse pointer over the black square in the bottom right corner. The pointer will change to a plus sign " + ".

3. Click the left mouse button and drag the fill handle down to cell D4.

4. Release the mouse button. Cells D2 - D4 will be filled with the IF function.

Notice how the cell references in the IF function have been change in cells D2 to D4 to reflect the IF functions new location.

[pic][pic][pic][pic][pic][pic][pic][pic][pic]

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

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

Google Online Preview   Download