51 Best Excel Tips And Tricks To Skyrocket Your Productivity

Master 51+ Excel tips to build advanced Excel skills: formulas, pivot tables, charts, data analysis & data visualization

What you'll learn

Practice with fun, interactive, and highly effective lessons from a best-selling Excel instructor

Become an absolute Excel POWER USER

Get LIFETIME access to resources, Excel project files, quizzes, and 1-on-1 expert support

Master Excel's powerful data analysis tools like Goal Seek, Scenario Manager, Solver, and Analysis ToolPak

Build tools to help you automate, streamline, and absolutely revolutionize your workflow with Excel

Requirements

Experience with formulas & functions, basic charts, and PivotTables strongly encouraged (but not required)

This course is designed for PC users (some tips & tools may not be available for Mac)

Microsoft Excel 2007-2019 or Office 365 (Note: some tips may not apply to all versions of Excel)

Description

The goal of this training is to turn regular individuals into Excel POWER USERS.It's not about providing exhaustive, 101-style deep dives into Excel's fundamental features or about showcasing corny, useless Excel "hacks". It's about showcasing some of the most potent and successful tools & strategies used by Excel pros and sharing them through distinct, real-world case studies and crystal-clear examples.This Excel course is non-linear and continually changing, unlike the majority of them. This enables you to discover freshly added Excel tips that are added on a regular basis, and freely browse to other sections or specific lectures.  To enable you to learn these tools and strategies in brief, digestible sessions, each video is created to be a standalone and self-contained Excel demonstration.What's best? Each Excel example and dataset covered in the course is included in a downloadable Excel workbook and solution file that is categorized by category, scored by difficulty (1–5), and hyperlinked for quick and easy access.The Excel hints and tricks discussed in this course can be divided into six main groups:Excel productivity tips include advanced sorting and filtering, cell protection, flash fill, and navigation.Tips for formatting Excel (freeze panes, grouping rows and columns, advanced custom formats, etc.)Excel formula hints (fuzzy lookups, duplicate detection, randomization, formula auditing tools, etc.)Excel visual aids, including sparklines, full maps, personalized templates, and form controls(Slicers & timelines, custom layouts, value calculations, etc.) Excel Pivot Table Tips(Outlier identification, Monte Carlo simulation, forecasting, CUBE functions, etc.) Excel analytics tips

Overview

Section 1: Introduction

Lecture 1 Introduction

Section 2: 51 Best Excel Tips and Tricks to skyrocket your productivity

Lecture 2 1. Save workbooks in ‘Excel Binary‘ format

Lecture 3 2. Hassle free formatting with Ctl+1

Lecture 4 3. Improve your speed by using Autosum

Lecture 5 4. Customize your Quick Access Toolbar [QAT]

Lecture 6 5. Format Painter

Lecture 7 6. Use wildcards to find & replace data

Lecture 8 7. Everything is special with Paste Special

Lecture 9 8. Arithmetic operations with Paste Special

Lecture 10 9. Advanced excel tip – Breakdown lengthy formulas

Lecture 11 10. Quick filter technique

Lecture 12 11. Sort data before working on large files

Lecture 13 12. Named ranges can add a lot of value

Lecture 14 13. Differentiate Input cells & Formula cells with a colour coding

Lecture 15 14 Control+Enter can solve formula fill problem

Lecture 16 15 Never ever hard code your formulas

Lecture 17 16 Hide cell contents in Conditional Formatting

Lecture 18 17 Use Transpose to transform your data

Lecture 19 18 Learn CVZY shortcuts to save 90% of your time

Lecture 20 19 Avoid using Merge & Center

Lecture 21 20 Beautify your reports with structured formatting

Lecture 22 21 Use built-in styles for quick formatting

Lecture 23 22 Use Transpose formula for live updates

Lecture 24 23 Find & Select (or GoTo special) to identify all the formulas in a worksheet

Lecture 25 24 Be careful with volatile formulas

Lecture 26 25 Calculation Options can help as well as ruin your work

Lecture 27 26 best excel tip – GET RID OF GRIDLINES

Lecture 28 27 Use TRIM formula to remove unwanted spaces

Lecture 29 28 Extract Day, Month & Year from a date

Lecture 30 29 Measure length of a string with LEN formula

Lecture 31 30 Use IFERROR formula for error management

Lecture 32 31 Right formula can extract text from right side

Lecture 33 32 Left formula to extract text from Left side

Lecture 34 33 MID formula – extract middle name

Lecture 35 34 Dynamic Right & Left formulas

Lecture 36 35 Use SUBSTITUTE formula to replace text

Lecture 37 36 Make use of Upper, Lower & Proper functions to play with the text

Lecture 38 37 Be careful with circular reference errors

Lecture 39 38 Trace precedents

Lecture 40 39 Trace Dependents

Lecture 41 40 BEST EXCEL TIPS – Take full advantage of Sumif & Sumifs

Lecture 42 41 Boost your analytical skills with SUMPRODUCT formula

Lecture 43 42 Don’t scroll Up & Down, use Freeze Panes

Lecture 44 43 Generate Random numbers using Randbetween formula (Caution)

Lecture 45 44 Use ‘Group’ feature to organize your workbook

Lecture 46 45 Excel tip for spead – Be a keyboard user

Lecture 47 46 Commenting is essential – yet we ignore

Lecture 48 47 “Prepare for Tsunami” – Always backup your workbooks

Lecture 49 48 Quick filter check

Lecture 50 49 Use custom sort – cool excel tip

Lecture 51 50 Spell check works in Excel too

Lecture 52 51 Don’t let someone ruin your hard work – Protect your formulas

Section 3: EXCEL SPREADSHEET DESIGN – 7 GOLDEN RULES

Lecture 53 Introduction

Lecture 54 Rule 1 – Adopt a Standard And Stick To It

Lecture 55 Rule 2 – Know Your Audience

Lecture 56 Rule 3 – Include a Welcome Sheet

Lecture 57 Rule 4 – Separate Your Data

Lecture 58 Rule 5 – Design For Longevity

Lecture 59 Rule 6 – Use Consistent, Clear Structure

Lecture 60 Rule 7 – Control Data Input

Section 4: Conclusion

Lecture 61 Conclusion

This course is NOT recommended for absolute beginners (some prior experience with Excel is encouraged),Anyone looking to pursue a career in data analysis or business intelligence,Excel users looking to level-up and master the same tools and techniques used by Excel MVPs,Anyone hoping to maximize productivity, work more efficiently, and take their career to a new level