What you will learn
After 4 weeks you will know:
- Best practices for Excel Modelling
- How to structure a good Excel Model
- How to standardize formatting in your company
- How to be consistent when creating Excel Sheets
- How/when to apply documentation
- Ways to test, validate, and check Excel models
- Which formulas to use in certain situations
- How to make good use of colors and other formatting
- Which charts to use for which situations
- How to structure Data
- Final steps to take before publishing a model
- Guidelines for protection and hidden information
- How to use Power Query
How you will learn
The course will take 4 weeks, each week you should spend approximately an hour per day, roughly 20 hours in total. Each week will contain several lessons. Each lesson will contain a video, a reading, at least one group discussion and in some cases an assignment.
- The video – The videos in this course are not dry tutorials, they are interviews/discussions with some of the top professionals in Excel worldwide. We use these videos to introduce topics and to spark your interest, while also emphasizing why these topics are so important.
- The discussion – We hope to get a dialogue going between yourself and the others in the course. These discussions will often focus on the practical application of the topics in the lesson material. We believe in collaborative learning and think that you will get the most out of this course by actively discussing with others. This is your own responsibility and we hope you will take the time to adequately formulate your answers and responses.
- The reading – We understand that you may not have the time or patience to spend on long articles and readings. That’s why we have provided tables with an overview and links in every reading. Feel free to only read the sections that apply directly to you in your own workplace, and read at your own speed. We also offer Excel files to work out examples and make screenshots. So should you ever feel confused about a concept, it’s a good idea to look in the Excel files for a practical example. You can also edit and adjust these files yourself to gain a more practical understanding.
- The assignments – Each week will have several assignments: one major assignment to test the learning objectives, 2 peer reviewed assignments (in Excel) and 2 quizzes. They should help you evaluate what you are learning and to see the strides that you are making in modelling in Excel. These assignments also test our standards for the users who will receive the certificate.
Week 1: A Foundation for Better Spreadsheets
In this module you will learn about this course and course aims. You will be given tools to determine whether or not Excel is the correct program for a certain task. You will learn how to organize your input data according to best practices. You will learn how to differentiate between good and bad formatting in Excel, and will receive some practical tips on how to improve your own formatting. Finally you will be able to define documentation in Excel, and learn how to apply good documentation to your own Excel models.
- Introduction: basic rules for expert users
- Use the right tool for the right job
- Get, clean, and reshape your input data
- Set your standards for consistent formatting
- Apply accurate documentation from the start
Week 2: Good use of Formulas and Functions
This week is about the lifeblood of Excel, the functions and formulas. You will learn how you can apply functions and formulas to your models responsibly, how you can discern between functions to choose the ones that work best for you in a particular scenario, and how you can understand and explain the strengths and weaknesses of different functions.
- Find & Search Functions
- Calculation Functions
- Other Functions
Week 3: Spreadsheet Structure and Logical Flow
In this module you will learn how to build up a spreadsheet with a logical structure and flow, you will get some great tips on keeping your models simple, and you will learn about the dangers and effectiveness of protected and hidden information in Excel. Essentially you will gain the tools to make your spreadsheets safer and more understandable for your colleagues/clients.
- Maintain a logical spreadsheet flow
- Keep it simple
- When possible, protect, don’t hide
Week 4: Finishing Touches
In this final module you will learn about the final things to do in a spreadsheet. You will discover what a finished spreadsheet looks like, which checks you can build in to ensure correct results, and you will gain the tools to check last minute things in your model to ensure the final result is correct. The checking and testing process is incredibly important, use this module to learn more about that process and apply it to your own work.
- Charts and Reporting
- Checking, Testing, and Validation
- Final things to check and double check