The aim of this course is to give you a combination of knowledge about basic and advanced functions and functionalities provided by Microsoft Excel, while guiding you in creating a dashboard and/or advanced data structures step by step from the beginning till the very end.
When I started learning Excel, I realized that it is very
important to know Excel functions, but knowing them is not enough. You
have to build it into your daily work and start thinking the way Excel
I would like to give you a case study, where starting from the beginning we are building a dashboard together while explaining all the necessary steps to do so. My goal is to show you how to create an interactive, automated, easy to use data structure for reporting purposes by using basic and advanced functions, moreover, designing and advanced controlling elements.
With this course I will focus on a real-life example I had
to face during my work and try to shed light on what Excel is capable
of. The structure of the course touches the basics as well, so that if
you do not have a strong basis, it still gives you guidance and a firm
fund to continues and handle more complex data structures and dashboards. My aim is to show you how the functions work and then give you some tips how to use them in creating data structures.
Although this case study covers one specific problem and its solution, I would encourage you to use all the skills you can learn from it, as dozens of similar structures can be created on the basis of the curriculum you find in this course.
In case you have any question, please let me know, I will gladly help you.
I wish you all the best for the course!
Introduction: this video is about me and about the course's topic.
In this video I am going to show you the advanced data structure that we are going to create in this course in 5 steps.
Let's collect all the advantages and disadvantages (if any) of an interactive data structure and see why is it worth knowing how to create one.
This video wraps up some already covered advantages by showing the exact example, while i will show you the dataset behind the interactive dashboard as well.
In this video, I am going to introduce you the data set we are using and give some tips how to transform and modify so that we can extract all the data it contains and we need.
In this course I am going to show you how to create basic functions for advanced data structures and how to combine them.
This video lecture is about how to make some cell content disappear with some basic functions.
Let's summarize what we have covered and what are the steps ahead of us.
Short intro on what what are we going to learn in the 2nd section.
In this video I will show you how to fill up the y axis of the dashboard with a specific combination of formulas.
In this video, I am going to show you how to use the Transpose paste function and what is the advantage of it.
In this video I will show you how to fill up the x axis of the dashboard with a specific combination of formulas.
Before we jump into the heavy part, let me explain some of the functions we are going to use and which are necessary for creating such an automated data structure. The functions are: SUMIF, INDIRECT, MATCH.
In this video I will show you how to start with an advanced data structure and how to create the data table for reporting purposes. In this introductory video, we are going to make it in a static way, however, I will explain more in the video. The dynamic structure will be shown in the next section.
In this video I will shed light on some tips regarding CHAR & SUMIFS functions.
Short intro on what what are we going to learn in the 3rd section.
In this lesson we are going to finish what we have started in the previous section. I am going to show you how to make the cell content dynamic, and how to use it for creating a total automatic and controlled data table for reporting purposes.
In this lecture, i will give you a tip on a combination of formulas that can hide the cell content in an automatic way in our data frame.
In this lecture, i will give you a tip on a combination of formulas that can hide the cell content in an automatic way in our data set.
In this video we are covering the basics and advanced paste settings in Excel.
In this lecture we are going to complete our data table and will fill up the frame with the combination of functions we have learned.
In this video I'm going to show an example on combining functions i.e. ISERR and HLOOKUP.
Short intro on this section of the tutorial.
Let's revise what kind of charts Excel can provide and what is the use of each and every one of them.
In this video, we are going to check out some easy-to-use modification shortcuts for charts.
We are going to revise how to use the secondary axis of a chart and what are the benefits.
In this lesson i will show you how to visualize our advanced data structure - in a static way for now. I will explain more on that in the video.
This video is about the 'Name manager'; as a first step toward having an interactive dynamic graph.
In this video, I am going to present you what are the steps to make a dynamic chart. As the chart - so as our table - is really complex, I will show you the second part in the next video. During the video, I am going to present some useful combination of formulas that are necessary for making a dynamic graph.
In this lesson we are going to finish the dynamic graph and I will explain some advantages of using it.
This lecture is a bonus one: not connected strictly to this advanced data structure, however, really useful for showing trends in case you don't have much space on your workbook.
Summary of what we have learned in this section and what are the points that are still ahead of us.
Short intro on the last section and on the graphical design to make your dashboard marketable.
This lesson is about the benefits and necessity of the graphical design improvement in general. The video explains more on the main 3 graphical elements you need to pay attention to.
This lecture is about the necessity of identifying the elements of the table, chart etc. and giving them proper names to make it more understandable for the first look. Part 1.
This lecture gives you guidance on how to make the advanced data structures better and marketable. Part 2.
The video explains the importance of having the data in the right format, in the right order, in the right size. Part 3.
This lesson shows the final touches on the advanced data structure how to make it more interactive and marketable.
Shows some tips on how to freeze the panes.
The last bonus lecture gives you guidance on how to arrange several workbooks in case you are working in many workbooks parallel.
Summary of what we have learned in this section and how the final structure looks like.
I'm Adam, working for an oil company in sales optimization, aiming the potential sales volumes and allocation throughout the company's market. I have a strong interest in the Oil Industry, therefore I always wanted to work in the industry. after finishing Business Administration (BSc) I studied International Economics and Business in Budapest and in the Netherlands. My interest drove me back to University to study Optimization in Oil Industry in the framework of a post-graduate course. Now I am working for the biggest Oil & Gas Corporation in Central Eastern Europe as an optimization expert, supporting decision-making and analyzing the regional markets.