About this course: Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces around the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. Across the globe, millions of job advertisements requiring Excel skills are posted every day. At a time when digital skills jobs are growing much faster than non-digital jobs, completing this course will position you ahead of others, so keep reading. In this last course of our Specialization Excel Skills for Business you will build on the strong foundations of the first three courses: Essentials, Intermediate I + II. In the Advanced course, we will prepare you to become a power user of Excel - this is your last step before specializing at a professional level. The topics we have prepared will challenge you as you learn how to use advanced formula techniques and sophisticated lookups. You will clean and prepare data for analysis, and learn how to work with dates and financial functions. An in-depth look at spreadsheet design and documentation will prepare you for our big finale, where you will learn how to build professional dashboards in Excel.
Who is this class for: Taking Excel Skills for Business: Advanced will complete the learning journey of this Specialization. Attaining the certificate at the end of the fourth course will be a highly valuable asset for anyone wanting to improve their employment portfolio. This course is for anyone who works with Excel on a regular basis, for self-taught learners with well-developed intermediate skills who want to fill gaps in their knowledge and for anyone wanting to consolidate their well-developed knowledge of Excel.
Created by: Macquarie University
Taught by: Dr Yvonne Breyer, Director, Learning and Teaching
Faculty of Business and Economics
| Basic Info | Course 4 of 4 in the Excel Skills for Business Specialization |
| Level | Intermediate |
| Commitment | 6 weeks of study, 2-4 hours per week |
| Language | English |
| How To Pass | Pass all graded assignments to complete the course. |
| User Ratings |
Syllabus
WEEK 1
Spreadsheet Design and Documentation
As an intermediate Excel user, you have the basics down. The first module of the Advanced course focusses on creating and maintaining accurate, flexible, responsive and user-friendly spreadsheets. You will learn how to design flexible and auditable spreadsheets, construct transparent calculations, create user-friendly navigation, and use styles, themes and formatting to tie it all up in a nice bow.
7 videos, 7 readings, 4 practice quizzes
- 阅读: Welcome
- 阅读: Course Structure & Assessments
- 阅读: Course goals and weekly learning objectives
- 讨论提示: Excel: Who, Why, How?
- Video: Week 1 Discussion
- 阅读: Read me before you start: Quizzes and navigation
- 阅读: Read me before you start: Versions and regions
- 阅读: Download the Week 1 workbooks
- Video: Practice Video: Spreadsheet Design Principles
- 练习测验: Spreadsheet Design Principles
- Video: Practice Video: Calculations
- 练习测验: Calculations
- Video: Practice Video: Formatting
- 练习测验: Formatting
- Video: Practice Video: Documentation
- 练习测验: Documentation
- Video: Practice Video: Interface and Navigation
- 阅读: Keyboard Shortcuts, Terminology, and Ninja Tips
Graded: Week 1 Final Assignment
WEEK 2
Advanced Formula Techniques
Excel often requires you to perform complex analysis on large sets of data. This can be made a lot easier using array formulas, which can perform multiple calculations simultaneously and ensure the accuracy of your spreadsheet. This module covers the use of array formulas, how to build them and use them effectively.
7 videos, 2 readings, 4 practice quizzes
- Video: Week 2 Discussion
- 阅读: Download the Week 2 workbooks
- Video: Practice Video: Tables and Structured Referencing
- 练习测验: Tables and Structured Referencing
- Video: Practice Video: Using Functions to Sort Data
- 练习测验: Using Functions to Sort Data
- Video: Practice Video: Introduction to Array Formulas
- 练习测验: Introduction to Array Formulas
- Video: Practice Video: Working with an Array Function (TRANSPOSE)
- Video: Practice Video: Solving Problems with Array Formulas
- 练习测验: Week 2 Practice Challenge
- 阅读: Keyboard Shortcuts, Terminology, and Ninja Tips
Graded: Week 2 Final Assignment
WEEK 3
Data Cleaning and Preparation
Been handed a messy spreadsheet? This module focuses on data cleaning and preparation. You will learn how to use dynamic and automated fixes, clean messy data, automate these processes through functions, and ensure data remains clean dynamically. We will use functions to fix dates, replaces blanks and remove unwanted characters from data – all to help us build an efficient solution that lasts.
7 videos, 2 readings, 6 practice quizzes
- Video: Week 3 Discussion
- 阅读: Download the Week 3 workbooks
- Video: Practice Video: Replace blanks with repeating values
- 练习测验: Replace blanks with repeating values
- Video: Practice Video: Fix Dates (DATE, MONTH, YEAR, DAY, TEXT)
- 练习测验: Fix Dates (DATE, MONTH, YEAR, DAY, TEXT)
- Video: Practice Video: Remove Unwanted Spaces (TRIM, CLEAN)
- 练习测验: Remove Unwanted Spaces (TRIM, CLEAN)
- Video: Practice Video: Diagnostic Tools (ISNUMBER, LEN, CODE)
- 练习测验: Diagnostic Tools (ISNUMBER, LEN, CODE)
- Video: Practice Video: Remove Unwanted Characters (SUBSTITUTE, CHAR, VALUE)
- 练习测验: Remove Unwanted Characters (SUBSTITUTE, CHAR, VALUE)
- 练习测验: Week 3 Practice Challenge
- 阅读: Keyboard Shortcuts, Terminology, and Ninja Tips
Graded: Week 3 Final Assignment
WEEK 4
Financial Functions and Working with Dates
This module introduces financial functions into our Excel knowledge base. Financial functions are often used in businesses and Excel has a variety of tools to help you with that. This week, you will learn to apply date formulas in calculations, create loan schedules with financial functions, as well as use depreciation functions.
7 videos, 2 readings, 5 practice quizzes
- Video: Week 4 Discussion
- 阅读: Download the Week 4 workbooks
- Video: Practice Video: Working with Dates (EOMONTH, EDATE, WORKDAY.INTL)
- 练习测验: Working with Dates (EOMONTH, EDATE, WORKDAY.INTL)
- Video: Practice Video: Financial Functions (FV, PV, PMT)
- 练习测验: Financial Functions (FV, PV, PMT)
- Video: Practice Video: Loan Schedule (PMT, EDATE)
- 练习测验: Loan Schedule (PMT, EDATE)
- Video: Practice Video: Net Present Value and Internal Rate of Return (NPV, IRR)
- 练习测验: Net Present Value and Internal Rate of Return (NPV, IRR)
- Video: Practice Video: Depreciation Functions (SLN, SYD, DDB)
- 练习测验: Depreciation Functions (SLN, SYD, DDB)
- 阅读: Keyboard Shortcuts, Excel Terminology, and Ninja Tips
Graded: Week 4 Final Assignment
WEEK 5
Advanced Lookup Functions
It is always a good idea to make your spreadsheets (and work!) more automated. Automated lookups can make your work fast and efficient. This module builds on your array capabilities and explores a range of functions to create dynamic lookup ranges – INDIRECT, ADDRESS, OFFSET and INDEX.
6 videos, 2 readings, 3 practice quizzes
- Video: Week 5 Discussion
- 阅读: Download the Week 5 workbooks
- Video: Practice Video: INDIRECT
- 练习测验: INDIRECT
- Video: Practice Video: ADDRESS
- 练习测验: ADDRESS
- Video: Practice Video: Introduction to OFFSET
- 练习测验: Introduction to OFFSET
- Video: Practice Video: Solving Problems with OFFSET
- 阅读: Keyboard Shortcuts, Excel Terminology, and Ninja Tips
Graded: Week 5 Final Assignment
WEEK 6
Building Professional Dashboards
To bring it home, this module guides you through the steps of building a professional dashboard. Data is meaningless unless it tells a story and can be used as business intelligence. This module will help you set up a dashboard – learn how to visualise data through graphs and charts, create data models, and add interactivity.
7 videos, 2 readings, 1 practice quiz
- Video: Week 6 Discussion
- 阅读: Download the Week 6 workbooks
- Video: Practice Video: Dashboard Design
- 练习测验: Dashboard Design
- Video: Practice Video: Prepare Data
- Video: Practice Video: Construct Dashboard
- Video: Practice Video: Creative Charting
- Video: Practice Video: Interactive Dashboard
- 阅读: Keyboard Shortcuts, Excel Terminology, and Ninja Tips
Final Assessment
Graded: Final Assessment
FAQs
How It Works
课程作业
每门课程都像是一本互动的教科书，具有预先录制的视频、测验和项目。
来自同学的帮助
与其他成千上万的学生相联系，对想法进行辩论，讨论课程材料，并寻求帮助来掌握概念。
证书
获得正式认证的作业，并与朋友、同事和雇主分享您的成功。
Creators
Macquarie University
Macquarie is a hub of inspired and unconstrained thinking. Macquarie was founded with a unique purpose: to bring minds together unhindered by tradition. Created to challenge the education establishment, Macquarie has a rich track record of innovation. Macquarie actively shapes the complex issues that define the future of humanity.
Pricing
| Purchase Course | |
|---|---|
| Access to course materials | Available |
| Access to graded materials | Available |
| Receive a final grade | Available |
| Earn a shareable Course Certificate | Available |
Ratings and Reviews
I have and will continue to recommend this course to my peers. I have learned a great deal in a short period. Very well put together
It was great to take such course from amazing experts
Thank you all, thank you Coursera
Very useful. The assessment is challenge in a good way. During those training courses, it help me learn to find a solution and have Excel knowledge in depth. Highly recommend anyone to take this course, not only can you use on a daily life for personal use, but also help in your professional work as well.
Coursera provides universal access to the world’s best education,
partnering with top universities and organizations to offer courses online.
© 2018 Coursera Inc. All rights reserved.
Great course and excellent professors! Everything was perfectly structured, well-explained, and user-friendly presented. I guess it's going to be one of my favorite Coursera courses I have ever had! Great job and big thanks for this, dear professors!