Microsoft Excel Data Analytics Concepts: Beginner to Pro

From beginner to expert in Microsoft Excel

This course will provide step-by-step tutorials to help you build and acquire basic Microsoft Excel skills. It doesn’t matter if you have experience working with MS Excel or not; this course is designed to walk you through all the basic and most advanced functions of Excel.

This Microsoft Excel course involves using four (4) different business scenarios/cases to help you grasp the effectiveness of Excel across different settings.

• In Section One, we’ll use a sample e-Commerce data set to understand the basic functions of Excel in business.

• In Section Two, we’ll use real estate sales data for more complex Excel functions such as reporting, visual presentations, and mortgage calculations.

• In Section Three, we’ll use IT Service Business data to demonstrate Excel's complex functions, data validation, and consolidation features.

• In Section Four, we’ll use Rental Income data to search and compare logic conditions and perform a loan amortization schedule for house buyers.

Finally, we’ll step into a more advanced Excel tool for data forecast and prediction. We’ll utilize the What-If analysis to analyze the Income Statement under three conditions:

• Using a one-variable data set

• Using a two-variable data set

• Using optimistic, midrange, and pessimistic business scenarios.

But that’s not all! How about we fire up more advanced features in Excel, including the Developer Tool (Macros), hyperlinks, and automatic emails, and convert TXT (text) files to CSV (MS Excel) files?

You will master the Learning Objectives as you work through these tutorials, which will provide you with a strong foundation in advanced spreadsheet functionality and enable you to astound your coworkers, friends, and family as you investigate numerous ways spreadsheets could be used to access, manage, manipulate, and report data. You'll learn how to use spreadsheets to help you make better business decisions through tutorials and case studies.

This course starts with the basics of Microsoft Excel, intended to refresh your knowledge and understanding of the Microsoft Excel interface. The first few lectures discuss formatting, editing, basic calculations, manipulation, and general data entry activities. We'll slowly transition into advanced topics in Excel, where your learning will be significantly enhanced. As we progress towards advanced topics in Microsoft Excel, we'll think about how this product contributes to business decision-making through data analysis in the work environment or your everyday life. Below are just a few topics that you will master:

1. Excel Fundamentals and Formulas: entering data to spreadsheets, creating formulas and functions that require relative and absolute cell referencing; format cells based on different data types (i.e., dates, numbers, currency, etc.); format spreadsheet background to promote readability; merge cells; arrange worksheets into logical sections to allow reusability and reliability of your Excel files; simplify expressions used in calculations.

2. Excel’s Advanced Formulas, Functions, and Charts: Utilize typical financial, mathematical, string, lookup, and logical functions; explore functions such as PMT(), FV(), PV(), IPMT(), PPMT(), NPV(), LEFT(), RIGHT(), VLOOKUP(), and logic operators (AND, OR); Identify the type of chart (line, bar, pie) that is most appropriate for your business case scenario; interpret and summarize the findings of your graphs and data analysis.

3. Data Analysis Functions: provide meaningful names to cell ranges to improve efficiency and productivity when working with Excel; create a spreadsheet for efficient retrieval and entry of data; create automated data analysis techniques for better and efficient data presentation and analysis; create pivot tables and conditional formation rules; create filters to meet specific criteria, utilize subtotals to summarize listed information.

4. Consolidating Data: add comments in cells to label or clarify characteristics of data; consolidate and automatically summarize information from various sources such as different workbooks and multiple worksheets; manage multiple and individual worksheets by grouping; utilize 3-D references and mathematical functions to consolidate data from other worksheets; merge data collection from multiple sources into a single worksheet.

5. What-If Analysis, Importing, and Macros: determine the optimal solution for business considering given parameters and constraints; utilize Goal Seek to predict sales; infer meaning to the results of forecasts and multiple What-If analyses; import, clean, and analyze text data in Excel; define and implement security precautions in your spreadsheet; identify the repetitive task and automate them with Macros; create validation rules to restrict the entry of new or inappropriate data.

What’s Included?

• 5+ hours of step-by-step video lectures by an experienced trainer.

• Quizzes and prompt questions to test your understanding of course material.

• Downloadable files you can use to follow along and practice with.

• Labs at the end of each section to test your understanding.

I have designed this course so that you can master Microsoft Excel in less time and with ease. If you are an absolute beginner at Excel or are interested in getting to know the program better, this course will teach you how to use Excel's features for data analysis.

Enrolling in this course marks your journey to mastering Excel and becoming an Expert who is confident in your ability to use Microsoft Excel for various tasks.