Previous Episodes
Hide and unhide worksheets securely
(06:06)
Resizing, moving, copying, and manipulating charts and other objects
(10:17)
Using list boxes and combo boxes to facilitate forms creation
(07:36)
Using the FORMULATEXT and N functions for worksheet and formula documentation
(05:53)
Using the ISBLANK and COUNTBLANK functions to check for and tabulate blank cells
(07:55)
Summarizing data from diverse ranges with Data > Consolidate
(09:33)
Using check boxes and Conditional Formatting together for dynamic displays
(12:39)
Becoming more productive with these 10 tiny tips you'll use often
(08:00)
Transposing data and charts for a different perspective on your data
(08:57)
Applying conditional formatting across rows of data
(06:43)
Exploring various layouts for your PivotTable
(04:27)
Splitting and consolidating workbooks
(06:47)
Using date and time as metrics in a PivotTable
(06:53)
Adding illustrations to your workbook
(12:07)
Using Pick list, VLOOKUP, Table, and Chart together
(08:05)
Using formulas to calculate values across worksheets and workbooks
(11:12)
Dynamically presenting data via chart slicers
(07:15)
Parsing your data to create unique lists
(03:21)
Using the REPT function to represent data visually
(03:06)
Using source data from a table to make your PivotTable more dynamic
(07:08)
Accelerating Cut, Copy, Paste, and Insert tasks
(07:26)
Increasing your productivity with custom lists
(05:09)
Highlighting PivotTable results with conditional formatting
(06:10)
Customizing templates to fit your unique needs
(06:06)
Creating dynamic charts with in-cell conditional formatting
(03:26)
Enhancing table filtering with slicers
(05:23)
Converting charts into pictures
(04:51)
Custom grouping in PivotTables
(04:31)
Quick formatting tips
(08:09)
The top five unknown but useful keyboard shortcuts
(06:17)
Using the TREND and GROWTH functions for projecting future results
(05:54)
Using scroll bars and spin buttons to facilitate forms creation
(10:01)
Creating colorful 3D buttons for worksheet annotation and macro buttons
(06:52)
Calculating with hours, minutes, and times of day
(11:02)
Tracking down cell dependencies across multiple worksheets and workbooks
(06:24)
Discovering time-saving tools in the Go To Special feature
(11:22)
Adding, managing, and printing comments for worksheet documentation
(11:22)
Reducing data entry time by expanding AutoCorrect options
(06:10)
Setting up custom views for quick access to different worksheet displays
(06:30)
Using hyperlinks for rapidly switching worksheet locations and jumping to websites
(07:18)
Controlling worksheet security by allowing selected users to edit specific cell ranges
(07:22)
Using option buttons, group boxes, and checkboxes to facilitate forms creation
(08:07)
Exploring what-if scenarios using Goal Seek
(09:55)
Creating text, numerical, date, and time lists in a flash
(06:54)
Creating Variable Conditional Formatting Rules
(08:04)
Handling Dates with unusual formats
(06:05)
Using wildcards (asterisk, tilde, and question mark)
(11:00)
Custom formats using asterisk, semicolon, and brackets
(13:00)
Meeting unusual filtering needs via Advanced Filter
(12:38)
Create an Organization Chart
(08:56)
Two-way lookup using the MATCH and INDEX functions
(11:28)
Using Excel's error-checking functions
(10:37)
Custom formats, relative addressing, and conditional formatting
(08:26)
Auditing
(09:01)
Adding comments and shapes
(07:35)
Drawing borders and border grids
(05:45)
Building a flexible monthly table using functions
(11:44)
Adding pictures as worksheet backgrounds
(04:15)
ADDRESS, ROW, and COLUMN functions
(08:08)
Join data with new CONCAT and TEXTJOIN functions
(06:15)
Working with formulas in tables
(13:02)
Locating Data Validation rules and violations
(07:20)
Creating an Excel template
(07:57)
Restoring missing column titles
(06:41)
Creating a pick list dependent on another pick list
(06:59)
Using Command Shortcuts with the Alt key
(05:51)
Set up a macro for sorting dynamic data
(08:42)
Use random number functions for sorting and creating sample data
(09:57)
Use calcuated fields and calculated items in a PivotTable
(08:57)
Replace characters by position with the REPLACE function
(05:46)
Work with formulas in tables
(09:03)
Keystroke shortcuts from A to Z - using the Ctrl key
(16:16)
Use special tricks for working with dates in Charts
(11:36)
Use a data form for data entry and exploring data
(06:42)
Creating an Excel template to simplify updating a monthly file
(07:10)
Calculate dates efficiently using Excel's hidden function - DATEDIF
(07:36)
Create dynamic sheet name references with the INDIRECT function
(06:45)
Avoid errors and hidden data with the powerful AGGREGATE function
(07:08)
Hide worksheets, row, columns, cells and other Excel elements
(07:26)
Use slicers and charts together for dynamic presentations
(09:04)
Save a chart as a template - use the template for current or new charts
(06:02)
MAXIFS and MINIFS added to the COUNTIF/SUMIF family of functions
(08:12)
Adjust a chart's source data and adjust its series order
(09:07)
Use the OFFSET function for tabulating moving data
(11:23)
Alter numeric data without formulas
(10:19)
Use Flash Fill to rapidly combine or separate columnar data
(10:28)
Adjust banded-row formatting
(14:35)
Change the shape of comment boxes and other objects
(06:32)
How and when to use Excel's 10 rounding functions
(13:26)
Calculate faster with the AutoSum button and AutoSum keystroke shortcut
(06:54)
How to use mixed cell references
(08:25)
Activate the Speak On Enter and Speak Cells features
(05:38)
Accelerate data entry with five special techniques
(12:08)
Insert a text box or shape and link a cell's content to it
(06:58)
Use Watch Window and other techniques to track changing cells
(08:21)
How to create time interval entries
(11:39)
How to create frequency tabulations and distributions
(08:10)
Create an expanded list from a summary using PivotTable techniques
(07:25)
Solve matching issues with matching phone numbers and SS numbers
(10:00)
Use CHOOSE to calculate fiscal quarters and as a VLOOKUP alternative
(07:27)
Use Outlining tools to quickly expand and collapse data for dynamic presentations
(09:19)
Use the SUBTOTAL (and AGGREGATE) functions to avoid double counting
(09:59)
Create lists of all 2nd Tuesdays, last Tuesdays. and other date series
(10:44)
Create linkage formulas that refer to other worksheets and workbooks
(11:49)
Formula nesting: Using multiple functions in the same formula
(13:22)
Keyboard shortcuts for numeric formats
(08:18)
Use the Subtotal command to analyze data with single- or multiple-level subtotals
(09:15)
Work with MAX, MAXIFS, LARGE, and related functions
(08:14)
Use the dynamic TODAY and NOW functions for real-time up-to-date calculations
(09:24)
Use the FIND, MID, LEFT, and RIGHT functions
(10:38)
Flip the left-to-right order of columns with the INDEX function or sort by columns
(08:18)
Working with hidden data when copying cells and creating charts
(10:58)
Use conditional formatting data bars to accentuate negative data
(04:16)
Creative use of sparklines in merged cells with axes
(07:01)
Copy formats quickly using dragging techniques and the Format Painter
(05:33)
Displaying multiple worksheets and workbooks together
(10:13)
Tips for creating and updating sample data
(11:01)
Use conditional formatting to overcome formatting limitations of the IF function
(08:54)
Use VLOOKUP, MATCH and Index functions with array formulas
(10:38)
Summarize data from different worksheets with a PivotTable
(07:20)
AutoFill shortcuts for date series, one/two week intervals, EOM, and formulas
(06:59)
Create summary statistics using COUNTIFS, SUMIFS, and AVERAGEIFS functions
(08:40)
Use custom formulas in data validation to keep out bad data
(13:18)
Dealing with circular errors
(08:20)
Tips for creating charts quickly
(06:56)
Creating heat maps using conditional formatting
(08:31)
Using the INDEX function to extract data by row and column
(11:01)
Displaying tips when using Excel in presentations
(06:23)
The Wrap Text, Merge and Center, and Indent options
(09:02)
Using fill effects (gradients, patterns, styles) for colorful cell background variations
(05:10)
Working with multiple worksheets simultaneously
(08:42)
Exploring font choices not found on the Home tab
(04:02)
Using WordArt for special titles and headings
(04:33)
Creating a 2-way lookup with VLOOKUP, HLOOKUP, INDEX, and MATCH functions
(07:34)
Borders and gridlines: Exploring variations, options, and differences
(08:24)
New Excel 2016 chart types: Tree map and sunburst
(07:29)
Freezing column and row titles
(07:48)
Use data validation to force entries to be uppercase or lowercase
(10:48)
Avoiding common chart distortions
(11:49)
Use the new Funnel chart available in Excel 365
(04:03)
Combine data using CONCATENATE, CONCAT, and TEXTJOIN functions and the ampersand (&) character
(06:01)
Recognizing Excel Formula errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM, and #NAME!
(09:39)
Creating cross-totals with the SUMIFS function and mixed references
(05:50)
New Excel 2016 chart types: Histogram, Pareto, and box, and whisker
(10:08)
Using the AND and OR functions independently or within IF functions
(09:58)
Vertical and horizontal alignment settings and orientation options
(07:12)
Sorting tips and shortcuts
(08:40)
A look at new chart types in Excel 2016: Waterfall
(06:57)
Work with formulas and formats: Decimals and fractions
(05:42)
Creating mixed reference formulas; converting to a table; applying conditional formatting
(08:06)
Control gridlines in charts and on worksheets
(07:45)
Use the INDIRECT function with intersection formulas and range names to extract data
(06:34)
Using Expand/Collapse on PivotTables and PivotCharts for rapid display changes
(07:14)
Control phone number and postal code formats using built-in options or customized formats
(07:40)
Change the default Excel chart type; create and use chart templates
(06:13)
Create your own customized date and time formats
(07:31)
Create on-screen warnings and reminders with comments or data validation messages
(06:18)
Use REPLACE for formats only, for data only, or for both
(06:11)
Create numeric formats: Display in thousands or millions
(11:40)
Converting dates like 20102006 into usable date entries
(04:25)
Create custom lists for letter series like A to Z
(06:26)
Freeze Panes and Split
(05:30)
NETWORKDAYS.INTL and WORKDAY.INTL
(05:29)
Calculate % of change
(05:41)
Fill in all blank cells within a range with either formatting or data
(04:13)
Use the COUNT and COUNTA functions
(04:19)
Use workbook protection to prevent use of sheet commands
(05:27)
Rank data with the RANK and RANK.AVG functions
(05:37)
Use nonstandard fiscal years and quarters in PivotTables
(05:35)
Identify or extract unique entries in a list
(05:22)
Keyboard, mouse, and command techniques for viewing worksheets
(05:18)
Create artistic charts with random numbers
(07:23)
Extract day, month, and year from date fields
(05:07)
Compare data with EXACT, FIND, and SEARCH functions
(04:58)
How to covert ROMAN numberals
(04:25)
Use range names for more readable formulas
(05:38)
Customize your Quick Access Toolbar
(06:07)
Conditional formatting based on date proximity
(04:51)
How to adjust names
(05:45)
Differences and limitations of converting data to a table
(05:17)
Avoid the #DIV/0 error message
(04:56)
Explore formatting options not available on the Home tab
(04:25)
Control table formatting with custom formats
(03:12)
Use date functions for age and tenure calculations
(05:23)
Avoid unintentional entries when typing code numbers
(04:29)
Sorting by moving columns
(04:16)
Calculating cumulative totals
(03:52)
Using the CONVERT function for different numbering systems
(04:56)
Creating an automatically expanding chart by basing it on a table
(04:24)
Keystroke shortcuts using the Alt key
(05:20)
Using the Solver Add-in
(04:29)
Column widths, row heights, merging cells, and related formatting issues
(05:01)
Adjusting default layouts and date grouping in PivotTables
(04:14)
Auditing cell content with Watch Window and dependent cell formulas
(04:21)
Use the LEN and REPT functions for specialized cell testing and display options
(04:04)
Use the TRIM and CLEAN functions to remove excess spaces and non-printing characters
(05:06)
Create double-spaced and triple-spaced printouts while repeating column headings
(04:13)
Helpful keystroke shortcuts
(05:03)
Identify weekdays and weekend days in data
(04:50)
Use chart and filter for presentations
(04:09)
Create picture links
(05:37)
How to use the error-checking rules in Excel
(04:59)
Protect worksheets and lock cells
(05:24)
Use mixed addresses in Excel formulas
(05:35)
Rapid filtering with Filter by Selection
(03:45)
Display large values
(05:16)
Tracking down conditional formatting and data validation rules
(05:42)
Transposing data and using the TRANSPOSE function
(05:43)
Displaying gridlines, borders, and column/row headings when printing
(04:37)
Exploring some of the 200-plus SmartArt graphic options
(04:01)
Using the CEILING and FLOOR functions for specialized rounding needs
(05:16)
Inserting, reshaping, and formatting shapes: Rectangles, arrows, stars, and banners
(05:39)
Tabulating totals with the VLOOKUP function and array constants
(05:12)
Working with array formulas more easily
(04:23)
Using the new UNIQUE function to count and copy unique list entries
(03:36)
Creating and updating sample data
(05:43)
Format macros to make you a more efficient Excel user
(05:23)
Use the new SORT and SORTBY functions to extract sorted lists
(05:44)
Use themes to adjust worksheet colors, fonts, and effects
(03:42)
Calculate loan payments and investments with PMT and FV functions
(06:16)
Chart display options with blank cells in source data
(04:25)
Adjust Conditional Formatting rules by altering percentage breakpoints
(04:36)
Discover new formula capabilities with new functions and dynamic arrays
(05:06)
Sort or filter data based on color font or cell color background
(05:50)
Simplify the use of special characters and symbols
(05:24)
Select from over 800 icons to enliven worksheets
(06:29)
Use the new RANDARRAY function that replaces RAND and RANDBETWEEN
(06:26)
How and when to use an area chart
(04:16)
Insert colorful images with the People Graph Add-in
(05:37)
Use formulas to create interactive charts
(05:32)
Track variables using the Scenario Manager
(04:43)
Get totals quickly without creating formulas
(05:02)
Use column or row references to create dynamic formulas
(04:31)
Overcoming obstacles when working with dates in charts
(04:45)
Create map-type charts based on geographical locations
(04:20)
Techniques for creating date series
(04:42)
Extract filtered data with data validation and the FILTER function
(04:34)
Use Find and Replace to change cell contents and formats
(06:12)
Add formula tools and symbols to the Quick Access Toolbar
(05:12)
Use the XLOOKUP function to replace VLOOKUP
(05:23)
Change PivotTable settings for titles and summaries
(05:58)
Use the Excel filtering capability for dates
(04:57)
Use worksheet names in formulas
(05:31)
Use data validation rules with special phone and social security formats
(05:44)
Work with time calculations in formulas
(05:14)
Prevent and locate duplicate worksheet entries
(04:41)
Look forward and backward using EDATE and other date functions
(05:37)
Display and highlight worksheet formula cells
(05:46)
Split or join columnar data with Text to Columns or Flash Fill
(04:55)
Highlight milestone data with conditional formatting and cumulative formulas
(05:38)
Use the XMATCH function to replace MATCH
(03:43)
Create a powerful macro
(04:40)
Work with hidden and visible data in filtered and subtotaled lists
(05:54)
Use TODAY, NOW, YEARFRAC, and DATEDIF
(06:17)
Use data validation rules to prevent duplicate entries in a range
(04:38)
Use various techniques to hide cells, rows, columns, and worksheets
(04:56)
Align, arrange, and rotate shapes in worksheets
(05:40)
File documentation with the Workbook Statistics button
(04:22)
Avoid misleading visuals when rescaling
(05:16)
Use the Fill Justify feature to wrap long text
(04:02)
Create range names from Column and Row headings
(03:51)
Use the SUBSTITUTE and REPLACE functions
(04:47)
Accelerate Conditional Formatting with the Quick Access Toolbar
(04:15)
Use wildcard symbols in functions
(05:14)
Use wildcard symbols in filtering and commands
(06:07)
Condense lengthy nested IF functions with the newer IFS function
(04:43)
Add flair to charts with these formatting options: Gap width, shadow, glow, 3D, and more
(06:18)
Control worksheet security by allowing selected users to edit specific cell ranges
(05:43)
Retrieve vital data based on location using the Geography tool
(05:02)
Use the new SEQUENCE function to quickly build numeric and date arrays
(05:46)
Calculating text length and word count with LEN, TRIM, and SUBSTITUTE functions
(06:19)
Create a list box to facilitate selection of data from a list
(05:35)
Use various Excel count functions: COUNT, COUNTA, COUNTBLANK, and more
(05:36)
Enhance readability with banded rows via conditional formatting
(05:25)
Seven different ways to drag data using the Ctrl, Shift, and Alt keys
(06:03)
Use the SWITCH function: Compare with IFS, CHOOSE, and other lookup functions
(05:48)
Adjust charts quickly with quick layout, change colors, and chart styles options
(04:39)
Analyze PivotTable data with 13 different Show Values As options
(05:10)
Expand/collapse or sort displayed results when using the SUBTOTAL command
(05:18)
Use the AGGREGATE function to circumvent errors and ignore hidden data
(05:17)
Use the F9 key to evaluate parts of a formula, recalculate random entries, and control iteration
(06:11)
Using last-to-first (bottom-up) searches with XLOOKUP and XMATCH
(04:29)
Explore Paste Special options: Skip Blanks, Column Widths, Add, Transpose, and more
(05:54)
Reduce data entry drudgery with Ctrl+Enter, AutoComplete, and AutoCorrect
(05:56)
Using the LET function to simplify formulas via programming concepts
(05:04)
Create 51 new US State worksheets, or any multiple cluster of worksheets, in a flash
(04:35)
Create moving averages with formulas and chart trendlines
(05:47)
Use error-checking functions in Excel: IFERROR, ISERR, and ISERROR
(06:06)
Use Alt+Enter and Wrap Text for line-wrapping titles and improving formula readability
(05:26)
Work with cell colors, patterns, and effects to emphasize data and provide visual flair
(04:34)
Dynamic array formulas, new ways to calculate: One formula displays results in multiple cells
(05:27)
Fonts such as Arial and Calibri and a rundown of various available fonts in Excel
(06:05)
Use 3D formulas to tabulate data from multiple worksheets with the same layout
(06:36)
Multiple built-in date formats as well as tons of self-defined variations; m/d/y and mmm-d-yyyy
(06:24)
Where does that cell get its data from?: Tracking down cell precedents
(04:15)
Controlling date entry restrictions using data validation
(03:59)
Remove duplicates from a list vs. creating a new list without duplicates
(04:26)
New data types: Extract valuable online data into your worksheets
(05:09)
Use the FILTER and UNIQUE functions together for dynamic extractions
(05:01)
Adjusting text entries with the UPPER, LOWER, and PROPER functions
(04:55)
Refine filtering needs via custom filter options
(05:55)
Comparing column charts and bar charts: Pros and cons
(06:28)
How to use exponentiation (powers and roots) in Excel formulas
(05:50)
Tracking down errors with the ISTEXT, ISNUMBER, and ISNONTEXT functions
(04:18)
Tips for saving time when typing function names
(06:38)
SUMIFS, SUMPRODUCT, and SUM functions compared and contrasted
(06:37)
Conversion of values into binary, octal, decimal, and hexadecimal equivalents
(04:30)
Quick number formatting with keystroke shortcuts and icon buttons
(07:07)
Date calculation and formatting issues related to the 2029/2030 switch
(04:34)
Using wildcards in the new XLOOKUP and XMATCH functions
(05:56)
Using slicers as analytical tools
(06:02)
Conditional formatting using contrasting heat maps on multiple vs.single ranges
(05:44)
Create dynamic pick lists using the UNIQUE and SORT functions, and data validation
(06:11)
Contrasting uses of clustered column and stacked column charts
(06:57)
Use the XLOOKUP function with multiple column criteria and multiple column results
(04:39)
Tips for updating entire columns of date entries when using real or sample data
(04:15)
Using TRIM, CLEAN and other functions to clean up text data
(05:47)
Create multiple range names from the top row and/or left column
(05:47)
Use dragging techniques to extend dates by weekdays only, month, or year
(03:55)
Use the LAMBDA function to create worksheet functions
(06:27)
Get the formula results you want using Goal Seek
(05:07)
Sort your data based on a Custom List that you define
(05:43)
Create a dynamic sheet name reference with the INDIRECT function
(04:41)
Calculate dates and/or days of the week for selected future holidays
(04:06)
Use dragging techniques for faster Paste Special options
(05:12)
How to use and not use SS numbers; fake SS numbers; display as XXX-XX-9999
(04:51)
Five indispensable keystroke shortcuts for Windows and Mac users
(05:35)
Using new formula-writing techniques to simplify wide-ranging formulas
(06:06)
Calculate time differences within days and across multiple days
(06:26)
Use keyboard, mouse, and commands to zoom in/out quickly in Excel Windows and Mac versions
(05:45)
Make dynamic presentations with rapid expand/collapse detail features in outlining
(05:54)
Get data analysis suggestions with the Analyze Data (formerly Ideas) tool
(05:41)
Using the Distinct vs. Unique options when using the UNIQUE function
(04:58)
Use the Screen Snip tool or keystroke shortcut to take a picture of the screen or a portion of it
(04:29)
Exploring Excel's Sunburst chart to display hierarchical data
(05:05)
Creating floating text boxes with shadow, reflection, glow, and other shape effects
(04:27)
Page Setup tip: Print repeating titles, shrink-to-fit, landscape/portrait, double-spaced layout
(06:04)
Use Data Validation based on multiple criteria
(05:38)
Customize the Quick Access Toolbar using the Alt key or a mouse
(05:44)
Exploring treemap charts to display hierarchical data
(06:05)
Use the Quick Analysis tool for easy access to power tools
(03:36)
Create a number series with the Ctrl key and left/right mouse button
(05:56)
Combine IF, MAX, VALUE, and COUNTIF functions to analyze sales
(04:51)
Unhide multiple worksheets at once and use the very hidden option
(05:05)
Use the Fill Justify command to quickly rewrap text into different cells
(03:45)
Create a 15th day or end-of-month series and adjust to weekdays only
(04:27)
Use the Go To Special option to select and populate blank cells
(05:04)
Accentuate values with conditional formatting and sparklines
(06:10)
Transpose data with formula substitution and the TRANSPOSE function
(04:34)
Use emojis and other symbols in formulas and charts
(05:50)
Use the Advanced Filter for specialized filtering needs
(05:48)
Combo charts: Combine different chart types to present data more clearly
(05:33)
Combining the SEQUENCE, LEN, and MID functions to extract data into multiple cells
(06:29)
Exploring Edit Default Layout options when using PivotTables
(04:28)
Navigation tips: Shortcuts to jump within and between worksheets and workbooks
(05:44)
Use DATEDIF to calculate months or days after yearly or monthly anniversaries
(05:18)