 Download Download Presentation Excel Lesson 13 Using Powerful Excel Functions

# Excel Lesson 13 Using Powerful Excel Functions

Download Presentation ## Excel Lesson 13 Using Powerful Excel Functions

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
##### Presentation Transcript

1. Excel Lesson 13Using Powerful Excel Functions Microsoft Office 2010 Advanced Cable / Morrison

2. Objectives • Use the COUNT and COUNTA functions. • Utilize the COUNTBLANK function to count blank cells. • Use the Subtotal function with filtered data. • Create a named range. 2 2

3. Objectives (continued) • Use a named range in a function. • Use a SUMIF function. • Search for data using VLOOKUP. • Search for data using HLOOKUP.

4. Vocabulary • condition • criteria • function number • lookup table 4 4

5. Introduction • Excel offers powerful functions to count, sum, and locate specific information. • COUNT function • COUNTA function • COUNTBLANK function • Subtotal function • SUMIF function • VLOOKUP function • HLOOKUP function

6. Using COUNTA versus COUNT • To find the total number of cells that have numeric data, use the COUNT function. COUNT function counts numeric data

7. Using COUNTA versus COUNT (continued) • To count cells with text, use the COUNTA function. • The COUNTA function counts cells if they contain text or a combination of text and numbers. COUNTA function counts cells with text

8. Using COUNTBLANK • The COUNTBLANK function counts the number of cells that do not have any numbers or text entered in them. • You may want to count blank cells to see how many cells do not contain data.

9. Using COUNTBLANK (continued) • COUNTBLANK function counts cells without data

10. Counting Filtered Cells Using theSubtotal Function • The Subtotal function can be used to count, sum, or average filtered data. • The Subtotal function is =SUBTOTAL (function number, data range). • The function numberis a number that represents a calculation.

11. Counting Filtered Cells Using theSubtotal Function (continued) • SUBTOTAL function counts displayed cells

12. Creating Named Ranges • You can give arange a meaningful name. • Named ranges can be used in functions. • Especially helpful if you need to use a range in several functions • In a range name, use an underscore (_), rather than a space, to separate words. • Excel does not accept spaces in named ranges.

13. Creating Named Ranges (continued) • Name Box with name entered

14. Using a Named Range in a Function • Using named ranges in functions is convenient. • You can enter the range name instead of the cell names. • To select and use a named range: • Click the Name Box down arrow. • Select the named range that you want to include in a function.

15. Using a Named Range in a Function (continued) • Function with named range

16. Using the SUMIF Function • The SUMIF function will only total data that meets a certain condition. • A conditionis a requirement that needs to be met. • Criteriaare the conditions you are searching for in a data range. • The SUMIF function is =SUMIF(range,criteria,sum_range).

17. Using the SUMIF Function (continued) • Example of SUMIF Function

18. Creating a VLOOKUP Function • LOOKUP functions allow you to find data that already exists. • A lookup tableis the range of data you create in either a vertical or horizontal format, which Excel will use to match against the criteria. • The VLOOKUP function is used when the lookup table is listed vertically in columns.

19. Creating a VLOOKUP Function (continued) • Example of VLOOKUP function

20. Using the HLOOKUP Function • Use the HLOOKUP function when the data in the lookup table is listed horizontally in rows. • To use the HLOOKUP function, enter the following information into the Function Argument dialog box: • Lookup_value • Table_array • Row_index_num

21. Using the HLOOKUP Function (continued) • Example of HLOOKUP function

22. Using the HLOOKUP Function (continued) • Function Arguments for HLOOKUP dialog box

23. Summary In this lesson, you learned: • The COUNT function counts cells with numeric data and the COUNTA function counts cells with text or a combination of text and numbers. • You can use COUNTBLANK to count cells without any data.

24. Summary (continued) • The SUBTOTAL function can be used to count filtered data. • You can name a range of data. • A named range can be used in a function. • The SUMIF function totals data that meet a certain condition.

25. Summary (continued) • You can use the VLOOKUP function to find data that is displayed vertically in a lookup table. • You can use the HLOOKUP function to find data that is displayed horizontally in a lookup table.