9 programs
Choose the best training program according to your career.
Improve the performance of your business with our innovative Industrial Training Program.
Read More →
Our instructor-led training classes are designed to make you an expert.
Read More →
Real-life case studies are a great way to demonstrate your practical knowledge.
Read More →
Explore blogs, latest technologies and industry updates with practical examples.
Read More →
Browse all available training programs and choose the right one for your career.
Read More →Know more about who we are and what we stand for.
Discover our mission to equip individuals with essential IT skills and industry expertise.
Read More →Learn, practice and plan your career — bilkul free.
Book a free video counselling session and pick the right course for you.
Read More →Download the Excel for data analytics guide PDF from Uncodemy: formulas, lookups, PivotTables, Power Query and charts, with worked examples.
This page offers Uncodemy's Excel for data analytics guide as a PDF download. Excel remains the first tool most analysts use. The guide covers the formulas, lookup functions, pivot tables, Power Query steps and chart habits that matter most for analysis, and it explains how to keep a workbook accurate and easy to audit.
The download below is its own file. It covers formulas, lookups, pivot tables, Power Query and charts, plus good practice for accurate, auditable workbooks.
Core formulas, lookups, pivot tables, Power Query and chart habits, with worked formula examples, a pivot checklist and a practice plan.
Excel is available almost everywhere, opens files from nearly any source and lets you see your data while you work on it. That immediacy makes it the best tool for a first look at a dataset, for quick calculations and for sharing results with colleagues who do not use code. Its limits are equally real: it slows down with large files, formulas are hard to audit and manual edits are not repeatable. A good analyst knows both sides and uses Excel where it is strongest, then moves to SQL, Python or a BI tool when the data or the task outgrows it.
| Task | Functions | Example idea |
|---|---|---|
| Totals and averages | SUM, AVERAGE, MEDIAN, COUNT, COUNTA | Average order value by month |
| Conditional totals | SUMIFS, COUNTIFS, AVERAGEIFS | Revenue for one region in one quarter |
| Lookups | XLOOKUP, VLOOKUP, INDEX with MATCH | Pull a product category into an orders sheet |
| Logic | IF, IFS, AND, OR, IFERROR | Flag orders above a threshold |
| Text | TRIM, LEFT, RIGHT, MID, TEXTJOIN, SUBSTITUTE | Clean and combine names |
| Dates | TODAY, EOMONTH, DATEDIF, TEXT, WEEKDAY | Group sales by month or weekday |
| Statistics | STDEV.S, PERCENTILE, CORREL | Check spread and relationships |
Most business questions need data from more than one table, such as orders and products. Lookup functions fetch a matching value from another range. XLOOKUP is the modern choice: it searches in any direction, returns an exact match by default and lets you specify what to show when nothing is found. VLOOKUP is older and requires the key to be in the first column. INDEX with MATCH remains popular for flexibility. Whichever you use, check that the lookup key is unique in the lookup table and test for unmatched keys, because a silent mismatch gives blanks or wrong values.
These formulas assume a table named Orders with the columns shown. Adjust the names to your own sheet.
=SUMIFS(Orders[Amount], Orders[Region], "North", Orders[Status], "<>Cancelled")
=XLOOKUP([@ProductID], Products[ProductID], Products[Category], "Not found")
=IFERROR([@Revenue] / [@Orders], 0)
=TEXT([@OrderDate], "yyyy-mm")A pivot table summarises thousands of rows into a compact table in seconds. You drag fields into rows, columns, values and filters, choose a summary such as sum, count or average, and Excel produces the result. Add slicers for easy filtering, group dates into months or quarters and use show values as options to display percentage of total or difference from a previous period. Always build pivots from a clean, tabular source with one header row, no blank rows and one fact per row. Refresh the pivot after the source changes, since it does not update on its own.
Power Query, found under Get and Transform on the Data tab, lets you import data and apply cleaning steps such as removing columns, changing types, splitting text, filtering rows, merging tables and unpivoting. Each step is recorded in order, so when new data arrives you click refresh and every step runs again. This turns a one-off manual clean-up into a repeatable process and leaves a visible audit trail of what was done. For analysts who handle weekly or monthly files, learning Power Query is one of the most valuable skills in Excel.
Take a sales dataset of a few thousand rows. Clean it with Power Query, add a category with a lookup, build a pivot of revenue by month and region, add two slicers and chart the main trend. Then write three sentences on what the pivot shows. Repeat on a new dataset after a week and try to do it faster and with fewer errors. That cycle of build, summarise and explain is exactly what junior analysts do every day.
Ready to master Excel for analysis with a trainer? Explore Uncodemy's Data Analytics course and learn from industry trainers.