Excel Tips Weekly

Contents

• Excel Tips - New This Week Excel Tips - New This Week

  • Make dynamic presentations with rapid expand/collapse detail features in outlining

    5m 54s
• Previous Episodes Previous Episodes

  • Hide and unhide worksheets securely

    6m 6s
  • Resizing, moving, copying, and manipulating charts and other objects

    10m 17s
  • Using list boxes and combo boxes to facilitate forms creation

    7m 36s
  • Using the FORMULATEXT and N functions for worksheet and formula documentation

    5m 53s
  • Using the ISBLANK and COUNTBLANK functions to check for and tabulate blank cells

    7m 55s
  • Summarizing data from diverse ranges with Data > Consolidate

    9m 33s
  • Using check boxes and conditional formatting together for dynamic displays

    5m 58s
  • Becoming more productive with these 10 tiny tips you'll use often

    8m
  • Transposing data and charts for a different perspective on your data

    8m 57s
  • Applying conditional formatting across rows of data

    6m 43s
  • Exploring various layouts for your PivotTable

    4m 27s
  • Splitting and consolidating workbooks

    6m 47s
  • Using date and time as metrics in a PivotTable

    6m 53s
  • Adding illustrations to your workbook

    12m 7s
  • Using a pick list, VLOOKUP, table, and chart together

    8m 5s
  • Using formulas to calculate values across worksheets and workbooks

    11m 12s
  • Dynamically presenting data via chart slicers

    7m 15s
  • Parsing your data to create unique lists

    3m 21s
  • Using the REPT function to represent data visually

    3m 6s
  • Using source data from a table to make your PivotTable more dynamic

    7m 8s
  • Accelerating Cut, Copy, Paste, and Insert tasks

    7m 26s
  • Increasing your productivity with custom lists

    5m 9s
  • Highlighting PivotTable results with conditional formatting

    6m 10s
  • Customizing templates to fit your unique needs

    6m 6s
  • Creating dynamic charts with in-cell conditional formatting

    3m 26s
  • Enhancing table filtering with slicers

    5m 23s
  • Converting charts into pictures

    4m 51s
  • Custom grouping in PivotTables

    4m 31s
  • Quick formatting tips

    8m 9s
  • The top five unknown but useful keyboard shortcuts

    6m 17s
  • Using the TREND and GROWTH functions for projecting future results

    5m 54s
  • Using scroll bars and spin buttons to facilitate forms creation

    10m 1s
  • Creating colorful 3D buttons for worksheet annotation and macro buttons

    6m 52s
  • Calculating with hours, minutes, and times of day

    11m 2s
  • Tracking down cell dependencies across multiple worksheets and workbooks

    6m 24s
  • Discovering time-saving tools in the Go To Special feature

    11m 22s
  • Using Go To Special

    6m 1s
  • Reducing data entry time by expanding AutoCorrect options

    6m 10s
  • Setting up custom views for quick access to different worksheet displays

    6m 30s
  • Using hyperlinks for rapidly switching worksheet locations and jumping to websites

    7m 18s
  • Controlling worksheet security by allowing selected users to edit specific cell ranges

    7m 22s
  • Using option buttons, group boxes, and checkboxes to facilitate forms creation

    8m 7s
  • Exploring what-if scenarios using Goal Seek

    9m 55s
  • Creating text, numerical, date, and time lists in a flash

    6m 54s
  • Creating variable conditional formatting rules

    8m 4s
  • Handling dates with unusual formats

    6m 5s
  • Using wildcards (asterisk, tilde, and question mark)

    11m
  • Custom formats using asterisk, semicolon, and brackets

    13m
  • Meeting unusual filtering needs via Advanced Filter

    5m 28s
  • Create an organization chart

    8m 56s
  • Two-way lookup using the MATCH and INDEX functions

    11m 28s
  • Using Excel error-checking functions

    10m 37s
  • Custom formats, relative addressing, and conditional formatting

    8m 26s
  • Auditing

    9m 1s
  • Adding comments and shapes

    7m 35s
  • Drawing borders and border grids

    5m 45s
  • Building a flexible monthly table using functions

    11m 44s
  • Adding pictures as worksheet backgrounds

    4m 15s
  • ADDRESS, ROW, and COLUMN functions

    8m 8s
  • Join data with new CONCAT and TEXTJOIN functions

    6m 15s
  • Working with formulas in tables

    13m 2s
  • Locating data validation rules and violations

    7m 20s
  • Creating an Excel template

    7m 57s
  • Restoring missing column titles

    6m 41s
  • Creating a pick list dependent on another pick list

    6m 59s
  • Using command shortcuts with the Alt key

    5m 51s
  • Set up a macro for sorting dynamic data

    8m 42s
  • Use random number functions for sorting and creating sample data

    9m 57s
  • Use calcuated fields and calculated items in a PivotTable

    8m 57s
  • Replace characters by position with the REPLACE function

    5m 46s
  • Work with formulas in tables

    9m 3s
  • Keystroke shortcuts from A to Z: Using the Ctrl key

    16m 16s
  • Use special tricks for working with dates in charts

    11m 36s
  • Use a data form for data entry and exploring data

    6m 42s
  • Creating an Excel template to simplify updating a monthly file

    7m 10s
  • Calculate dates efficiently using Excel's hidden function - DATEDIF

    7m 36s
  • Create dynamic sheet name references with the INDIRECT function

    6m 45s
  • Avoid errors and hidden data with the powerful AGGREGATE function

    7m 8s
  • Hide worksheets, row, columns, cells and other Excel elements

    7m 26s
  • Use slicers and charts together for dynamic presentations

    9m 4s
  • Save a chart as a template: Use the template for current or new charts

    6m 2s
  • MAXIFS and MINIFS added to the COUNTIF/SUMIF family of functions

    8m 12s
  • Adjust a chart's source data and adjust its series order

    9m 7s
  • Use the OFFSET function for tabulating moving data

    11m 23s
  • Alter numeric data without formulas

    10m 19s
  • Use Flash Fill to rapidly combine or separate columnar data

    10m 28s
  • Adjust banded-row formatting

    14m 35s
  • Change the shape of comment boxes and other objects

    6m 32s
  • How and when to use the 10 rounding functions in Excel

    13m 26s
  • Calculate faster with the AutoSum button and AutoSum keystroke shortcut

    6m 54s
  • How to use mixed cell references

    8m 25s
  • Activate the Speak On Enter and Speak Cells features

    5m 38s
  • Accelerate data entry with five special techniques

    12m 8s
  • Insert a text box or shape and link a cell's content to it

    6m 58s
  • Use Watch Window and other techniques to track changing cells

    8m 21s
  • How to create time interval entries

    11m 39s
  • How to create frequency tabulations and distributions

    8m 10s
  • Create an expanded list from a summary using PivotTable techniques

    7m 25s
  • Solve matching issues with matching phone numbers and SS numbers

    10m
  • Use CHOOSE to calculate fiscal quarters and as a VLOOKUP alternative

    7m 27s
  • Use outlining tools to quickly expand and collapse data for dynamic presentations

    9m 19s
  • Use the SUBTOTAL (and AGGREGATE) functions to avoid double counting

    9m 59s
  • Create lists of all 2nd Tuesdays, last Tuesdays. and other date series

    10m 44s
  • Create linkage formulas that refer to other worksheets and workbooks

    11m 49s
  • Formula nesting: Using multiple functions in the same formula

    13m 22s
  • Keyboard shortcuts for numeric formats

    8m 18s
  • Use the Subtotal command to analyze data with single- or multiple-level subtotals

    9m 15s
  • Work with MAX, MAXIFS, LARGE, and related functions

    8m 14s
  • Use the dynamic TODAY and NOW functions for real-time up-to-date calculations

    9m 24s
  • Use the FIND, MID, LEFT, and RIGHT functions

    10m 38s
  • Flip the left-to-right order of columns with the INDEX function or sort by columns

    8m 18s
  • Working with hidden data when copying cells and creating charts

    10m 58s
  • Use conditional formatting data bars to accentuate negative data

    4m 16s
  • Creative use of sparklines in merged cells with axes

    7m 1s
  • Copy formats quickly using dragging techniques and the Format Painter

    5m 25s
  • Displaying multiple worksheets and workbooks together

    10m 13s
  • Tips for creating and updating sample data

    11m 1s
  • Use conditional formatting to overcome formatting limitations of the IF function

    8m 54s
  • Use VLOOKUP, MATCH, and INDEX functions with array formulas

    10m 38s
  • Summarize data from different worksheets with a PivotTable

    7m 20s
  • AutoFill shortcuts for date series, one/two week intervals, EOM, and formulas

    6m 59s
  • Create summary statistics using COUNTIFS, SUMIFS, and AVERAGEIFS functions

    8m 40s
  • Use custom formulas in data validation to keep out bad data

    13m 18s
  • Dealing with circular errors

    8m 20s
  • Tips for creating charts quickly

    6m 56s
  • Creating heat maps using conditional formatting

    8m 31s
  • Using the INDEX function to extract data by row and column

    11m 1s
  • Displaying tips when using Excel in presentations

    6m 23s
  • The Wrap Text, Merge and Center, and Indent options

    9m 2s
  • Using fill effects (gradients, patterns, styles) for colorful cell background variations

    5m 10s
  • Working with multiple worksheets simultaneously

    8m 42s
  • Exploring font choices not found on the Home tab

    4m 2s
  • Using WordArt for special titles and headings

    4m 33s
  • Creating a two-way lookup with VLOOKUP, HLOOKUP, INDEX, and MATCH functions

    7m 34s
  • Borders and gridlines: Exploring variations, options, and differences

    8m 24s
  • New Excel 2016 chart types: Tree map and sunburst

    7m 29s
  • Freezing column and row titles

    7m 48s
  • Use data validation to force entries to be uppercase or lowercase

    10m 48s
  • Avoiding common chart distortions

    11m 49s
  • Use the new funnel chart available in Excel 365

    4m 3s
  • Combine data using CONCATENATE, CONCAT, and TEXTJOIN functions and the ampersand (&) character

    6m 1s
  • Recognizing Excel Formula errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM, and #NAME!

    9m 39s
  • Creating cross-totals with the SUMIFS function and mixed references

    5m 50s
  • New Excel 2016 chart types: Histogram, Pareto, and box, and whisker

    10m 8s
  • Using the AND and OR functions independently or within IF functions

    9m 58s
  • Vertical and horizontal alignment settings and orientation options

    7m 12s
  • Sorting tips and shortcuts

    8m 40s
  • A look at new chart types in Excel 2016: Waterfall

    6m 57s
  • Work with formulas and formats: Decimals and fractions

    5m 42s
  • Creating mixed reference formulas; converting to a table; applying conditional formatting

    8m 6s
  • Control gridlines in charts and on worksheets

    7m 45s
  • Use the INDIRECT function with intersection formulas and range names to extract data

    6m 34s
  • Using Expand/Collapse on PivotTables and PivotCharts for rapid display changes

    7m 14s
  • Control phone number and postal code formats using built-in options or customized formats

    7m 40s
  • Change the default Excel chart type; create and use chart templates

    6m 13s
  • Create your own customized date and time formats

    7m 31s
  • Create on-screen warnings and reminders with comments or data validation messages

    6m 18s
  • Use REPLACE for formats only, for data only, or for both

    6m 11s
  • Create numeric formats: Display in thousands or millions

    11m 40s
  • Converting dates like 20102006 into usable date entries

    4m 25s
  • Create custom lists for letter series like A to Z

    6m 26s
  • Freeze Panes and Split

    5m 30s
  • NETWORKDAYS.INTL and WORKDAY.INTL

    5m 29s
  • Calculate % of change

    5m 41s
  • Fill in all blank cells within a range with either formatting or data

    4m 13s
  • Use the COUNT and COUNTA functions

    4m 19s
  • Use workbook protection to prevent use of sheet commands

    5m 27s
  • Rank data with the RANK and RANK.AVG functions

    5m 37s
  • Use nonstandard fiscal years and quarters in PivotTables

    5m 35s
  • Identify or extract unique entries in a list

    5m 22s
  • Keyboard, mouse, and command techniques for viewing worksheets

    5m 18s
  • Create artistic charts with random numbers

    7m 23s
  • Extract day, month, and year from date fields

    5m 7s
  • Compare data with EXACT, FIND, and SEARCH functions

    4m 58s
  • How to covert ROMAN numberals

    4m 25s
  • Use range names for more readable formulas

    5m 38s
  • Customize your Quick Access Toolbar

    6m 7s
  • Conditional formatting based on date proximity

    4m 51s
  • How to adjust names

    5m 45s
  • Differences and limitations of converting data to a table

    5m 17s
  • Avoid the #DIV/0 error message

    4m 56s
  • Explore formatting options not available on the Home tab

    4m 25s
  • Control table formatting with custom formats

    3m 12s
  • Use date functions for age and tenure calculations

    5m 23s
  • Avoid unintentional entries when typing code numbers

    4m 29s
  • Sorting by moving columns

    4m 16s
  • Calculating cumulative totals

    3m 52s
  • Using the CONVERT function for different numbering systems

    4m 56s
  • Creating an automatically expanding chart by basing it on a table

    4m 24s
  • Keystroke shortcuts using the Alt key

    5m 20s
  • Using the Solver Add-in

    4m 29s
  • Column widths, row heights, merging cells, and related formatting issues

    5m 1s
  • Adjusting default layouts and date grouping in PivotTables

    4m 14s
  • Auditing cell content with Watch Window and dependent cell formulas

    4m 21s
  • Use the LEN and REPT functions for specialized cell testing and display options

    4m 4s
  • Use the TRIM and CLEAN functions to remove excess spaces and non-printing characters

    5m 6s
  • Create double-spaced and triple-spaced printouts while repeating column headings

    4m 13s
  • Helpful keystroke shortcuts

    5m 3s
  • Identify weekdays and weekend days in data

    4m 50s
  • Use chart and filter for presentations

    4m 9s
  • Create picture links

    5m 37s
  • How to use the error-checking rules in Excel

    4m 59s
  • Protect worksheets and lock cells

    5m 24s
  • Use mixed addresses in Excel formulas

    5m 35s
  • Rapid filtering with Filter by Selection

    3m 45s
  • Display large values

    5m 16s
  • Tracking down conditional formatting and data validation rules

    5m 42s
  • Transposing data and using the TRANSPOSE function

    5m 43s
  • Displaying gridlines, borders, and column/row headings when printing

    4m 37s
  • Exploring some of the 200-plus SmartArt graphic options

    4m 1s
  • Using the CEILING and FLOOR functions for specialized rounding needs

    5m 16s
  • Inserting, reshaping, and formatting shapes: Rectangles, arrows, stars, and banners

    5m 39s
  • Tabulating totals with the VLOOKUP function and array constants

    5m 12s
  • Working with array formulas more easily

    4m 23s
  • Using the new UNIQUE function to count and copy unique list entries

    3m 36s
  • Creating and updating sample data

    5m 43s
  • Format macros to make you a more efficient Excel user

    5m 23s
  • Use the new SORT and SORTBY functions to extract sorted lists

    5m 44s
  • Use themes to adjust worksheet colors, fonts, and effects

    3m 42s
  • Calculate loan payments and investments with PMT and FV functions

    6m 16s
  • Chart display options with blank cells in source data

    4m 25s
  • Adjust Conditional Formatting rules by altering percentage breakpoints

    4m 36s
  • Discover new formula capabilities with new functions and dynamic arrays

    5m 6s
  • Sort or filter data based on color font or cell color background

    5m 50s
  • Simplify the use of special characters and symbols

    5m 24s
  • Select from over 800 icons to enliven worksheets

    6m 29s
  • Use the new RANDARRAY function that replaces RAND and RANDBETWEEN

    6m 26s
  • How and when to use an area chart

    4m 16s
  • Insert colorful images with the People Graph Add-in

    5m 37s
  • Use formulas to create interactive charts

    5m 32s
  • Track variables using the Scenario Manager

    4m 43s
  • Get totals quickly without creating formulas

    5m 2s
  • Use column or row references to create dynamic formulas

    4m 31s
  • Overcoming obstacles when working with dates in charts

    4m 45s
  • Create map-type charts based on geographical locations

    4m 20s
  • Techniques for creating date series

    4m 42s
  • Extract filtered data with data validation and the FILTER function

    4m 34s
  • Use Find and Replace to change cell contents and formats

    6m 12s
  • Add formula tools and symbols to the Quick Access Toolbar

    5m 12s
  • Use the XLOOKUP function to replace VLOOKUP

    5m 23s
  • Change PivotTable settings for titles and summaries

    5m 58s
  • Use the Excel filtering capability for dates

    4m 57s
  • Use worksheet names in formulas

    5m 31s
  • Use data validation rules with special phone and social security formats

    5m 44s
  • Work with time calculations in formulas

    5m 14s
  • Prevent and locate duplicate worksheet entries

    4m 41s
  • Look forward and backward using EDATE and other date functions

    5m 37s
  • Display and highlight worksheet formula cells

    5m 46s
  • Split or join columnar data with Text to Columns or Flash Fill

    4m 55s
  • Highlight milestone data with conditional formatting and cumulative formulas

    5m 38s
  • Use the XMATCH function to replace MATCH

    3m 43s
  • Create a powerful macro

    4m 40s
  • Work with hidden and visible data in filtered and subtotaled lists

    5m 54s
  • Use TODAY, NOW, YEARFRAC, and DATEDIF

    6m 17s
  • Use data validation rules to prevent duplicate entries in a range

    4m 38s
  • Use various techniques to hide cells, rows, columns, and worksheets

    4m 56s
  • Align, arrange, and rotate shapes in worksheets

    5m 40s
  • File documentation with the Workbook Statistics button

    4m 22s
  • Avoid misleading visuals when rescaling

    5m 16s
  • Use the Fill Justify feature to wrap long text

    4m 2s
  • Create range names from Column and Row headings

    3m 51s
  • Use the SUBSTITUTE and REPLACE functions

    4m 47s
  • Accelerate Conditional Formatting with the Quick Access Toolbar

    4m 15s
  • Use wildcard symbols in functions

    5m 14s
  • Use wildcard symbols in filtering and commands

    6m 7s
  • Condense lengthy nested IF functions with the newer IFS function

    4m 43s
  • Add flair to charts with these formatting options: Gap width, shadow, glow, 3D, and more

    6m 18s
  • Control worksheet security by allowing selected users to edit specific cell ranges

    5m 43s
  • Retrieve vital data based on location using the Geography tool

    5m 2s
  • Use the new SEQUENCE function to quickly build numeric and date arrays

    5m 46s
  • Calculating text length and word count with LEN, TRIM, and SUBSTITUTE functions

    6m 19s
  • Create a list box to facilitate selection of data from a list

    5m 35s
  • Use various Excel count functions: COUNT, COUNTA, COUNTBLANK, and more

    5m 36s
  • Enhance readability with banded rows via conditional formatting

    5m 25s
  • Seven different ways to drag data using the Ctrl, Shift, and Alt keys

    6m 3s
  • Use the SWITCH function: Compare with IFS, CHOOSE, and other lookup functions

    5m 48s
  • Adjust charts quickly with quick layout, change colors, and chart styles options

    4m 39s
  • Analyze PivotTable data with 13 different Show Values As options

    5m 10s
  • Expand/collapse or sort displayed results when using the SUBTOTAL command

    5m 18s
  • Use the AGGREGATE function to circumvent errors and ignore hidden data

    5m 17s
  • Use the F9 key to evaluate parts of a formula, recalculate random entries, and control iteration

    6m 11s
  • Using last-to-first (bottom-up) searches with XLOOKUP and XMATCH

    4m 29s
  • Explore Paste Special options: Skip Blanks, Column Widths, Add, Transpose, and more

    5m 54s
  • Reduce data entry drudgery with Ctrl+Enter, AutoComplete, and AutoCorrect

    5m 56s
  • Using the LET function to simplify formulas via programming concepts

    5m 4s
  • Create 51 new US State worksheets, or any multiple cluster of worksheets, in a flash

    4m 35s
  • Create moving averages with formulas and chart trendlines

    5m 47s
  • Use error-checking functions in Excel: IFERROR, ISERR, and ISERROR

    6m 6s
  • Use Alt+Enter and Wrap Text for line-wrapping titles and improving formula readability

    5m 26s
  • Work with cell colors, patterns, and effects to emphasize data and provide visual flair

    4m 34s
  • Dynamic array formulas, new ways to calculate: One formula displays results in multiple cells

    5m 27s
  • Fonts such as Arial and Calibri and a rundown of various available fonts in Excel

    6m 5s
  • Use 3D formulas to tabulate data from multiple worksheets with the same layout

    6m 36s
  • Multiple built-in date formats as well as tons of self-defined variations; m/d/y and mmm-d-yyyy

    6m 24s
  • Where does that cell get its data from?: Tracking down cell precedents

    4m 15s
  • Controlling date entry restrictions using data validation

    3m 59s
  • Remove duplicates from a list vs. creating a new list without duplicates

    4m 26s
  • New data types: Extract valuable online data into your worksheets

    5m 9s
  • Use the FILTER and UNIQUE functions together for dynamic extractions

    5m 1s
  • Adjusting text entries with the UPPER, LOWER, and PROPER functions

    4m 55s
  • Refine filtering needs via custom filter options

    5m 55s
  • Comparing column charts and bar charts: Pros and cons

    6m 28s
  • How to use exponentiation (powers and roots) in Excel formulas

    5m 50s
  • Tracking down errors with the ISTEXT, ISNUMBER, and ISNONTEXT functions

    4m 18s
  • Tips for saving time when typing function names

    6m 38s
  • SUMIFS, SUMPRODUCT, and SUM functions compared and contrasted

    6m 37s
  • Conversion of values into binary, octal, decimal, and hexadecimal equivalents

    4m 30s
  • Quick number formatting with keystroke shortcuts and icon buttons

    7m 7s
  • Date calculation and formatting issues related to the 2029/2030 switch

    4m 34s
  • Using wildcards in the new XLOOKUP and XMATCH functions

    5m 56s
  • Using slicers as analytical tools

    6m 2s
  • Conditional formatting using contrasting heat maps on multiple vs.single ranges

    5m 44s
  • Create dynamic pick lists using the UNIQUE and SORT functions, and data validation

    6m 11s
  • Contrasting uses of clustered column and stacked column charts

    6m 57s
  • Use the XLOOKUP function with multiple column criteria and multiple column results

    4m 39s
  • Tips for updating entire columns of date entries when using real or sample data

    4m 15s
  • Using TRIM, CLEAN and other functions to clean up text data

    5m 47s
  • Create multiple range names from the top row and/or left column

    5m 47s
  • Use dragging techniques to extend dates by weekdays only, month, or year

    3m 55s
  • Use the LAMBDA function to create worksheet functions

    6m 27s
  • Get the formula results you want using Goal Seek

    5m 7s
  • Sort your data based on a Custom List that you define

    5m 43s
  • Create a dynamic sheet name reference with the INDIRECT function

    4m 41s
  • Calculate dates and/or days of the week for selected future holidays

    4m 6s
  • Use dragging techniques for faster Paste Special options

    5m 12s
  • How to use and not use SS numbers; fake SS numbers; display as XXX-XX-9999

    4m 51s
  • Five indispensable keystroke shortcuts for Windows and Mac users

    5m 35s
  • Using new formula-writing techniques to simplify wide-ranging formulas

    6m 6s
  • Calculate time differences within days and across multiple days

    6m 26s
  • Use keyboard, mouse, and commands to zoom in/out quickly in Excel Windows and Mac versions

    5m 45s