How to make advanced data structures for reporting in Excel
4.2 (10 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.
589 students enrolled
Wishlisted Wishlist

Please confirm that you want to add How to make advanced data structures for reporting in Excel to your Wishlist.

Add to Wishlist

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.2 (10 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.
589 students enrolled
Created by Adam Holczer
Last updated 12/2016
English
Current price: $10 Original price: $60 Discount: 83% off
1 day left at this price!
30-Day Money-Back Guarantee
Includes:
  • 5.5 hours on-demand video
  • 1 Article
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I 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
View Curriculum
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 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
Students Who Viewed This Course Also Viewed
Curriculum For This Course
51 Lectures
05:33:44
+
Introduction
5 Lectures 29:33

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

Preview 08:46

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.

Preview 09:05

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.

Pros and cons of a Dashboard or advanced data structure
03:36

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.

Advantages and the data set behind the structure
04:12

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.

Preview 03:54
+
1) Create the basics for the data structure
8 Lectures 51:36

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.

Analyzing the dataset
06:30

Foundation of the data structure
05:56

'Support sheet' concept
02:38

The 'listbox' control panel
10:23

The 'arrow' control panel
09:37

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

Basic functions for automated data structures
07:34

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

Bonus lecture
05:09

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

Conclusion of Section 1.
03:49
+
2) Creating the frame of the case study structure
9 Lectures 44:36

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

Intro
01:54

Creating the first automatic cell
03:43

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

Filling up the 'y' axis of the data structure
08:49

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

Introduction to the 'Transpose' funcionality in Excel
02:33

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

Filling up the 'x' axis of the data structure
04:13

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.

Introduction to some advanced Excels formulas
10:20

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.

Making the first automatic cell of the data structure
04:09

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

Bonus lecture
06:21

Let's summarize what we have covered and what are the steps ahead of us.
Conclusion of Section 2.
02:34
+
3) Filling up the data structure for reporting purposes
8 Lectures 56:46

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

Intro
01:41

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.

Making the first interactive row
18:48

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 the frame
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 set.

.
Make cell content disappear in our data table
07:35

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

Copy and paste special formulas
07:16

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. 

Filling up the data table
06:53

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

Bonus lecture
05:22

Conclusion of Section 3.
03:32
+
4) Visualization of the data structure
11 Lectures 01:42:07

Short intro on this section of the tutorial.

Intro
07:38

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

Basic and advanced chart types
12:48

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

Chart modification - shortcuts
08:44

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

Using primary and secondary axis
04:53

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.

Visualize our data table - creating a static chart
07:10

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

First step in creating a dynamic graph
04:01

Creating the dynamic graph - part 2.
24:04

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.

Creating the dynamic graph - part 1.
16:10

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

Finalizing the dynamic graph
05:55

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.

Bonus lecture
07:22

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

Conclusion of Section 4.
03:22
+
5) Upgrading the graphical design
9 Lectures 49:04

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

Preview 01:47

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.

General introduction to finetuning
04:32

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 titles
09:22

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

Importance of borders and highlighting
10:45

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

'One pager' concept
05:51

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

Improving the outlook - check the 'View' on the ribbon
05:46

Shows some tips on how to freeze the panes.

Bonus lecture
02:55

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

Bonus lecture 2.
04:26

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


Conclusion of Section 5.
03:40
+
Data
1 Lecture 00:01
Crude production dataset
00:01
About the Instructor
Adam Holczer
4.5 Average rating
27 Reviews
2,264 Students
6 Courses
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.