Excel for Data Analysis: Basic to Expert Level

Full Course Content Last Update 12/2019

Learn data analysis through a practical course with Microsoft Excel® using S&P 500® Index ETF prices historical data. It explores main concepts from basic to expert level which can help you achieve better grades, develop your academic career, apply your knowledge at work or do your business data analysis research. All of this while exploring the wisdom of best academics and practitioners in the field.

Become a Data Analysis Expert in this Practical Course with Excel

• Use main Excel file or workbook types and protect them before sharing.

• Store data in workbooks consisting of one of more worksheets.

• Navigate worksheets through their cells which can be grouped in ranges.

• Move quickly through worksheet using keyboard shortcuts.

• Perform data calculations using basic arithmetic formulas, main categories built-in functions and arrays.

• Correct formula errors and perform user input data validation.

• Organize data interactively using tables and customizable pivot tables while sorting, filtering and performing calculations on their contents.

• Visualize data using conditional formatting, main chart types and single cell sparklines.

• Implement data scenarios summary forecast using what-if analysis and find value for achieving certain calculation result using goal seek changing cell iteration.

• Analyze data and estimate optimal parameter using data analysis tools package and solver Microsoft Excel® Add-ins.

• Identify data trends using moving average and exponential smoothing tools.

• Summarize data descriptive statistics and print its frequency histogram.

• Estimate correlation between variables and analyze regression summary output between explained and explanatory variables.

• Generate random numbers based on specific probability distribution.

• Estimate optimal parameter using constrained minimization.

Become a Data Analysis Expert and Put Your Knowledge in Practice

Learning data analysis is indispensable for business data analysis applications in areas such as consumer analytics, finance, banking, health care, e-commerce or social media. It is also essential for academic careers in data analysis, applied statistics, economics, econometrics and quantitative finance. And it’s necessary for business data analysis research.

But as learning curve can become steep as complexity grows, this course helps by leading you step by step using S&P 500® Index ETF prices historical data for data analysis to achieve greater effectiveness.

Content and Overview

This practical course contains 42 lectures and 6.5 hours of content. It’s designed for all data analysis knowledge levels and a basic understanding of Microsoft Excel® is useful but not required.

At first, you’ll define data analysis operations using Microsoft Excel®. Next, you’ll define data analysis tools package and solver Microsoft Excel® Add-ins.

Then, you’ll define Excel file, file options, workbook and main workbook types. Next, you’ll define ribbon. For ribbon, you’ll define file tab, home tab, insert tab, page layout tab, formulas tab, data tab, review tab and view tab. After that, you’ll define worksheet. Later, you’ll define cells and cells references. For cells references, you’ll define relative reference, absolute reference, mixed reference and 3D reference. Then, you’ll define ranges and ranges operators. For ranges operators, you’ll define union range operator, intersect range operator and mathematical union. Next, you’ll define keyboard shortcuts.

Next, you’ll define formulas, status bar and functions. For functions, you’ll define date & time functions category, financial functions category, logical functions category, lookup & reference functions category, mathematic & trigonometric functions category, statistics functions category and text functions category. Then, you’ll define array formulas. After that, you’ll define formula errors and formula auditing options. Later, you’ll define range of cells defined name and user input data validation.

After that, you’ll define data analysis. Then, you’ll define sorting and filtering table data. Next, you’ll define conditional formatting. For conditional formatting, you’ll define highlight cell rules, top/bottom rules, data bars rules, color scales rules and icon set rules. Later, you’ll define column chart, line chart, pie chart, scatter chart, line sparkline, column sparkline and win/loss sparkline. After that, you’ll define tables and customizable pivot tables. For customizable pivot tables, you’ll define field selection feature, sorting and filtering feature, value field settings feature and chart feature. Then, you’ll define what-if data scenario summary and goal seek changing cell iteration. Next, you’ll define data analysis tools package Microsoft Excel® Add-in. For data analysis tools package, you’ll define moving average analysis tool, exponential smoothing analysis tool, descriptive statistics analysis tool, histogram analysis tool, correlation analysis tool, regression analysis tool and random number generation analysis tool. Finally, you’ll define solver Microsoft Excel® Add-in. For solver, you’ll define optimal parameter estimation through constrained minimization.