Advanced Excel Class - San Diego

This Advanced Excel class continues where the Intermediate class left off: we cover functions, macros, and lookups.

Useful Functions

Learn 30 important functions every Excel user should know.

Count Functions

  • Count (count cells containing numbers)
  • CountA (count cells containing text)
  • CountBlank (count blank cells)
  • CountIf (count cells that meet a single criteria)
  • CountIfs (count cells that meet multiple criteria)

Sum Functions

  • SumIf (add cells that meet a single criteria)
  • SumIfs (add cells that meet multiple criteria)
  • SumProduct (sum the product cells meeting multiple criteria)

Average Functions

  • Average (average numbers)
  • AverageIf (average cells that meet a single criteria)
  • AverageIfs (average cells that meet multiple criteria)

If Functions

  • If (assign a true or false result to cells that meet a single criteria)
  • And (assign a true or false value to rows that meet multiple criteria)
  • Or (assign a true or false value to rows meet one or another criteria)

HLookup / VLookup / Index / Match Funtions

  • HLookup (look up data in a row)
  • VLookup (look up data in a column)
  • Match and Index (look up data in a column and row)

Financial Functions

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

Text Functions

  • Concatenate (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 Functions

  • Date (calculate days/months/years between two different dates)
  • Month (display the month)
  • Year (display the year)

Miscellaneous Functions

  • "Nest" functions (combine functions - place one function within another)
  • Round Function (round off results to a given number of decimal places)

Named Ranges / Dynamic Ranges

Use Named Ranges in formulas and functions to refer to specific regions of a spreadsheet. Create Dynamic Ranges that automatically expand to include new data added at the bottom of a spreadsheet.

"What-If" Tools

Excel includes several "What-If" tools that will change the way you think about Excel.

GOAL SEEK is a great tool when you know the answer you’re looking for and need to change a single variable to achieve that goal. For example, what would the interest rate on a loan need to be in order to make your monthly payments a certain amount.

SOLVER is a more powerful tool than Goal Seek. Solver helps you find solutions to problems involving multiple variables. It also allows you to set "Constraints" (limits to what Solver can do) so the results are realistic.

SCENARIOS are a set of values that a user can create and then substitute at any time in the worksheet. The user can switch to any of these new scenarios to view different results in the worksheet.

DATA TABLES allow you to analyze data and produce a table to show the results using one or two variables. For example, in a single table you can see the profitability of different combinations of unit prices and units sold.

Macros

Create simple recorded "macros" to automate common, repetitive tasks. NOTE: We do not cover creating or editing VBA macros in this class.

Workbook Controls

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

* Note: This class description is for Excel training in San Diego. We also offer Excel training in San Francisco, Sacramento, San Jose, and Los Angeles.