Data Analysts Excel Training
Training in Excel for Data Analysts provides vital skills for various job roles. Excel is a powerful tool for data analysis, offering functionalities such as data cleaning, pivot tables, and advanced formulas. Proficiency in Excel enables Data Analysts to efficiently manage, analyze, and visualize data. Key skills include creating dynamic dashboards, using Excel functions for complex calculations, and performing data modeling.
These abilities enhance decision-making and support business insights. Excel’s widespread use across industries makes it a valuable skill, opening career opportunities in roles like Data Analyst, Business Intelligence Analyst, and Financial Analyst. Mastery of Excel positions you as an essential contributor to data-driven strategies in any organization.
Training in Excel enhances your ability to transform raw data into actionable insights, supporting datadriven decision-making. Excel’s flexibility and extensive functionality make it an essential tool across various industries. Mastery of Excel positions you as a crucial asset in roles that require strong analytical and reporting skills. This technical expertise enables you to contribute effectively to business processes, strategic planning, and financial analysis, thereby advancing your career in data-centric fields.
Benefits of Excel Training for Data Analyst Roles
- Enhanced Efficiency: Automating tasks and creating advanced reports saves time and improves accuracy.
- Scalability: Excel is widely used and integrates easily with other tools, making it a versatile skill.
- Better Career Opportunities: Mastery in Excel often opens up pathways to data-centric roles and leadership opportunities.
- Cost-Effectiveness: Excel serves as an entry-level tool before transitioning to advanced analytics platforms.
Course Detail
| TOPICS | SUB TOPICS | DAY | TOTAL |
| DATA SUMMARISATION (WITHOUT PIVOT TABLE) | ARITHMATIC FUNCTIONS(SUMMING)(3) | DAY1 | 5 |
| ARITHMATIC FUNCTIONS(COUNTING)(5) | DAY2 | ||
| REFERENCE TYPES(ABSOLUTE,RELATIVE)+USES | DAY3 | ||
| FIXING,FREEZING,LOCKING REFERENCE | DAY4 | ||
| COMBINED ADVANCE TASKS | DAY5 | ||
| CONDITIONAL STATEMENTS | CONDITIONAL STATEMENTS/FN INTRO | DAY6 | 3 |
| CONDITIONAL STATEMENTS+ LOGICAL FUNCTIONS | DAY7 | ||
| NESTED CONDITIONAL STATEMENT + LOGICAL FUNCTIONS | DAY8 | ||
| CONSTRAINT CHECKING FUNCTION | "IS" SERIES FUNCTION+ALPHANUMMERIC | DAY9 | 2 |
| "IS" SERIES FUNCTION+EXCEPTIONAL HANDLING | DAY10 | ||
| EXCEPTIONAL HANDLING | TYPES OF ERROR IN EXCEL | DAY11 | 3 |
| EXCEPTIONAL HANDLING FUNCTIONS IN EXCEL | DAY12 | ||
| PRACISE QUESTION SET ON EXCEPTIONAL HANDLING | DAY13 | ||
| DATA CLEANING (TEXT FUNCTIONS L-1 ) | TEXT FUNCTIONS INTRO+EXAMPLES+FIND ADVANCE | DAY14 | 3 |
| LEFT DYNAMIC,RIGHT DYNAMIC | DAY15 | ||
| MID DYNAMIC ADVANCE QUESTIONS | DAY16 | ||
| DATA CLEANING (TEXT FUNCTIONS L-1) | TEXT FUNCTIONS + CONDITIONAL STATEMENTS | DAY17 | 3 |
| TEXT FUNCTIONS+EXCEPTIONAL HANDLING | DAY18 | ||
|
TEXT
FUNCTIONS+COMBINED BOTH(CONDITIONAL+EXCEPTIONAL) |
DAY19 | ||
| DATA CLEANING (TEXT FUNCTIONS L-3) | TEXT FUNCTIONS + ASCII CODES(UPPER/LOWER) | DAY20 | 3 |
| TEXT FUNCTIONS+NUMERICAL CODES | DAY21 | ||
| TEXT FUNCTIONS+COMBINED ALL LEVELS NESTED STATEMENTS | DAY22 | ||
| DATA FORMATTING USING TEXT FUNCTIONS | STRING LENGTH HANDLING & CONCATENATION TASK | DAY23 | 3 |
| SUBSTITUTION TASKS (LENGTH WISE SUBSTITUTION) | DAY24 | ||
| CASING(UPPER,LOWER,PROPER) | DAY25 | ||
| DATA SUMMARIZATION (USING ARRAY METHODS) LEVEL-0 | MAXIMIZATION AND MINIMIZATION INTRO | DAY26 | 3 |
|
REVISITING ALL THE
PREVIOUS FILE WITH THIS TECHNIQUE(SUMMING) |
DAY27 | ||
|
REVISITING ALL
THE PREVIOUS FILE WITH THIS TECHNIQUE(COUNTING) |
DAY28 | ||
| DATA SUMMARIZATION (USING ARRAY METHODS) LEVEL-1 | TEXT FUNCTIONS + ARRAY + DYNAMIC DRAGS | DAY29 | 6 |
| ALTERNATIVE TECHNIQUES OF SUMMING THE DATA | DAY30 | ||
| N CRITERIA CHECKING/ARRAY | DAY31 | ||
| DIFFERENT POSITIONS CRITERIA SUMMING | DAY32 | ||
| COMPLEX CRITERIA CALCULATION USING ARRAY TECHNIQUE | DAY33 | ||
| ARRAY TECHNIQUE SUMMARIZATION IN FORMATTED CELLS | DAY34 | ||
|
DATA SUMMARIZATION |
NUMERIC BOXES+ AVERAGE
CALCULATION USING ARRAY TECHNIQUE |
DAY35 | 7 |
| (USING ARRAY METHODS) LEVEL-2 | COLORFUL MAZES + ARRAY SUMMARIZATION | DAY36 | |
| DUAL CRITERIA + MULTIPLE COMBINATIONS TASK | DAY37 | ||
| SAME RANGE MULTIPLE CRITERIA (ARRAY) | DAY38 | ||
| MULTIPLE RANGE MULTIPLE CRITERIA SAME SHEET(ARRAY) | DAY39 | ||
| MULTIPLE RANGE MULTIPLE CRITERIA DIFFERENT SHEET(ARRAY) | DAY40 | ||
| DATA SUMMARIZATION (USING ARRAY METHODS) LEVEL-3 | YEARLY,MONTHLY,QUATERLY SUMMARIZATION OF CREDIT CARD | DAY41 | 3 |
|
DYNAMIC MONTHLY TO QUATERLY
AND YEARLY CRITERIA CHECKING |
DAY42 | ||
|
ARRAY TECHNIQUE
USING HELPING COLUMN(MULTIPLE RELATED CRITERIA) |
DAY43 | ||
| PERIOD WISE DATA SUMMARIZATION (USING TIMELINE FUNCTIONS) |
BASIC DATE AND TIME
FUNCTION INTRO + DATE SHORT QUESTIONS |
DAY44 | 5 |
| DATE SHORT QUESTIONS LEVEL 2 | DAY45 | ||
|
DATE FORMATTING
& DATE SUMMARIZING USING(DATEDIF,TEXT)FN |
DAY46 | ||
| TEXT FN/DATE FN +ARRAY SUMMARIZATION | DAY47 | ||
| TIME BASIC FUNCTIONS | DAY48 | ||
| MICROSOFT DATE & TIME EXAMPLES | DATE FUNCTIONS(3)+EXAMPLE +TASKS | DAY49 | 10 |
| DAY FUNCTIONS(3)+EXAMPLE +TASKS | DAY50 | ||
| END OF GIVEN PERIOD FUNCTIONS(3)+EXAMPLE +TASKS | DAY51 | ||
| TIME FORMATTING | DAY52 | ||
| TIME FUNCTIONS(3)+EXAMPLE +TASKS | DAY53 | ||
| DATE FUNCTIONS(3)+EXAMPLE +TASKS | DAY54 | ||
|
DERIVING TOTAL WORKING DAYS
FROM 2 DATES(CUSTOM WEEKEND) |
DAY55 | ||
|
ESTIMATING DATES FROM A
GIVEN DATE /NO OF DAYS (CUSTOM WEEKEND) |
DAY56 | ||
| WEEK FUNCTIONS(FORMATTING , HANDLING , CALCULATING) | DAY57 | ||
| YEAR FUNCTIONS(CALCULATING,SUMMARIZING) | DAY58 | ||
|
MICROSOFT DATE
& TIME FUNCTION QUESTIONS |
LOGIN & LOGOUT HOURS | DAY59 | 3 |
| CURRENT TIME PERIOD RELATED CALCULATION | DAY60 | ||
| ADDING DAYS/MONTHS/YEARS DYNAMICALLY | DAY61 | ||
| DUE DATE CALCULATION | DAY62 | ||
| EXTRACTION & CONCATENATION METHOD | CONCAT INTRO + REQUIRED FUNCTIONS INTRO(INDIRECT) | DAY63 | 6 |
| EXTRACTION NUMBERS+CHARACTERS | DAY64 | ||
| EXTRACTION NUMBERS (EVEN,ODD),EVEN(SUM),ODD(SUM) | DAY65 | ||
| EXTRACTION NUMBERS (GREATER/LESSER CRITERIA) | DAY66 | ||
| EXTRACTION UPPER,LOWER | DAY67 | ||
| COUNT UPPER(SUBS METHOD)+COUNT LOWER(SUBS METHOD) | DAY68 | ||
|
SUBSTITUTE +EXTRACTION |
EXACT METHOD +EXTRACTION TECHNIQUE | DAY69 | 3 |
| NAME SPLIT TASK(MORE THAN 3 WAYS) | DAY70 | ||
| SWAP UPPER & LOWER | DAY71 | ||
| BRAINSTORMING NESTED FUNCTIONS | UNIT CONVERSION TASKS(WITH /WITHOUT BUILT IN METHODS) | DAY72 | 3 |
| ROMAN FORMATTING,MODULUS FN | DAY73 | ||
| ARITHMATIC TASKS (POWER,MODULUS,CUBE ROOT ETC) | DAY74 | ||
|
MICROSOFT INFORMATION
EXTRACTION FUNCTIONS |
INFO(PATH,VERSION,OS,SHEETS) EXTRACTION | DAY75 | 3 |
| DATE+EXTRACTION TASK | DAY76 | ||
| INDIRECT+CONTACT+SUBSTITUTE TASK | DAY77 | ||
| CONDITIONAL FORMATTING | CONDITIONAL FORMATTING INTRO(5 BUILT IN TECHNIQUES) | DAY78 | 3 |
|
NEW RULE,CLEAR
RULE,MANAGE RULE(CONDITIONAL FORMATING ACTIVITY) |
DAY79 | ||
| BUILT IN V/S NEW FORMULA CREATION | DAY80 | ||
| DATA VALIDATION | BUILT IN OPTIONS | DAY81 | 5 |
| FORMATTING AND TEXT MESSAGES | DAY82 | ||
| DV LEVEL 1(7 TASKS) | DAY83 | ||
| DV LEVEL 2(5 TASKS) | DAY84 | ||
| CONDITIONAL FORMATING+DATA VALIDATION | DAY85 | ||
| EXTRACTING INFORMATION USING VLOOKUP FUNCTION |
VLOOKUP &
MATCH (INTRO,ARGUMENT EXPLANATION,BASIC TASKS,RANGE CRITERIA) |
DAY86 | 11 |
|
VLOOKUP FROM DIFFERENT
TABLES(EXCEPTION HANDLING+DYNAMIC DRAG+DOUBLE VL) |
DAY87 | ||
|
VLOOKUP FROM
DIFFERENT SHEETS & WORKBOOKS(EXCEPTION HANDLING+DYNAMIC DRAG) |
DAY88 | ||
| VLOOKUP FROM MULTIPLE COLUMN STATIC(BRAND VLOOKUP) | DAY89 | ||
| VLOOKUP FROM MULTIPLE COLUMN DYNAMIC | DAY90 | ||
| VLOOKUP BASIC SHEEET TO SHEET | DAY91 | ||
|
VLOOKUP USING
HELPING COLUMN(WITHOUT CHOOSE)GET DETAIL VLOOKUP QUES |
DAY92 | ||
|
REVERSE VLOOKUP(GET
DETAIL VLOOKUP QUESTION -WITHOUT HELPING COLUMN) |
DAY93 | ||
| (VLOOKUP + IF CONDITION) & (VLOOKUP + BLANK CELLS) | DAY94 | ||
| VLOOKUP +RUNNING COUNT | DAY95 | ||
| INDEX INTRO+(VLOOKUP & INDEX)+DOUBLE FILTER | DAY96 | ||
| EXTRACTING INFORMATION USING INDEX FUNCTION | SOLVING REVERSE VLOOKUP FILE USING INDEX | DAY97 | 7 |
|
INDEX FROM DIFFERENT
TABLES(EXCEPTION HANDLING+DYNAMIC DRAG+DOUBLE VL) |
DAY98 | ||
|
INDEX FROM
DIFFERENT SHEETS & WORKBOOKS(EXCEPTION HANDLING+DYNAMIC DRAG) |
DAY99 | ||
| INDEX FROM MULTIPLE COLUMN STATIC(BRAND INDEX) | DAY100 | ||
| INDEX FROM MULTIPLE COLUMN DYNAMIC | DAY101 | ||
| INDEX BASIC SHEEET TO SHEET | DAY102 | ||
| INDEX+RUNNING COUNT | DAY103 | ||
| EXTRACTING INFORMATION USING ADDRESS-INDIRECT FUNCTION | SOLVING REVERSE VLOOKUP FILE USING ADDRESS-INDIRECT | DAY104 | 7 |
|
ADDRESS-INDIRECT FROM DIFFERENT
TABLES(EXCEPTION HANDLING+DYNAMIC DRAG+DOUBLE VL) |
DAY105 | ||
|
ADDRESS-INDIRECT FROM DIFFERENT SHEETS
& WORKBOOKS(EXCEPTION HANDLING+DYNAMIC DRAG) |
DAY106 | ||
|
ADDRESS-INDIRECT
FROM MULTIPLE COLUMN STATIC(BRAND ADDRESS-INDIRECT) |
DAY107 | ||
| ADDRESS-INDIRECT FROM MULTIPLE COLUMN DYNAMIC | DAY108 | ||
| ADDRESS-INDIRECT BASIC SHEEET TO SHEET | DAY109 | ||
|
ADDRESS-INDIRECT+RUNNING
COUNT + WITHOUT HELPING COLUMN |
DAY110 | ||
| EXTRACTING INFORMATION USING HLOOKUP FUNCTION | HLOOKUP INTRO TASKS | DAY111 | 4 |
| SUM BETWEEN NAMES | DAY112 | ||
| SUM BETWEEN NAMES+INDIRECT+MULTIPLE CRITERIA | DAY113 | ||
| SUM BETWEEN NAMES+INDIRECT+MULTIPLE RELATED CRITERIA | DAY114 | ||
| PIVOT TABLE | CREATING A PIVOTTABLE | DAY115 | 6 |
| SETTING UP THE LAYOUT | DAY116 | ||
| SORTING AND FILTERING DATA | DAY117 | ||
| GROUPING DATA | DAY118 | ||
| FORMATTING PIVOTTABLES | DAY119 | ||
| USING CALCULATED FIELDS | DAY120 | ||
| PIVOT CHARTS | BASIC CHARTS(BAR,LINE,PIE,HISTOGRAM) | DAY121 | 6 |
| HIERARCHAL VISUALIZATION(TREE,SUNBURST) | DAY122 | ||
| DISTRIBUTION VISUALIZATION | DAY123 | ||
| RELATIONSHIP VISUALIZATION(SCATTER,BUBBLE ,HEATMAP) | DAY124 | ||
| TIME SERIES VISUALIZATION(AREA,CANDLESTICK) | DAY125 | ||
| GEOSPATIAL VISUALIZATION(CHOROPLETH ,DOT MAP) | DAY126 | ||
| ADVANCE CHARTS | INFOGRAPHICS | DAY127 | 5 |
| DONUGHT PROGRESS CHART | DAY128 | ||
| STACK COLUMN CHART | DAY129 | ||
| OVERLAPPED COLUMN CHART | DAY130 | ||
| WATERFALL CHART | DAY131 | ||
| REPORTING | CREATING CHARTS | DAY132 | 6 |
| CUSTOMIZING CHARTS | DAY133 | ||
| USING SPARKLINES | DAY134 | ||
| APPLYING CONDITIONAL FORMATTING | DAY135 | ||
| CREATING PIVOTCHARTS | DAY136 | ||
| DESIGNING INTERACTIVE DASHBOARDS | DAY137 | ||
| POWER QUERY | INTRODUCTION TO POWER QUERY | DAY138 | 7 |
| IMPORTING DATA | DAY139 | ||
| TRANSFORMING DATA | DAY140 | ||
| DATA CLEANING | DAY141 | ||
| COMBINING DATA | DAY142 | ||
| QUERY SETTINGS AND LOAD OPTIONS | DAY143 | ||
| USING POWER QUERY IN DATA ANALYSIS | DAY144 | ||
| DASHBAORD+PROJECTS | PROJECT1(BY TRAINER) | DAY145 | 3 |
| PROJECT2(IN GROUP) | DAY146 | ||
| PROJECT3(INDIVIDUAL) | DAY147 | ||
| WHAT-IF-ANALYSIS? &FORECAST SHEET | SCENARIO MANAGER | DAY152 | 4 |
| GOAL SEEK | DAY153 | ||
| DATA TABLE | DAY154 | ||
| FORECAST SHEET | DAY155 | ||
| DATA MODEL | 3-D MAPPING | DAY156 | 3 |
| RELATIONSHIP MANAGER | DAY157 | ||
| SOLVER | DAY158 | ||
| ANALYSIS TOOL PACK | ANOVA(SINGLE-TWO FACTOR)(WITH/WITHOUT REPLICA) | DAY159 | 13 |
| CORRELATION AND COVARIANCE | DAY160 | ||
| DISCRIPTIVE STATISTICS | DAY161 | ||
| EXPONENTIAL SMOTHING | DAY162 | ||
| F-TEST TWO SAMPLE | DAY163 | ||
| FOURIER ANALYSIS & HISTOGRAM | DAY164 | ||
| MOVING AVERAGE & RANDOM NUMBER GENERATING | DAY165 | ||
| RANK AND PERCENTILE | DAY166 | ||
| REGRESSION | DAY167 | ||
| SAMPLING | DAY168 | ||
| T-TEST(SINGLE FACTOR) | DAY169 | ||
| T-TEST(MULTIPLE FACTOR) | DAY170 | ||
| Z-TEZT | DAY171 | ||
| BASICS OF CLEANING DATA | THE BASICS OF CLEANING YOUR DATA | DAY172 | 10 |
|
REMOVING DUPLICATES
ROW,REMOVING SPACE AND NON PRINTING TEXT |
DAY173 | ||
| FINDING AND REPLACING TEXT,CHANGING CASE OF TEXT | DAY174 | ||
| USE CALCULATED COLUMNS IN AN EXCEL TABLE | DAY175 | ||
| CREATE A MACRO | DAY176 | ||
| SPELL CHECKING | DAY177 | ||
| USING CUSTOM DICTIONARIES | DAY178 | ||
| REMOVING SPACES AND NONPRINTING CHARACTERS FROM TEXT, MERGING AND SPLITTING COLUMNS | DAY179 | ||
| FIXING NUMBERS AND NUMBER SIGNS,FIXING DATES AND TIMES | DAY180 | ||
| TRANSFORMING AND REARRANGING COLUMNS AND ROWS , RECONCILING TABLE DATA BY JOINING OR MATCHING | DAY181 | ||
| THIRD PARTY PROVIDERS FOR DATA CLEANING | Add-in Express Ltd. | DAY182 | 4 |
| Add-Ins.com(DUPLICATE FINDER) | DAY183 | ||
| AddinTools(ADDINS TOOL ASSIST) | DAY184 | ||
| WINPURE(LIST CLEANER LITE & LIST CLEANER PRO) | DAY185 | ||
| LIVE PROJECT WITH INTELLUS DIRECT | |||
| LIVE PROJECT WITH INTELLUS PRIME | |||