Advanced Excel Class in San Diego

This class is intended for:

PC/Windows Users: Excel 2016-2019-365
Certificate of Completion (optional): $5
Course Length: 1 Day

Excel Functions

Basic Functions:

• Sum (sum or total of a range of cells)
• Average (average a range of cells)
• Max (display highest number in a range cells)
• Min (display lowest number in a range of cells)

Count:

• Count (count cells containing values)
• CountA (count non-blank cells)
• CountBlank (count blank cells)
• CountIf (count cells that meet a single criteria - a word, a number, etc.)
• CountIfs (count cells that meet multiple criteria - words, numbers, etc.)

Sum:

• SumIf (add cells that meet a single criteria - a word, a number, etc.)
• SumIfs(add cells that meet multiple criteria - words, numbers, etc.)
• SumProduct (sum cells meeting multiple criteria - words, numbers, etc.)

Average:

• Average (average numbers)
• AverageIf(average cells that meet a single criteria - words, numbers, etc.)
• AverageIfs(average cells that meet multiple criteria - words, numbers, etc.)

If:

• If (assign a true or false result to cells that meet a single criteria)
• Nested Ifs (assign a true or false result to cells that meet multiple criteria)
• Ifs (assign a true or false result to cells that meet multiple criteria)
• If Error (substitute something in place of an error message)
• If And(assign a true or false value to rows that meet multiple criteria)
• If Or(assign a true or false value to rows meet one or another criteria)

VLookup | HLookup | XLookup:

• VLookup (look up data in a row)
• HLookup (look up data in a column)
• XLookup (look up data in a row AND a column)

Financial:

• PMT (calculate payments and interest rates on a loan)
• FV (calculate the future value of an investment)

Text:

• Concatenate | Concat Concatenate (combine data from multiple cells into one cell)
• Text Join (combine data from multiple cells into one cell)
• Trim (remove extra blank spaces between Concatenated text)
• Upper (convert all text to uppercase)
• Lower (convert all text to lowercase)
• Proper (capitalize first letter of each word)
• Left (extract a number of characters from the left side of a cell)
• Right (extract a number of characters from the right side of a cell)
• Mid (extract a number of characters from the middle of a cell)

Date & Time:

• Today (displays currrent day and time)
• Now (display current date)
• Day (extract day from a cell containing a date)
• Month (extract the month from a cell containing a date)
• Year (extract the year from a cell containing a date)
• Date Calculations (determine the number of days/months/years between two dates)

Nested Functions:

Combine functions" "nest" one function within another.

"What If" Tools:

Goal Seek allows you to calculate an unknown value in a given formula, but is only useful for problems that involve finding a single variable. When the desired result of a calculated cell is known, but not the input value that calculation needs to reach that result, you can use Goal Seek.

Solver is a tool that helps you find solutions to problems involving multiple variables.

Scenarios are part of a group of commands that can be called what-if analysis tools. A scenario is a set of values that a user can create and save and then substitute at any time in the worksheet. The user can then switch to any of these new scenarios to view different results in the worksheet. To compare several scenarios, you can create a report that summarizes them on the same page.

Data Tables allow you to analyze data and produce a table to show the results. When a single variable like the unit price changes, use a one-input What-If Table. If the unit price AND number of units sold changes, use the two-input What-If Table.

Basic Recorded Macros:

Create Absolute and/or Relative Macros to automate common, repetitive tasks.

Worksheet Controls:

Add buttons, sliders, and checkboxes to a worksheet to create a "user-friendly" interface for less experienced users.

Call 858-578-9476

Computers Etc. Software Training Center, Inc.
858-578-9476