Ravindra BagaleCourses & study guides मराठी Track your progress

Excel for Data Analysts and MIS Executives: Cleaning, PivotTables, Dashboards and KPIs (With Templates)

By Last updated:

This guide is for people in the analysts profession who use Excel every day. You will see every common file, the formulas people actually type, and downloadable templates with made-up Indian ₹ data. Related short lessons are linked — we do not re-teach them here.

What you'll learn

  • Which roles use Excel in analysts
  • Every common Excel file type for this profession
  • Sample tables with realistic ₹ examples (made-up names)
  • Formulas: TRIM text, Duplicate flag, KPI status, Attainment % and more
  • Ready templates you can download and reuse

Downloads: analysts-clean-kpi-dashboard.xlsx (Raw + clean + checklist + KPI scorecard + dashboard canvas + refresh log)

Example data only

All names, amounts, rates and dates are made up for practice. They are not real client or employer records. Follow your organisation's own rules and software for live work.

In short

One profession → many Excel files. Each file is one clear list. Yellow cells are inputs; green cells are formulas. Start with the Roles table, then open the matching template.

Why

Some topics already have a focused lesson. Use those for depth; use this guide for the full profession picture.

What

Chapter What it covers
7. PivotTables and PivotCharts Build PivotTables step by step.
Sales Dashboard with PivotTables Dashboard from Pivot + Slicer + Timeline.
Power Query clean data Clean before you analyse.

How

  1. Skim the link if you need that one skill.
  2. Come back here for the full file list and templates.

1. Who uses Excel

Why

MIS is a calendar of numbers. Excel is still the pack most managers open first.

What

Role Typical Excel work
MIS executive Daily/weekly pack: KPI sheet + charts from clean data.
Data analyst Clean raw export, Pivot, insight notes.
Reporting analyst Dashboard layout, slicer-ready tables.
Business intern (analytics) Duplicate check, TRIM, proper case, basic SUMIFS.

How

  1. Read the Roles table.
  2. Start with Clean data, not the dashboard.
  3. Download the template pack.

2. Every common Excel file

Why

If you only learn one dashboard, you miss the daily registers that feed it.

What

File What one row / sheet holds
Raw data dump As exported from CRM/ERP — often messy.
Clean data table Trimmed text, real dates, no blank key rows.
Data quality checklist Nulls, duplicates, outliers, date range.
Pivot staging table Flat columns ready for Insert > PivotTable.
KPI scorecard KPI name, target, actual, status, owner.
Dashboard canvas KPI tiles + chart placeholders + filter note.
Refresh log When data was refreshed and by whom.

How

  1. Pick the file that matches your role.
  2. Download the template that contains that sheet.
  3. Replace yellow sample rows with your practice data (still made-up if you share screenshots).

3. Formulas people use

Why

Typed numbers go stale. Formulas update when inputs change.

What

Name Formula What it does
TRIM text =TRIM(A2) Remove extra spaces.
Duplicate flag =IF(COUNTIF($A$2:$A$200,A2)>1,"Dup","OK") Mark repeated keys.
KPI status =IF(actual>=target,"On track","Behind") Simple RAG without colour alone.
Attainment % =actual/target How close to target.
SUMIFS metric =SUMIFS(amount,city,sel,month,m) One city-month total.
Unique count helper =SUMPRODUCT(1/COUNTIF(range,range)) Teaching unique count (or use UNIQUE in 365).
Error trap =IFERROR(vlookup_or_x,"Missing") Stop #N/A on dashboards.
Days since refresh =TODAY()-refresh_date Stale data warning.

How

  1. Open the Formulas used sheet inside each template.
  2. Change only yellow cells.
  3. Watch green cells recalculate.

3. Clean before you Pivot

Why

Dirty data makes confident wrong charts.

What

A cleaning sheet with TRIM, duplicate flags and a checklist.

How

  1. Open analysts-clean-kpi-dashboard.xlsx › Raw data.
  2. Copy to Clean data; yellow fixes only.
  3. Filter Dup = Dup and fix or remove.
  4. Tick the checklist sheet before Pivot.
KPI scorecard — Status formula

Figure. KPI scorecard — Status formula

4. KPI scorecard and dashboard canvas

Why

Leaders want five numbers, not fifty columns.

What

KPI scorecard with status formulas and a simple dashboard layout sheet.

How

  1. Open KPI scorecard. Change Actual (yellow); Status flips.
  2. Open Dashboard canvas. Note where each chart will sit.
  3. Link: build real PivotCharts in the Sales Dashboard chapter (Related).

5. Refresh discipline

Why

A beautiful board with yesterday’s dump misleads.

What

A refresh log with days-since formula.

How

  1. Enter today’s date in Refresh log.
  2. Days since refresh is green.
  3. If days > 1 on a daily MIS, re-export source data.

6. Sheet previews

Why

See the layout before you open Excel.

What

Screenshots of the main sheets in the downloadable templates.

How

Scroll the figures, then download the matching .xlsx.

Clean data — TRIM city + Dup flag

Figure. Clean data — TRIM city + Dup flag

KPI scorecard — Status formula

Figure. KPI scorecard — Status formula

7. Common mistakes

Mistake What goes wrong Fix
Pivoting the raw dump Blank cities and duplicate orders inflate totals. Clean first; Pivot the clean table.
Targets typed into chart titles only Status cannot update. Keep Target and Actual as cells.
Hiding #N/A by deleting formulas Next refresh breaks. Use IFERROR and fix the key.

8. FAQ

Do I need Power Query?

For big daily dumps, yes — see the Power Query lessons. This guide starts with worksheet cleaning.

PivotTables in LibreOffice?

Calc has DataPilot; Excel Pivot UI differs. Templates teach flat data ready for either.

9. Try at home

Try at home

  1. How many Dup flags appear on Clean data?
  2. On KPI scorecard, set one Actual below Target — does Status show Behind?
  3. What does Days since refresh show if Refresh date is yesterday?

Recap

In short

  1. 4 roles use Excel in this profession.
  2. 7 common file types are listed above.
  3. 1 downloadable templates with yellow inputs and green formulas.
  4. Rates and rules here are teaching examples only.
  5. Use Related chapters for focused skills.

Quick revision cards

Quick revision

1 interview card, 1 comparison card and 1 revision card. Save on your phone or download as PNG. Numbers are made up for practice.

Interview card: Before a PivotTable, what do you clean in Excel?
Card 1 of 3: Interview card – Before a PivotTable, what do you clean in Excel? · Download PNG
Comparison card: Raw dump vs Clean table
Card 2 of 3: Comparison card – Raw dump vs Clean table · Download PNG
Revision card: KPI status formula
Card 3 of 3: Revision card – KPI status formula · Download PNG