Autoplay
Autocomplete
Previous Lesson
Complete and Continue
MS Excel for Business Professionals
-= INTRODUCTION =-
Welcome to the course! Introduce yourself here
Course resources - templates, schedules, links
Recommended training schedule
-= MODULE 1 - MAKE YOUR DATA ORGANIZED =-
What is organized data?
How to make a structure: MECE concept
Example: Structured Customers List
MS Excel: Introduction
MS Excel Overview
Interface and Ribbon Concept
Quick access toolbar
Workbook: Overview
Workbook: New
Workbook: Open
Workbook: Save and Save As
Worksheet: Overview
Worksheet: Rename
Worksheet: Delete
Worksheet: Add new
Cells and Ranges: Overview
Data Input: Text
Data Input: Numbers
Data Input: Date and Time
Data Input: Editing cells content
Cells and Ranges: Selection
Data Input: Copy and Paste
Data Input: Undo and Redo
Columns and Rows: Overview
Columns and Rows: Change the width and height
Formatting: Typeface, size and color
Formatting: Cell background
Formatting: Simple borders
First Challenge!
Make a To-Do List in MS Excel: Introduction
Make a To-Do List in MS Excel: Guidelines
Make a To-Do List in MS Excel: Solution
Make any other structured list you need
-= MODULE 2 - THE BUDGET MODELS =-
What is a budget model?
Types of budgets
Problems you can solve with this model
Example: Beverages Company Budget
MS Excel: Manipulations with data
Worksheet: Copy
Worksheet: Formatting tabs
Data Input: Paste special options
Data Input: The FILL handles
Rows and Columns: Insert
Rows and Columns: Delete
Cell Data Formats: Number
Cell Data Formats: Text
Cell Data Formats: Date and Time
Cell Data Formats: Percentage
Format Clearing
Format Painter
MS Excel: Introduction to Calculations
Introduction to Formulas
How to get proficient with math
Math Formulas: Addition
Math Formulas: Subtraction
Math Formulas: Multiplication
Math Formulas: Division
Math Formulas: Power
Math Formulas: Order of Operations (BODMAS)
Introduction to Functions
Formulas: SUM
Formulas: COUNT
Formulas: AVERAGE
Formulas: MIN AND MAX
Formulas: ROUNDING
2nd challenge: Your personal budget
Make your personal budget: Introduction
Make your personal budget: Structure
Make your personal budget: Guidelines
Make your personal budget: Solution
-= MODULE 3 - THE LARGE DATA-SETS ANALYSIS =-
What is a large data-set?
What kind of data companies have and you could use?
Business problems you can solve by large datasets analysis
Example: Identify profitable products and customers for a retail company
Example: Identify the most profitable models for an auto dealer
MS Excel: More on Data Manipulations
Worksheet: Hide and Unhide
Worksheet: Moving within Workbook
Worksheet: Moving within Workbooks
Cells and Ranges: Selection Shortcuts
Cells and Ranges: Clearing the data
Columns and Rows: Hide and Unhide
Formatting: Custom Borders
Formatting: Text direction in a cell
Formatting: Cell Merge
Formatting: Worksheet Themes
Formatting: Data into a table for formatting
MS Excel: Conditional Formatting
Adding Conditional Formatting Rules
Change Cells Format based on its Value
Removing Conditional Formatting Rules
Highlighting Duplicate Values
Color Scales in Conditional Formatting
Display Formula text on a worksheet
MS Excel: More on Calculations
Referencing: Relative and Absolute
Referencing: Absolute for Column
Referencing: Absolute for Row
Linking worksheets and workbooks
3D Referencing
Name Manager: Naming Cell or Range
Name Manager: Applying Names in Formulas
Text Functions: Basic
Text Functions: Advanced
Date and Time Functions: Basic
Date and Time Functions: Advanced
Logical Functions - Conditional Calculations with IF
Error Handling Functions
Extract data from the internet
MS Excel: Database tools and functions
Rows and Columns Fix on the screen
Sort
Filter
Remove duplicates
Text to Columns
Split the screen
Grouping rows and columns (Outlining)
Pivot Tables: Add a pivot table (5:12)
Pivot Tables: Making a custom report
Pivot Tables: Changing formatting
Pivot Tables: Changing structure
Data Validation: List
Data Validation: Conditions
VLOOKUP: Introduction
VLOOKUP: Exact Match
VLOOKUP: Handling errors
HLOOKUP
SUMIF, SUMIFS
COUNTIF, COUNTIFS
AVERAGEIF, AVERAGEIFS
3rd challenge: Identify profitable products and customers for a beverages company
Profitable products and customers: Introduction
Profitable products and customers: Guidelines
Profitable products and customers: Solution
-= MODULE 4 - CALCULATIONAL MODELS =-
What is a calculational model
Types of calculational models
Structure of a calculational model
Example: Equipment modernization - Return on Investment calculation
Modeling assumptions
Forecasting earnings
Forecasting expenses
Putting it all together
MS Excel: Finance functions
Time value of money
Finance functions: PMT
Finance functions: PV
Finance functions: FV
Finance functions: NPV
4th challenge: Make a sales forecast for a touristic company
Touristic company sales forecast: Introduction
Touristic company sales forecast: Guidelines
Touristic company sales forecast: Solution
-= MODULE 5 - DATA VISUALISATION =-
Why do you need to visualise your data
Types of data visualisation
Business problems where visuals are better than numbers
What is a dashboard
Example: Sales Dashboard for a software distribution company
MS Excel: Using Charts
Charts overview
Adding a chart from scratch
2-D Column charts - Clustered, Stacked and 100% Stacked
2-D Lines, including Stacked and 100% Stacked
Pie charts
Modifying chart type
Chart design
Adding secondary axis to a chart
MS Excel: Graphic Objects
Equations and Symbols
Drawing shapes
External picture
Stacking order and alignment
Grouping graphic objects
Print responsibly
Set up print areas, print scales
Page orientation and margins
Adding a header and a footer
Fix rows and columns for print - adding titles
Print preview
Printing options - copies, collated
Security
Protection of the worksheet
Protect specific cells and make a form for data entry
Access by password
Protecting workbook by password
5th Challenge: Making a performance dashboard for a construction project
Performance dashboard: Introduction
Performance dashboard: Guidelines
Performance dashboard: Solution
-= CONGRATULATIONS AND NEXT STEPS =-
Review your goals
Set the new goals and practice
Go for advanced courses
Credits - thank you for people involved
Protect specific cells and make a form for data entry
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock