Microsoft Excel in the business context - Advanced course

As we increase the complexity of our formulas, it becomes harder and harder to comprehend the ranges and references we are building into the formula. Using the name manager and named ranges, we can reduce the level of complexity by assigning our own names to ranges within the workbook. This allows for a more user-friendly view on the syntax and makes formulas much more understandable, both for us and everyone else.

Mac users: Unfortunately, the Excel version for Mac hides the Name manager function. However, you can still access it with the shortcut FN + CMD + F3

As we increase our vocabulary of different formulas, it is also important to bear in mind how they operating in the background. In this video, you will learn how Excel treats days and dates, and what we can do to avoid that they are displayed as serial numbers.

Let's apply the newly learned formulas to an actual example from work - mapping out the duration of activities in a project, and keeping track of the expected date when they will be completed.

Let's further increase our understanding of formulas, this time focusing on the ones related to time.

Keeping track of the hours employees spend at work is a process often carried out by specific software. Using Excel and the formulas we have been learning, I will teach you how you can create a time management system in Excel and easily account for the hours worked, also accounting for any breaks.

OFFSET is one of the most undervalued functions, mainly because people don't fully understand its potential. Step by step, I want to slowly introduce you to the syntax and different use cases, whilst slowly leading up to one of the key topics in this course: Dashboards

Now that we know what the OFFSET function can be used for, let's figure out together how we can create a dashboard, which dynamically updates itself based on the current month. Furthermore, we are going to visualize our analysis in our chart containing two axes.

Let's close the loop on our first dashboard. After this video, you will be able to link excel charts and tables with Microsoft PowerPoint. I will also teach you the various ways of copying and pasting data from one software to the other, and you will learn about the various advantages and disadvantages of each option.

Let's say you track the weekly production output in a bar chart, showing the latest 12 weeks of units produced. Week after week, you need to manually adjust the range in order to depict the right values on your chart.

Combining offset with our knowledge of named ranges, we can create a dynamically updating chart, also known as a 12-week rolling chart.

In the first part of our exercise on dashboards, I want to highlight some key functionalities and why a solid preparation of the underlying data is absolute key.

In the second part of our lesson on dashboards, I want to show you how we can extend our dashboard by adding a quarterly view. In order for this to be fully automatic, we need to build on our knowledge from the beginner's course, especially the INDEX function.

Sometimes, the focus is more on urgency rather than on the aesthetics of a nice visualization. In this final video, I want to show you how you can create a similar dashboard using Pivot Tables, whilst also saving time.

Kicking off our lesson on retrieving data from other sources, I want to introduce you to the indirect function. It's another one of the functions that are undervalued, but it makes for a great way to dynamically change the file path from which data is retrieved.

Also, you will learn how you can neatly arrange all open Excel windows.

Web queries allow for a direct access to websites, enabling you to always have the latest information on stock prices or weather data, right in your Excel files

Using the standard filter function, we quickly face the problem that all applied filters mutually affect each other. Using advanced filters, however, we can work with multiple layers that allow for specific filter methods individually.

Only available to Office365 users

With the FILTER function, Microsoft is going a major step forward with regards to dynamic filtering. Whilst it brings about a change in the syntax, this function is great for quickly applying different filters to your data sets.

Available for Office365 users only

In this short video, I want to show you how you can leverage the UNIQUE function to quickly filter out duplicates from a range and arrive at a nice and clean set of unique values, which you can then use for a drop-down list.

We could not possibly end this advanced course without at least providing some insights into the use of Visual Basic (VBA) in Excel. Don't feel intimidated by the prospect of having to learn this programming language! I will teach you how you can quickly create your own macros using the macro recorder.

Now that we have come to the end of our course, I want to reflect and share some final thoughts on the course and the future of Microsoft Excel with you.