This repository contains a professional Excel training workbook. It focuses on seven specific functions to help you analyze data and build financial models. You can practice these techniques to improve your spreadsheet skills and work with confidence.
This course covers tools for logic, data organization, and scenario testing. You will learn to use these features:
- Goal Seek: Find the input value needed to reach a specific result.
- Data Validation: Control what data users enter into your cells.
- Scenario Manager: Create and compare different sets of variables.
- Name Manager: Use text labels instead of cell references to keep formulas clear.
- Advanced Filter: Manage large lists with complex search criteria.
- Dependable Dropdowns: Build menus that change based on previous choices.
- INDIRECT: Create flexible references that update as you change your data.
These tools allow you to perform deep data analysis without manual repetition.
Ensure your computer has the following software installed:
- Microsoft Excel: Any version from 2016 or newer works.
- Windows 10 or Windows 11 operating system.
- Basic familiarity with opening and saving files on your computer.
Follow these steps to download the workbook to your computer:
- Visit the releases page to see the files.
- Locate the file ending in
.xlsx. - Click the filename to start the download.
- Open your "Downloads" folder in Windows File Explorer.
- Move the file from your "Downloads" folder to your chosen "Documents" or "Projects" folder.
Once you moved the file to your folder, follow these steps to start your training:
- Double-click the file named
excel-course-part-2-functions.xlsx. - Microsoft Excel will open the file automatically.
- If you see a bar at the top of the window that says "Enable Editing," click that button to begin typing in the cells.
- Start with the tab labeled "Start Here" to read your instructions.
Each section includes a sheet with data and a sheet with exercises. Read the instructions on the exercise sheet first. Try to apply the formula or tool as described. If you finish the exercise, compare your result to the provided answer sheet.
If you get stuck, use the "Formula Review" tab inside the workbook. It provides a breakdown of how the functions work in practice.
Advanced filters keep your lists clean. When you open the sheet labeled "Advanced-Filter," look at the top cells. These are the criteria ranges. You can type names, dates, or numbers into these cells to show only the rows that match your input.
The Name Manager keeps your work organized. When you define a name, you assign a word to a range of cells. This means your formulas look like =SUM(Revenue) instead of =SUM(A1:A500). Use this tool to prevent mistakes in your reports.
The Scenario Manager allows you to test variables. You can build a "Best Case," "Worst Case," and "Expected Case" for your financial model. The tool stores these values, allowing you to swap between them in two clicks.
If you cannot find the requested tools, check your "Data" or "Formulas" tabs in the Excel ribbon. Sometimes, the program hides buttons if your window size is too small. Maximize your window to reveal all buttons.
If a formula returns an error, check the spelling of your cell names. Excel requires exact spelling for names created in the Name Manager. If you still see an error, check the "INDIRECT" exercise sheet for a step-by-step example on linking cells.
- Create a backup copy of the workbook before you start the exercises so you can revert to the blank version if you need to try again.
- Use the "Help" tab within Excel if you need a visual guide on where specific buttons sit in the menu.
- Dedicate thirty minutes to each section to fully grasp how the functions perform calculations.
- Practice daily for better retention of these analytical skills.