DEV Community

Velma Ketra Lukaya
Velma Ketra Lukaya

Posted on

Getting started with MS Excel for data analytics: From basics to data cleaning

#analytics #beginners #data #tutorial

Microsoft Excel from Beginner to Data Analysis: What Each Part Does, When to Use It, and How to Find It

This guide has two purposes.

  • An introduction to Excel itself: the interface, the ribbons, the formula bar, formatting, formulas, charts, PivotTables, and dashboards.

  • Process to take raw data and turn it into something trustworthy and visual: clean it, organize it, calculate on it, analyze it, display it and report it.

That second thread matters because a beautiful dashboard built on messy data is still a bad analysis. So as we move through Excel's tools, we'll keep returning to the question: where does this fit in the data → clean → calculate → analyze → visualize → report pipeline?

1. Getting Started with Excel

Microsoft Excel is a spreadsheet application used to work with data. You can use it to:

  • Enter and store data
  • Organize datasets
  • Clean messy data
  • Perform calculations
  • Analyze information
  • Create charts
  • Build PivotTables
  • Create reports and dashboards

A useful mental model for the whole journey is:

Data → Clean → Calculate → Analyze → Visualize → Report
Enter fullscreen mode Exit fullscreen mode

In practice, data is retrieved from an engineer, a system, another team, as raw figures, text, and numbers. Your job as the analyst is to clean it, decide what it means, and deliver insight from it. Before you can analyze anything, the data needs a reasonable structure and quality. Data cleaning is specifically about working on the quality and structure of the data before analysis begins

Opening Excel

When you open Excel, you'll usually land on a start screen with options such as:

  • Blank Workbook
  • Recent workbooks
  • Templates
  • Options for opening existing files

How to get there

  1. Open Microsoft Excel.
  2. From the start screen, select Blank Workbook.

You now have a new Excel workbook.

2. Understanding the Excel Interface

Before entering data, it helps to know what you're looking at. A workbook is made up of several major areas:

  • Title Bar
  • Quick Access Toolbar
  • Ribbon
  • Name Box
  • Formula Bar
  • Worksheet (columns, rows, cells)
  • Sheet tabs
  • Status Bar

2.1 Title Bar

Sits at the top of the window and shows the name of the currently open workbook for example, Book1 - Excel. Once you save, the title updates to whatever you named the file. It tells you at a glance which workbook and which file is currently active.

2.2 Quick Access Toolbar

Shortcuts to commonly used commands (Save, Undo, Redo, etc.), so you don't have to dig through the Ribbon for actions you use constantly.

[: Excel interface showing the Title Bar and Quick Access Toolbar]

3. The Ribbon

The Ribbon is the area that holds all of Excel's tools and toolbars, organized into tabs. Each tab groups related commands together.

Tab Main Purpose
Home Formatting and everyday editing
Insert Tables, charts, PivotTables, shapes and other objects
Page Layout Page and printing settings
Formulas Functions and formula tools
Data Sorting, filtering, validation, cleaning and analysis
Review Comments, protection and reviewing
View How the workbook is displayed

3.1 Home Tab

It holds tools for:

  • Font, font size, bold, italic, underline, font colour
  • Fill colour, borders
  • Alignment, Wrap Text, Merge & Center
  • Number formatting
  • Conditional Formatting
  • Sorting and filtering
  • Copy and paste

How to get there: Home → choose the relevant tool

4. Entering and Formatting Data

4.1 Cells

An Excel worksheet is made of cells which are the intersection of a column and a row. Columns are letters (A, B, C…), rows are numbers (1, 2, 3…). The first cell is A1. The cell in column C, row 5 is C5.

4.2 What is a Cell Reference?

A cell reference tells Excel exactly where a piece of information lives. A1 means column A, row 1. You use references inside formulas:

=A1+B1
Enter fullscreen mode Exit fullscreen mode

This tells Excel to take the value in A1 and add the value in B1.

5. Rows, Columns and Ranges

Rows run horizontally and are numbered. A row commonly represents one record-one employee, one transaction, one observation.

Columns run vertically and are lettered. A column commonly represents one variable or field.

Employee ID Name Department Salary
1001 Jane Finance 80,000
1002 John HR 70,000

Each row is an employee; each column is a field describing that employee.

Ranges are groups of cells. A1:A10 means cells A1 through A10. A1:D10 is a rectangular range from A1 to D10. Ranges matter because most Excel functions operate on them:

=SUM(A1:A10)
Enter fullscreen mode Exit fullscreen mode

6. Worksheets and Workbooks

A workbook is the Excel file itself e.g., Employee Analysis.xlsx. A workbook can contain multiple worksheets, which you can think of as individual pages inside it — for example: Employee Data, Calculations, PivotTable, Dashboard.

Renaming a worksheet: Right-click the sheet tab → Rename (or double-click the tab).

7. Basic File Operations

7.1 Save

File → Save, or Ctrl + S on Windows.

7.2 Save As

File → Save As — creates a new copy or saves under a different name/format. Common file types:

  • .xlsx — standard Excel workbook
  • .xls — older Excel format
  • .csv — comma-separated values

A CSV is fine for simple tabular data, but it doesn't preserve Excel-specific features like multiple worksheets, formatting, PivotTables, or charts.

8. Formatting Your Data

Formatting changes how data looks without changing its underlying value. Good formatting makes a spreadsheet easier to read and easier to trust.

8.1 Font

Home → Font — type, size, bold, italic, underline.

8.2 Font Colour

Home → Font Color — useful for headers, warnings, important values, titles.

8.3 Fill Colour

Home → Fill Color — great for headers, KPI cards, and highlighting.

8.4 Borders

Home → Borders — bottom, top, left/right, all, or outside borders make table structure easier to see.

8.5 Alignment

Left, centre, right, top, middle, bottom. Text is conventionally left-aligned; numbers and dates right-aligned.

A useful diagnostic: if a column of numbers is sitting left-aligned instead of right-aligned, that's often a sign Excel is treating them as text, not numbers which will silently break SUM, AVERAGE, and other calculations on that column. Checking that a column's alignment is consistent throughout is a quick way to catch a data-type problem before it costs you a wrong total.

8.6 Wrap Text

Home → Alignment → Wrap Text makes long text wrap onto multiple lines within the same cell.

8.7 Merge & Center

Home → Merge & Center combines cells into one and centres the content — handy for titles like EMPLOYEE SALARY REPORT. Avoid merged cells inside your raw data table, though — they interfere with sorting, filtering, and analysis.

8.8 Number Formatting

Home → Number display 10000 as 10,000, 10,000.00, or as currency (e.g., KSh 10,000). You can also format as percentage, date, or time.

9. AutoFill and Flash Fill

AutoFill

Extends a pattern (January, February, March…) or copies a formula down a column. Enter the first value, select the cell, grab the small square at the bottom-right corner (the fill handle), and drag.

Flash Fill

Recognizes a pattern in your data and completes the rest automatically. For example, if a column has "John Smith", "Jane Doe", "Peter Kamau" and you start typing just the first names in the next column, Flash Fill can complete the pattern for you.

How to get there: Data → Flash Fill, or Ctrl + E

10. The Formula Bar

The Formula Bar shows the actual contents of the currently selected cell. If C2 contains =A2+B2, the Formula Bar shows that formula not the number it evaluates to. This is essential for viewing and editing long formulas, checking what's really stored in a cell, and telling a formula apart from its displayed result.

11. Writing Your First Formula

Every formula starts with =.

=10+20   → 30
Enter fullscreen mode Exit fullscreen mode

The real power comes from cell references:

A1 = 10
B1 = 20
=A1+B1   → 30
Enter fullscreen mode Exit fullscreen mode

12. Excel Arithmetic Operators

Operator Meaning Example
+ Addition =A1+B1
- Subtraction =A1-B1
* Multiplication =A1*B1
/ Division =A1/B1
^ Exponent =A1^2

^ raises a number to the power of something — =10^3 means 10 raised to the power of 3.

13. Basic Excel Functions

A function is a built-in Excel formula designed for a specific task. Excel's basic functions split naturally into two families: aggregate functions, which combine numbers into a single computed value, and statistical/counting functions, which tell you something about the shape or completeness of your data.

Aggregate functions

SUM— adds values.

=SUM(A2:A10)
Enter fullscreen mode Exit fullscreen mode

Use for total sales, total salary, total expenses, total marks.

AVERAGE-the mean.

=AVERAGE(A2:A10)
Enter fullscreen mode Exit fullscreen mode

MEDIAN- the middle value when the data is sorted. Unlike the average, the median isn't dragged around by extreme values, which makes it a better "typical value" when a dataset has outliers.

=MEDIAN(A2:A10)
Enter fullscreen mode Exit fullscreen mode

MODE- the most frequently occurring value.

=MODE.SNGL(A2:A10)
Enter fullscreen mode Exit fullscreen mode

MIN / MAX — smallest / largest value in a range.

=MIN(A2:A10)
=MAX(A2:A10)
Enter fullscreen mode Exit fullscreen mode

PRODUCT— multiplies a range of values.

=PRODUCT(A2:A5)
Enter fullscreen mode Exit fullscreen mode

POWER— raises a number to a power (same idea as ^).

=POWER(10,3)   → 1000
Enter fullscreen mode Exit fullscreen mode

SQRT— square root.

=SQRT(25)   → 5
Enter fullscreen mode Exit fullscreen mode

Statistical / counting functions

These are especially useful for checking the completeness of a dataset before you analyze it:

COUNT— counts cells containing numbers only.

=COUNT(A2:A10)
Enter fullscreen mode Exit fullscreen mode

COUNTA— counts all non-blank cells (text and numbers).

=COUNTA(A2:A10)
Enter fullscreen mode Exit fullscreen mode

COUNTBLANK— counts only blank cells.

=COUNTBLANK(A2:A10)
Enter fullscreen mode Exit fullscreen mode

The distinction between COUNT, COUNTA, and COUNTBLANK matters: running all three on the same column is a fast way to spot missing data before it silently distorts an analysis.

14. Relative and Absolute Cell References

Write =A2*B2 and drag it down, and Excel automatically adjusts it to =A3*B3, =A4*B4, and so on. These are relative references — they shift with the formula.

Sometimes you want a reference to stay fixed. Use $ to lock it an absolute reference:

=$A$1
Enter fullscreen mode Exit fullscreen mode

This always points to A1, no matter where the formula is copied. For example, if A1 holds a tax rate of 16%:

=B2*$A$1
Enter fullscreen mode Exit fullscreen mode

Copied down the column, $A$1 stays locked while B2 still adjusts to B3, B4, etc.

15. Intermediate Functions

Once basic formulas feel comfortable, you can start asking Excel to make decisions and find information across your dataset the core of real analysis.

16. Conditional (Aggregate) Functions

These calculate something based on one or more conditions: COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS.

COUNTIF — counts cells matching one condition.

=COUNTIF(B2:B100,"Female")
Enter fullscreen mode Exit fullscreen mode

"How many female employees are in the dataset?"

COUNTIFS — counts cells matching multiple conditions (which can span different columns).

=COUNTIFS(B2:B100,"Female",C2:C100,"Finance")
Enter fullscreen mode Exit fullscreen mode

"How many female employees work in Finance?"

SUMIF — sums values based on one condition.

=SUMIF(B2:B100,"Finance",D2:D100)
Enter fullscreen mode Exit fullscreen mode

"Total salary paid to employees in Finance."

SUMIFS — sums with multiple conditions.

=SUMIFS(D2:D100,B2:B100,"Finance",C2:C100,"Female")
Enter fullscreen mode Exit fullscreen mode

AVERAGEIF / AVERAGEIFS- average based on one or more conditions.

=AVERAGEIF(B2:B100,"Finance",D2:D100)
Enter fullscreen mode Exit fullscreen mode

A quick way to remember the argument order: SUMIF and AVERAGEIF start with the condition range, then the criteria, then the range to calculate. SUMIFS and AVERAGEIFS flip that they start with the range to calculate, followed by all of the condition range/criteria pairs.

17. Logical Functions

Logical functions let Excel automate decision-making: determining bonuses, matching customer records, checking eligibility, or classifying values into bands like High, Medium, and Low.

IF

=IF(logical_test, value_if_true, value_if_false)
=IF(B2>=50,"Pass","Fail")
Enter fullscreen mode Exit fullscreen mode

AND — TRUE only if every condition is true. Reach for AND when you're combining conditions across different columns that must all hold e.g., married, male, and over 50.

=AND(A2>50,B2>50)
Enter fullscreen mode Exit fullscreen mode

OR — TRUE if at least one condition is true. Reach for OR when you're checking multiple possible values within the same column e.g., department is Finance or HR.

=OR(A2="Finance",A2="HR")
Enter fullscreen mode Exit fullscreen mode

Combining IF with AND/OR

=IF(AND(B2>=50,C2>=50),"Pass","Fail")
Enter fullscreen mode Exit fullscreen mode

"Pass only if both B2 and C2 are at least 50."

Nested IF — chains multiple conditions to build categories:

=IF(B2>=80,"High",IF(B2>=50,"Medium","Low"))
Enter fullscreen mode Exit fullscreen mode
  • 80+ → High
  • 50–79 → Medium
  • Below 50 → Low

A nested IF is read from the top down: Excel checks the first condition, and if it's false, falls through to the next IF inside it. This matters when you're deciding where a blank value should land, put the ISBLANK check first, so blanks get routed to their own outcome instead of silently falling into the last category.

ISBLANK — checks whether a cell is empty. Useful for handling incomplete datasets, often combined with IF:

=IF(ISBLANK(A2), "No data", IF(A2>=50,"Pass","Fail"))
Enter fullscreen mode Exit fullscreen mode

18. Date Functions

Dates are everywhere in real-world datasets, and they're a frequent source of cleaning headaches.

TODAY() — today's date. Useful for tracking deadlines, calculating age, flagging overdue tasks.

NOW() — current date and time.

YEAR() / MONTH() / DAY() — extract the year, month, or day from a date.

DATEDIF(start, end, "unit") — the difference between two dates, e.g. =DATEDIF(A2,B2,"Y") for complete years.

NETWORKDAYS(start, end) — working days between two dates, automatically excluding weekends. Useful for project timelines, employee working days, turnaround time.

Dates are also one of the most common places data quality breaks down. Watch for hire-date entries like 30/02/2019 (there's no February 30th) or 2020/13/05 (there's no 13th month)
These are invalid dates that need to be fixed at the source before any date function can be trusted on that column.

19. Text Functions

Text functions are essential for cleaning datasets. They apply specifically to text-type data, and are typically used to standardize text, remove extra spaces, extract part of a string, or combine text from multiple cells.

UPPER / LOWER / PROPER — change case.

=UPPER(A2)    "nairobi" → "NAIROBI"
=PROPER(A2)   "john kamau" → "John Kamau"
Enter fullscreen mode Exit fullscreen mode

TRIM — removes unnecessary/extra spaces, especially useful on imported data.

=TRIM(A2)
Enter fullscreen mode Exit fullscreen mode

LEFT / RIGHT — extract characters from the left or right side of a string.

=LEFT(A2,3)
=RIGHT(A2,3)
Enter fullscreen mode Exit fullscreen mode

LEN — counts the number of characters in a string. Handy for calculating the length of a substring before extracting it, or for spotting entries that are suspiciously short or long.

=LEN(A2)
Enter fullscreen mode Exit fullscreen mode

MID — extracts characters from the middle of a string. If A2 contains EMP-10545:

=MID(A2,5,5)   → "10545"
Enter fullscreen mode Exit fullscreen mode

FIND — returns the position of one piece of text within another.

CONCAT — combines text from multiple cells.

=CONCAT(A2," ",B2)
Enter fullscreen mode Exit fullscreen mode

If A2 = "John" and B2 = "Kamau", the result is "John Kamau".

A few real applications

  • Phone numbers with country codes: use LEFT to pull out a country code (e.g. +254), and combine it with a logical function (IF) to assign a country name based on the code you found.
  • Employee IDs like EMP-10945: use MID to pull the numeric part out on its own.
  • Generating email addresses: combine LOWER and CONCAT to build a standardized address from a first name and last name, e.g.: 
  =LOWER(CONCAT(A2,B2,"@company.com"))
Enter fullscreen mode Exit fullscreen mode

This is a common cleaning/standardization exercise: taking two separate text fields and deriving a consistent third field from them.

20. Lookup Functions

Lookup functions let Excel search for something and pull back related information essential once your data is spread across multiple tables.

VLOOKUP — searches for a value in the first column of a range and returns a value from another column in that same range.

=VLOOKUP(lookup_value, table_array, column_index, FALSE)
=VLOOKUP(A2,$F$2:$I$100,2,FALSE)
Enter fullscreen mode Exit fullscreen mode

Two things to keep in mind: the column_index is a number, not a column letter, and the lookup value must be located in the first column of the range you're searching
That's VLOOKUP's main limitation.

Example: Table 1 has Employee ID → Name. Table 2 has Employee ID → Salary. VLOOKUP lets you pull salary into Table 1 using Employee ID as the shared key.

XLOOKUP — a more flexible successor available in newer Excel versions. It can search in either direction and doesn't require the lookup column to be first it can traverse data horizontally without VLOOKUP's positional restriction.

=XLOOKUP(what_you_are_looking_for, where_to_search, what_to_return)
Enter fullscreen mode Exit fullscreen mode

HLOOKUP

HLOOKUP (Horizontal Lookup) searches for a value across the top row of a table and returns a corresponding value from a specified row below it. It is useful when data is arranged horizontally rather than vertically.

The basic syntax is
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

INDEX + MATCH — a two-function combination that does what VLOOKUP does, without the "must be in the first column" restriction. MATCH finds the position of what you're looking for; INDEX returns the value sitting at that position.

=INDEX(what_you_want_to_return, MATCH(lookup_value, lookup_array, 0))
Enter fullscreen mode Exit fullscreen mode

Because INDEX and MATCH are separate, the lookup value doesn't need to be in the first column of anything, you just point MATCH at wherever it lives.

21. Organizing Your Data

Once you can calculate and manipulate values, the next step is making sure the dataset itself is properly organized, this is where data cleaning and structure become the priority. Common problems to watch for:

  • Missing fields
  • Mixed data types (e.g. numbers stored as text)
  • Inconsistent labeling (e.g. "IT" and "I.T" treated as different categories)
  • Inconsistent capitalization or letter case
  • Inconsistent date patterns, or outright invalid dates
  • Pseudoblanks
  • Duplicate records
  • Outliers

22. Data Cleaning

Data cleaning means preparing data so it can be analyzed reliably. Imagine a Department column containing:

Finance
finance
FINANCE
Fin.
Enter fullscreen mode Exit fullscreen mode

Excel treats these as four different values unless you standardize them first.

A suggested cleaning order

There's no single required sequence, but working through checks in roughly this order catches problems before they compound:

  1. Cell size and display — column widths, and freezing header rows/columns so you don't lose context while scrolling.
  2. Data type consistency — check that a column is entirely numbers, entirely text, or entirely dates. A column that "looks" numeric but has left-aligned entries mixed in with right-aligned ones usually has a hidden text/number mismatch.
  3. Sort — sorting a column often surfaces inconsistent entries, blanks, or oddities that were easy to miss row by row.
  4. Find and Replace — start with the easiest cases (often numerical or clearly-patterned text), confirm your changes by re-sorting afterward, and make sure you're being column-specific rather than replacing across the whole sheet by accident.
  5. Remove duplicates — after the above steps, so you're not duplicating already-flawed data.
  6. Conditional formatting — use it last, as a visual QA pass to spot what's left.

22.1 Missing Values

Possible approaches:

  • Delete the entire row, if a large amount of important data for that record is missing.
  • Leave the cell blank.
  • Replace it with a clear label like "Unknown."
  • Fill it using an appropriate statistic: mean, median, or mode, when that's contextually reasonable (for example, filling a missing HR-related field with the mode if you're confident about the typical value for that group).

The right choice depends on the situation, not a fixed rule Always ask what a blank means in that specific column before deciding how to treat it.

22.2 Pseudoblanks

A cell isn't always technically empty even when it represents missing information. Watch for:

None
N/A
Unknown
-
Enter fullscreen mode Exit fullscreen mode

These pseudoblanks should generally be converted to one consistent representation (e.g., "Unknown") so they're treated the same way during analysis.

That said, be careful: in some datasets a genuinely blank cell and a cell marked "None" don't mean the same thing. For example, in a training-attendance column, a blank might mean "no data collected," while "None" might specifically mean "attended zero trainings" which is a real, meaningful value.
Don't collapse the two automatically; check what each one is actually representing first.

22.3 Find and Replace

Home → Find & Select → Replace, or Ctrl + H.

Use case: your dataset contains Nairobi, NBI, Nrb referring to the same place
Find and Replace lets you standardize them quickly. As above, do this column by column and re-check your work rather than assuming a global find-and-replace is safe.

22.4 Removing Duplicates

Duplicate records distort totals, averages, and counts, but don't delete every row that merely looks similar. First decide which field should actually be unique: Employee ID, phone number, email, patient ID, etc.

A useful QA step before deleting anything: use Conditional Formatting to highlight duplicates based on that unique identifier first (e.g., duplicate phone numbers or duplicate Employee IDs), then look at what's flagged.

  • If every field in the flagged rows matches, it's a true duplicate hence safe to delete.
  • If only the unique identifier matches but other fields differ, investigate before deleting, it may be a genuine data-entry issue rather than a duplicate. Once you've resolved it, filter to confirm the duplicates are gone, and clear the conditional formatting rule so it doesn't linger and confuse the next person to open the file.

How to actually remove duplicates:

  1. Select your dataset.
  2. Data → Remove Duplicates.
  3. Choose the column(s) that determine uniqueness.
  4. Click OK.

22.5 Outliers

An outlier is a value unusually different from the rest of the data. If employee ages are 24, 27, 31, 29, 26, 4 that 4 shouldn't be deleted immediately. It could be a data entry error, a genuine (if unusual) observation, or a special case. Investigate before ruling it out, without changing the data itself in the process of checking.

This is also where central tendency measures earn their keep: the mean is pulled toward outliers, but the median is largely unaffected by them, which is why median is often the safer "typical value" to report alongside, or instead of, the mean when a dataset has extreme values.

23. Conditional Formatting

Conditional Formatting changes how cells look based on defined conditions without changing the underlying data itself.

How to get there: Home → Conditional Formatting

  • Highlight Cells Rules — greater than, less than, equal to, between (inclusive of the borderline numbers), contains specific text or a subset of a word.
  • Top/Bottom Rules — top 10, bottom 10, top %, bottom %, or a top-N like "top 5 earners."
  • Colour Scales — apply a gradient of colour based on magnitude, e.g. a 0–10 scale shading from white toward dark green as values increase.
  • Data Bars — an in-cell bar sized to the value.

Conditional formatting is particularly good for spotting patterns, high/low values, missing information, and potential problems at a glance for example, quickly seeing the top 3 earners in a particular age group in a large dataset.

24. Sorting Data

Sorting rearranges data by a chosen variable: text (A→Z or Z→A), numbers (smallest→largest or reverse), or dates (oldest→newest or reverse).

How to get there: Data → Sort

You can also perform multi-level sorting where one sort is applied after another. For example: sort by Department, then within each department, sort by Gender.

25. Filtering Data

Filtering temporarily hides rows that don't meet your criteria Nothing is deleted, and you can always return to the full dataset afterward.

How to get there: Data → Filter

Once enabled, dropdown arrows appear in your headers, offering:

  • Text filters — equals, does not equal, begins with, ends with, contains, or a specific value anywhere in the text.
  • Number filters — greater than, less than, equal to, or between (inclusive of the boundary values).
  • Date filters — equal to, before, after, between.

26. Freeze Panes

Large datasets get hard to navigate once your headers scroll out of view. Freeze Panes keeps selected rows or columns visible while you scroll through the rest.

How to get there: View → Freeze Panes

For example, if headers are in row 1, freeze the top row so it stays put no matter how far down you scroll.

27. Excel Tables

An Excel Table turns a plain range into a structured, self-maintaining table; a more structured way of formatting your data.

How to get there

  1. Select your dataset.
  2. Insert → Table.
  3. Confirm your table has headers.
  4. Click OK.

Shortcut: Ctrl + T

Tables automatically give you filters, structured formatting, automatic expansion as you add rows, structured references, and much easier PivotTable/chart source management. A good dataset should generally have one header row, consistent columns, no unnecessary blank rows, and no merged cells inside it.

28. Data Validation

Data Validation controls what a user can type into a cell. Its most common use is a dropdown list. Instead of letting people freely type "Finance," "finance," "FINANCE," or "Fin." give a fixed list: Finance, HR, Marketing, IT.

How to get there: Data → Data Validation → Allow → List, then enter the permitted values or point to a range containing them.

This is one of the cheapest ways to prevent inconsistent labeling before it ever enters your dataset.

29. Charts: Turning Data into Visual Information

Once your data is clean and analyzed, it's time to visualize it. The guiding principle: choose the chart based on the question you're trying to answer, not the other way around.

Column and Bar Charts

Best for comparing categories i.e "Which department has the highest total salary?"

How to get there: select the data → Insert → Column or Bar Chart

For long category names, consider whether a clustered or stacked layout communicates the comparison more clearly.

Pie and Donut Charts

Pie charts show parts of a whole "What percentage of employees belong to each department?"
Donut charts work similarly with a hollow centre.
Use both only with a small number of categories: once you're at roughly six or seven categories or more, they become hard to read. At that point, consider another chart

Line Charts

Best for showing a trend over time how a value changes across months, for example.

How to get there: Insert → Line Chart

Area Charts

Similar to a line chart, but the area beneath the line is shaded It useful when you want to emphasize magnitude or volume rather than just direction.

Scatter Charts

Used to examine the relationship between two numeric variables, "Is there a relationship between hours studied and exam score?" Plot one variable on each axis: select the two variables directly and insert the chart.

When describing what a scatter chart shows, it helps to name the relationship along a simple scale: no relation, weak, or strong and whether it's positive or negative.

Combo Charts

Combines two chart types in one e.g., one category shown as a column and another as a line, such as Sales (columns) against Profit Margin (line). Useful for comparing two related measures on different scales.

Formatting a Chart

Once created, click the chart to reveal chart-specific tabs (Chart Design, Format) where you can adjust:

  • Axes and their titles
  • Chart Title
  • Horizontal / vertical axis
  • Legend
  • Data labels
  • Data table
  • Error bars
  • Trendline
  • Gridlines
  • Chart style

Whatever charts you build, try to keep a consistent visual theme with the rest of your dashboard this matters especially for charts with visually "light" elements, like a donut chart's hollow centre.

30. PivotTables

A PivotTable is one of Excel's most powerful tools: a summary table that lets you summarize, analyze, and present data interactively by dragging and dropping fields to spot patterns and trends without changing the original data. It's the foundation for most reports and dashboards.

Instead of manually calculating "total salary by department" across 10,000 rows, you build a PivotTable and let Excel do it.

30.1 Preparing Data for a PivotTable

Before building one, make sure:

  • Text blanks are filled (don't leave meaningful gaps).
  • All columns have headers.
  • There's nothing extra to the right of, or below, the dataset.
  • The original data structure is preserved.

A good structure looks like:

Employee ID Name Department Gender Salary
1001 Jane Finance Female 80,000
1002 John HR Male 70,000
1003 Mary Finance Female 90,000

30.2 Creating a PivotTable

  1. Click anywhere inside your dataset.
  2. Insert → PivotTable.
  3. Choose your data source.
  4. Choose where you want the PivotTable placed.
  5. Click OK.

30.3 The Four PivotTable Areas

  • Rows — determines what categories appear vertically. E.g., Department → Rows produces Finance, HR, IT, Marketing.
  • Values — the numbers Excel calculates. E.g., Salary → Values gives totals per department.
  • Columns — breaks the values down further by another category. E.g., Department → Rows, Gender → Columns, Salary → Values lets you compare salary across department and gender at once.
  • Filters — restricts the whole PivotTable to a subset, e.g. Department = Finance only.

Caution: it doesn't really matter which column you drag into Values — apart from the fact that it must be a numeric column, and ideally one without blanks. Blanks in the values column can distort counts and totals, so pick a clean numeric field.

30.4 Grouping Data in PivotTables

Sometimes you want bands instead of individual values — e.g., age ranges (18–25, 26–35, 36–45) instead of every single age. Right-click a value inside the PivotTable and choose Group.

31. PivotCharts

A PivotChart is a chart connected directly to a PivotTable rather than manually selecting data for a chart, the chart is linked to whatever the PivotTable is currently showing. This is especially useful for dashboards: change the PivotTable, and the chart updates with it.

How to get there

  1. Click inside the PivotTable.
  2. Insert → PivotChart.
  3. Choose the chart type.
  4. Click OK.

32. Slicers

Slicers are a visual, click-based way to filter PivotTables instead of opening a dropdown and picking "Finance," you click a button labelled Finance.

How to get there: click your PivotTable → PivotTable Analyze → Insert Slicer, then choose the field to filter by (Department, Gender, Location, Year, etc.).

Connecting one slicer to multiple charts: by default, a slicer only controls the PivotTable it came from. To make one slicer filter several PivotTables/PivotCharts at once (which is what you want on a dashboard), right-click the slicer, choose Report Connections, and select every PivotTable that should respond to it. Without this step, clicking a slicer on your dashboard may only update one chart while the rest stay frozen which defeats the point of an interactive dashboard.

33. Building an Excel Dashboard

A dashboard is a single-page visual report that summarizes all the key information at a glance as opposed to a longer, more detailed report. It should be interactive and updatable via slicers, and typically includes a title, KPIs, and a set of charts chosen specifically for what you've analyzed.

┌─────────────────────────────────────────────┐
│             SALES DASHBOARD                 │
├─────────────┬─────────────┬─────────────────┤
│ TOTAL SALES │ CUSTOMERS   │ AVG. SALE       │
│  KSh 5.2M   │    1,240    │ KSh 4,200       │
├─────────────┴─────────────┴─────────────────┤
│                                             │
│              SALES TREND                    │
│                                             │
├───────────────────────┬─────────────────────┤
│ SALES BY DEPARTMENT   │ SALES BY REGION     │
│                       │                     │
└───────────────────────┴─────────────────────┘
Enter fullscreen mode Exit fullscreen mode

33.1 Setting Up the Dashboard Sheet

A simple way to start: create a new sheet, select the whole sheet (Ctrl + A), and fill it with a background colour that matches your intended theme, before placing anything on it.

33.2 Dashboard Header

Create a header using a shape: Insert → Illustrations → Shapes → Rounded Rectangle is a common, clean choice.

From there, adjust fill colour, outline, add your title text, choose a font, and centre it.

33.3 KPI Cards

KPIs (Key Performance Indicators) are the numbers you want someone to understand instantly e.g. Total Sales, Customers, Average Sale, and similar headline figures. Place these directly below the header, since they're the first thing a viewer should see.

Worth knowing: in plain Excel, KPI cards are typically static text/values you update manually or via formula, they don't automatically shift when a slicer is clicked, the way they would in a tool like Power BI.
Genuinely interactive KPIs that respond live to slicer clicks are more of a Power BI capability; in Excel, plan your dashboard with that limitation in mind.

33.4 Adding Charts to the Dashboard

Move your finished charts and PivotCharts onto the dashboard sheet. A typical dashboard mixes:

  • KPI cards
  • A trend chart (e.g., sales over time)
  • A breakdown by category (department, region, etc.)
  • A comparison or performance chart

Choose the charts based on the questions your data is actually answering not just because you happen to have built them.

33.5 Making the Dashboard Interactive

A department slicer with buttons for Finance, HR, IT, Marketing (or a year slicer with 2024, 2025, 2026) lets a viewer click through the data themselves instead of reading a static snapshot.

Remember to connect each slicer to every relevant PivotTable via Report Connections (see Section 32) so the whole dashboard responds together, not just one chart.

33.6 Dashboard Design

A dashboard isn't "every chart you've ever made" it's a small, curated set that communicates quickly. Keep it:

  • Clean and uncluttered
  • Consistent — one visual theme across header, KPI cards, charts, fonts, and colors
  • Well-aligned
  • Focused on the handful of numbers and trends that actually matter

36. Power Query: Automating the Cleaning Step

Everything covered so far e.g. removing duplicates, fixing pseudoblanks, standardizing text, splitting columns — can also be done manually, once.
Power Query is Excel's built-in tool for building a repeatable data-cleaning and transformation process. Instead of manually editing cells, you record a series of transformation steps once, and Power Query replays those exact steps automatically every time you refresh the data even if the underlying data has changed or grown.

This is where Power Query sits in the bigger workflow: it belongs at the Clean and Organize stages, before you ever touch a formula, PivotTable, or chart. Think of it as an automated, auditable version of the manual cleaning process from earlier in this guide.

36.1 Why Use Power Query Instead of Manual Cleaning?

  • Repeatability — clean a dataset once, and reapply the same steps to next month's file in seconds.
  • Traceability — every transformation is recorded as a visible step, so you (or anyone else) can see exactly what was done to the data and in what order.
  • Non-destructive — Power Query works on a copy of the data pulled into its own editor; your original source file or sheet is untouched.
  • Handles messier sources — it can pull data from CSVs, folders full of files, databases, and web pages, not just a worksheet already sitting in Excel.

36.2 Getting Data Into Power Query

How to get there: Data → Get Data (or Data → From Table/Range if your source is already an Excel Table on the same sheet)

Common source options include:

  • From Table/Range — turns existing worksheet data into a query
  • From Text/CSV
  • From Folder — combines multiple files in a folder into one dataset
  • From Web
  • From Database (SQL Server, Access, etc.)

Once you select a source, Power Query opens the Power Query Editor in a separate window.

36.3 The Power Query Editor

The Editor has three areas worth knowing:

  • Preview pane (centre) — shows your data as it currently looks, after whatever steps have been applied so far.
  • Queries pane (left) — lists every query you've built in this workbook.
  • Applied Steps pane (right) — the recorded, ordered list of every transformation you've made to this query.

The Applied Steps panel is the heart of Power Query. Each transformation e.g. removing a column, filtering rows, changing a data type, appears as its own named step. You can click any step to see the data exactly as it looked at that point, reorder steps, or delete one without affecting the others. This is what makes Power Query auditable in a way manual cleaning never is.

36.4 Core Transformations

Most of the cleaning tasks covered earlier in this guide have a direct Power Query equivalent but recorded as a repeatable step instead of a one-time edit.

Changing a column's data type
Click the data-type icon in a column header, or Transform → Data Type. This is worth doing early and deliberately a column silently stored as text instead of number is one of the most common causes of broken calculations downstream.

Removing duplicates
Select the column(s) that should be unique → Home → Remove Rows → Remove Duplicates. Same logic as before: decide which field defines a duplicate before removing anything.

Removing or keeping specific rows
Home → Remove Rows lets you remove blank rows, error rows, duplicates, or the top/bottom N rows. Home → Keep Rows does the inverse.

Filtering
Click the dropdown arrow in a column header, just like a worksheet filter but here, the filter becomes a permanent, recorded step rather than a temporary view.

Splitting a column
Transform → Split Column → By Delimiter (or by number of characters). Useful for pulling apart something like EMP-10545 into a prefix and an ID number, or splitting a full name into first and last name — the Power Query equivalent of the LEFT/RIGHT/MID work from earlier.

Merging or combining columns
Transform → Merge Columns combines multiple columns into one — the Power Query equivalent of CONCAT useful for building something like a standardized email address as a repeatable step rather than a manual formula.

Trim, Clean, and Case changes
Transform → Format offers Trim (remove extra spaces), Clean (remove non-printable characters), and UPPERCASE/lowercase/Capitalize Each Word — direct equivalents of TRIM, UPPER, LOWER, and PROPER.

Replacing values
Transform → Replace Values — the Power Query equivalent of Find and Replace, but applied as a saved, reusable step rather than a one-off edit. This is a clean way to handle pseudoblanks: replace "N/A", "None", and "-" with a single consistent value like "Unknown" in one recorded step.

Filling down blanks
Transform → Fill → Down fills a blank cell with the value from the cell above it and is useful for datasets where a category label is only entered once at the top of a group and left blank for the rows beneath it.

36.5 Combining Data From Multiple Sources

Append Queries — stacks two or more queries with matching columns on top of each other, like combining January, February, and March exports into a single table.
Home → Append Queries

Merge Queries — joins two queries together based on a matching column, similar in spirit to VLOOKUP or INDEX/MATCH, but built to work reliably across entire tables rather than one lookup at a time.
Home → Merge Queries

You'll choose a join type
The most common is a Left Outer join, which keeps every row from your main table and pulls in matching data from the second table wherever it exists.

36.6 Loading the Result Back Into Excel

Once your Applied Steps produce clean, well-structured data, you close out of the Editor and choose where it should land.

How to get there: Home → Close & Load (or Close & Load To… for more control)

Options typically include:

  • Load as a Table on a worksheet
  • Load as a PivotTable Report directly
  • Load Only Create Connection — keeps the query available as a source for other queries or PivotTables, without dumping the data onto a sheet

36.7 Refreshing the Query

When new data arrives you don't repeat any manual cleanup. You just:

Data → Refresh All

Power Query reruns every recorded step, in order, against the new data, and the cleaned result updates automatically ready to feed straight into your PivotTables, charts, and dashboard.

Where Power Query Fits in the Bigger Picture

Updating the full workflow from earlier in this guide, Power Query slots in right after data enters Excel and before manual analysis begins:

34. The Complete Excel Workflow

Putting it all together, the journey from a blank workbook to a finished dashboard follows this path:

OPEN EXCEL
     ↓
CREATE WORKBOOK
     ↓
ENTER DATA
     ↓
FORMAT DATA
     ↓
CLEAN DATA
     ↓
ORGANIZE DATA
     ↓
WRITE FORMULAS
     ↓
USE FUNCTIONS
     ↓
SORT & FILTER
     ↓
ANALYZE DATA
     ↓
CREATE PIVOTTABLES
     ↓
CREATE CHARTS / PIVOTCHARTS
     ↓
ADD SLICERS
     ↓
BUILD DASHBOARD
     ↓
REPORT INSIGHTS
Enter fullscreen mode Exit fullscreen mode

The mindset worth carrying forward: don't start with a chart start with good data. A beautiful dashboard built on poorly structured data is still a poor analysis.

35. Excel Cheat Sheet: Where Do I Find It?

I want to… Go to…
Change font Home → Font
Change font colour Home → Font Color
Change cell colour Home → Fill Color
Add borders Home → Borders
Wrap text Home → Wrap Text
Merge cells Home → Merge & Center
Change number format Home → Number
Highlight values Home → Conditional Formatting
Find/replace text Home → Find & Select → Replace
Sort data Data → Sort
Filter data Data → Filter
Remove duplicates Data → Remove Duplicates
Flash Fill Data → Flash Fill
Data Validation Data → Data Validation
Freeze rows/columns View → Freeze Panes
Create a Table Insert → Table
Create a PivotTable Insert → PivotTable
Create a PivotChart Insert → PivotChart
Create a normal chart Insert → Charts
Add a slicer PivotTable Analyze → Insert Slicer
Connect a slicer to multiple PivotTables Right-click slicer → Report Connections
Create shapes for a dashboard Insert → Shapes
Find formulas/functions Formulas

Finally

Learning Excel isn't about memorizing hundreds of buttons. It's about understanding what problem each tool solves and where that problem sits in the bigger picture of turning raw data into a decision someone can act on.

If you remember only one framework from this guide, remember:

  1. Enter — put your data into Excel.
  2. Format — make the information readable.
  3. Clean — fix missing values, inconsistent labels, duplicates, invalid dates, and other data-quality problems.
  4. Organize — use tables, sorting, filtering, validation, and freeze panes.
  5. Calculate — use formulas and functions like SUM, AVERAGE, MEDIAN, IF, COUNTIF, and XLOOKUP.
  6. Analyze — use conditional functions, lookups, and PivotTables to actually answer questions.
  7. Visualize — pick the chart that fits the question, not just the one that looks nicest.
  8. Report — bring KPIs, charts, PivotTables, and slicers together into one interactive dashboard.

That progression is what takes you from someone who can type numbers into Excel to someone who can use Excel to clean, analyze, and communicate insight from data.

Top comments (0)