Excel: Tips and Tricks

Course details

In this course, Dennis Taylor—an Excel expert who has 25+ years of experience in spreadsheet authoring and training—dives into the capabilities of Excel and how you can harness its full potential. Discover how to utilize key shortcuts and techniques that streamline your workflow, and improve your proficiency in tasks like data entry, sorting, filtering, moving, and copying data. Explore ways Excel can manage large datasets effectively, and leverage formulas to maintain accuracy in your calculations. Learn how to enhance your presentations with visually appealing charts and shapes that can dynamically represent your data. Glean useful tips on managing lists, adjusting formats, and keeping your spreadsheets organized and accessible. Plus, get hands-on experience using Excel to transform raw data into clear and insightful reports. By the end of this course, you will be equipped with the skills to handle Excel with confidence and create impactful, polished documents effortlessly.

Learning objectives
Apply time-saving keyboard shortcuts and navigation techniques to increase Excel workflow efficiency by at least 30%.
Create advanced formulas with minimal keystrokes using formula building techniques and function autocomplete features.
Implement professional-level spreadsheet formatting using conditional formatting, custom number formats, and cell styles to enhance data visualization.
Master Excel's latest data analysis tools including dynamic arrays, XLOOKUP, and Power Query to transform complex data into actionable insights.
Design interactive dashboards and reports using PivotTables, charts, and data visualization techniques to effectively communicate data stories.

Concepts

Introduction

Become a proficient Excel user

Seven Super Shortcuts

Enter data or formulas in nonadjacent cells simultaneously
Copy data or formulas down a column
Instantly enter today's date or time
Convert formulas to values with a simple drag
Display all worksheet formulas instantly
Create charts instantly with these keystroke shortcuts
Zoom in and out quickly

Ribbon and Quick Access Toolbar Tips

Access ribbon commands with Alt key sequences
Expand or collapse ribbon and full screen views

Display Shortcuts

Create split screens and frozen titles in a flash
Restore missing labels or hide repeating labels

Navigation and Selection Shortcuts

Navigate between workbooks and worksheets efficiently
Navigate within worksheets efficiently
Use keystrokes to open, close, save, and create new workbooks
Select an entire row, column, range, or worksheet
Select noncontiguous ranges and visible cells only

Data Entry and Editing Shortcuts

Accelerate data entry
Enter dates and date series efficiently
Enter times and time series efficiently
Use custom lists for rapid data entry and list-based sorting

Drag-and-Drop Techniques

Accelerate copy and move tasks
Drag and insert cells with the Shift key
Display Paste Special options instantly
Accelerate formatting with the right mouse button

Formula Shortcuts

Create formulas rapidly
Select all dependent or precedent cells
Use AutoSum shortcuts
Highlight formula cells only with conditional formatting
Perform calculations without formulas

Operational Shortcuts

Realign imported text
Collapse and expand detail using group and ungroup buttons
Create double-spaced or triple-spaced printouts quickly

Formatting Shortcuts

Use keyboard shortcuts for frequently used numeric formats
Accentuate data with alignment tools
Add or remove borders and strikethrough with shortcuts
Display values in thousands or millions without formulas
Quickly format phone and Social Security numbers

Data Management Techniques

Clean up spaces quickly with the TRIM function
Split column data using Text to Columns or Flash Fill
Ensure unique entries with data validation rules
Fast sorting and filtering with the right mouse button

Charting and Visual Object Tips

Drag to control chart placement and sizing
Create and manipulate shapes with Shift, Ctrl, and Alt
Create dynamic and static linked images
Quickly create full or partial screen capture images

Ten Tiny Tips

Continue learning Excel