Beginner to pro use Excel like a management consultant

Beginner to pro use Excel like a management consultant

What you’ll learn

Start using Excel like a management consultant
Learn the most powerful tricks, shortcuts, and functions
See the best way to address your everyday Excel problems
Dramatically increase your speed and productivity
Laser-focused lessons on what you will really need, no time wasted on functions you’ll never use

Requirements

Microsoft Excel 2007-2021 or Office 365
Basic experience with Excel required for easier understanding of the course lessons (this is not an Excel 101 course)

Description

!!! New end-of-section tests added December 2022!!!Do you want to get better at Microsoft Excel, but you’re overwhelmed by the amount of effort required and you don’t know where to start from?Well, HERE is where you should start from!I designed this course to give you a complete and yet essential overview of the key functions, formulas, and shortcuts you need to know to master Excel. By the end of the course, you will be able to:Customize the Quick Access Toolbar, a very powerful tool often neglectedUse your keyboard and not your mouse to speed up navigating within a sheet and through the sheetsMerge databases with VLOOKUP, INDEX/MATCH, and with the brand new XLOOKUP functionUse dynamic array functions (Excel 365 only)Create and format Pivot Tables and extract complex data from them, with functions like calculated fieldsManage sensitivities and scenarios (e.g., of a business plan) effectivelyAdd your own macros to your workbooks without the need to know VBA programming languageProperly print Excel sheetsUse advanced formulas to manage text cellsManage links and hyperlinksConsolidate your knowledge of some classic Excel functions like IF, SUMIF, SUMPRODUCT, and SUBTOTALCreate your own custom formatting and advanced sorting And use many more additional functions and keyboard shortcuts you may not know to boost your productivityTo achieve the above, we will use real-life examples of some of the tasks you could face in your daily job, like analyzing a database, managing a business plan, or creating customer lists and input forms.I worked in a top-tier management consulting firm for over 10 years. During these years, I’ve strived to become the fastest and most effective I could in Excel, taking courses and experimenting. I’ve also witnessed the most common struggles and pitfalls with Excel you may suffer from, having worked with dozens of clients and having met hundreds of people. And I designed this course to help you solve them.The good news is that it’s going to be easier than what you expect. Yes, there are hundreds of functions and shortcuts in Excel. But the untold truth is that you’ll really need to master only a small subset of those to dramatically increase your performances. And I distilled them for you in this course!In the lessons I’ll teach you step by step how to use Excel formulas, but that’s not all. Most importantly, I’ll also tell you all the valuable tips and tricks I learned in years of intense Excel usage and experimenting.You can consider this course as your best investment. The time spent learning is going to be just a tiny fraction of the amount of time you’ll be saving with your new skillset.To support your learning, you’ll have the chance to download multiple additional resources:A blank version of the files, to follow the lessons along with me, and the final version of the files, including all the steps done throughout the lessonsA pdf cheat sheet containing the list of my preferred shortcuts (one for Windows, one for Mac OS users)My personal Quick Access Toolbar configurationA link to join our online Facebook community for lifelong learningFinally, if you’re a Mac user, you’ll find a dedicated lesson summarizing the main differences vs. Excel for Windows and dedicated downloadable resources to help you get the most out of this course.Are you ready to revolutionize the way you work?Disclaimer: the content of the lessons was developed by myself specifically for this course, and it does not represent a transposition of any of the trainings created by my employer. Also, the views expressed on this course are mine alone and do not necessarily reflect the views of my employer.

Overview

Section 1: Introduction

Lecture 1 Welcome to the course

Lecture 2 Course bonuses and downloads

Lecture 3 Note on Excel for Mac OS users

Lecture 4 How to save up to 5 days/year with one simple change

Lecture 5 Customize your Quick Access Toolbar

Lecture 6 Use my battle-tested Quick Access Toolbar

Lecture 7 Brief note on your Excel versions

Section 2: Effectively manage databases

Lecture 8 Introduction

Lecture 9 Move quickly within the page

Lecture 10 Move quickly between sheets

Lecture 11 Fast-manage your columns and rows

Lecture 12 There’s something better than merging cells: Center across selection

Lecture 13 Merge databases with VLOOKUP

Lecture 14 VLOOKUP on steroids: INDEX/MATCH

Lecture 15 Speed-up pasting in multiple cells

Lecture 16 Manage formula errors with IFERROR

Lecture 17 Exercise: practice VLOOKUP and INDEX/MATCH

Lecture 18 Exercise solution

Lecture 19 AUTOSUM

Lecture 20 Improve your database readability with FREEZE PANES

Lecture 21 Function keys: yes, they’re useful! (GO TO and more)

Lecture 22 Count unique values from a list: the old way

Lecture 23 Count unique values from a list: the new (and best) way

Lecture 24 Exercise: end of section test

Lecture 25 Test solution

Section 3: Make the most of your data with Pivot Tables

Lecture 26 Introduction

Lecture 27 Create your Pivot Table

Lecture 28 Format your Pivot Table

Lecture 29 Extract the insights you need: sort, expand/collapse, filter

Lecture 30 Extract the insights you need: value calculation

Lecture 31 Create custom fields: group items

Lecture 32 Create custom fields: Calculated Field

Lecture 33 Use Calculated Field to properly calculate % changes in Pivot Tables

Lecture 34 One, two, multiple correct answers: make sure you know what you’re looking for

Lecture 35 Link cells to Pivot Tables: the “regular data” way

Lecture 36 Link cells to Pivot Tables: the GETPIVOTDATA way

Lecture 37 Group data in buckets

Lecture 38 Retrieve data from Pivot Tables

Lecture 39 Exercise: review what you learnt so far

Lecture 40 Exercise solution

Lecture 41 Exercise: end of section test

Lecture 42 Test solution

Section 4: Master a business plan

Lecture 43 Introduction

Lecture 44 Get to know your business plan

Lecture 45 Choosing among ranges: nested IF

Lecture 46 Choosing among ranges: IFS

Lecture 47 Choosing among ranges: fuzzy lookup

Lecture 48 Rounding values: ROUND, ROUNDUP, ROUNDDOWN, MROUND

Lecture 49 Paste Link: once you know it, you can’t do without it

Lecture 50 Name cell ranges to make your Business Plan more intuitive

Lecture 51 Quickly fill multiple cells simultaneously

Lecture 52 Unleash the power of the double-click

Lecture 53 Trace precedents/dependents

Lecture 54 Create hyperlinks

Lecture 55 Introduction to business plan scenarios

Lecture 56 Add switches with DATA VALIDATION

Lecture 57 Find the right value with GOAL SEEK

Lecture 58 Run one-dimensional sensitivities with DATA TABLE

Lecture 59 Run two-dimensional sensitivities with DATA TABLE

Lecture 60 Optimize complex problems with SOLVER

Lecture 61 Exercise: practice using Data Tables

Lecture 62 Exercise solution

Lecture 63 Design your own Macros. Wait, what??

Lecture 64 Add buttons to your file to better use your Macros

Lecture 65 Print Excel sheets

Lecture 66 Exercise: end of section test

Lecture 67 Test solution

Section 5: Manage customer lists and data collection

Lecture 68 Introduction

Lecture 69 Format imported text with TEXT TO COLUMNS

Lecture 70 Extract text from a string (LEFT, MID, FIND)

Lecture 71 Finding the n-th occurrence of a character (RIGHT, LEN, SUBSTITUTE, CHAR)

Lecture 72 Exercise: replicate the formula

Lecture 73 Exercise solution

Lecture 74 The easy way out: FLASH FILL

Lecture 75 Combine text from multiple cells with CONCAT

Lecture 76 Fill a cell with the worksheet name

Lecture 77 Edit multiple sheets simultaneously

Lecture 78 Remove rows and columns you don’t need

Lecture 79 Make your files safe for sharing: protect your sheets

Lecture 80 Save a single Excel sheet and transfer sheets across files

Lecture 81 Use cell content as part of the formulas: INDIRECT

Lecture 82 Combine VLOOKUP and INDIRECT to create dynamic references to sheets

Lecture 83 Quickly sum the same cell from multiple sheets

Lecture 84 Manage wrongly formatted numbers

Lecture 85 Edit links

Lecture 86 Apply simple conditional formatting

Lecture 87 Manage conflicting conditional formatting rules

Lecture 88 Apply advanced conditional formatting

Lecture 89 Exercise: end of section test

Lecture 90 Test solution

Lecture 91 Test solution: text formulas deep dive

Section 6: Conclusion

Lecture 92 Congratulations

Section 7: Appendix 1: fix the basics

Lecture 93 Section introduction and disclaimer (this section is different from the others)

Lecture 94 Section downloads

Lecture 95 How to use IF in Excel (IF, IF and AND / OR, nested IF, IFS)

Lecture 96 How SUMIF and SUMIFS work

Lecture 97 How COUNTIF and COUNTIFS work

Lecture 98 How to use logical operators (greater than or equal to, etc.) & wildcards (*, ?)

Lecture 99 How SUMPRODUCT works

Lecture 100 How SUBTOTAL works and when it is useful

Lecture 101 Use an apostrophe to manage numbers starting with 0 or text starting with + or –

Lecture 102 Master number formatting: use Excel presets and create custom number formats

Lecture 103 Master Excel sorting (basic sort, multiple sort levels, sort horizontally)

Lecture 104 Sorting databases with formulas: how to avoid messing up your DB

Lecture 105 Quick tip: how to edit a cell and stay in the same cell or move to another cell

Lecture 106 Quick tip: How to create workbooks with just one sheet

Section 8: Appendix 2: XLOOKUP, the new kid on the block!

Lecture 107 Section introduction

Lecture 108 Section downloads

Lecture 109 How XLOOKUP works and why it’s better than VLOOKUP

Lecture 110 How to use XLOOKUP instead of INDEX/MATCH

Lecture 111 Manage errors with XLOOKUP (XLOOKUP optional fields)

Lecture 112 Manage approximate matches with XLOOKUP (XLOOKUP optional fields)

Lecture 113 Use XLOOKUP and VLOOKUP with wildcards (XLOOKUP optional fields)

Lecture 114 Use XLOOKUP with multiple matches (XLOOKUP optional fields)

Section 9: Appendix 3: extra lessons

Lecture 115 Change geographical settings of your Excel

Lecture 116 Most useful Windows shortcuts

People using Excel on their jobs willing to improve their productivity,Graduating students willing to join a consulting firm or any company with an Excel expertise that will impress their managers,Companies/employers looking for an effective course to improve their staff Excel skills,Anybody using Excel willing to expand their skill set

Course Information:

Udemy | English | 11h 16m | 7.05 GB
Created by: Federico Ferrari

You Can See More Courses in the Office Productivity >> Greetings from CourseDown.com