The 5-Day Excel Challenge

Name: The 5-Day Excel Challenge
Price: -1 USD

via LinkedIn Learning

Go to class Write review

Details

Go to class

Provider

LinkedIn Learning
Pricing

Free Trial Available
Languages

English
Certificate

Certificate Available
Duration & workload

2 days 53 minutes
Sessions

On-Demand

Found in

Overview

Save Big on Coursera Plus. 7,000+ courses at $160 off. Limited Time Only!

Grab it

Get a new productivity-boosting tip every Tuesday from Excel expert Dennis Taylor.

Syllabus

Excel Tips

Using the new Checkbox button on the Insert tab

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
Ten quick tips to use daily: formulas, formatting, and accessing commands
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

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
Filtering lists with AND and OR criteria when using the FILTER function
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.)
Create forms using check boxes and option buttons to simplify data entry
Use the FILTER and SORT functions to maintain updated lists and rapidly changing source data
Create a moving average with a chart or by using a formula
Redesigned Data Validation feature: Now more efficient when using large lists
Link pictures of data ranges to changing data
Use the UNIQUE, TRANSPOSE, SUMIF, and SUMIFS functions in table formulas
Create multiple PivotTables from a single source
Explore icon sets in the Excel Conditional Formatting feature
Use the IMAGE function to paste an image into a cell
Use TOCOL and TOROW to realign array data into single columns and rows
Use SUM, SUBTOTAL, and AGGREGATE functions with filtered lists and lists with hidden rows
Use bubble charts to show relationships between sets of values
Contrast TEXTJOIN, CONCAT, and TEXTSPLIT to gather and split data
Use shrink-to-fit options to adjust the display of cell content and compress printed output
Locate formula precedents via keystroke shortcuts and the Trace Precedents command
Try these editing tips and keystroke shortcuts to use with long formulas
Customize and utilize status bar displays and copy/paste its data
Command and keystroke shortcuts for sorting frequently used data
Extract text from cells using the new TEXTBEFORE and TEXTAFTER functions
Quickly link cell content for main titles and axis titles for selected chart types
Use the TAKE, DROP, FILTER and SORTBY functions to build dependent lists
Create new lists by changing the PivotTable Report Layout to Tabular and adding repeating labels
Rapid filtering by content, cell color, or font color via right-clicking
Restructure column/row data using new WRAPROWS and WRAPCOLS functions
Working with date and time-based data and the over-24-hour display issue
Multiple tips for creating charts quickly
Extract specific rows/columns from lists using new CHOOSEROWS and CHOOSECOLS functions
Overcoming rounding concerns in formulas with functions and decimal displays
Highlighting PivotTable results with conditional formatting
An overview of the 12 database functions in Excel
Advantages and disadvantages of using pie charts to display data
Rapid sorting via right-clicking options
Converting charts into pictures
Using three functions to track worksheet cells that are text, numbers, or formulas
Using new BYROW and BYCOL functions to analyze data
Create formulas rapidly with these shortcuts
Exploring 3-D Column and 3-D Bar charts
New VSTACK and HSTACK functions
Calculating day of the week totals from a large transaction list
Contrasating uses of the newer functions: XMATCH and XLOOKUP
Various techniques for highlighting (selecting) cell ranges quickly
Inserting special symbols using AutoCorrect options and the Insert Symbol command

Reviewing Excel Information functions
Manipulating text functions LEFT, MID, and TEXT to create new ID numbers
Discovering timesaving tools in the Go To Special feature
Calculating chronological and fiscal quarters

Sorting by moving columns left/right
Using Conditional Formatting for analysis and data emphasis
Using Indent buttons to control text displays and currency formatting
Create shapes and text boxes with content linked to worksheet cells
Dynamically presenting data via chart slicers
Sorting by day of month for birthday and anniversary lists
Using wildcards (asterisk, question mark, and tilde) in formulas and commands
Using SEARCH, FIND, and COUNT to tabulate data occurrences
Comparing the Data Analysis Histogram feature and Excel's Histogram chart
Using the AND and OR functions independently or within IF functions
Use various techniques to flip lists upside-down or left-to-right
Combine data with the TEXTJOIN, CONCAT, and CONCATENATE
Use Excel's Advanced Options to fine-tune settings and increase efficiency
Use ALT and other shortcuts when working with AutoComplete, Data Validation, etc.
Use the powerful COUNTIF family—8 functions—for statistical analysis
Show relationships between sets of values with a Scatter(XY) Chart
Locate and track a worksheet's Data Validation rules and violations
Use AGGREGATE to avoid errors and ignore hidden data
Explore multiple approaches for locating, preventing, and deleting duplicate data
Use a single SEQUENCE function to create various date layouts
Combine a Validation list, XLOOKUP function, table, and chart to build a presentation model
Pros and cons of using Stacked Area charts and Stacked Column Charts
Nested use of FILTER, SORT, and CHOOSECOLS functions for selective column retrieval
Learn about the new Office Theme, the default Aptos Font and new colors in M365 apps
Analyze data using TOCOL, TOROW, LARGE, and SMALL
Refine chart elements quickly with the powerful and overlooked Quick Layout button
Tips and techniques for displaying Excel worksheets effectively during presentations
Workday tabulations with NETWORKDAYS, WORKDAY, and SEQUENCE functions
Using entire column (or row) references to create more dynamic formulas
Using Excel's Accessibility Checker to follow best practices for those with disabilities
Using XLOOKUP and CHOOSECOLS to return multiple non-adjacent column data
Create dynamic presentations with Slicers, Filters, and Charts
Compare and contrast multiple column sorting with SORT and SORTBY
Use keystroke and mouse shortcuts to display and navigate worksheets
Show gain/loss in census figures with the RANK, IF, and LET functions
Apply conditional formatting across data rows
Expand and collapse date displays in PivotTables
Use TODAY and NOW for real-time up-to-date calculations
Use Freeze Panes and Split to view different worksheet portions
Use CEILING and FLOOR for specialized numeric rounding needs
Highlight negative positive values with Conditional Formatting and Data Bars
Use AutoSum to simplify and accelerate formula creations
Dynamic sheet name reference with INDIRECT
Create custom and built-in lists to sort and fill in sequences
Consolidate and split workbooks
Expand, collapse and sort displayed results when using Subtotal
Random number functions: RAND, RANDBETWEEN, and RANDARRAY
Using the new GROUPBY and PIVOTBY functions
Creating 3D and other formulas that use data from different worksheets
Quick searching for menu commands with the right mouse button