How to make advanced data structures for reporting in Excel

Name: How to make advanced data structures for reporting in Excel
Rating: 4.4 (19 reviews)

A case study for transforming data structures or dashboards into reporting layouts in 5 steps

Created byAdam Holczer

Last updated 12/2016

English

What you'll learn

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

Course content

7 sections • 51 lectures • 5h 33m total length

Introduction - about me and about the course8:46
Introduction: this video is about me and about the course's topic.
Advance data structure example9: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.
Pros and cons of a Dashboard or advanced data structure3: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.
Advantages and the data set behind the structure4: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.
Structure of the course3: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.

Analyzing the dataset6: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 structure5:56
'Support sheet' concept2:38
The 'listbox' control panel10:23
The 'arrow' control panel9:37
Basic functions for automated data structures7:34
In this course I am going to show you how to create basic functions for advanced data structures and how to combine them.
Bonus lecture5:09
This video lecture is about how to make some cell content disappear with some basic functions.
Conclusion of Section 1.3:49
Let's summarize what we have covered and what are the steps ahead of us.

Intro1:54
Short intro on what what are we going to learn in the 2nd section.
Creating the first automatic cell3:43
Filling up the 'y' axis of the data structure8:49
In this video I will show you how to fill up the y axis of the dashboard with a specific combination of formulas.
Introduction to the 'Transpose' funcionality in Excel2:33
In this video, I am going to show you how to use the Transpose paste function and what is the advantage of it.
Filling up the 'x' axis of the data structure4:13
In this video I will show you how to fill up the x axis of the dashboard with a specific combination of formulas.
Introduction to some advanced Excels formulas10: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.
Making the first automatic cell of the data structure4: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.
Bonus lecture6:21
In this video I will shed light on some tips regarding CHAR & SUMIFS functions.
Conclusion of Section 2.2:34
Let's summarize what we have covered and what are the steps ahead of us.

Intro1:41
Short intro on what what are we going to learn in the 3rd section.
Making the first interactive row18: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.
Make cell content disappear in the frame5: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.
Make cell content disappear in our data table7: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.
.
Copy and paste special formulas7:16
In this video we are covering the basics and advanced paste settings in Excel.
Filling up the data table6: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.
Bonus lecture5:22
In this video I'm going to show an example on combining functions i.e. ISERR and HLOOKUP.
Conclusion of Section 3.3:32

Intro7:38
Short intro on this section of the tutorial.
Basic and advanced chart types12:48
Let's revise what kind of charts Excel can provide and what is the use of each and every one of them.
Chart modification - shortcuts8:44
In this video, we are going to check out some easy-to-use modification shortcuts for charts.
Using primary and secondary axis4:53
We are going to revise how to use the secondary axis of a chart and what are the benefits.
Visualize our data table - creating a static chart7: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.
First step in creating a dynamic graph4: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
Creating the dynamic graph - part 1.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.
Finalizing the dynamic graph5:55
In this lesson we are going to finish the dynamic graph and I will explain some advantages of using it.
Bonus lecture7: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.
Conclusion of Section 4.3:22
Summary of what we have learned in this section and what are the points that are still ahead of us.

Intro1:47
Short intro on the last section and on the graphical design to make your dashboard marketable.
General introduction to finetuning4: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.
Importance of titles9: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.
Importance of borders and highlighting10:45
This lecture gives you guidance on how to make the advanced data structures better and marketable. Part 2.
'One pager' concept5:51
The video explains the importance of having the data in the right format, in the right order, in the right size. Part 3.
Improving the outlook - check the 'View' on the ribbon5:46
This lesson shows the final touches on the advanced data structure how to make it more interactive and marketable.
Bonus lecture2:55
Shows some tips on how to freeze the panes.
Bonus lecture 2.4:26
The last bonus lecture gives you guidance on how to arrange several workbooks in case you are working in many workbooks parallel.
Conclusion of Section 5.3:40
Summary of what we have learned in this section and how the final structure looks like.

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.

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!

Who this course is for:

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

How to make advanced data structures for reporting in Excel

What you'll learn

Explore related topics

Course content

Introduction5 lectures • 30min

1) Create the basics for the data structure8 lectures • 52min

2) Creating the frame of the case study structure9 lectures • 45min

3) Filling up the data structure for reporting purposes8 lectures • 57min

4) Visualization of the data structure11 lectures • 1hr 42min

5) Upgrading the graphical design9 lectures • 49min

Data1 lecture • 1min

Requirements

Description

Who this course is for: