English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 305 lectures (32h 4m) | 17.3 GB
6 Courses in 1! Beginner to advanced Excel training from the Excel experts – Simon Sez IT
***Exercise and demo files included***
Excel has always been the go-to software for data organization and financial analysis. Even in the age of Big Data, Excel remains indispensable and relevant, thus almost everyone should at least have some basic Excel skills. If you are brand-new to Excel, then this huge course bundle is perfect for you!
This BIG Excel bundle includes not just two, not three, but six full courses to bring you from Excel newbie to Data Analyst quick! Okay, maybe not that quick, as this bundle gives you 32+ hours of tutorials including more than 300 individual video lectures!
With the Excel 2019/365 beginner course, you’ll gain a fantastic grounding in Microsoft Excel. The course will teach you how to understand spreadsheet basics, including creating your first workbook and how to navigate Excel, an introduction to formulas and functions, how to create amazing-looking charts and graphs, and much more.
Excel PivotTables is an interactive way of quickly summarizing large amounts of data. It is ideal if you are looking to perform data analysis tasks quickly and efficiently in Excel. The beginner and advanced PivotTables courses will discuss the importance of cleaning your data before you can create your first Pivot Table. You will also learn how to make the most of this powerful data analysis function.
The Excel for Business Analysts course focuses on the specific functions, formulas, and tools that Excel has that can help conduct business or data analysis. We take you on a no-nonsense journey to teach you how to use them. In this course, you’ll learn several tools and functions that can be used for analysis and visualization, plus some more advanced techniques designed to aid in forecasting.
We finish by taking you through our favorite and most-useful advanced Excel functions before moving on to teaching you how to use basic Macros and VBA to automate and supercharge your spreadsheets.
This is the only Excel course you are ever going to need!
Excel 2019/365 for Beginners
What you will learn:
- What’s new in Excel 2019
- Creating workbooks
- Entering text, numbers and working with dates
- Navigating workbooks
- Page setup and print options
- Working with rows, columns, and cells
- Cut, Copy and Paste
- Introduction to functions and formulas
- Formatting in Excel, including formatting cells and numbers
- Creating charts and graphs
- Sorting and Filtering
- Introduction to PivotTables
- Logical and lookup formulas – the basics
PivotTables for Beginners
What you will learn:
- How to Clean and Prepare your Data
- How to create a Basic Pivot Table
- How to use the Pivot Table Fields pane
- How to Add Fields and Pivoting the Fields
- How to Format Numbers in Pivot Table
- Different ways to Summarize Data
- How to Group Pivot Table Data
- How to use Multiple Fields and Dimension
- The Methods of Aggregation
- How to Choose and Lock the Report Layout
- How to apply Pivot Table Styles
- How to Sort Data and use Filters
- How to create Pivot Charts based on Pivot Table data
- How to Select the right Chart for your data
- How to apply Conditional Formatting
- How to add Slicers and Timelines to your dashboards
- How to Add New Data to the original source dataset
- How to Update Pivot Tables and Charts
Advanced PivotTables in Excel
What you will learn:
- How to do a PivotTable (a quick refresher)
- How to combine data from multiple worksheets for a PivotTable
- Grouping, ungrouping, and dealing with errors
- How to format a PivotTable, including adjusting styles
- How to use the Value Field Settings
- Advanced Sorting and Filtering in PivotTables
- How to use Slicers, Timelines on multiple tables
- How to create a Calculated Field
- All about GETPIVOTDATA
- How to create a Pivot Chart and add sparklines and slicers
- How to use 3D Maps from a PivotTable
- How to update your data in a PivotTable and Pivot Chart
- All about Conditional Formatting in a PivotTable
- How to create amazing looking dashboards
Excel for Business Analysts
What you will learn:
- How to merge data from different sources using VLOOKUP, HLOOKUP, INDEX MATCH, and XLOOKUP
- How to use IF, IFS, IFERROR, SUMIF, and COUNTIF to apply logic to your analysis
- How to split data using text functions SEARCH, LEFT, RIGHT, MID
- How to standardize and clean data ready for analysis
- About using the PivotTable function to perform data analysis
- How to use slicers to draw out information
- How to display your analysis using Pivot Charts
- All about forecasting and using the Forecast Sheets
- Conducting a Linear Forecast and Forecast Smoothing
- How to use Conditional Formatting to highlight areas of your data
- All about Histograms and Regression
- How to use Goal Seek, Scenario Manager, and Solver to fill data gaps
Advanced Formulas and Functions in Excel
You will learn to:
- Filter a dataset using a formula
- Sort a dataset using formulas and defined variables
- Create multi-dependent dynamic drop-down lists
- Perform a 2-way lookup
- Make decisions with complex logical calculations
- Extract parts of a text string
- Create a dynamic chart title
- Find the last occurrence of a value in a list
- Look up information with XLOOKUP
- Find the closest match to a value
Macros and VBA for Beginners
What you will learn:
- What is VBA?
- How to record your first macro
- How to record a macro using relative references
- How to record a complex, multi-step macro and assign it to a button
- How to set up the VBA Editor
- How to edit Macros in the VBA Editor
- How to get started with some basic VBA code
- How to fix issues with macros using debugging tools
- How to write your own macro from scratch
- How to create a custom Macro ribbon and add all the Macros you’ve created
What you’ll learn
- How to get started using Microsoft Excel as a complete beginner
- How create, save, and open a workbook.
- How to use Mathematic Functions (SUM, AVERAGE, MIN, MAX)
- Complete data entry, cell formatting, working with multiple worksheets and the “Ribbon” interface.
- The basics of entering and editing data in a workbook.
- Discover how to format dates and numbers.
- How to use Logical Functions (IF, COUNTIF, SUMIF)
- Effectively use autofill and flashfill so you are more productive.
- Discover the essentials of using fomulas and functions.
- How to use Lookup Functions (VLOOKUP, XLOOKUP, HLOOKUP, INDEX MATCH)
- How to rename, insert, move, and reference an Excel worksheet.
- Discover how to create charts and graphs
- Learn practical methods to sort and filter your data
- How to protect and secure your Excel data
- How to Clean and Prepare your Data in Excel
- How to create Pivot Tables
- Using the Fields pane and adding Fields and Calculated Fields
- How to create Pivot Charts and add sparklines and slicers
- Selecting the right Chart to present your data
- Adding Slicers and Timelines and how to apply on multiple tables
- How to combine data from multiple worksheets for a PivotTable
- All about GETPIVOTDATA
- How to use 3D Maps from a PivotTable
- Adding new Data to the original source dataset
- How to update your data in a PivotTable and Pivot Chart
- All about Conditional Formatting in a PivotTable
- Learn to unlock advanced Excel formulas
- Create multi-dependent dynamic drop-down lists in Excel
- Perform a 2-way lookup in Excel
- Automate repetitive tasks in Excel using Macros
- How to edit Macros in the VBA Editor
- How to write your own macro from scratch
Table of Contents
Intro to Excel 2019
1 Intro to Excel
2 WATCH ME Essential Information for a Successful Training Experience
3 DOWNLOAD ME Course Exercise Files
4 Downloadable course transcripts
5 What’s New In Excel 2019
6 Course Overview
7 Overview of the Excel window
8 Mouse-Features
9 Back Stage View
Creating Workbooks
10 Entering Text and Numbers
11 Creating Basic Formulas
12 Relative References
13 Order of Operations
14 Working with Ranges
15 Module 2 – Practice Exercise
Saving Workbooks
16 Working with the Save and Save As Commands
17 File Extensions, Share, Export and Publish Files
18 Module 3 – Practice Exercise
Navigating Workbooks
19 Opening a File
20 Working with Larger Files
21 Freeze Panes Option
22 Split Screen Option
23 Module 4 – Practice Exercise
Page Setup & Print Options
24 Headers and Footers
25 Print Titles
26 Comments
27 Page Setup Options
28 Fit to Print on One Page
29 Printing Workbooks
30 Module 5 – Practice Exercise
Working with Rows, Columns and Cells
31 Adding and deleting rows columns and cells
32 Changing Column and-Row Widths
33 Module 6 – Practice Exercise
Moving Data
34 Cut, Copy and Paste
35 Copying Formulas
36 Module 7 – Practice Exercise
Formulas & Functions
37 Overview of Formulas
38 Create Formulas Using Functions Part 1
39 Create Formulas Using Functions Part 2
40 Absolute Values
41 Module 8 – Practice Exercise
Working with Sheets
42 Adding Deleting and Renaming Sheets
43 Additional Sheet Tab Options
44 Dimensional Formulas
45 Module 9 – Practice Exercise
Formatting Worksheets
46 Formatting Cells Part 1
47 Formatting Cells Part 2
48 Formatting Numbers
49 Borders and Shading
50 Format as Table
51 Using Styles
52 Using Format Painter
53 Protecting Sheets
54 Fill Handle and Custom Lists
55 Module 10 – Practice Exercise
Charts
56 Creating Charts
57 Types of Charts
58 Editing Charts Part 1
59 Editing Charts Part 2
60 Using Graphics to Enhance Charts
61 Linking Chart Titles
62 Module 11 – Practice Exercise
Working with Names
63 Creating Range Names
64 Managing Names
65 Using Names in Formulas
66 Module 12 – Practice Exercise
Sorting and Filtering
67 Remove Duplicates
68 Sort Data
69 Filter Data
70 Advanced Filters
71 Module 13 – Practice Exercise
Outlining and Subtotaling
72 Creating an Outline
73 Subtotals
74 Module 14 – Practice Exercise
Working with Views
75 Arranging Files
76 New Window Option
77 Custom Views
78 Module 15 – Practice Exercise
Linking Files
79 Creating Formulas Between File
80 Working with Links
81 Consolidating Data
82 Module 16 – Practice Exercise
Advanced Formula Creation
83 If Statements
84 VLookups
85 Data Validation
86 Formula Auditing
87 Module 17 – Practice Exercise
Pivot Tables
88 Pivot Tables
89 Pivot Charts
90 Module 18 – Practice Exercise
Additional Excel Features
91 Slicers
92 Goal Seeker
93 Text to Columns
94 Watch Window
95 Customizing Ribbons and Tab
96 Module 19 – Practice Exercise
Shortcuts
97 Excel Shortcuts Every User Should Know
Closing
98 Closing Video
Pivot Tables Beginner Introduction
99 Course Introduction
100 WATCH ME Essential Information for a Successful Training Experience
101 DOWNLOAD ME Course Exercise Files
102 DOWNLOAD ME Course Support Files
103 What are Pivot Tables and Why are They Useful
Pivot Tables Beginner Preparing Data for Analysis
104 The Importance of Cleaning Data
105 Removing Blank Rows
106 Removing Duplicate Rows
107 Clearing Formatting
108 Applying Number Formatting
109 Changing the Case
110 Removing Spaces and Non-printing Characters
111 Merging and Splitting Columns
112 Convert Numbers Stored as Text
113 Finding and Replacing Text
114 Spell Check
115 Exercise 01
Pivot Tables Beginner The Importance of Excel Tables
116 Excel Tables vs Pivot Tables
117 Format Data as a Table
118 Exercise 02
Pivot Tables Beginner Creating a Basic Pivot Table Report
119 Using Recommended Pivot Tables
120 Creating a Pivot Table from Scratch
121 Pivot Table Ribbons and Fields
122 Pivoting the Fields
123 Adding Multiple Dimensions
124 Deleting Fields and Locking the Report Layout
125 Exercise 03
Pivot Tables Beginner Aggregation and Grouping
126 Methods of Aggregation
127 Combining Methods of Aggregation
128 Grouping and Ungrouping Data
129 Exercise 04
Pivot Tables Beginner Formatting Values
130 Applying Number Formatting
131 Handling Cells with No Data
132 Exercise 05
Pivot Tables Beginner Layout Options
133 Subtotals and Grand Totals
134 Choosing a Report Layout
135 Inserting Blank Rows
136 Exercise 06
Pivot Tables Beginner Formatting Pivot Tables
137 Applying Pivot Table Styles
138 Modifying Pivot Table Styles
139 Creating a Custom Pivot Table Style
140 Exercise 07
Pivot Tables Beginner Sorting and Filtering
141 Sorting Pivot Table Data
142 Filtering Pivot Table Data
143 Top 10 Filter
144 Exercise 08
Pivot Tables Beginner Creating Pivot Charts
145 Selecting the Right Chart Type
146 Clustered Column and Bar Charts
147 Line Charts
148 Pie and Donut Charts
149 Map Charts
150 Applying Chart Layouts
151 Exercise 09
Pivot Tables Beginner Formatting Pivot Charts
152 Adding a Slicer
153 Formatting a Slicer
154 Adding a Timeline
155 Connecting Slicers and Timelines
156 Exercise 10
Pivot Tables Beginner Applying Conditional Formatting
157 Highlight Cell Rules
158 Data Bars
159 Color Scales
160 Icon Sets
161 Exercise 11
Pivot Tables Beginner Updating Pivot Tables and Pivot Charts
162 Adding Data to the Dataset
163 Updating Pivot Tables and Pivot Charts
164 Exercise 12
Beginner Course Close and Next Steps
165 Course Close and Next Steps
Pivot Tables Advanced Introduction
166 Introduction to Advanced Pivot Tables
167 WATCH ME Essential Information for a Successful Training Experience
168 DOWNLOAD ME Course Exercise Files
169 DOWNLOAD ME Course Support Files
170 PivotTables Recap
Pivot Tables Advanced Importing Data
171 Importing Data From a Text File
172 Importing data from Access
173 Exercise 01
Pivot Tables Advanced Preparing Data for Analysis
174 Cleaning Data
175 Tabular Data
176 Exercise 02
Advanced Creating and Manipulating PivotTables
177 Creating and Manipulating a PivotTable
178 Combining Data from Multiple Worksheets
179 Grouping and Ungrouping
180 Report Layouts
181 Formatting Error Values and Empty Cells
182 Exercise 03
Advanced Formatting PivotTables
183 PivotTable Styles
184 Custom Number Formatting
185 Exercise 04
Pivot Tables Advanced Value Field Settings
186 Summarizing Values
187 Show Values As
188 Exercise 05
Advanced Sorting and Filtering
189 Advanced Sorting
190 Advanced Filtering
191 Exercise 06
Advanced Interacting with PivotTables
192 Inserting and formatting Slicers
193 Inserting and formatting Timelines
194 Connecting Slicers to Multiple PivotTables
195 Using Slicers in Protected Workbooks
196 Exercise 07
Advanced Calculations
197 Creating a Calculated Field
198 Creating a Calculated Item
199 Solve Order and List Formulas
200 GETPIVOTDATA
201 Exercise 08
Advanced Pivot Charts
202 Creating a Pivot Chart
203 Formatting a Pivot Chart – Part 1
204 Formatting a Pivot Chart – Part 2
205 Creating a Map Chart using Pivot Data
206 Dynamic Chart Titles
207 Include a Sparkline with your PivotTable
208 Exercise 09
Advanced Conditional Formatting
209 Highlighting Cell Rules
210 Graphical Conditional Formats
211 Conditional Formatting and Slicers
212 Exercise 10
Advanced Dashboards
213 Creating an Interactive Dashboard – Part 1
214 Creating an Interactive Dashboard – Part 2
215 Updating Pivot Charts and PivotTables
216 Exercise 11
Pivot Tables Advanced Course Close
217 Course Close
Introduction to Excel for Business Analysts
218 Introduction
219 WATCH ME Essential Information for a Successful Training Experience
220 DOWNLOAD ME Course Exercise Files
221 DOWNLOAD ME Course Support Files
The Basics
222 A Recap of Basic Formulas
223 Absolute vs Relative Referencing
224 Exercise 01
Merging and Looking Up Data
225 Merging Data from Different Sources
226 Looking up Information with VLOOKUP (Exact)
227 Looking up information with VLOOKUP (Approx)
228 How to use VLOOKUP with Data that Expands
229 Using HLOOKUP
230 Combining VLOOKUP and MATCH
231 Using INDEX, MATCH and XLOOKUP
232 Data Validation Lists with Lookups
233 Exercise 02
Making Better Decisions with IF
234 How to use the IF function
235 More Examples of the IF Function
236 Working with Nested Ifs
237 The IFS function
238 Error handling with IFERROR and IFNA
239 Using MAX and MIN instead of the IF function
240 Using SUMIFS and COUNTIFS
241 Exercise 03
Preparing Data for Analysis
242 Splitting Data Using Text Functions
243 Using the Flash Fill command
244 Concatenating Data
245 Standardizing Data
246 Formatting Data as a Table
247 Exercise 04
PivotTables
248 An introduction to PivotTables
249 Creating a PivotTable
250 Summarizing Data
251 Calculations in PivotTables
252 Using Slicers to Filter information
253 Exercise 05
Visualizing Data with Charts
254 Creating a Pivot Chart
255 Formatting a Pivot Chart
256 Using Sparklines
257 A Basic Interactive Dashboard
258 Exercise 06
Forecasting
259 Forecast Sheets
260 The Forecast Function
261 Exercise 07
Additional Useful Tools in Excel
262 Conditional Formatting
263 The INDIRECT function
264 The OFFSET function
265 Using Histograms
266 Regression
267 Exercise 08
WhatIf Analysis
268 Goal Seek
269 Scenario Manager
270 Data Tables
271 Solver
272 Exercise 09
Course Close
273 Course Close
Intro to Advanced Formulas in Excel
274 Course Introduction
275 WATCH ME Essential Information for a Successful Training Experience
276 DOWNLOAD ME Course Files
Advanced Formulas
277 Filter a Dataset Using a Formula
278 Sort a Dataset Using a Formula and Defined Variables
279 Multiple Dependent Dynamic Drop-down Lists
280 Perform a 2-way Lookup
281 Make Decisions with Complex Logical Calculations
282 Extracting Parts of a Text String
283 Creating a Dynamic Chart Title
284 Finding the Last Occurrence of a Value in a List
285 Looking Up Information with XLOOKUP
286 Find the Closest Match to a Value
Course Close
287 Course Close
Macros and VBA Course Introduction
288 Introduction
289 WATCHE ME Essential Information for a Successful Training Experience
290 DOWNLOAD ME Course Files
291 DOWNLOAD ME Course Transcript
292 Using Macros to Automate Repetitive Tasks
293 VBA Explained
Creating Macros
294 Recording, Saving and Running a Macro
295 Recording a Macro with Relative Referencing
296 Complex Macros
297 Recording a Macro to Sort and Filter Data
298 Protecting cells with a Macro
Editing Macros with VBA
299 The VBA Editor
300 Understanding VBA Code
301 Editing Macros
302 Debugging Macros
303 Creating a Macro from Scratch
Custom Macro Ribbon
304 Creating a Macro Custom Ribbon
Course Close
305 Course Close
Resolve the captcha to access the links!