Data Analysis & Work Efficiently using Excel: Novice to Pro

Improve your productivity, Microsoft Excel and Data Analytics skills by mastering variety of topics with diverse Datasets on different domains eg. Manufacturing, Sales, Medical, Finance and more, by completing this course you are in your way to become a Data Analyst, Data Analytics and Advanced Excel professional.

Increase your value and efficiency in your career and life by learning practical and applicable Excel productivity tools and knowledge with 9 chapters and more than 100 learning videos with a number of assignments and quizzes by the end of each chapter. In this course you should learn the concept, have the opportunity to practice and see examples of how to actually apply this knowledge in the field.

Skills covered in the course

• Work Efficiently, I will provide you my personal tips and tricks to gradually increase your speed and productivity in work in Excel or any other software.

• Excel Shortcuts, We will study many essential shortcuts that used very often by Excel users, this will help you to increase your speed drastically and help you to avoid using the mouse while work.

• Data Analytics, dealing and analyzing data is crucial for Microsoft Excel users, in this course we will study many tools and methods to analyze data, from data preprocessing and to building a dashboards and automatic reports creation.

• Excel Dashboards, Excel doesn't provide direct dashboard creation, but we will use some of the Automation tools we will learn during the course to build one.

• Statistics, we will learn a number of mathematical and statistical Excel formulas and functions such as finding the mean, mode, median and others.

• Vlookup, we will provide a number of lookup techniques (Vlookup, Hlookup, Xlookup, Vlookup match combination to join multiple columns in a single formula)

• Pivot Tables, we will build variety pivot tables to group and summaries our data and study it from different perspectives and create a reports out of that data.

• Data Visualization, visualizing the data helps us to clearly see the information quickly and clearly, and this will help us to make faster and better decisions, we will use Excel Charts to create Data Visualizations for different data and different scenarios.

• Automation, We can say Automation is the final level of being productive, when we have the machine perform the tasks for us in a single click, in this course we will study many of these Automation tools, eg. (Efficient use of Excel Functions and Formulas, Power Query, Python and Excel Macros)

• Power Query, beside automating data preprocessing from different sources, we will introduce a number of handy tools eg. (DAX, Data Modeling and Power Pivots) which are vital for anyone who want to study Business Intelligence tools like Power BI.

• Data Modeling, by using Data Models we can link between a number of tables without merging them into a single one, we will be using Excel data models to link between these tables and gain insights taken from these tables simultaneously without physically joining them.   

• DAX, by using DAX we can have more flexible calculations on the data inside our data models without the need to add new columns, even some of the calculations are hard or impossible without using DAX, and by learning DAX you will have a good base if you want to continue learning Power BI in your learning roadmap.

• Python, for anyone already familiar with Python, we will use PY built in formula to interact with Excel objects (Cells, Tables) inside of Python codes, this will help us to use Python libraries to perform Machine Learning, Data Science, and others.

• Excel Macros, A good Automation option that will help you to record changes on Excel's objects and rerun the recorded steps again and again upon need.

• Excel VBA, VBA provides huge flexibility to update Excel's Macros in actual codes, by learning Macros and updating them in VBA codes you will gain higher level and high ability to automate things in Excel.

• Optimization using Excel's Solver, By using the Solver's algorithms we can optimize numeric decisions to achieve highest value or lowest costs.

The skills acquired in this "data analyst course" is completely transferrable for other technologies and softwares eg.  Data Science, SQL for managing databases, BI Tools such as (Power BI and Tableau), Python and its Data Analytics libraries such as (Pandas, Matplotlib and Seaborn).

Course Breakdown Structure:

• Chapter 1 (Working Efficiently): In the first chapter you will be introduced for my personal guidelines and principles that I found to be effective to follow to achieve high and rapid speed when working in any software especially in Excel, these techniques were developed in an accelerated working environment were speed and accuracy is a must, following and applying these guidelines should allow you to accomplish your tasks in scalable and automated ways.

• Chapter 2 (Excel Shortcuts & Basic Tools): Learn most frequently used keyboard shortcuts and other useful tools such as Quick Access Toolbar, Freeze Pans, Sorting and Filtering. Also in this chapter we will study a number of formatting techniques such as cells formatting and conditional formatting.

• Chapter 3 (Data Preprocessing and Formulas): In this chapter we will learn many of the data processing techniques for the common data types (text, numeric and dates), we will also study a number of useful mathematical functions and techniques such as calculating the cumulative sum for given numbers.

• Chapter 4 (Data Analysis): After learning to have our data ready, in this chapter we will learn more advanced data tools such as grouping and joining multiple table together, we will also learn how to visualize our data "EDA" using Excel charts and building a dashboard to analyze the data from different perspectives which is essential for any data analyst.

• Chapter 5-7: In these three chapters we will introduce several Automation tools (Power Query, Macros and PY) and they can be used to automate data preprocessing and analysis and other tasks such as creating and modifying Excel objects eg. sheets, cells and ranges. By automating our tasks we can have our job done by the machine in a single click.

• Chapter 8 (Optimization using Excel Solver): Learn one of Excel's tools that can be used to perform numeric based decisions, it will help us to find best mixture of numbers to achieve  best performance or result, in this chapter we will learn how to use this tool on variety of applications eg. Capital Budgeting and Resources Allocation.

• Chapter 9 (Practical Examples): You will get the opportunity to apply most of the topics, productivity and Automation methods we studied so far in practical examples, most of them taken from my actual work eg. Automated Reports Creation, Data Extraction and Transformation and Using Excel's Solver effectively.