Excel For Data Analytics: Beginner Friendly Overview.
Source: Dev.to
Excel is one of the main tools used in data analysis.
With Excel you can:
- manipulate data,
- summarize it with pivot tables,
- visualize it, and
- perform quick statistics.
Excel is typically used for small‑to‑medium data sets. It still lets you perform all the usual data‑transformation tasks.
Here is the interface of an Excel workbook (2019 version).
1️⃣ Excel Skills to Learn
- Data cleaning – remove blank spaces, incorrect, and outdated information.
- Conditional formatting – highlight cells based on rules.
- Formulas – perform calculations.
- Sorting & filtering – organize data.
- Graphing & charting – create visualizations.
- Pivot tables – calculate, summarize, and analyze data.
- Aggregation – combine data for analysis.
1️⃣ Basic Formatting and the SUM Function
Formatting is a key step in cleaning data sets. Useful formatting tools include:
- Conditional formatting
- Format as Table
- Sort & filter
- Find & select
SUM Function Example
2️⃣ MAX, MIN, and AVERAGE Functions
Excel provides many built‑in functions. Below are the five basic ones we’ll cover: SUM, AVERAGE, COUNT, MAX, and MIN.
Step 1 – Add a New Worksheet
- Click the + button at the bottom‑left of the workbook.
- Right‑click the new sheet (Sheet2) and rename it Functions.
Step 2 – Explore the Function Library
- Open the Formulas tab.
- If needed, click the down‑arrow at the far right of the toolbar to reveal the Function Library group.
- Click the dropdown under AutoSum to see the five basic functions:
| Function | Description |
|---|---|
| AVERAGE | Returns the arithmetic mean of the referenced numeric values. |
| COUNT | Counts the number of numeric cells in the range. |
| MAX | Returns the highest numeric value in the range. |
| MIN | Returns the lowest numeric value in the range. |
| SUM | Adds all the numbers in the range. |
MAX Function
MIN Function
AVERAGE Function
COUNT Function
These are the basic concepts of Excel. When you dive deeper you’ll encounter more advanced functions such as VLOOKUP, XLOOKUP, HLOOKUP, and many others. Pivot tables and dashboards are also powerful tools for summarizing and visualizing data.
What’s Next?
In my next post I’ll show how to go from data cleaning to interactive dashboards using larger data sets.
See you there!
# SUM function
- Aggregate data for analysis