The Complete Microsoft Excel Course 2024

The course is comprehensive and accessible to users with varying levels of experience with Excel. Four different tracks are provided: Beginner, Intermediate, Genius, and All Lessons and Exercises. The Beginner track assumes little to no experience with Excel, while the Intermediate and Genius tracks cater to users with different levels of expertise. The All Lessons and Exercises track covers all lectures and exercises in the course, making it suitable for users who want a complete learning experience.

The course is delivered through a comprehensive Excel document called the Complete Excel Dashboard, which is available for download in the first lecture.

The course is designed to be engaging and fun, with the use of props and many exercises to facilitate learning. IN a comprehensive optional section, the course also provides users with a solid introduction to programming concepts using VBA/macros.

The course is structured into six sections:  course overview, quick reference and resources, foundations, defined names and formulas, charts and pivot tables, programming in Excel, and other topics. Each section is tailored to different skill levels, allowing students to choose the track that best suits their needs.

Throughout the course, the instructor teaches basic and advanced Excel functions, as well as how to create charts, pivot tables, and dashboards.

As the course progresses, students can update their progress by marking checkboxes in the provided spreadsheet. The instructor has also created a custom ribbon for easy navigation and access to various course materials.

This lecture focuses on settings and security of the spreadsheet, which works on both Mac and Windows versions of Excel. The lecture teaches students on how to optionally enable macros, add the developer tab and how to customize Excel.

In this lecture, students learn how to access help videos created for the course for every single icon in Excel. The lecture also discusses spreadsheet design, color palettes and Excel spreadsheet best practices.

This lecture explains how to download Excel file templates and case study Excel files discussed in the course. These files include an investment research spreadsheet for analyzing publicly traded companies, options analysis spreadsheets, a venture capital boot camp business plan Excel file, a complete personal finance dashboard, a complete financial analyst portfolio management dashboard spreadsheet, and a cryptocurrency spreadsheet.

In addition to asking questions in the course, this lecture explains how to ask Chris Haroun questions live every week on his weekly Office Hours webcast.

Section two of the course covers the basics of creating, editing, and more in Excel. It is designed for beginners, and those with some experience can skip or select specific topics to learn. The section covers two main parts: Excel Foundations, Part one, which includes text and number formatting, conditional formatting, and introduction to formulas; and the second part which covers images, icons, charts, buttons, hyperlinks, and creating a dashboard. 

In Exercise 2.1 of the course, the instructor will explain the concept of the "rule of 72," which is used to calculate how many years it takes for money to double at different interest rates. The exercise will only use icons from the home ribbon. Optional video tutorials are available for each icon.

In this lecture, the speaker discusses formatting text in Microsoft Excel while learning about finance. He mentions the Rule of 72, which calculates how many years it takes for money to double based on the interest rate. He also demonstrate how to use quick keys for formatting, such as using Ctrl + B for bold and Ctrl + I for italics (investment bankers often use quick keys as it saves time). Also discussed is how to format cells using the right-click menu or toolbar. Students are encouraged to use quick keys for efficiency reasons.

Exercise 2.1 discusses making cells bold or italic and adding the percentage symbol. Three different methodologies should be used, including quick keys, using the icon at the top, and right-clicking and selecting format cells.

This lesson explains the answers to Exercise 2.1 formatting changes by also using more advanced formatting features, including right-clicking and selecting "Format Cells."

The instructor demonstrates cell alignment techniques in Excel. He explains simple alignment options like centering, left justifying, or right justifying text in cells. He then explains how to align text to the top, middle, or bottom of cells. The instructor then introduces the "merge and center" feature, which allows merging and centering text in cells, as well as how to wrap text in merged cells to avoid hidden text issues.

This exercise discusses aligning text by using several methodologies, including using icons, right-clicking and quick keys.

The answer lecture explains aligning text by using several methodologies, including using icons, right-clicking and quick keys.

The instructor explains how to add borders and shading in Excel. He highlights cells and selects border options, including top, outside, and custom colors. He demonstrates using the pencil tool to draw borders and remove them. He also shows how to shade cells using the paint bucket tool and how to use custom colors by accessing the Help videos. He concludes by explaining the option to select multi-color variants and access hex codes in the Help menu.

In this exercise the instructor explains how to use custom colors for both the borders and shading, and encourages students to choose any color palette they want from the help menu. He specifies that four colors should be used in this exercise, with one color for the border, and three additional colors for different sections of the worksheet. He also mentions the option to choose a custom font for the text. 

This lecture discusses the answers for creating borders and shading in a document. The instructor starts by selecting a color palette and a font. Then, he proceeds to complete the exercise by applying the selected colors and font to the document. He copies the colors and pastes them using the paint bucket tool. He also adjusts the border line color and style, making it thicker and getting rid of the "dancing ants." He completes the exercise by inserting the remaining colors from the palette and removing the grid lines.

The instructor introduces the concept of writing formulas in Excel. He explains that pressing the equals sign indicates that a formula is being written. For example, typing "=sum" followed by opening brackets prompts Excel to expect a calculation. The instructor demonstrates adding two cells together and finding the maximum number using the "max" function. He also shows how to multiply two numbers and divide a number by another number using the formula bar.

In this exercise, the instructor asks the user to create formulas in the highlighted cells to match the values on the right side of the screen. He specifically instructs not to use the formulas ribbon, as it will be covered in detail in Section 3.2. Instead, he encourages using the formulas discussed in the previous video. 

Instructor begins the lecture by discussing the rule of 72, which is a quick way to estimate how many years it takes to double an investment. He discusses how to copy and paste the formula for other calculations. He also uses functions like Max, Min, and Average to find the highest, lowest, and average values in a set of numbers.

The instructor introduces conditional formatting, a fun and useful feature in Excel. He demonstrates how to highlight cells that are greater than a certain value, add data bars, and customize icon sets like traffic lights and stars. He shows how to clear rules from selected cells to remove the formatting. He also explains how to create custom rules based on specific conditions, such as displaying full, half, or blank stars depending on the value in the cell.

This exercise asks students to use conditional formatting as an easy way to change cell formatting.

This answer video explains how to use conditional formatting as an easy way to change cell formatting.

Exercise 2.2 contains 11 lessons and 11 exercises to learn more about Excel foundations/features. Here in Exercise 2.2, we discuss basic features from the home ribbon, insert menu, picture format menu, and page layout menu. Please note, there are no macros or coding involved in this exercise (macros are discussed in Section 5).

The instructor demonstrates how to add and edit images in a spreadsheet and utilize the time-saving Paintbrush feature. To insert an image, simply select the insert menu, choose shapes or illustrations, and pick a desired shape. Once an image is inserted, the Shape Format menu appears, allowing for color and effect changes. To add text to the image, click on it and start typing. Formatting options can be accessed via the Home menu.

3D images can also be inserted by going to Insert > Illustrations > 3D Models. Images from the internet or personal photos can be added by selecting Insert > Pictures. Another useful feature is the Picture Format ribbon, where cropping, correcting, and background removal options are available. The Paintbrush feature allows quick formatting of multiple cells. Just select the format painter, then click and drag over the desired cells. Double-click the paintbrush to continuously apply the same format.

In this exercise, the instructor guides you through three parts of inserting and editing images. In part one, you'll add four buttons to the spreadsheet to make the left side look like the right side. Part two involves using the paintbrush feature to quickly format a dashboard. To do this, select the paintbrush icon from the home menu and apply it to the desired cells. Finally, in part three, you'll type ten items you want to save money on and the corresponding monthly savings. You can overwrite the instructor's sample data if you wish.

This lecture discusses the answers to creating an organized and visually appealing presentation using various tools and techniques. The instructor demonstrates how to add four buttons with customized colors, shadows, and text, ensuring they are properly aligned and formatted. He provides step-by-step instructions to insert images and shapes, adjust their size, and apply specific colors using the color palette. The lecture also covers methods to copy, paste, and position elements on the slide, as well as utilizing the paintbrush feature to apply formatting across multiple elements.

The instructor demonstrates the use of Excel's vast collection of royalty-free icons that can be easily added to spreadsheets.

In this exercise, the instructor demonstrates a two-part process using Excel. First, students are instructed to insert four icons (settings, money, government, and charts) on top of the buttons they've created. Next, they are guided to add the same icons in green to steps two, three, and four, ensuring that the color is changed to green as well. This practical activity provides hands-on experience with inserting and customizing icons in Excel for a more engaging and visually appealing presentation.

In this answers video, the instructor walks through the process of inserting and customizing icons in Excel.

In this tutorial, the instructor demonstrates how to order, align, and work with images, shapes, and icons in Excel. He inserts three different colored circles and shows how to manipulate their ordering using the 'send to back' and 'send backwards' options. He explains that there are multiple ways to reorder objects in Excel, especially when dealing with many images.

The instructor then introduces a lesser-known feature called the 'selection pane,' which can be accessed through the 'home' tab. This pane displays all the objects in the sheet and allows users to easily drag and reorder them, as well as toggle their visibility.

To illustrate the power of Excel, the instructor shows how to create a simple macro that hides an object and plays a beep sound when a button is clicked. He demonstrates how to record the macro and add it to a button, as well as modify the generated code in Visual Basic.

Lastly, the instructor teaches how to align images by selecting multiple objects and using the 'align' options, such as 'align to top' and 'align left.' He also shows how to evenly distribute multiple images horizontally using the 'distribute horizontally' option.

Throughout the tutorial, the instructor encourages viewers to experiment with Excel features and make mistakes to learn effectively. He also invites beginners and intermediate users to join the all-track programming section to further explore the capabilities of Excel.

In this exercise, the instructor guides users through exercise 2.2.3, focusing on ordering images and layering objects in Excel.

In this answers video, the instructor demonstrates creating a shape, tracing it for accurate dimensions, and adjusting its color. He then moves the shape, removes its border, and sends it to the back of other objects. The exercise concludes with minor formatting adjustments, completing the task.

The instructor discusses copying and pasting special in Excel, particularly when dealing with formulas. Instead of pasting directly, he uses paste special to maintain the formatting while transferring the formula. The instructor then experiments with changing the font and copying the format to the cell with the formula. By using paste special again, he transfers the font without disrupting the formula. The lesson showcases the versatility and usefulness of the paste special feature in Excel, allowing for precise control over content and formatting.

In this exercise, the instructor demonstrates creating a formula to calculate annualized savings and guides the user to copy and paste it into designated cells for accurate results.

In this answers video, the instructor demonstrates calculating values and efficiently copying & pasting results.

In this instructional video, the instructor emphasizes the importance of using the "&" sign when writing formulas in Excel. He guides viewers through joining text and values in a formula by using quotes and the "&" sign. He highlights common mistakes and offers solutions, ensuring that learners understand the material.

In this exercise, the instructor demonstrates inserting the "&" symbol in a formula, combining text and values, and using proper referencing techniques.

In this answers video, the instructor demonstrates step-by-step calculations using the "&" symbol, while guiding viewers through formulas and techniques, ensuring an easily understandable and accurate outcome.

In this tutorial, the instructor walks students through a comprehensive lesson on utilizing Excel formulas to calculate future values and manage dollar amounts. He starts by demonstrating how to create a formula that combines text and values from different cells, using the CONCATENATE function, to display a combined result such as "Chris made $50."

He then explains the concept of using "sticky" dollar signs ($) to lock cell references, both vertically and horizontally, ensuring that the correct cells are referenced as the formula is copied across different rows and columns.

The instructor also introduces the future value formula, a crucial concept for understanding the value of money in the context of investments and interest rates. He demonstrates how to calculate the future value of an investment with a 10% annual interest rate over one and two years, using the FV (Future Value) function in Excel.

Throughout the lesson, the instructor highlights the importance of understanding how different parameters in the FV function impact the outcome. He showcases Excel's helpful prompts and explanations of optional parameters enclosed in square brackets, ensuring a better understanding of the functions used.

This tutorial provides viewers with a strong foundation in Excel formulas, focusing on handling dollar amounts, understanding the use of "sticky" references, and calculating the future value of investments.

In this exercise, the instructor guides students through creating and applying the future value formula in Excel. He asks them to calculate the future value of annual savings of $720 over 50 years, considering a 9% interest rate. By completing this exercise, students gain a deeper understanding of financial concepts and Excel functions, preparing them for more advanced programming tasks in later sections.

In this answers video, the instructor  walks through the process of calculating the future value of savings using Excel's future value formula.

In lecture 2.2.7, the instructor demonstrates how to use Excel formulas to sum values and format the output. He first uses the sum function to calculate the total savings and then applies formatting to display the result as a currency. However, when trying to reference the sum in another cell, the formatting is lost. To fix this issue, the instructor introduces the text formula to display the sum as currency in the new cell. He then combines both the sum and text formulas into a single cell, showcasing how to achieve the desired result with just one formula.

In this exercise, the instructor challenges students to use formulas to create a sentence referencing various cell values. Students must combine text, numbers, and cell references using the "&" sign to build the sentence, ultimately demonstrating their understanding of Excel formulas and formatting techniques.

In this exercise, the instructor demonstrates the answer to exercise 2.2.7 by creating a complex sentence using Excel formulas. He combines text and cell values, using the ampersand sign and the SUM and TEXT functions, to accurately reference various items and their associated costs. The resulting sentence showcases the students' mastery of combining Excel functions and formatting.

The instructor demonstrates creating charts with ease. For customization, right-clicking is key. To edit text, right-click and select "Edit Text" to change the title. To change the color, right-click, choose "Format Data Series," and pick a color. To insert a chart with non-adjacent data, highlight the data, press "Control," then insert a recommended chart. The process is user-friendly and adaptable to various data layouts, ensuring that chart creation remains simple and accessible.

In this exercise, the instructor guides students on inserting a chart, removing clutter, positioning, and ensuring proper formatting.

In this answers video, the instructor demonstrates creating a chart, selecting data, customizing colors, formatting, adjusting axis decimals, and finalizing the chart.

In this tutorial, the instructor explains how to insert a pie chart in Excel. The instructor demonstrates changing the chart type and experimenting with design options for a more visually appealing result.

In this exercise, the instructor demonstrates adding a pie chart to Excel, encouraging practice and experimentation.

In this answers video, the instructor creates a pie chart, adjusts the title, moves the legend, resizes the chart, and customizes colors and borders.

In this tutorial, the instructor teaches how to insert button hyperlinks in Excel. The instructor demonstrates linking to a specific location in the document and providing a cell reference. He also adds screen tips to guide users on where the hyperlink will take them. 

In this exercise, the instructor teaches adding hyperlinks to grouped images, guiding users through steps with screen tips.

In this answers video, the instructor demonstrates creating defined names and linking them to grouped images, making navigation more efficient and user-friendly.

In this tutorial, the instructor demonstrates how to print in Excel by highlighting desired cells, adjusting page setup, setting landscape mode, and adding custom headers and footers. He also covers inserting the date, time, and location of the file, as well as page numbers for longer documents.

In this exercise, the instructor guides students to print a dashboard, adding headers, footers, and ensuring it's on one page in portrait mode.

In this answers video, the instructor demonstrates printing a dashboard, setting the correct orientation, headers, footers, and centering the content.

In this section, the instructor introduces defined names to simplify formulas, making programming easier and more efficient when using Excel and Visual Basic. Please note, programming is covered in Section 5 of the course. This current lecture is Section 3.1.

In this tutorial, the instructor demonstrates how to use defined names in Excel to make formulas easier to understand and manage. He builds a basic income statement as an example, showing how expenses and net profit can be calculated using defined names for sales, cost of goods sold, and G&A expenses.

In this exercise, the instructor demonstrates using defined names to simplify formulas in an income statement, guiding students through creating sales growth and defining names for expenses.

In this answers video, the instructor demonstrates the process of growing sales, defining names, and calculating various expenses using formulas in an income statement, ultimately showing how to calculate net profit and net profit margin.

In this lecture, the instructor discuses how to create, change, and delete defined names in Excel. He demonstrates creating a defined name for three years of sales data, then shows how to delete and rename it using the Name Manager in the formula tab. To avoid confusion when dividing by a named range containing multiple cells, he uses the '@' symbol before the named range. The instructor also briefly mentions using defined names in more complex scenarios like pivot tables, dashboards, and programming.

In this exercise, the instructor guides students on how to create, modify, and delete defined names for R&D and sales data, using the Name Manager and Create from Selection features, while addressing errors throughout the process.

In this answers video, the instructor demonstrates creating, modifying, and deleting defined names for R&D and sales data using the Name Manager and Create from Selection Excel features, while also fixing errors, showcasing a time-saving approach for handling data in spreadsheets.

In this lecture, the instructor discusses using strings in formulas to refer to defined names and introduces arrays with defined names.

In this exercise, the instructor guides students on creating defined names, using strings in formulas, and building arrays to organize and manage stock data using defined names.

In this answers video, the instructor walks through the solution for Exercise 3.13, demonstrating how to create defined names, use strings in formulas, and build arrays for stock data. He also explains how to use VLOOKUP to find values in an array.

In this section, the instructor discusses creating beginner, intermediate, and genius-level formulas, emphasizing how defined names make formula creation easier and more enjoyable. He previews the topics covered, including basic formula creation, advanced techniques, and case studies.