How to make advanced data structures for reporting in Excel

A case study for transforming data structures or dashboards into reporting layouts in 5 steps
4.5 (20 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
570 students enrolled
Instructed by Adam Holczer IT & Software / Other
$19
$195
90% off
Take This Course
  • Lectures 51
  • Length 5.5 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 4/2016 English

Course Description

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
"thinks". 

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!

What are the requirements?

  • You need a computer with possibly Microsoft Excel 2013 and you are free to go. The tutorial covers topics from the beginning till advanced level.

What am I going to get from this course?

  • learn basic and advanced excel functions step by step
  • apply funcionalities of Excel for your work to save time
  • learn about basic and advanced data structures
  • get to know how to create interactive dashboard
  • learn how to make data structures with the help of advanced functions
  • save time and be more efficient at work by applying interactive data structures for reporting purposes

What is the target audience?

  • This excel course is for newbies and for guys with advanced level in the same time. It guides you from the basics till the most advanced features of excel in creating advanced and ultimate interactive dashboard
  • For those who would like to learn more about dashboards
  • For those who would like to learn the basics about data structures
  • For those who would like to save time by applying automatization
  • for those who would like to be more efficient in reporting at work

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introduction
08:46

Introduction: this video is about me and about the course's topic.

09:05

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.

03:36

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.

04:12

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.

03:54

Let's summarize the agenda, how the course looks like, what are we going to cover and learn, and wrap up the challenges ahead of us.

Section 2: 1) Create the basics for the data structure
06:30

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.

Foundation of the data structure
05:56
'Support sheet' concept
02:38
The 'listbox' control panel
10:23
The 'arrow' control panel
09:37
07:34

In this course I am going to show you how to create basic functions for advanced data structures and how to combine them.

05:09

This video lecture is about how to make some cell content disappear with some basic functions.

03:49

Let's summarize what we have covered and what are the steps ahead of us.

Section 3: 2) Creating the frame of the case study structure
01:54

Short intro on what what are we going to learn in the 2nd section.

Creating the first automatic cell
03:43
08:49

In this video I will show you how to fill up the y axis of the dashboard with a specific combination of formulas.

02:33

In this video, I am going to show you how to use the Transpose paste function and what is the advantage of it.

04:13

In this video I will show you how to fill up the x axis of the dashboard with a specific combination of formulas.

10:20

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.

04:09

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.

06:21

In this video I will shed light on some tips regarding CHAR & SUMIFS functions.

02:34
Let's summarize what we have covered and what are the steps ahead of us.
Section 4: 3) Filling up the data structure for reporting purposes
01:41

Short intro on what what are we going to learn in the 3rd section.

18:48

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.

05:39

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.

07:35

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.

.
07:16

In this video we are covering the basics and advanced paste settings in Excel.

06:53

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. 

05:22

In this video I'm going to show an example on combining functions i.e. ISERR and HLOOKUP.

Conclusion of Section 3.
03:32
Section 5: 4) Visualization of the data structure
07:38

Short intro on this section of the tutorial.

12:48

Let's revise what kind of charts Excel can provide and what is the use of each and every one of them.

08:44

In this video, we are going to check out some easy-to-use modification shortcuts for charts.

04:53

We are going to revise how to use the secondary axis of a chart and what are the benefits.

07:10

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.

04:01

This video is about the 'Name manager'; as a first step toward having an interactive dynamic graph.

Creating the dynamic graph - part 2.
24:04
16:10

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.

05:55

In this lesson we are going to finish the dynamic graph and I will explain some advantages of using it.

07:22

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.

03:22

Summary of what we have learned in this section and what are the points that are still ahead of us.

Section 6: 5) Upgrading the graphical design
01:47

Short intro on the last section and on the graphical design to make your dashboard marketable.

04:32

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.

09:22

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.

10:45

This lecture gives you guidance on how to make the advanced data structures better and marketable. Part 2.

05:51

The video explains the importance of having the data in the right format, in the right order, in the right size. Part 3.

05:46

This lesson shows the final touches on the advanced data structure how to make it more interactive and marketable.

02:55

Shows some tips on how to freeze the panes.

04:26

The last bonus lecture gives you guidance on how to arrange several workbooks in case you are working in many workbooks parallel. 

03:40

Summary of what we have learned in this section and how the final structure looks like.


Section 7: Data
Crude production dataset
Article

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Adam Holczer, Senior Optimization Analyst

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.

Ready to start learning?
Take This Course