info@excelwithwayne.com

Excel With Wayne

Menu

Mini Course Syllabus

BASIC FORMULAS INDEX, MATCH, AND LOOKUPS

This chapter covers Basic Formulas and consists of 18 topics.
 
  • Basic Formulas Part One – This video explains how to copy a formula not involving “$” signs.
  • Basic Formulas Part Two – This video explains how to copy formulas across rows and down columns, the meaning of $ signs, and setting up a dynamic model of customer evolution.
  • Basic Formulas Part Three – This video explains how to create a worksheet that computes students final grades based on homework and exams.
  • Diving Score – This video explains using the LARGE function to help compute Olympic Diving Scores.
  • Gini Index – This video shows how to use Excel to compute a complex important formula (Gini index measuring income inequality). It also discusses Order of Operations (PEMDAS).
  • Body Mass Index – This video shows how to use PEMDAS to compute Body Mass Index (BMI).
  • Cost Curves – This video explains how to use PEMDAS to compute basic economic cost curves (Average Cost, Marginal Cost, etc.).
  • Retirement – This video explains how to model the evolution of a retirement fund.
  • Modeling Fish in a Pond – This video explains how to create a dynamic model that shows the evolution of the number of fish in a pond.
  • Amortization Schedule – This video explains how a loan payment reduces the balance of a loan by the end of the loan term.
  • Vertical Lookup – This video shows how to use the VLOOKUP function to compute a tax rate based on income and a products price based on its code.
  • Horizontal Lookup – This video uses the HLOOKUP function to lookup sales and employees during each month.
  • Product Prices Based on Date – This video uses a VLOOKUP to determine a product’s price based on a date when the price changes over time.
  • Vertical Lookup From Multiple Worksheets – This video shows how to use a VLOOKUP to efficiently pull multiple columns of data from different worksheets.
  • Vertical Lookup From Multiple Columns – This video shows how to efficiently pull multiple columns of data using a VLOOKUP and the COPY command.
  • Index Function – This video introduces the INDEX function.
  • Index With Rows and Columns – This video shows how to use the INDEX function to pick off an entire row or column.
  • Match Function – This video defines the MATCH function and explains the three values (-1,0 and, +1) that are related to the lookup type.
  • Combining Match and Index – This video shows how to combine the MATCH and INDEX functions to lookup the distance between two cities based on their names.
  • Match, Index, and Large Functions – This video shows how to use the MATCH, INDEX, and LARGE functions to find the country with the n’th largest population.

RANGE NAMES

This chapter covers Range Names and consists of 7 topics.
 
  • Name Box – This video shows how to create range names using the Name Box.
  • Create From Selection – This video shows how to create range names based on spreadsheet text using Create from Selection and using the Name Manager to edit and delete range names.
  • F3 Trick – This video shows how to use the F3 key to paste a range name into a formula.
  • Name Entire Row or Column – This video shows how to name an entire row or column.
  • Apply Names – This video shows how to ensure that if you create range names, they will show up in previously created formulas.
  • Worksheet and Workbook Names – This video shows how you can create range names that apply to only a single worksheet or the entire workbook.
  • Vertical Lookup With Range Names – This video shows how to use Range names in VLOOKUP formulas.

TEXT FUNCTIONS

This chapter covers Text Functions and consists of 7 topics.
 
  • Left, Right, Mid, Len, and Concatenate Functions – This video shows how to use the LEFT, RIGHT, MID, LEN and CONCATENATE functions to manipulate text in a single cell.
  • Find Function and Text to Columns – This video shows how to use the FIND function and TEXT to COLUMNS to parse data in a single cell.
  • Histograms – This video shows how to use the REPT function to create a simple histogram.
  • Clean Function – This video shows how to Use the CLEAN, FIND, and SUBSTITUTE functions to remove invisible characters from your data.
  • Flash Fill – This video shows how to use FLASH FILL in Excel 2013 and 2016 to automatically perform the work of text functions.
  • Control + E and Flash Fill – In this video we show how Control + E makes Flash Fill perform better.
  • The ROUND, ROUNDUP, ROUNDDOWN, and INT Functions – In this video we show how to use the ROUND, ROUNDUP, ROUNDDOWN, and INT functions to round numbers in any desired fashion.

DATE AND FINANCIAL FUNCTIONS

This chapter covers Date and Financial Functions and consists of 11 topics.
 
  • Date Functions Part One – This video shows how to use the YEAR, DAY, WEEKDAY, DATE, WORKDAY, and NETWORKDAYS functions to manipulate dates.
  • Date Functions Part Two – This video shows how to use the DATEDIF function to find years or months between two dates.
  • Static Time and Date – This video shows how to insert a Static Date (a date that does not change) into a spreadsheet.
  • Custom List – This video shows how to create a Custom List (like all NFL teams) that you can drag to Autofill.
  • MLK Day – This video shows how to find the date of Martin Luther King Day for a given year.
  • Net Present Value – This video shows how to use the NPV function to find the present value of a sequence of cash flows.
  • XNPV – This video shows how to use the XNPV function to find the present value of irregularly spaced cash flows.
  • Internal Rate of Return – This video shows how to use the IRR function to find the Internal Rate of Return of a sequence of cash flows.
  • Modified Internal Rate of Return and XIRR – This video shows how to use the XIRR function to find the IRR of irregularly spaced cash flows and using the MIRR function to find the Modified Internal Rate of Return.
  • Present Value and Future Value Functions – This video shows how to use the PV function to evaluate an annuity and the FV function to evaluate the accumulation of money for retirement.
  • Mortgage Functions – This video shows how to use the PMT, IPMT, PPMT, CUMPRINC, and CUMIPMT functions to analyze loans and mortgages.

AUDITING TOOLS, CIRCULAR REFERENCES, AND IF FUNCTIONS

This chapter covers Auditing Tools, Circular References, and IF Functions and consists of 10 topics.
 
  • Auditing – This video shows how to use Precedents to see which cells are needed to compute a formula and using Dependents to find cells that are dependent on spreadsheet inputs.
  • Circular Reference – This video shows how circular references can be used and resolved.
  • IF Statements – This video shows how to use IF statements to calculate purchasing costs and analyze the game of craps.
  • Odds and Evens – This video explains how to use IF statements and the MOD function to determine the winner in odds and evens.
  • Annual Revenue – This video shows how to use IF statements to extract annual sales from quarterly sales data.
  • Product Life Cycle – This video shows how to use IF statements to model sales during the product life cycle.
  • Flatten Table – This video shows how to use IF statements to extract the data in a 20 row by 12 column table into 240 different rows.
  • Error Trap – This video shows how to use the IFERROR function to remove error messages from your spreadsheets. It also covers the AGGREGATE function which allows you to ignore error values in calculations.
  • NFL Scores – This video shows how to use the IFERROR function to copy a formula down many rows when every 10th row has headings and not data.
  • The ISERROR Function – Inthis video we show how to use the IF and ISERROR functions to adjust a professor’s number of students based on whether a course lasted half a semester.

DATA TABLES, GOAL SEEK, AND SCENARIO MANAGER

This chapter covers Data Tables, Goal Seek, and Scenario Manager and consists of 5 topics.
 
  • One Way Data Table – This video shows how to use a One Way Data Table to determine how changing a single spreadsheet input affects multiple outputs.
  • Two Way Data Table – This video shows how to use a Two way data table to determine how changes in two spreadsheet inputs affect a single output.
  • Mortgage Data Table – This video shows how to use a Two way data table to determine how the monthly payment on a loan depends on the loan size and annual rate.
  • Goal Seek – This video shows how Goal Seek can be used to determine the number of unit sales needed for a juice bar to break even.
  • Goal Seek Finance – This video shows how to use Goal Seek to determine the annual rate on a loan that makes the monthly payment hit a desired value.
  • Scenario Manager – This video shows how the Scenario Manager can be used to describe how scenarios involving multiple spreadsheet inputs influence multiple spreadsheet outputs.

CONDITIONAL COUNTS AND SUMS

This chapter covers Conditional Counts and sums and consists of 9 topics.
 
  • COUNTIF and COUNTIFS Functions –In this video we show how the COUNTIF and COUNTIFS functions are used to do conditional counts on single or multiple criteria.
  • COUNT, COUNTA, and COUNTBLANK Functions –In this video we show how COUNT, COUNTA, and COUNTBLANK functions are used to count cells in a range containing numbers, non-blanks, or blanks.
  • SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS Functions –This video shows how the SUMIF and AVERAGEIF functions are used to calculate conditional sums and averages based on a single criterion. It also shows how the SUMIFS and AVERAGEIFS functions are used to calculate conditional sums and averaged based on multiple criteria.
  • SUMIFS With Duplicates –This video shows how the SUMIFS function and the Remove Duplicates functionality are used to calculate the sales each sales person made of each product.
  • Subtotals –This video shows how SUBTOTALS are used to determine how much each salesperson sold in each region.
  • Conditional Counts and Sums With Non-Blanks – This video shows how to do conditional sums and counts where one criteria is that the cell does not contain a blank.
  • Benford’s Law – In this video we show (amazingly) that for 70% of all countries the first digit in their population is 1,2,3, or 4!
  • Finding the n’th Match –In this video we show how to find the n’th match to a word in a column.
  • Analyzing NFL Play Effectiveness –In this video we show how to use text functions, IFERROR, COUNTIF, and AVERAGEIF functions to analyze the effectiveness of Adrian Peterson’s off tackle runs.

CONDITIONAL FORMATTING AND DATA VALIDATION

This chapter covers Conditional Formatting and Data Validation and consists of 8 topics.
 
  • Top and Bottom Rules – This video shows how to highlight the 20 warmest temperatures since 1881 and all above average and below average temperatures since 1881.
  • Highlight Cells Rules – This video shows how to highlight duplicates, any cells containing a given text string, and highlighting cells based on criteria involving dates.
  • Data Bars, Colors Scales, and Icon Sets – This video shows how Data Bars, Color Scales, and Icon sets are used to summarize numerical data.
  • Formula Option Part One – This video shows how cells can be formatted based on a complex Excel formulas. For example, highlight in green, each quarter a company’s sales increase, and in red each quarter a company’s sales decrease.
  • Formula Option Part Two – This video continues to show how to conditional format based on a formula.
  • Data Validation – This video illustrates how Data Validation can return an error message when incorrect data is entered. For example, if we enter that an NBA team scored 1000 points, we receive an error message. We also show how to create a drop-down list from which a user selects an input.
  • Conditional Formatting and Sports Part One – In this video we show how to highlight all baseball players who are good on HR’s, Stolen Bases, and Batting Average.
  • Conditional Formatting and Sports Part Two – In this video we show how to use data bars, color scales, and icon sets to format sports data.

TABLES

This chapter covers Tables and consists of 8 topics.
 
  • Worksheet Templates – This video shows that if you need a worksheet for each state set up in an identical format, how to easily use worksheet templates to set up the 50 needed worksheets.
  • Three Dimensional Formula – This video shows how three dimensional formulas can be used to sum data across worksheets.
  • Paste Special – This video shows how the Paste Special Menu can be used to transpose data and divide “spread out cells” in a worksheet by 100
  • Tables Introduction – This video shows how making data a Table ensures that formulas update automatically when new data is added.
  • Table Graphs – In this video we show how making data a Table ensures that charts update automatically when new data is added.
  • Table Slicers – In this video we show how slicers can be used to “slice and dice” the data in Tables.
  • Table Extended – In this video we show how tables make drop down boxes, VLOOKUPS, SUMIF formulas and NPV calculations automatically update when new data is added.
  • Aggregate Function With Slicers – In this video we show how the AGGREGATE function is needed to ensure calculations based on Table Slicers are correct.

PIVOT TABLES

This chapter covers Pivot Tables and consists of 11 topics.
 
  • Introduction to Pivot Tables – In this video we introduce PivotTables and show how to collapse and expand fields, sort data, and drill down to source data.
  • Analyzing Pivot Table Data – In this video we show how to ensure that a PivotTable updates automatically when new data is added and how to use Value Filters to determine customers that generate 80% of our sales.
  • Text Filters – In this video we show how text filters are used to filter a PivotTable based on text.
  • Grouping and Pivot Charts –In this video we show how to group data in a PivotTable and create a chart based on a PivotTable.
  • Value Field Settings and Show Values As -In this video we show how to summarize data as a Percentage of a Row or Column.
  • Calculated Fields –In this video we show how to create a calculated field based on PivotTable columns.
  • Slicers and Filters –In this video we show how slicers allow you to slice and dice sales by country and product.
  • Timelines –In this video we show how to slice and dice PivotTable calculations based on any subset of months, quarters or years. The functionality of this video is for Excel 2013 and Excel 2016 users.
  • Analyzing Surveys and More Pivot Charts – In this video we show how to summarize a survey with a PivotTable and PivotChart.
  • Putting it all Together – In this video we show how to use IF, VLOOKUP, PivotTables, and AVERAGEIFS functions to solve a tax accounting problem.
  • Election Demographics – In this video we use PivotTables and Slicers to analyze preferences of various demographic groups for Donald and Hillary.

DESCRIPTIVE STATISTICS

This chapter covers Descriptive Statistics and consists of 5 topics.
 
  • Histogram Creation – In this video we show how to use a histogram to summarize Cisco’s monthly returns.
  • Histogram Shapes – In this video we discuss symmetric, positively, and negatively skewed histograms.
  • Descriptive Statistics – In this video we discuss measures of typical value, variation about the mean, and skewness.
  • Rule of Thumb – In this video we discuss how to determine normal variation for any data set.
  • More Statistical Functions – In this video we discuss the PERCENT, PERCENTRANK, LARGE, SMALL, and RANK functions.

CHARTING

This chapter covers Charting and consists of 16 topics.
 
  • Combo and Secondary Axis Charts – In this video we create Combination charts and Charts with a Secondary Axis.
  • Handling Missing Data – In this video we show options for handling missing data in charts.
  • Handling Hidden Data – In this video we show options for handling hidden data in charts.
  • Adding Pictures to Column Charts – In this video we show how to add pictures to your column charts.
  • Data Labels – In this video we show how to add Data Labels to a column chart.
  • Data Tables – In this video we show how to add a Data Table below a column chart.
  • Labels From Cells – In this video we show how to label points with labels based on cells in Excel. This is for users of Excel 2013 and Excel 2016.
  • Creating Icon Sets – In this video we show how to use the WINGDINGS3 font to create customized Icon sets.
  • Band Charts – In this video we show how to create a Band Chart to summarize how inventory moves between lower and upper control limits. We also show how to create and use Chart Templates.
  • Check Boxes and Chart Dashboards – In this video we show how to use CheckBoxes to control which series show in a chart.
  • List Boxes and Chart Dashboards –In this video we show how to use List Boxes to control which series shows in a chart.
  • Spinners –In this video we show how to create a Spinner that enables you to change the values of any number of spreadsheet inputs by clicking on your Spinners.
  • Radar Charts –In this video we use Radar charts to summarize employee attributes on multiple dimensions.
  • Bubble Charts –In this video we use a Bubble Chart to summarize sales in different countries on three dimensions.
  • Sparklines Part One –In this video we use Sparklines to visually summarize daily customer counts in different bank branches in a single cell for each branch.
  • Sparklines Part Two –In this video we use Win Loss Sparklines to visually summarize week by week NFL results.

KEYSTROKE SHORTCUTS

This chapter covers Keystroke Shortcuts and consists of 8 topics.
 
  • Keystroke Formatting Shortcuts –In this video we show Keystroke shortcuts that are useful in formatting cells.
  • Worksheet Movement –In this video we show keystroke shortcuts that speed up worksheet movement.
  • Keystroke Formulas –In this video we show Keystroke shortcuts that help you better understand your spreadsheet’s formulas.
  • Cell Selection Shortcuts –In this video we show keystroke shortcuts that speed up selection of cells or worksheets.
  • Hyperlinks –In this video we show how to create Hyperlinks that enable you to click on the Hyperlink and move anywhere in your Workbook.
  • Linking Excel Data to Word or Power Point –In this video we show how to link data and/or charts in Excel to Word so that changes in the Excel Source data are reflected in the Word document.
  • Protecting a worksheet –In this video we show how to protect the formulas in your worksheet so that the user can change the spreadsheet inputs but not alter your formulas.
  • The Format Painter and F4 Key –In this video we show how the Format Painter or F4 key can be used to quickly copy formats.

EXCEL 2016 CHARTS

This chapter covers Excel 2016 Charts and consists of 10 topics.
 
  • Histograms – In this video we show how to use Excel 2016 to create much improved histograms.
  • Pareto Charts – In this video we show how to use Excel 2016 to create much improved Pareto charts.
  • Boxplots One Variable – In this video we show how to use Excel 2016 to create a Boxplot to summarize the results of the 1970 Draft Lottery.
  • Boxplots More Than One Variable – In this video we show how to use Excel 2016 to create a Boxplot that summarizes multiple test scores at three high schools.
  • Waterfall Chart – Time Series Data – In this video we show how to use an Excel 2016 Waterfall chart to summarize the progress of sales during a year.
  • Waterfall Chart – Revenue to Profit – In this video we show how to use an Excel 2016 Waterfall chart to summarize how various cost centers reduce Company Revenue and Profit.
  • Treemap Chart – Bookstore – In this video we use an Excel 2016 TreeMap or Mosaic chart to summarize hierarchical bookstore sales data.
  • Sunburst Chart – Bookstore – In this video we use an Excel 2016 Sunburst chart to summarize hierarchical bookstore sales data.
  • Sunburst Chart – Monthly Breakdown – In this video we use an Excel 2016 Sunburst chart to summarize quarterly and monthly sales data.
  • Treemap Chart -Restaurant – In this video we use the SUMIFS functions to prepare restaurant sales data and then summarize the sales data with a TreeMap chart.

MACROS

This chapter covers Macros and consists of 10 topics.
 
  • Inserting the Developer Tab – In this video we show how to make the Developer Tab appear on your ribbon.
  • Inserting a List of Names – In this video we record a Macro that inserts a list of team members starting in cell A1. We also create a button to run the macro.
  • Relative References With Macros – In this video we show how to use Relative References to make sure our list of names can be entered anywhere in our workbook.
  • Using Macros to Format Cells – In this video we create a macro to apply a desired format to a range of selected cells and show how to run the Macro by clicking on a picture (in this case an Aircraft Carrier).
  • Formatting a Range of Variable Size – In this video we show how to create a macro that formats a range of variable size.
  • Using a Macro to Freeze Values – In this video we create a macro that coverts formulas in a selected range to numerical values.
  • Using Macros For Reporting – In this video we create macros that enable us to toggle in a PivotTable between a view that shows sales for all customers and sales for only our top 20 customers.
  • Creating a Border With a Macro – In this video we show how to create a macro that places a border around a range of selected cells.
  • Personal Macro –In this video we discuss how to save a macro to your Personal Macro Workbook.
  • Sorting Macro –In this video we show how to create a macro that will sort a list by last and first name.

Excel With Wayne is an online Microsoft Excel Training Course taught by Dr. Wayne Winston. Dr. Winston is the world’s foremost Microsoft Excel Expert that teaches at some of the world’s largest organizations including Microsoft and government organizations such as the Navy and Army.

CONTACT INFO

Copyright © 2024 | Excel With Wayne
Facebook-f Twitter Linkedin-in

Report

Harassment or bullying behavior
Contains mature or sensitive content
Contains misleading or false information
Contains abusive or derogatory content
Contains spam, fake content or potential malware

Block Member?

Please confirm you want to block this member.

You will no longer be able to:

  • See blocked member's posts
  • Mention this member in posts
  • Invite this member to groups
  • Message this member
  • Add this member as a connection

Please note: This action will also remove this member from your connections and send a report to the site admin. Please allow a few minutes for this process to complete.

Report

You have already reported this .