Introduction to MS Excel for Data Analytics

Learning data science and analytics as a beginner? This article got you covered. Microsoft Excel is one of the most powerful tools used in data analytics and it is beginner-friendly.With wide range of uses in corporate worlds,Excel can turn raw data into meaningful and interpretable insights.
By the end of this article, I expect you to:

• Understand Microsoft Excel and its uses in data analytics
• Identify rows,columns and cells in a worksheet
• Create and organize data in table format
• Use basic Excel functions
• Learn Data cleaning by sorting,filtering and removing duplicates
• Create pivot tables for data summarizing
• Learn data visualization by use of simple charts
• Understand creation of dashboards for data reporting

Commonly used Excel Terms

1. Cell- A single box in Excel where data is entered.*(example:E1)
2. Column - A vertical group of cells labelled using alphabets (example:A,B,C)
3. Row - A horizontal group of cells labelled numerically (example:1,2,3)
4. Worksheet -A single page in Excel where data is entered and analyzed.
5. Workbook -This is an Excel file that contains one or more worksheets.
6. Dataset - A collection of related data normally in columns and rows.
7. Formula -An equation in Excel used to do calculations (example:B1+C2)
8. Function -A built-in formula that executes specific tasks. *(example: SUM,MAX,MIN)
9. Filter -This is used to display data that meets specific criteria
10. Sort - This is arranging data in a specific data (example; A-Z)
11. Pivot Table -A tool that is used to summarize and analyze large datasets since it calculates data automatically.
12. Charts -Visual representation of data
13. Dashboard -A single page that shows summaries of key data insights using charts and numbers.

1.Introduction to Excel

microsoft excel is a spreadsheet application by Microsoft used to organize,calculate,analyze and visualize data in rows and columns.
Its main functions include:

• Performing calculations using formulas and built-in functions.
• Organizing and managing data in tables
• Analyzing data by sorting, filtering and pivot tables.
• Data visualization by creating charts and graphs.
• Automating tasks using macros and basic scripting. Excel spreadsheet is made up of rows, columns, cells where each cell contains;
• Text(name,categories)
• Numbers
• Dates
• Formulas

Example of dataset

## organizing data for analysis
It is really important to ensure data being analysed is correctly structured i.e the columns and rows. Avoid having empty cells.

2.Data Cleaning

This involves:

• Removing duplicates data->remove duplicates
• Sorting data->sort A-Z OR Z-A
• Filtering data->filter

3.Basic Excel functions

1. Sum- adds numbers
   Example
   
   =SUM(E2:E877)
   

2. Average- finding the mean
   Example
   
   =AVERAGE(E2:E877)
   

3. Count- finding how many cells contain numbers
   Example
   
   =COUNT(E2:E877)
   

4. MIN- shows the minimum value in the cells
   example
   
   =MIN(E2:E877)
   

5. MAX- shows the maximum value in the cells
   example
   
   =MAX(E2:E877)
   

4. Pivot Tables

Pivot tables are used to summarize large datasets.
A pivot table is created by:
data->insert->pivot table and then you drag fields into rows,columns and values.

5.Charts in Excel

They help turn numbers into visuals that are easier to understand
We insert a chart by; Select data->insert->choose chart type
There are many chart types:

• column charts that compares categories
• line charts that shows trends over time
• pie chart that shows proportions and many more.

6.Dashboards

This is a single interactive worksheet that visually summarizes key data using charts,tables and KPIs.

In summary, mastering of excel is not difficult. It gives you a strong basis in order to advance to analytical tools such as Power BI and SQL.
I hope this guide provides a strong basic understanding of Excel and it's importance in data analytics.