Excel Data Analysis: Produce Great Reports, Basic Statistics

Learn to produce Pivot Tables, Logical Formulas and Special Summaries that allow you to analyze data quickly and easily.
4.3 (3 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.
698 students enrolled
$20
Take This Course
  • Lectures 17
  • Contents Video: 3.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 12/2013 English

Course Description

I am confident that you will learn valuable Excel skills in these videos. I have been teaching business people, young and seasoned, how to use Excel for more than a quarter century and am pleased that you are interested in learning from me with this Udemy course.

This course was initially designed for the “fresh-out-of-college” graduate, but is a complete course for anyone who wants to learn about the “BEST” way to set up a data list to analyze.

In this course I will show you how to take business data and turn it into comprehensive reports, the kind of reports that every business can use to make informed decisions.

You will learn what a “Well-Formed” list looks like and why it is so important to have a well-formed list. You will also learn to take your well-formed lists and create informative Pivot Tables and Charts.

Big data is such a BIG thing now. Business is finally realizing how important it is to capture and analyze big data. And even more important, business leaders are realizing that the big data is only as good as the analysis. I will show you how to prepare any size data set making it easy for you to analyze and produce meaningful reports.

What are the requirements?

  • Some Excel knowledge is helpful but not required: basic formula and formatting experience recommended

What am I going to get from this course?

  • By taking this course you will gain the needed skills to take raw data and turn it into meaningful reports.
  • This Microsoft Excel course is designed to help you learn the skills you need to produce reports from lists of data. You will learn how to prepare data to make it suitable for analysis and then how to use the built in tools of Excel to analyze the data, producing reports that help business leaders make better, informed decisions.

What is the target audience?

  • This course is built with the college graduate in mind, but anyone who needs to learn how to analyze Excel data will benefit from this course.

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: About this course
06:55

Learning to use Excel’s many data analysis tools can be very helpful in almost any business application. When large lists of data store your business history both recent and long ago, it can seem like a huge task to sift through all this information to make informed business decisions. The analysis tools of Excel not only make this task easier, but offer many features that make your data a powerful resource for your business. Tools such as Pivot Tables, Goal Seek, Solver, PowerPivot, statical functions, Subtotals, Scenario Manager are more easily utilized when you maintain well-formed lists. This course is designed to help you build well-formed lists then apply these data analysis tools effectively.

Visit www.tomvorves.com for questions and links to other classes on Udemy

Section 2: Data preparation techniques
08:31

This lecture will show what a well-formed list is and teach you about the two important characteristics of a well-formed list.

By ensuring your list complies to basic standards you will find your list is easier to manage. Activities such as sorting, filtering, grouping and Pivot Tables are less time consuming and more accurate.

Visit www.tomvorves.com for questions and links to other classes on Udemy

11:18

In this lesson you will learn to identify intrinsic data groups as well as derive hidden groups using data mining formulas.

Business decisions are often based on group analysis: how many, average, highest, lowest. There are also range and date comparison made to learn from past and plan for the future, as well as make sound decisions for the here and now.

Visit www.tomvorves.com for questions and links to other classes on Udemy

07:46

In this lesson you will learn to identify intrinsic data groups as well as derive hidden groups using data mining formulas.

Business decisions are often based on group analysis: how many, average, highest, lowest. There are also range and date comparison made to learn from past and plan for the future, as well as make sound decisions for the here and now.

Visit www.tomvorves.com for questions and links to other classes on Udemy

Section 3: Pivot Tables
26:16

This demonstration will show how you can quickly create and adjust a Pivot Table. You will learn to use the four main ‘pivot’ areas and how to produce basic reports from the Pivot Table.

A Pivot Table is a tool for quick data analysis based on groups. Once a well-formed list has been created and groups identified, a Pivot Table can display aggregates quickly. The resulting data set can then be used as a basis for charts and reports. This demo will illustrate the analysis power of a well-formed list and a Pivot Table based on that list.

Visit www.tomvorves.com for questions and links to other classes on Udemy

08:41

In this lecture I will show you the basics of creating a chart using a Pivot Table data set.

Charts are used to display data in a graphical way. The purpose of a chart is to deliver a message to your audience. The data sometimes can speak for itself, but there are times when a carefully selected and well-formatted chart can enhance your data message.

Visit www.tomvorves.com for questions and links to other classes on Udemy

Section 4: Decision making formulas
19:30

This video will demonstrate the use of functions designed to produce aggregates similar to what a Pivot Table does. We will start with the basic SUM function, then introduce you to SUMIF and SUMIFS. These functions can conditionally sum data lists.

Visit www.tomvorves.com for questions and links to other classes on Udemy

18:48

In this lesson we will learn to use database functions. DSUM, DAVERAGE, DCOUNTA, DMIN, DMAX.

The purpose of the database functions are very similar to the purpose of a pivot table: to produce aggregates.

Visit www.tomvorves.com for questions and links to other classes on Udemy

Section 5: What-If analysis tools
10:09

This video demonstrates the use of Excel's Goal Seek tool. It is designed to help us play "what-if" with the precedent values that feed a dependent formula.

There are times when we know what the result of a formula needs to be, but we are getting something other than what we want. By using the Goal Seek tool, we can tell Excel what our formula value needs to be, then point to the precedent cell and instruct Excel to find the correct value.

Visit www.tomvorves.com for questions and links to other classes on Udemy

04:49

This video will introduce you to the Scenario Manager which helps us store and retrieve multiple values for a range of cells.

By using the Scenario Manager we can avoid the common practice of duplicating sheets or workbooks to store different scenarios of our data. Allowing us to create as many scenarios and we need, the Scenario Manager is the perfect tool for on-going "what-if" analysis.

Visit www.tomvorves.com for questions and links to other classes on Udemy

Section 6: Visual reporting
07:51

In this demonstration you will learn to filter Pivot Tables with data Slicers which are interactive filters designed to make data presentation and analysis easier and faster.

Visit www.tomvorves.com for questions and links to other classes on Udemy

16:05

This demonstration will show you how to use Excel's Advanced Filter tools. Although most of us know how to Excel's Auto-Filter features, few have discovered Advanced Filters.

Advanced filtering allows us to see the criteria as well as use OR/AND operators in our list filtering. One added benefit of Advanced filtering: we can filter or data without altering our original data set.

Visit www.tomvorves.com for questions and links to other classes on Udemy

19:34

In this demonstration you will learn to use Conditional Formatting based on values in your data set.

Excel has many pre-built conditional formats available, which can be used as they are, or we can modify them to meet our specific needs. Conditional formats are dynamic, adjusting as the data in the sheet is modified, and graphically appealing making our large data sets more readable.

Visit www.tomvorves.com for questions and links to other classes on Udemy

Section 7: Bonus Section - Power tools overview
07:39

This short video will introduce you to SharePoint and how Excel lists can be used to create and populate collaborative lists stored in Microsoft SharePoint.

SharePoint is a collaborative work environment accessed primarily through a web browser allow quick sharing and collective content management by teams or departments. Where you have people and work to do, SharePoint is a great solution for collecting, storing and sharing business data, much of which can come from Microsoft Excel.

This is a brief demonstration of Excel and SharePoint integration. If you would like to learn more about this integration and SharePoint, look for my Udemy courses that are coming in the very near future.

Visit www.tomvorves.com for questions and links to other classes on Udemy

Watch my SharePoint introduction videos on Udemy: https://www.udemy.com/sharepoint-2013-introduction/

11:19

In this video I will introduce you to Microsoft PowerPivot 2013. PowerPivot gives us the ability to create advanced Pivot Tables based of multiple data sets, create relationships between these data set and pull data from many different data sources. PowerPivot is a data management tool that runs along side of Microsoft Excel.

This is a brief demonstration of PowerPivot. If you would like to learn more about this great data analysis tool, look for my Udemy courses that are coming in the very near future.

Visit www.tomvorves.com for questions and links to other classes on Udemy

06:14

Using PowerPivot I will demonstrate the use of a simple Key Performance Indicator (KPI).

This is a brief demonstration of KPIs. If you would like to learn more about this great data analysis tool, look for my Udemy courses that are coming in the very near future.

Visit www.tomvorves.com for questions and links to other classes on Udemy

13:52

This bonus video will help you learn to use a very popular and useful function called VLookup ("V" stands for vertical).

When you have to lists that need to be combined into one, VLookup can help. We don't always have the luxury of having two lists that can be simply copied and pasted together. There are mismatched records, discrepancies, or simply missing data from one or both lists. VLookup will take a common field found in both lists and find matches in both lists and return a value from the matching record.

Visit www.tomvorves.com for questions and links to other classes on Udemy

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Tom Vorves, Collaboration Evangelist

With more than 25 years of teaching experience, I have the knowledge and ability to break down the complexities of technology, making it simple to understand and learn.

My professional yet casual teaching style make for a successful and enjoyable learning experience.

Is it a goal to update your Microsoft Office skills? Are you interested in learning the latest version of software? Perhaps you need to implement SharePoint company wide. I am able to help you plan and deliver training that will meet your needs.

Small businesses, one-on-one, large corporate events, classroom style training, webinars, video training, whatever your situation, we will work together to create the best training experience specifically for you.

Ready to start learning?
Take This Course