Microsoft Excel for beginners in Data Analytics

Microsoft excel is a spreadsheet program used to store,organize,calcualte,analyze and visualize data with the aim of drawing insights for informed decision making.

It is widely used in business,accounting,education,research and data analysis.This article will illustrate the basics required for beginners to understand use and data manipulation for simple analysis.

Data in excel is presented on a work sheet that has columns(vertical group of cells labeled A,B,C,..at the top right) and rows(horizontal group of cells labeled 1,2,3,..at the left).Columns intersects with rows to form cells where real data elements are keyed in.

How to save work done in excel

1. Open an excel work book
2. Click on file at the top left of the ribbon
3. Click Save As
4. Choose a file/folder (e.g Desktop, Documents)
5. Type in a file name of your choice (e.g Dataset 1)
6. Important to ensure the file type is excel workbook(*.xlsx) and click Save.

Excel functions and their uses

1.Mathematical functions majorly used for calculations.
Function Use
SUM Adds numbers in a range
AVERAGE Finds the means of numbers
MIN Returns the smalest value
MAX Returns the largest value
ROUND Rounds numbers to the nearest decimal place
COUNT Counts numeric values

AVERAGE
AVERAGE(K2:K15)

MINIMUM
MIN(K2:K15)

MAXIMUM
MAX(K2:K15)

Data Cleaning
The most common methods used during data cleaning includes text nd number formatting, alignments(horizontal, vertical,wrap, merge and center) borders and fill colors and conditional formatting.This is also aimed at removing duplicates and blanks.

Handling missing values:
Find and highlight missing values
Select data range/column -> go to home on the ribbon -> select conditional formatting -> new rule -> select format only cells that contains blanks -> select a color and click okay.

Removing duplicates:
Highlight the dataset -> click home -> Conditional formatting -> highlight cell rules -> duplicate values -> select a color -> Ok.

Fixing data types:
Format numeric values as numbers,currencies as currency, texts as Text, dates as dates.They are crucial for right calculations.

VLOOKUP
VLOOKUP(10003,A2:E15,4,FALSE)

Pivot Tables:
Useful for in depth data analysis of a given dataset.Allows use of slicers, graphic and dashboard creation.
Steps
Select your data
. Go to insert -> Pivot Table
. Choose where to place, as a best practice choose new worksheet
. Select the important indicators or drag to rows, columns and values.

Summary

Excel is still a highly reliable tool that has vast use for data analysis in multiple fileds and still stands out among other softwares.Its highly compartible and can be used along with other tools like PowerBI,SQL and Python.