Master 75+ Excel tips and techniques and become a certified POWER USER, with unique hands-on demos and real-world projects to help you wrangle, analyze, and visualize your data in Excel.
About This Video
In Detail
This course …
Master 75+ Excel tips and techniques and become a certified POWER USER, with unique hands-on demos and real-world projects to help you wrangle, analyze, and visualize your data in Excel.
About This Video
In Detail
This course is not an introduction to Excel. It features some of the most powerful and effective tools and techniques used by Excel professionals, and shares them through crystal-clear demos and unique, real-world case studies.
Unlike most Excel courses, this course is non-linear and constantly evolving, which means that you can jump to different sections or individual lectures freely. Each video is designed to be an independent and self-contained demo, to help you learn these tools and techniques in quick, bite-sized lessons.
The Excel tips and techniques covered in this course fall into six categories:
While the level of difficulty varies considerably, the demos generally start simple and become progressively more complex within each section. There are no strict prerequisites for this course, but keep in mind that some demos may be challenging without a strong foundational knowledge of underlying Excel concepts (such as Pivot Tables or formulas, for example).
The codes are placed in the GitHub link as below- https://github.com/sanjanapackt/PacktPublishing-Excel-Pro-Tips-75--Tips-to-go-from-Excel-Beginner-to-Pro
Chapter 1 : Getting Started
Course Structure & Outline 00:01:41
Setting Expectations 00:01:14
Chapter 2 : Excel Productivity Tips
Introduction 00:00:30
Customizing the Excel Footer 00:04:03
Navigating Excel Workbooks with CTRL Shortcuts 00:08:01
Accessing Excel Ribbon Tools with ALT Key Tips 00:06:53
Accessing Excel’s “Go To” Special Options 00:04:24
Removing Blank Rows in Excel Worksheets 00:03:18
Creating Drop-Down Menus in Excel with Data Validation 00:07:11
Filling in Patterns with Autofill & Flash Fill 00:08:27
Customizing the Excel Ribbon Tools 00:06:54
Splitting Text to Columns in Excel 00:07:39
Synchronous Scrolling Across Multiple Excel Workbooks 00:02:59
Extracting Unique Values from a List in Excel 00:04:06
Using Named Range & Table References in Excel Formulas 00:08:13
Protecting Cells & Formulas in Excel Workbooks 00:08:10
Multi-Level Row & Column Sorting 00:10:55
Configuring Advanced Filter Criteria in Excel 00:09:27
Chapter 3 : Excel Formatting Tips
Introduction 00:00:26
Excel Formatting Shortcuts 00:04:28
Snapping Objects to the Excel Grid 00:04:30
Hiding Excel Workbook Elements 00:04:03
Replicating Formats & Styles with Excel’s Format Painter 00:06:42
Colour & Border Design Tips in Excel 00:07:03
Freezing Panes in Excel Worksheets 00:05:59
Centering Across a Selection 00:04:34
Creating an “Invisible” Format 00:03:56
Zip Codes & Phone Numbers 00:05:00
Grouping Worksheet Columns & Rows 00:06:26
Formatting Excel Errors with IFERROR 00:05:51
Converting Excel Text to Dates 00:10:32
Creating Dynamic Formats with Excel Formulas 00:10:42
Advanced Excel Number Formats 00:10:38
Chapter 4 : Excel Formula Tips
Introduction 00:00:29
Manual vs. Automatic Calculation Modes in Excel 00:04:19
Adding Line Breaks to Excel Formulas 00:04:06
Converting Units of Measurement with Excel Formulas 00:05:51
Building Real-Time Tools with TODAY & NOW Functions 00:10:19
Excel Formula Auditing Tools 00:11:19
Designing Pivot-Style Reports with Excel Formulas 00:08:37
Counting Words in Excel with LEN & SUBSTITUTE Functions 00:07:25
Configuring Dependent Drop-Down Menus with INDIRECT 00:08:29
Creating Custom Hyperlinks Between Excel Worksheets 00:08:13
Using Approximate Match VLOOKUP Functions 00:07:03
Randomizing a Selection with OFFSET & RANDBETWEEN Functions 00:06:23
Combining INDEX & MATCH Functions 00:08:41
Finding Matching Items Across Lists in Excel 00:11:01
Counting Duplicate Values with SUMPRODUCT 00:08:59
Using Many-to-Many Lookups in Excel 00:09:53
Chapter 5 : Excel Visualization Tips
Introduction 00:00:25
Configuring Excel Chart Properties (Move & Size) 00:05:08
Managing Hidden Chart Source Data in Excel Worksheets 00:02:44
Visualizing Geospatial Data with Filled Maps (Excel 2016+ or Office 365) 00:05:11
Customizing Excel Charts to Tell a Story 00:08:54
Adding Sparklines to Worksheet Cells 00:07:45
Designing Custom Chart Templates in Excel 00:06:07
Building Excel Heat Maps Using Colour Scales 00:07:12
Analysing Distribution with Histograms 00:09:29
Goal Pacing with Custom Excel Gauge Charts 00:10:25
Highlighting Time Periods with Combo Charts 00:08:06
Configuring Dynamic Chart Source Ranges 00:12:08
Creating Interactivity with Excel Form Controls 00:11:48
Chapter 6 : Excel Pivot Table Tips
Introduction 00:00:19
Customizing the Pivot Table Field List 00:04:07
Autofitting Column Width in Pivot Tables 00:04:22
Pivot Table Outline & Tabular Layouts 00:07:47
Counting Non-Numerical Fields Using Pivots 00:06:05
Grouping & Ungrouping Dates with Pivot Tables 00:08:04
Enabling Multiple Pivot Table Filters 00:05:00
Grouping Values into Buckets with Pivot Tables 00:06:50
Adding Value Calculations to Pivot Tables 00:09:02
Configuring Pivot Table Errors & Empty Items 00:06:22
Configuring Pivot Table Slicers & Timelines 00:10:23
Advanced Pivot Table Conditional Formatting 00:08:24
Removing & Reviving Pivot Table Source Data 00:08:20
Adding Custom Sort Lists in Excel 00:05:23
Solve Order & List Formula Reports 00:06:16
Chapter 7 : Excel Analytics Tips
Introduction 00:00:30
Excel Quick Analysis Tools 00:06:04
Comparing Outcomes Using the Scenario Manager 00:07:56
Solving Simple Optimizations with Goal Seek 00:06:34
Creating Basic Forecasts in Excel 00:11:10
Building an Outlier Detection Tool in Excel 00:09:24
Generating Automated Data Tables 00:08:07
Connecting & Transforming Data with Power Query 00:10:30
Creating Relational Data Models in Excel 00:10:24
Accessing the Data Model with CUBE Functions 00:16:22
Building a Monte Carlo Simulation in Excel 00:11:39
Advanced Optimization with Solver 00:12:17
Excel Analysis ToolPak (Preview) 00:08:10