Excel Tutorial
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Excel Tutorial as PDF for free.
More details
- Words: 1,151
- Pages: 12
Norton University
Computer II
Computer II Counting Numeric Cells To count only the numeric cells in a range named Data. Syntax =COUNT (Data) or COUNT (value 1, value 2, ......) Cells that contain a date or a time are considered to be numeric cells. Cells that contain a logical value (true or false) are no considered to be numeric cells.
Counting Nonblank Cells COUNTA function to return the number of nonblank cells in a range named Data: Syntax =COUNTA (Data) or COUNTA (value1,value2, ……) The COUNTA function counts cells that contain values, text, or logical values (true or false).
Getting a Quick Count or Sum 1. Selected range. 2. The Status Bar (displays the sum of the values in the selected rang). 3. You can right click the text the Auto Calculate display to bring up a menu with some of other option.
Microsoft Excel 2003
1
Norton University
Computer II
Counting Cell by Using the COUNTIF Function COUNTIF function is useful for single-criterion counting formulas. Syntax =COUNTIF (range, criteria) The COUNTIF function takes two arguments: 1. range: The cell or range that contains the values. 2. criteria: The logical criteria that to include a particular cell in the count.
Conditional Sums Using a Single Criterion The SUMIF function is very useful for single-criterion sum formulas. Syntax =SUMIF (range, criteria, sum_range) The SUMIF function takes three arguments: 1. range: The range containing the value to include a particular cell in the sum. 2. criteria: An expression to include a particular cell in the sum. Microsoft Excel 2003
2
Norton University
Computer II
3. sum_range: The range that contains the cells you want to sum.
Conditional IF The If conditional specify evaluates to True and another value if it evaluated to False. Syntax =IF (logical_test,value_if_true,value_if_false) The IF function takes three arguments: 1. logical_test: argument sets up some kind of equality (A=B), inequality (A<>B), or comparison (A>B, A<=B, and so on) that either is the case (true) or is not (false). 2. lalue_if_true: argument tells Excel what to calculate or input when the logical test is found to be true. 3. lalue_if_false: argument tells the program how to proceed or what to input when the logical test turns out to be false.
Microsoft Excel 2003
3
Norton University
Computer II
Creating a Chart Another useful feature of spreadsheet programs is that they allow you to display your data in the form of graphs and charts very easily. In Excel, charts can be placed anywhere in a workbook, and can be easily moved and modified. 1.
First select the cells containing the data you want to display.
2.
Then click on the Chart Wizard icon from the button on the Standard toolbar.
3.
After you have made your selections by clicking on them, select Next to proceed to the next stage.
4.
Then click Series Tab
Microsoft Excel 2003
4
Norton University
5.
Computer II
Click in the boxes for Name and Values, and then select a worksheet range; or type the name and values in the boxes.
6.
Selected Category (X) axis to Displays data on the selected axis as the default category (x) axis.
7.
Select Next to proceed to the next stage.
8.
Select Next to proceed to the next stage.
9.
Click As new sheet, and then type a name for the new chart sheet in the As new sheet box to place the chart on a new chart sheet.
Microsoft Excel 2003
5
Norton University 10.
Computer II
Click As object in, click a sheet name in the As object in box, to place the chart on a worksheet.
11.
Click Finish.
Microsoft Excel 2003
6
Norton University
Computer II
Sorting Worksheet Data Excel’s Sort feature makes it easy to rearrange the records or even the fields in your table of data or data list. When defining the key fields or rows for a sort, you can specify either an ascending or descending sort order for its data: •
When you specify ascending order (which is the default), Excel arranges text in A-toZ order and values from smallest to largest.
•
When you specify descending order, Excel reverses this order and arranges text in Zto-A order and values range from largest to smallest.
Sorting records in a data list 1.
Selects all the Records in the List
2.
Choose Data
3.
Sort to open the Sort dialog box shown
4.
In the Sort By drop-down list, select the name of the field you want used as the Primary Key in sorting the records.
5.
If you want to sort the records in Descending order using the Primary Key, click the Descending option button.
Microsoft Excel 2003
7
Norton University
Computer II
6. If the Primary Key field contains Duplicates and you want to specify how these records are to be sorted, select the name of the field to Sort By in the first Then By drop-down list.
7. When you finish defining all the keys you need to use in sorting the records in your data list, click OK.
Product Function Multiplies its Arguments. Syntax PRODUCT (number1, number2, ...) Number1, number2, ... are 1 to 80 numbers that you want to multiply.
Microsoft Excel 2003
8
Norton University
Computer II
SQRT Function Returns a positive square root. Syntax SQRT(number) Number is the number for which you want the square root.
POWER Function Returns the result of a number raised to a power. Syntax POWER(number,power) Number is the base number. It can be any real number. Power is the exponent to which the base number is raised.
Microsoft Excel 2003
9
Norton University
Computer II
AVERAGE Function Returns the average of the arguments. Syntax AVERAGE(number1,number2,...) Number1, number2, ... are 1 to 80 numeric arguments for which you want the average.
MAX Function Returns the largest value in a set of values. Syntax MAX(number1,number2,...) Number1, number2, ...
Microsoft Excel 2003
are 1 to 30 numbers for which you want to find the maximum value.
10
Norton University
Computer II
DATEDIF Function DATEDIF is a Function that calculates the number of days, months, or years between two dates. Syntax =DATEDIF ( Start_date,End_Date,Unit code)
Data List Filtering Using AutoFilter enables you to display only the records that you want to see. When AutoFilter mode is on, you can filter the data by selecting values.
Using auto filtering 1. AutoFilter on, position the cell pointer somewhere in one of the cells of the list
Microsoft Excel 2003
11
Norton University
Computer II
2. Choose Data Menu 3. Filter ➪ AutoFilter
Using advanced filtering 1. Before you can use the advanced filtering feature, you must set up a criteria range. 2. A criteria range is a designated range consists of at least two rows. a. All field names from the list. b. The other rows consist of your filtering criteria.
3. Choose Data Menu 4. Filter ➪ Advanced Filter 5. Select
Microsoft Excel 2003
12
Computer II
Computer II Counting Numeric Cells To count only the numeric cells in a range named Data. Syntax =COUNT (Data) or COUNT (value 1, value 2, ......) Cells that contain a date or a time are considered to be numeric cells. Cells that contain a logical value (true or false) are no considered to be numeric cells.
Counting Nonblank Cells COUNTA function to return the number of nonblank cells in a range named Data: Syntax =COUNTA (Data) or COUNTA (value1,value2, ……) The COUNTA function counts cells that contain values, text, or logical values (true or false).
Getting a Quick Count or Sum 1. Selected range. 2. The Status Bar (displays the sum of the values in the selected rang). 3. You can right click the text the Auto Calculate display to bring up a menu with some of other option.
Microsoft Excel 2003
1
Norton University
Computer II
Counting Cell by Using the COUNTIF Function COUNTIF function is useful for single-criterion counting formulas. Syntax =COUNTIF (range, criteria) The COUNTIF function takes two arguments: 1. range: The cell or range that contains the values. 2. criteria: The logical criteria that to include a particular cell in the count.
Conditional Sums Using a Single Criterion The SUMIF function is very useful for single-criterion sum formulas. Syntax =SUMIF (range, criteria, sum_range) The SUMIF function takes three arguments: 1. range: The range containing the value to include a particular cell in the sum. 2. criteria: An expression to include a particular cell in the sum. Microsoft Excel 2003
2
Norton University
Computer II
3. sum_range: The range that contains the cells you want to sum.
Conditional IF The If conditional specify evaluates to True and another value if it evaluated to False. Syntax =IF (logical_test,value_if_true,value_if_false) The IF function takes three arguments: 1. logical_test: argument sets up some kind of equality (A=B), inequality (A<>B), or comparison (A>B, A<=B, and so on) that either is the case (true) or is not (false). 2. lalue_if_true: argument tells Excel what to calculate or input when the logical test is found to be true. 3. lalue_if_false: argument tells the program how to proceed or what to input when the logical test turns out to be false.
Microsoft Excel 2003
3
Norton University
Computer II
Creating a Chart Another useful feature of spreadsheet programs is that they allow you to display your data in the form of graphs and charts very easily. In Excel, charts can be placed anywhere in a workbook, and can be easily moved and modified. 1.
First select the cells containing the data you want to display.
2.
Then click on the Chart Wizard icon from the button on the Standard toolbar.
3.
After you have made your selections by clicking on them, select Next to proceed to the next stage.
4.
Then click Series Tab
Microsoft Excel 2003
4
Norton University
5.
Computer II
Click in the boxes for Name and Values, and then select a worksheet range; or type the name and values in the boxes.
6.
Selected Category (X) axis to Displays data on the selected axis as the default category (x) axis.
7.
Select Next to proceed to the next stage.
8.
Select Next to proceed to the next stage.
9.
Click As new sheet, and then type a name for the new chart sheet in the As new sheet box to place the chart on a new chart sheet.
Microsoft Excel 2003
5
Norton University 10.
Computer II
Click As object in, click a sheet name in the As object in box, to place the chart on a worksheet.
11.
Click Finish.
Microsoft Excel 2003
6
Norton University
Computer II
Sorting Worksheet Data Excel’s Sort feature makes it easy to rearrange the records or even the fields in your table of data or data list. When defining the key fields or rows for a sort, you can specify either an ascending or descending sort order for its data: •
When you specify ascending order (which is the default), Excel arranges text in A-toZ order and values from smallest to largest.
•
When you specify descending order, Excel reverses this order and arranges text in Zto-A order and values range from largest to smallest.
Sorting records in a data list 1.
Selects all the Records in the List
2.
Choose Data
3.
Sort to open the Sort dialog box shown
4.
In the Sort By drop-down list, select the name of the field you want used as the Primary Key in sorting the records.
5.
If you want to sort the records in Descending order using the Primary Key, click the Descending option button.
Microsoft Excel 2003
7
Norton University
Computer II
6. If the Primary Key field contains Duplicates and you want to specify how these records are to be sorted, select the name of the field to Sort By in the first Then By drop-down list.
7. When you finish defining all the keys you need to use in sorting the records in your data list, click OK.
Product Function Multiplies its Arguments. Syntax PRODUCT (number1, number2, ...) Number1, number2, ... are 1 to 80 numbers that you want to multiply.
Microsoft Excel 2003
8
Norton University
Computer II
SQRT Function Returns a positive square root. Syntax SQRT(number) Number is the number for which you want the square root.
POWER Function Returns the result of a number raised to a power. Syntax POWER(number,power) Number is the base number. It can be any real number. Power is the exponent to which the base number is raised.
Microsoft Excel 2003
9
Norton University
Computer II
AVERAGE Function Returns the average of the arguments. Syntax AVERAGE(number1,number2,...) Number1, number2, ... are 1 to 80 numeric arguments for which you want the average.
MAX Function Returns the largest value in a set of values. Syntax MAX(number1,number2,...) Number1, number2, ...
Microsoft Excel 2003
are 1 to 30 numbers for which you want to find the maximum value.
10
Norton University
Computer II
DATEDIF Function DATEDIF is a Function that calculates the number of days, months, or years between two dates. Syntax =DATEDIF ( Start_date,End_Date,Unit code)
Data List Filtering Using AutoFilter enables you to display only the records that you want to see. When AutoFilter mode is on, you can filter the data by selecting values.
Using auto filtering 1. AutoFilter on, position the cell pointer somewhere in one of the cells of the list
Microsoft Excel 2003
11
Norton University
Computer II
2. Choose Data Menu 3. Filter ➪ AutoFilter
Using advanced filtering 1. Before you can use the advanced filtering feature, you must set up a criteria range. 2. A criteria range is a designated range consists of at least two rows. a. All field names from the list. b. The other rows consist of your filtering criteria.
3. Choose Data Menu 4. Filter ➪ Advanced Filter 5. Select
Microsoft Excel 2003
12
Related Documents
Excel Tutorial
November 2019 26
Excel Tutorial
June 2020 15
Excel Tutorial
May 2020 23
Tutorial Excel
May 2020 20
Tutorial Excel
October 2019 29