Advanced Excel formulas, PivotTables, data cleaning, Power Query and Power BI dashboards.
What you will cover
Advanced Excel formulas
PivotTables
data cleaning
Power Query
Power BI dashboards
Topic list from the institute's programme record.
Certification
Certificate terms for this programme are confirmed by the institute at admission. Any ADIVAS course-completion certificate is an institute certificate; it is not a university degree, an externally accredited qualification or a vendor certification (such as AWS or Microsoft) unless the institute states so in writing.
Curriculum 0.1 · Draft: written for ADIVAS and awaiting academic review and approval. Topics and weeks may change before your batch starts.
Your skill trail
You move through 4 stations. Each week builds one skill you can show, not just a topic you have heard.
1
Clean and structure data
Turn messy exported data into tidy Excel Tables ready for analysis.
Week 1 Tidy data and Excel Tables
2
Formulas that answer questions
Use lookup, logical and conditional-aggregation formulas on real data.
Week 2 Lookups and logical formulas
Week 3 Conditional totals and dynamic arrays
3
PivotTables and Power Query
Summarise data quickly and automate cleaning with repeatable queries.
Week 4 PivotTables and PivotCharts
Week 5 Power Query: import, clean, combine
4
Power BI dashboards
Model data, write measures and publish a dashboard with a clear story.
Week 6 Power BI Desktop and data modelling
Week 7 DAX measures and visuals
Week 8 Dashboard storytelling and capstone
★
Retail Sales Analysis Dashboard
Your project, reviewed by a trainer.
The ADIVAS mastery ladder: how each skill is recognised
Explored You have seen the idea and tried a first example.
Practising You can do it with hints and keep improving.
Proficient You complete the weekly practical task correctly on your own.
Proven You apply it in a project reviewed by your trainer.
Skill-level tracking is being added to the learner workspace. Your trainer already reviews weekly practical work.
What you will be able to do
Clean and standardise messy data using text, date and lookup formulas and Excel Tables.
Write lookup and logical formulas (XLOOKUP, INDEX/MATCH, IF, SUMIFS, COUNTIFS) and explain how they work.
Summarise data with PivotTables, slicers and PivotCharts to answer business questions.
Import, clean and combine data from several files using Power Query, with repeatable steps.
Build a simple star-style data model in Power BI and write basic DAX measures.
Design a clear dashboard, check numbers against the source and present insights honestly.
Who it suits
School Senior school students comfortable with basic spreadsheets who want to work with real data sets and charts.
College Commerce, science and engineering students who need data analysis skills for projects and internships.
Professional Accounts, sales, HR and operations staff who prepare monthly reports in Excel and want to automate and visualise them.
Prerequisites: Comfort with basic Excel: entering data, SUM, AVERAGE, simple formatting and charts. Tools: Microsoft Excel (desktop, with Power Query), Power BI Desktop (free, Windows), Sample data files supplied by the institute, Free Power BI online account is optional for sharing.
Weekly plan
Clean and structure data Weeks 1
Week 1: Tidy data and Excel Tables
Theory
What makes data tidy: one row per record, one column per field
Excel Tables, structured references and named ranges
Text clean-up: TRIM, CLEAN, LEFT, MID, TEXTJOIN, Flash Fill
Dates stored as text and how to convert them
Data validation, Remove Duplicates and Find and Replace
Lab
Take a 300-row sales export with extra spaces, mixed date formats and duplicate rows; convert it into an Excel Table, standardise customer names and dates and remove duplicates while keeping a copy of the original.
Homework
Clean a second export (a school fee list supplied by the trainer) and write down every change made.
Deliverable
A cleaned Table with a short change log sheet.
Weekly checkpoint
Ten-question quiz on tidy data plus a 20-minute clean-up task on a new file.
You will be able to
Clean and standardise messy data using text, date and lookup formulas and Excel Tables.
Formulas that answer questions Weeks 2–3
Week 2: Lookups and logical formulas
Theory
IF, IFS, AND, OR and nested conditions
XLOOKUP with exact match and a not-found message
VLOOKUP limits and INDEX/MATCH as an alternative
Handling errors with IFERROR and IFNA
Absolute and relative references
Lab
Using the cleaned sales Table, pull product category and price from a product master with XLOOKUP, grade each order as Small, Medium or Large with IFS and handle missing products without showing #N/A.
Homework
Rebuild two lookups using INDEX/MATCH and compare the results with XLOOKUP.
Deliverable
A workbook with price lookup, order grade and an error-handling column.
Weekly checkpoint
Formula-reading questions, a debugging task (find the wrong reference) and a lookup build.
You will be able to
Write lookup and logical formulas (XLOOKUP, INDEX/MATCH, IF, SUMIFS, COUNTIFS) and explain how they work.
Week 3: Conditional totals and dynamic arrays
Theory
SUMIFS, COUNTIFS, AVERAGEIFS with several conditions
SUMPRODUCT for weighted totals
FILTER, SORT, UNIQUE and spill ranges
Date functions: EOMONTH, EDATE, NETWORKDAYS, TEXT
Conditional formatting rules driven by formulas
Lab
Build a monthly region-by-product summary using SUMIFS, list unique customers with UNIQUE, filter the top orders with FILTER and highlight overdue invoices with a conditional-format formula.
Homework
Write five SUMIFS formulas that answer questions supplied by the trainer and check each against a manual filter.
Deliverable
A summary sheet that updates when a new month of data is pasted into the Table.
Weekly checkpoint
Mixed formula quiz and a timed summary build.
You will be able to
Write lookup and logical formulas (XLOOKUP, INDEX/MATCH, IF, SUMIFS, COUNTIFS) and explain how they work.
PivotTables and Power Query Weeks 4–5
Week 4: PivotTables and PivotCharts
Theory
Creating a PivotTable from a Table
Rows, columns, values, filters and value-field settings (sum, count, % of total)
Grouping dates into months and quarters, and number ranges into bands
Slicers, timelines and PivotCharts
Calculated fields and when to avoid them
Lab
Create a PivotTable showing sales by region and month, add slicers for product category, group dates into quarters and build a PivotChart for the top five products.
Homework
Answer three business questions (best month, weakest region, discount effect) using only PivotTables.
Deliverable
A one-page Pivot report with two slicers and two charts.
Weekly checkpoint
Pivot interpretation questions and a build-from-scratch task.
You will be able to
Summarise data with PivotTables, slicers and PivotCharts to answer business questions.
Week 5: Power Query: import, clean, combine
Theory
Get and Transform: from Excel, CSV and folder
Applying steps: split, change type, fill down, unpivot, merge and append
Combining twelve monthly CSV files from one folder
Refreshing a query and reading the Applied Steps list
Common errors: type changes and changed column names
Lab
Load three monthly CSV files from a folder, append them, fix data types, unpivot a wide month-column layout into rows, merge a product master and load the result to a Table; then drop in a fourth file and refresh.
Homework
Document each Applied Step in plain words for a colleague.
Deliverable
A refreshable query workbook plus a one-page step explanation.
Weekly checkpoint
Sequence questions on query steps and a practical build with a surprise new file.
You will be able to
Import, clean and combine data from several files using Power Query, with repeatable steps.
Power BI dashboards Weeks 6–8
Week 6: Power BI Desktop and data modelling
Theory
Power BI workflow: get data, transform, model, report, publish
Tables, relationships and cardinality (one-to-many)
Star layout: one fact table and several dimension tables
Date table and why it is needed
Row-level versus aggregated thinking
Lab
Import the sales fact table, product, customer and date tables into Power BI Desktop, define relationships, hide key columns and test with a simple matrix visual.
Homework
Draw your model on paper with the relationship lines and write which side is the many side.
Deliverable
A .pbix file with a correct four-table model.
Weekly checkpoint
Diagram-based model questions and a relationship debugging task.
You will be able to
Build a simple star-style data model in Power BI and write basic DAX measures.
Week 7: DAX measures and visuals
Theory
Measures versus calculated columns
SUM, DIVIDE, CALCULATE, filter context in simple terms
Time intelligence basics: year-to-date and same period last year using a date table
Choosing visuals: bar, line, card, table, map; avoiding pie overuse
Slicers, drill-through and tooltips
Lab
Write measures for Total Sales, Total Cost, Profit Margin % with DIVIDE and Sales YTD; build a report page with cards, a line chart by month and a bar chart by region with a slicer.
Homework
Check three measure values against your Excel PivotTable and note any difference and why.
Deliverable
A report page whose numbers match the Excel pivot.
Weekly checkpoint
DAX reading questions and a short build under time.
You will be able to
Build a simple star-style data model in Power BI and write basic DAX measures. Design a clear dashboard, check numbers against the source and present insights honestly.
Week 8: Dashboard storytelling and capstone
Theory
Layout: top-left insight, consistent colours, labels and titles
Number reconciliation: dashboard versus source
Publishing, sharing and refresh options in simple terms
Ethical charts: honest axes and not hiding bad news
Presenting findings in three sentences
Lab
Finish the capstone dashboard, reconcile its totals with the source file, write three insights with a recommendation and present for five minutes.
Homework
Collect feedback from one peer and make two improvements.
Deliverable
Capstone dashboard, reconciliation note and a one-page insight summary.
Weekly checkpoint
Capstone assessment with a viva on how each number was produced.
You will be able to
Design a clear dashboard, check numbers against the source and present insights honestly.
Sample lessons
Week 5
Power Query: clean once, refresh forever
Power Query records every cleaning step you perform, such as removing spaces, changing data types or merging a lookup table. The steps are saved in order as Applied Steps. When next month's file arrives you press Refresh and the same steps run again, so you do not repeat work by hand or make new mistakes. Think of it as a recipe that cooks fresh data the same way every time.
1Source files (CSV/Excel)
2Power Query steps
3Clean Table
4PivotTable / Power BI
5Refresh next month
Step by step
Open Data, Get Data, From Text/CSV and choose the first monthly file.
Choose Transform Data to open the Power Query editor.
Trim text columns, set the Date column to Date type and the Amount to Decimal.
Remove duplicate rows using the Bill No column and fill down blank store names.
Close and Load to a Table, then add a new file to the folder and press Refresh.
In real life
An accounts assistant used to spend a day each month copying branch files into one sheet. With a folder query she drops the new files in and refreshes in a minute, and the cleaning is identical every month.
Worked example
Problem. A CSV has the date 05-11-2025 stored as text and the Amount column contains the text "1,200 " with a trailing space. How do you prepare both columns?
Solution. Trim the Amount column, replace the comma, change type to Decimal. For the date, set the type using the correct locale so day and month are read as intended, then verify a few known rows. Apply the steps in Power Query so they repeat on refresh.
Common mistakes
Cleaning in the worksheet by hand so the work cannot be repeated.
Renaming a source column later, which breaks the query step that expects the old name.
Changing data types before trimming text, which can turn valid values into errors.
Try it yourself
Combine two CSV files of different months into one query and add a Month column.
Unpivot a table with columns Jan, Feb, Mar into Month and Amount columns.
Week 6
Star model: facts and dimensions
A good Power BI model keeps numbers that change with every sale in a fact table (quantity, amount) and descriptive details in dimension tables (product, customer, date). Each dimension has one row per item and connects to the fact table by a key in a one-to-many relationship. Filters flow from the dimension to the fact table, which is why a slicer on Product Category changes the sales total.
Sales (fact)
Product
Customer
Date
Store
Step by step
Load Sales, Product, Customer and Date tables.
Open Model view and connect Product[ProductID] to Sales[ProductID] as one-to-many.
Connect Customer and Date in the same way.
Mark the Date table as a date table.
Test by placing Category and Sum of Amount in a table visual.
In real life
A garment shop analyses sales by product type, town and month. Instead of one huge sheet repeating the product name on every row, a small product table is linked once, which keeps the file light and the numbers consistent.
Worked example
Problem. A slicer on Customer City does not change the sales total. What is the most likely model issue?
Solution. There may be no active relationship between Customer and Sales, or it points the wrong way or is many-to-many. Check Model view, add the one-to-many relationship using the customer key and set the filter direction to single from Customer to Sales.
Common mistakes
Joining two fact tables directly instead of through shared dimensions.
Duplicate keys in a dimension table, which breaks one-to-many.
Leaving all columns visible so report builders pick keys by mistake.
Try it yourself
Sketch a star model for a school fee collection system.
List which columns of a sales export belong to the fact table and which to dimensions.
Try the questions
The same skills are asked at three levels: School (Classes 6–12), College and Professional. Switch the level and the language (English or Tamil), and say how sure you are before you check: knowing what you are unsure about is part of learning.
Classes 6–12: everyday examples, no prior knowledge. Practice only. Nothing here is graded or saved.
Question 1 / 4 · Recognise tidy data
A class teacher keeps marks with one row for every student and one column for every subject. Why is this easy to analyse?
Projects
Retail Sales Analysis Dashboard
A Madurai retail chain with three stores gives you twelve monthly CSV exports of sales. They contain extra spaces, mixed date formats and some duplicate bills. Clean and combine them, analyse sales and profit, and deliver an Excel report and a Power BI dashboard that the owner can read in two minutes.
Tasks
Combine the twelve CSV files with Power Query and fix data types and duplicates.
Build lookups for product category and cost from a product master.
Create a PivotTable report with slicers for store and category.
Model the data in Power BI with a date table and write measures for Sales, Cost, Profit and Profit Margin %.
Reconcile dashboard totals with the combined source and write three recommendations.
Sample input
Files Sales-Apr.csv to Sales-Mar.csv, products.xlsx. Example row: 12/04/2025 | Store 2 | Rice 25kg | Qty 4 | Price 1450
Expected output
A refreshable workbook, a dashboard page with cards, a monthly trend and a store comparison, a reconciliation table showing difference zero, and a written summary.
How it is assessed
Criterion
Weight
Data cleaning and repeatable steps
25%
Correct formulas and lookups
20%
Pivot analysis
15%
Model and DAX measures
20%
Dashboard clarity and honest charts
20%
Stretch challenge: Add a forecast line or a what-if parameter for a discount percentage and explain the limits of the result.
How you are assessed
Each week: eight questions (formula reading, data interpretation, one diagram), a 30-minute hands-on task on a new data file and a short note explaining what was done and why.
Practical work. Learners clean, analyse and visualise unseen data in the lab; work is checked for correct numbers, repeatable steps and clear explanation.
What trainers look for
Weight
Correct results and reconciliation
35%
Clean, repeatable method
25%
Chart and dashboard clarity
20%
Explanation of choices
20%
Where this can lead
Prepares you for MIS-style reporting and data-entry analyst tasks that use Excel heavily.
Prepares you to build monthly management dashboards and to continue to data analytics studies.
These describe skills you will build. ADIVAS does not guarantee jobs, placement or salary.
Other programmes in Accounting, Finance & Business Applications
