Microsoft Excel Power Functions Seminar

Excel Training Customized to Focus on the Functions You Use

We know you don’t need to know or use all 250 plus Excel functions. So, this Excel training is customized to focus on the functions that help you the most.

Microsoft Excel has over 250 functions and many are never used even by Excel professionals. Many of these overlooked functions are powerful time-savers and frequently eliminate the need for complex macros and other techniques. The key is to learn to use the Excel functions that make you most effective.

Don’t overlook the many valuable functions Excel users often miss.

This training is customized to include topics of special concern to your group, department, division or organization. We can even adapt this course to incorporate your data files.

This seminar explores many different functions and associated commands and techniques - all with examples. The course is tailored to the version of Excel you are using.

Who Should Attend

Anyone who could save time and improve efficiency by learning overlooked functions in Excel.

Overview of Concepts and Deliverables Provided

IF, VLOOKUP; other table functions

  • Advanced uses of the IF Function – nested IFs, compound IFs with AND and OR
  • VLOOKUP – lets you look up information in tables in current or other worksheets
  • MATCH – locate data by row
  • INDEX – retrieve by location
  • Using MATCH and INDEX together
  • Range names - create and use with formulas and functions
  • Absolute, Relative and Mixed references

Math Functions

  • Rounding Functions – change calculations
    • ROUND, ROUNDUP, ROUNDDOWN, INT, CEILING, FLOOR, MROUND, TRUNC
  • ODD and EVEN – adjust to nearest odd or even value
  • FACT – factorial; MOD – find remainder
  • RAND and RANDBETWEEN – random number generators

Statistical Functions

  • MEDIAN – find the middle value
  • RANK – rank data without sorting
  • LARGE – find 2nd, 3rd, 4th, etc. largest
  • COUNTBLANK – tabulate blank cells

Array Functions

  • FREQUENCY-for frequency distributions
  • TRANSPOSE – flip row/column orientation
  • Write your own array formulas

Analysis ToolPak

  • How to access even more functions

Text Functions

  • FIND and MID – locate and extract data from text cells
  • LEFT and RIGHT
  • TRIM – remove spaces from text cells
  • Using concatenation with functions
  • UPPER, LOWER and PROPER – adjust case
  • REPLACE and SUBSTITUTE

Date Functions

  • WEEKDAY – day of week identifier
  • NETWORKDAYS – count working days between two dates

Other Functions

  • SUMIF and COUNTIF – functions that tabulate data based on conditions

Information Functions

  • ISBLANK and ISERR
  • ISTEXT and ISNUMBER

 

Microsoft Excel Charts Training

Learn to Create Excel Charts to Show Comparisons, Patterns, and Trends

Learn Excel tricks that turn boring data into attention-getting charts. Use colors effectively, pick the right chart for the occasion, and accentuate or suppress data creatively.

This seminar teaches you how to use the powerful functions and shortcuts for creating Microsoft Excel Charts and Graphs. From creating a pie chart to using Excel pivot tables, this seminar will be tailored to your needs. You will learn to use Excel’s chart functions and the many visual and graphical features that make your data presentation come alive.

The course is tailored to the version of Excel you are using. Typically, this course is a 1/2-day program. We often combine it with other programs for a full day of training for one or more departments or groups.

Who Should Attend

Anyone who wants to add spice, functionality and pizzazz to your Excel worksheets and charts.

Overview of Concepts and Deliverables Provided

Techniques for creating charts

  • Instantly create charts on another sheet
  • Create a chart on your worksheet with just two clicks
  • Re-size, move and copy charts
  • Gather data from multiple worksheets

Select from over 100 chart types and styles

  • Manipulate pie, stacked-column and line charts
  • Mix chart types (column and line, column and area, etc.) on the same chart
  • Use multiple Y-axis charts

Customize charts

  • Change the row/column orientation of charts
  • Use drag and drop techniques to add data to charts
  • Control colors for bars, columns, backgrounds, wedges and lines
  • Control legend and title placement
  • Insert moving average, logarithmic and polynomial trendlines
  • Adjust scaling for chart types
  • Rotate 3-D column and pie charts
  • Add unattached text and customized arrows
  • Use ClipArt to build columns charts

 

Mastering Excel Chart and Graphical Features

Training Makes Excel Charts & Graphs Easy - Learn how to turn numbers into results-oriented charts and graphs!

Microsoft Excel offers a wide variety of chart types and sub types, to help you present data and make your point quickly and effectively.

This seminar helps you master Microsoft Excel Charts. We show you how to select and modify built-in chart types, create your own combination charts, and make custom chart types. Discover MS Excel’s many visual and graphical features that can add functionality and pizzazz to your worksheets and charts. Learn how to use the Forms toolbar for creative interactivity, enhanced visual appeal and much more.

Who Should Attend

Anyone who needs to use charts when working in Excel.

Training Benefits of this Computer Skills Course

  • Learn new techniques for creating charts
  • Quickly change the row and column orientation in charts
  • Determine the best chart type to use in any situation
  • Create forms in your charts
  • Add creativity to the charts you design

Overview of Concepts and Deliverables Provided

Techniques for creating charts

  • Instantly create charts on another sheet
  • Use the Chart Wizard to create a chart on the worksheet
  • Adjust charts to align edges on cell boundaries
  • Re-size, move and copy charts
  • Gather data from multiple worksheets

Selecting chart types and styles (over 100 choices)

  • Recognize the limits of pie, stacked-column and line charts
  • Mix chart types (column and line, column and area, etc) on the same chart
  • Use multiple Y-axis charts

Change the row/column orientation of charts

Customize charts

  • Use drag and drop techniques to add data to charts
  • Control colors for bars, columns, backgrounds, wedges and lines
  • Control the placement of legend boxes and titles
  • Insert trend lines for moving averages, logarithmic, polynomial and others
  • Adjust scaling for all chart types; rotate 3-D column and pie charts
  • Add unattached text and customized arrows
  • Use ClipArt to build the columns in a column chart
  • Use the OFFSET function to create charts that update automatically
  • Change a chart’s location to/from a worksheet/chart sheet

Explore WordArt for titles and headings

Everything you always wanted to know about the Drawing Toolbar

  • Text boxes and AutoShapes
  • Fill effects (gradients, textures, shading), 3D and shadow effects; how to manipulate shapes (aligning, grouping and ungrouping, rotating, flipping, etc.)
  • Use Flowcharts and Connectors

Create forms--text, check boxes, drop arrow lists

How to set up spinner arrows, option buttons, scroll bars and combo boxes

  • Create dynamic charts that change as you click arrows
  • Combine Data Filter and Charting features for dynamic chart presentation

 

Excel Tips, Tricks, & Shortcuts Seminar

Learn Easy, Time-Saving Tips and Techniques to Create Customized, Professional Spreadsheets

Improve spreadsheet skills immediately with the Excel tips, tricks and techniques you learn in this seminar. Much of what you will learn is not found in standard Excel courseware.

Discover the power of Excel in this spreadsheet training for Microsoft Excel!

This course successfully builds Excel skills with easy-to-follow demonstrations that walk you through every click! Our unique interactive approach gives you the opportunity to develop new skills in a safe learning environment. The course is continually updated to make certain your Excel training gives the latest and most useful Excel techniques.

Who Should Attend

Anyone who uses Excel, wants to improve their skills and learn new ways to make the most of your spreadsheets.

Training Benefits of this Computer Skills Course

  • Learn new Excel keyboard shortcuts
  • Gain new Excel skills that you have never seen before
  • Learn ways to select and navigate certain groups of cells
  • Manipulate data quickly and efficiently
  • Plus so many more tips, tricks and shortcuts!

Overview of Concepts and Deliverables Provided

Keyboard Shortcuts

  • Instantly toggle between displaying formulas or values in a worksheet
  • Instantly show all cells that are dependent on any cell; show all cells that contribute to a cell's content
  • Instantly insert the current date or time in a cell; Instantly insert a new worksheet in the current workbook
  • Instantly create a chart on a new worksheet or click twice to create a chart in the current worksheet
  • Instantly jump to the beginning or end of a column or row with the mouse or keyboard
  • Instantly select just the visible cells from a range that includes hidden columns and rows
  • ...plus a list of over 100 keystroke shortcuts

Selection and Navigation shortcuts

  • Highlight all cells containing formulas; all cells with values; all cells with text
  • Select all blank cells within a selected range
  • Zoom with the mouse wheel
  • Enter the same formula, function or data in multiple cells in one action

Formatting Shortcuts

  • Create multi-line entries in cells during data entry
  • Create special formats to display large values in thousands or millions
  • Create split-screens and freeze/unfreeze panes with double-clicking techniques
  • Insert, delete or hide rows/columns with just two clicks
  • Highlight every fifth row with a simple conditional formatting formula

Data Manipulation

  • Learn the best ways to copy, move and insert data ranges without commands
  • Copy a formula down a column instantly
  • Transpose data from row-column to column-row orientation
  • Copy and paste values only with a simple drag technique
  • Insure all entries in a range are unique; Create a list of unique entries from a range with repeating entries
  • Copy and paste visible cells only (ignoring cells that are part of hidden columns and rows)

Auto-Fill Techniques

  • Build date lists quickly – weekdays only, every Friday, last day of each month, etc.
  • Create numerical lists fast; Start a numerical list with a single cell; Create time series

Formulas and Functions

  • Adjust all numerical cells in a range by a set value or percent without using formulas
  • Quickly adjust relative, absolute and mixed addresses
  • Edit portions of formulas to see interim results
  • Determine day of week in date entries; Count the number of unique entries in a range

Worksheet Images and Charts

  • Capture a frozen image of part of your worksheet (like a snapshot) before changing data
  • Capture a changing image of a worksheet range (like a TV camera) and put it elsewhere
  • Create animated charts that work with scroll bars
  • and much, much more...