Downloadable files are included with most videos except for test videos. If you order this product, you will not get the printed tests that correspond to the Spring, Fall, and Region-State 3 Practice Packets.

Excel Pivot Tables Basics - In the first video, we focus on the IF formula and construction of IF statements in Excel. You will also learn--how to create pivot tables, how to use/find/display the field list and pivot table-specific ribbons in Excel, how to toggle from the current pivot table setup to the classic (Excel 2003 and before) and pivot table layout/functionality. This lesson will also help you understand the parts of a pivot table (column headers, row headers, values, and report filters), how to structure pivot tables, how to create new fields for you pivot table by grouping; specifically, we'll briefly talk about manually grouping item but then look at how Excel can help you to group dates (to break a date down into multiple fields for year, month, day, etc.), how to display multiple values (or multiple fields in either the rows or columns section) in a pivot table, how to manipulate pivot table layouts to adjust how data is displayed, how to use different provided pivot table layouts (tabular, compact, outline) and how these layouts differ, how to change heading or field names, how to use dragging and dropping or overwriting to adjust the order in which column or row values appear, etc. All videos in this Bundle were recorded with Microsoft 2010 and are compatible with 2013 and later versions withvery few minor differences.These differences in newer versions of Microsoft include: (1) "Insert" Tab may be arranged differently (2) The "Options" may be called "Analyze" tab and (3) In a Pivot Table, the "Row Labels," "Column Labels," and "Report Filters" names have been shortened to read "Rows," "Columns," or "Filters."

Excel Pivot Tables Intermediate - In the second video lesson, we focus on intermediate level features and capabilities of pivot tables in Excel. At this level, we explore duplicating or copying existing pivot tables, grouping manually to create new fields within a pivot table, using subtotals - adding them, determining where they'll show, picking what math function to use for them, etc., formatting values within pivot tables, displaying values in alternative way (as a percentage of row totals, as a percentage of column totals, as a percentage of grand totals, etc.), sorting and filtering fields by values (of that field or other fields) to order data in more useful ways, utilizing filtering to show top 10 (or any other count) or bottom 10 (or any other count) of items, and more.

Excel Pivot Tables Advanced - In the third and final video, we focus on duplicating or copying existing pivot tables, grouping manually to create new fields within a pivot table, using subtotals - adding them, determining where they'll show, picking what math function to use for them, etc, formatting values within pivot tables, displaying values in alternative way (as a percentage of row totals, as a percentage of column totals, as a percentage of grand totals, etc), sorting and filtering fields by values (of that field or other fields) to order data in more useful ways, utilizing filtering to show top 10 (or any other count) or bottom 10 (or any other count) of items, etc. To illustrate these things, we'll go through some examples in Microsoft Excel.

Excel IF Statements Basics - In the first video, we focus on the IF formula and construction of IF statements in Excel. In this lesson, you'll learn how the IF formula works, how to think of the IF formula as a sentence to help with constructing accurate IF statements, how to diagram an IF statement graphically, which will help you to conceptualize and understand how nested IF statements work, which we'll cover in latter videos, what the parts are to an IF statement and how to think about these types of formulae and statements, etc.

Excel Nested IF, PART 1 - In the second video lesson, we introduce you to a nested IF statement in Excel. A nested IF statement is a formula that includes more than one IF statement within it. In this video, you'll learn how to think about construction of IF statements and nested IF statements, how to diagram IF statements and nested IF statements graphically, which will help you to master the creation of complex IF statements, what the parts are to an IF statement and how to think about nesting multiple IF statements in situations where you have multiple decision criteria that need to be considered in order to output the correct value, etc.

Excel Nested IF, PART 2 - In the third video, we dig deeper on the subject of nested IF statements by walking through an example which requires the use of 5 disparate IF statements nested into one formula. A nested IF statement is a formula that includes more than one IF statement within it. In this session, you'll learn how to think about construction of IF statements and nested IF statements, tips and tricks to use to know how to sequence different decision criteria in your nested IF statement, and how multiple different approaches to the same nested IF statement can yield equally viable results despite differences in approach. This will also give you some insight into some personal preferences you may find that you have when it comes to setting up nested IF statements, how to ‘reality check’ your IF statement by walking through some examples to see how sample values are treated by the nested IF statement, etc.

Excel SUMIF & COUNTIF- In the final lesson, we will focus our attention on the COUNTIF and SUMIF formulas in Excel, which allow you to calculate conditional counts and conditional sums. In this video, you'll learn how COUNTIF and SUMIF work and when they're applicable, the input variables for both COUNTIF and SUMIF formulas (including an explanation of when the 'sum range' input variable for a SUMIF formula is an optional input), how to count the number of cells within a range that match a criteria than you stipulate (using the COUNTIF function), how to sum up the values in a range of cells that meet a criteria you stipulate (using the SUMIF formula), how to use the SUMIF formula in such a way that you values you sum are related to but not the same as the values you consider when evaluating your indicated criteria, examples of situations when you may find the SUMIF and COUNTIF formulae to be useful. ALSO - tips and tricks as well as suggestions of what to watch out for when using both COUNTIF and SUMIF, etc. To illustrate these things, we'll go through some examples in Microsoft Excel.

Access Select Query Basics - In the initial lesson, we focus on the most basic type of query within it: the Select Query. In this video, you'll learn about how queries work, how to create queries, how to add and remove tables and queries, how to add or remove fields from queries, hiding and showing fields in query outputs, basic filters and criteria, sorting/ordering query output results, using the group by feature, understanding when and how to use 'where' with group by, etc. To illustrate these things, we'll go through three example select queries.

Access Table Queries - In the second video lesson, we focus on make tables and the make table query feature in Microsoft Access. In this video, you'll learn about how make table queries differ from select and other queries, what a make table query output looks like, how make table query output tables can be used, how to create and run make table queries, why and when you may want to use make table queries in Access, etc.

Access Append/Delete Queries - In the third video, we focus on the append query and delete query features in Microsoft Access. In this video, you'll learn about how append and delete queries work, how they are different from select and make-table queries, how to create the table query, what to watch out for when using them, how to think about and approach "undoing" queries that you've inadvertently run twice, how to duplicate tables, etc. To illustrate these things, we'll go through example append queries and delete queries in Microsoft Access.

Access Update Queries - In the fourth video, we focus on the update query features and functionality in Microsoft Access. In this video, you'll learn how update queries work, how they differ from other types of queries, what to watch out for when working with them, how to create update queries involving multiple tables and constraints, how to think about "undoing" an update query, what to watch out for with update queries, how to build in safety nets such that update queries don't inadvertently result in corrupted or double-counted data, etc. To illustrate these things, we'll go through example update queries in Microsoft Access.

Access Crosstab Queries - In the fifth video, we focus on the crosstab query features and functionality in Microsoft Access. In this video, you'll learn how crosstab queries work, what row headings, column headings and values are in the context of crosstab queries, what the limitations of Access crosstab queries are, how Access crosstab queries are similar and dissimilar to Excel pivot tables, what the limitations and benefits of both crosstab queries and pivot tables are, etc. To illustrate these things, we'll go through example crosstab queries in Microsoft Access (and example pivot table creation and manipulation in Excel to provide a point of reference).

Excel Dates & Times Video - In this 20-minute video lesson, we focus on the dates and times in Microsoft Excel. You'll learn: how dates and times work, different common date and time number formats, whole numbers versus decimals/fractions in dates and times when formatted as general numbers, Common Formulas (TODAY, DAY, MONTH, YEAR, DATE, TIME, WEEKDAY, etc), Basic calculations involving dates and times, etc. To illustrate these things, we'll go through some examples in Microsoft Excel. Note that starting point worksheets are attached as are the ending point worksheets (in the attached Excel workbook) to allow you to work off of the starting document and compare your results to the ending document.

Excel LOOKUP & HLOOKUP Video - In this 16-minute video lesson, we will focus our attention on the VLOOKUP and HLOOKUP formulas in Excel, which allow you to compare lists or pull in data from distinct but related flat data sets. In this tutorial, you'll learn how to use VLOOKUP and HLOOKUP formulas in Excel to compare lists or pull in data from multiple flat data files, the parts to the formulas and how they work, things to look out for when using the formulas, shortcuts and tips to use when using the formulas. To illustrate these concepts, we'll go through some examples in Microsoft Excel. Note that a starting point worksheet is attached as is the ending point worksheets (in the attached Excel workbook) to allow you to work off the starting document and compare your results to the ending document.

Videos are sold as an online subscription and expires June 1 (at end of the school year).

Once your credit card, PayPal, or purchase order is processed/verified, we will create an account for you manually and email you your login credentials. If you already have an account, please login here.

TIP: Once your videos are viewed once, they move to the “Completed” tab. Please be sure to let your students know to look in the “Completed” tab in case someone else on your team has already viewed your videos