A beginner's guide to MS Excel for Data Analytics.

#beginners #programming

Ms Excel is a Powerful tool developed by Microsoft that enables you to store, organize, analyze, and visualize data. It is a spreadsheet that organizes data in Rows, Columns and cells.

It is a useful tool for data analytics that makes it easy to derive insights from large datasets.

In this article, I will walk you through the different functions, formulas, charts, and Pivot tables and dashboards.

Below is an example of an Excel Interface:

Let's define a few terms

Workbook: Is a collection of spreadsheets.
Worksheet/spreadsheets: is a page or sheet in the Workbook where data can be entered and manipulated using functions and formulas.
Cell: This is an Intersection between a row and a column where you can input data.
Dataset: A collection of related data arranged in Rows and Columns stored in a workbook.
Range: Is a collection of cells that can be used to perform formulas and functions.
Chart: Is a graphical visualisation tool that displays data, e.g., a linegraph or Piechart. It works by plotting data points on a graph.
Dashboard: Is a single page that displays all relevant charts and Key Insights at a glance.
Pivot charts: These are summary tables extracted from a large dataset that help to derive useful insights.

1. Formulas and Functions

Formulas are special equations that allow you to calculate values on a spreadsheet.

All formulas must start with an equal sign (=).

Formulas contain the following basic math operators:

Addition: + e.g A1+B1
Subtraction: - e.g A1-B1
Multiplication: * e.g A1*B1
Division: / e.g A1/B1

Functions are special inbuilt formulas that have been created by Excel to manipulate data on a spreadsheet.

> Examples of Functions include:

IF(): Is a logical function that will output data according to a specific test/condition. If the condition e.g =if(A1<10,"Low","High")
AVERAGE(): Is a function that calculates the average of a range of cells that contains a list of values. e.g =AVERAGE(A1:A10)
SUMIF(): Is a function that calculates the totals of a specific product and outputs them in a specified column. e.g =sumif(A:A,"Pencils",B:B)
COUNT(): Is a function that counts the number of cells in a specific range of values. It helps to determine the size of a list of items.
e.g = COUNT(A1:A50)

2. Data Validation & Conditional Formatting:

These are tools that allow you to customize how your data appears in a spreadsheet.

Data Validation: is an inbuilt feature that allows users to restrict the data that is entered into a cell. This ensures the data is accurate and consistent. Examples of data validation include: "Decimals", "Whole number" etc

Conditional formatting: Is an inbuilt feature that allows a user to apply a specific formatting criteria to a cell or range of cells.

3. Filtering & Sorting:

Filtering: Is a tool found in the data tab that allows you to display data according to a specified Criteria.

Sorting: Is a tool also found in the data tab that allows you to arrange data in a specific order or criteria e.g sorting in ascending or descending order.