Class Central is learner-supported. When you buy through links on our site, we may earn an affiliate commission.

LinkedIn Learning

Excel Tips Weekly

via LinkedIn Learning

Overview

Syllabus

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

Taught by

Dennis Taylor

Reviews

4.7 rating at LinkedIn Learning based on 380 ratings

Start your review of Excel Tips Weekly

Never Stop Learning.

Get personalized course recommendations, track subjects and courses with reminders, and more.

Someone learning on their laptop while sitting on the floor.