1 / 25

Excel Lesson 13 Using Powerful Excel Functions

Excel Lesson 13 Using Powerful Excel Functions. Microsoft Office 2010 Advanced. Cable / Morrison. 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.

clem
Download Presentation

Excel Lesson 13 Using Powerful Excel Functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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.

More Related