.png)
Excel Skills for Data Analytics
in Data Analytics with Microsoft ExcelAbout this course
Microsoft Excel skills for Data Analytics
Microsoft Excel is the industry leading spreadsheet software program. Excel has advanced analytics and visualization tools include PivotTables, PivotChart, advanced functions, Power Query, PowerPivot, and charts.
Turn your raw data into meaningful insights with this comprehensive Excel skills for analytics course. Whether you're a complete beginner or have some basic knowledge, this course will equip you with the essential skills to clean, manage, and visualize your data effectively.
Learn essential skills like navigating the Excel interface, managing workbooks, organizing data, and applying basic formulas. Dive into advanced functions such as XLOOKUP, VLOOKUP, SUMIFS, AVERAGEIFS, FILTER, SORT etc to uncover trends and tell compelling stories with charts.
Learning Objectives
Mastering the Interface:
- Navigate the Ribbon Menu with ease for efficient task management.
- Create, save, rename, and delete workbooks and sheets effectively.
- Organize your data by formatting, duplicating, arranging, and managing sheets.
- Master row and column operations, including insertions, deletions, resizing, hiding, and rearranging.
Data Entry and Manipulation:
- Leverage AutoFill and Custom Lists for efficient data entry and consistent formatting.
- Understand and apply data types to ensure accuracy and proper use.
Formulas and Functions for Analysis:
- Confidently use essential formulas and functions like VLOOKUP, IFS, XLOOKUP, MIN, SUM, and MAX to analyze and transform data.
- Employ proper cell referencing (relative, absolute, and mixed) for dynamic and accurate formulas.
- Analyze data using SUMIFS, COUNTIFS, AVERAGEIFS, FILTER, SORT and UNIQUE
- Analyze and visualize data using PivotTables and charts.
Data Visualization:
- Create informative charts (bar, line, column, pie) to communicate trends and insights visually.
Data Management Techniques:
- Organize and refine your data using filters, sorting, and auto filters.
- Freeze panes for easier navigation and data manipulation in large spreadsheets.
Additional Benefits:
- Gain hands-on practice through real-world scenarios and exercises.
- Develop critical thinking and problem-solving skills within the context of data analysis.
- Increase your efficiency and productivity in using Excel for various tasks.
Target Audience:
This course is ideal for:
- Beginners with no prior Excel experience.
- Individuals with limited Excel knowledge seeking to improve their data analysis skills.
- Professionals who want to utilize Excel for more insightful data management and presentations.
Prerequisites:
- Basic computer literacy is recommended.
By completing this course, you will be equipped with the knowledge and skills to confidently use Excel for data analysis, unlocking its potential to extract valuable insights from your information.
Requirements
PC with at least Microsoft Excel 2013 installed
No prior knowledge of Excel is required.
Comments (0)
Introduction to the course
What you will need to get started
Explore Microsoft excel interface
Excel backstage view
Manage workbooks
Manage worksheets
Working rows and columns
working with cells
Entering data into cell
Entering time and multiple values
Formatting values
Applying custom formatting
Auto Fill
Custom List
Undo and redo operations
Introduction to Basic Excel formulas
Introduction to basic functions
The IF Function
Date functions
Cell referencing - Relative reference
Cell referencing - Absolute cell reference
Cell referencing - Mixed cell reference
Using defined names in formulas
VLookUP and XLookUP
VLookUP Demo
Download and follow along with the TEXT function lessons
Introduction to text functions
Changing TEXT case
Extract Text from Text strings
Extract text new excel functions
Joining multiple text strings
Using the TRIM and REPT Functions
Combining arrays
Using TOROW and TOCOL functions to change rows to column and vice versa.
Introduction to Excel charts
Working with column charts
Column chart 2
Stack column chart
Working with bar chart.
Download Bar chart guide
Working with line chart
Download line chart guide
Pie chart
Best practices for working with pie charts
Download Pie chart guide
Exploring recommended charts
Sorting Data
Sorting Text Data
Sort data numerically
Sort data by color
Multi-level sort
Date filters
Filter numeric data
Filter data by text
Advanced filter
Freeze panes
Split windows
Remove duplicates from datasets
Using the UNIQUE function
The SORT AND SORTBY Functions
The FILTER Function
Download the sample workbooks
Benefits of working with Excel Tables
Creating an excel table
Changing table name
Applying Table Styles
Sorting Table
Filtering Table
Calculated Columns
Adding Total Row
Structured Reference 1
Structured Reference 2
Structured Reference 3
Using PivotTables with Tables
Using Chart with Tables
Resizing table
Convert table to range
Using COUNTIFS function
Using SUMIFS function
Using AVERAGEIFS function
Using MINIFS and MAXIFS
XLOOKUP AND VLOOKUP - EXACT MATCH
VLOOKUP vs XLOOKUP - APPROXIMATE MATCH
Donwload the practice file and follow alogn with the videos.
Preview of practice datasets
Create your first PivotTable
Navigating the areas of a PivotTable
Working with multiple PivotTable Fields
Answer business questions with PivotTables
Changing default PivotTable name
Summarize values by
Show Values As: % of Grand Total
Show Values As: % of Row Total
% of Column totals
Running totals
Applying Pivotable Styles
PivotTables Fields Pane
Module Conclusion
Course conclusion
.png){kind=small}
.png){kind=small}
