Introduction to Microsoft Excel for Data Analytics

What is Excel?

Microsoft Excel is a spreadsheet program that helps you collect, organize, analyze, and visualize data.

Key Components

• Rows: Horizontal lines of cells, numbered from 1, 2, 3, etc.
• Columns: Vertical lines of cells, labeled A, B, C, etc.
• Cells: The intersection of a row and a column, where data is entered.

Excel Interface

• Taskbar: The bar at the top containing tools and options for your spreadsheet.
• Ribbon: The main toolbar with tabs like Home and Insert for different functions.
• Quick Access Toolbar: A small toolbar for frequently used commands like Save and Undo.
• Formula Bar: Displays the content of the active cell and allows editing.
• Name Box: Shows the address of the active cell (e.g., A1).
• Worksheet Tabs: Each tab at the bottom represents a separate sheet in the workbook.
• Workbook: The entire Excel file that contains one or more worksheets.

Key Features

• Saving: Use File > Save As to save your work for the first time.
• Quick Save: Press Ctrl + S to save changes quickly.

Formatting and Cleaning Data in Excel

Text Formatting

To format text as Bold, Italic, or Underline, and to adjust Font Size and Type, select the desired cells (e.g., A1:D1), navigate to the Home tab, and in the Font group, click B for Bold, I for Italic, U for Underline, change the font type (e.g., Calibri, Arial), adjust the font size (e.g., from 11 to 14), and select a font color.

Alignment

Alignment determines how content is positioned within a cell:

1. Select the cells you want to format.
2. Go to the Home tab and find the Alignment group.
3. Choose from the following options:
   • Horizontal Alignment: Align Left, Center, or Align Right.
   • Vertical Alignment: Top, Middle, or Bottom.
   • Wrap Text: Allows long text to display on multiple lines within the same cell.
   • Merge & Center: Combines multiple cells into one and centers the text.

Number Formatting

Number formats modify how numbers appear without changing their actual values. Common formats include:

Steps:

1. Select the cells containing numbers.
2. Go to the Home tab and find the Number group.
3. Use the dropdown menu to select the desired format (e.g., Number, Currency, Percentage, Short Date).

Conditional Formatting

Conditional Formatting automatically applies formatting based on specific rules:

1. Select the data range (e.g., B2:B20).
2. Go to the Home tab and click on Conditional Formatting.

1. You can use Highlight Cell Rules to format cells based on conditions like Greater Than or Less Than, apply Top/Bottom Rules to emphasize the highest or lowest values, and utilize Data Bars to visually represent values by filling cells proportionally.

Removing Duplicates

To eliminate duplicate entries in the range A1:U877, first, select the dataset. Then, go to the Data tab and choose the option to Remove Duplicates, ensuring the "My data has headers" box is checked if your first row contains titles.

The "Employee ID" is used as the unique identifier to detect and remove duplicate entries in the dataset shown in the image. Utilizing a unique identifier is crucial to ensure each employee is represented only once, maintaining the integrity and accuracy of the data.

Sorting and Filtering Data

Sorting Data

Sorting allows you to rearrange the order of rows based on the values in selected columns.
You can sort text in alphabetical order (A to Z or Z to A), numbers from smallest to largest or largest to smallest, and dates from oldest to newest or newest to oldest.

To perform a basic sort:

1. Click a cell in the column you want to sort.
2. Go to the Data tab and choose Sort A to Z or Sort Z to A.
3. Excel will sort the entire table based on the selected column.

For a more advanced, multi-level sort:
Add additional sort levels by clicking Add Level (e.g., sort by First Name A-Z first, then by Department A-Z and finally by Hire Date- Oldest to Newest).

Filtering Data

Filtering allows you to hide rows that don't meet specific criteria and show only the rows that do. This helps you quickly find and focus on the data you need.

To apply a filter:
Click anywhere in your table (ensure it has headers).
Go to the Data tab and click Filter (or on the Home tab, click Sort & Filter > Filter). Dropdown arrows will appear on the header cells.

Using the dropdown arrows to check or uncheck specific values, or apply text, number, or date-based filters.
For example, if you want to filter data for employees in the HR and Finance departments, click on the dropdown filter arrow in the Department Column Header, and select as follows:

Result after applying filter:

Freeze Panes

Freeze Panes is a useful feature that keeps specific rows or columns visible as you scroll through your worksheet. This is particularly helpful for keeping headers or important information in view.
Select the entire data range > Go to View Tab in the top ribbon > Freeze Panes

Options include:
-Freeze the Top Row
-Freeze the First Column
-Custom Freeze

Basic Calculations in Excel: Formulas and Functions

Excel provides two primary ways to perform calculations: formulas and functions.

Formulas

Formulas are user-defined expressions that allow you to perform calculations and manipulations on data. Formulas can be simple, such as =A1+B1, or more complex, involving multiple operations and cell references.

To create a formula:

1. Start the formula with an equal sign (=).
2. Include cell references, values, and mathematical operators (e.g., +, -, *, /).
3. The formula will calculate the result and display it in the cell.

Example: To calculate the sum of cells A1 and B1, the formula would be =A1+B1.

Functions

Functions are pre-built, predefined formulas that perform specific calculations or operations. Functions have a unique syntax and require arguments to be provided.

To use a function:

1. Start with an equal sign (=).
2. Type the function name, followed by open and close parentheses ().
3. Inside the parentheses, provide the necessary arguments, separated by commas.

Example: To calculate the sum of the values in cells A1 through A10, the function would be =SUM(A1:A10).

Some common Excel functions include:

• SUM(): Adds up the values in a range of cells.
• AVERAGE(): Calculates the average of the values in a range of cells.
• COUNT(): Counts the number of cells in a range that contain numeric values.
• IF(): Performs a logical test and returns one value if the test is true, and another if the test is false.

Formulas	Functions
Formulas are user-defined expressions that perform calculations or manipulations on data.	Functions are pre-built, predefined formulas that perform specific calculations or operations.
Formulas can be simple, such as =A1+B1, or complex, involving multiple operations and cell references.	Functions have a specific syntax and require arguments to be provided, such as =SUM(A1:A10).
Formulas are flexible and can be customized to suit specific needs.	Functions are optimized for specific tasks and provide a standardized way to perform common calculations.
Formulas can include a combination of cell references, operators, and values.	Functions are identified by their unique names, such as SUM, AVERAGE, IF, etc.
Formulas are entered directly into cells, starting with an equal sign (=).	Functions are called within a formula, also starting with an equal sign (=).

Text Functions

Excel provides a set of text functions that help clean and standardize text data. These functions include:

UPPER(kenya): Converts all letters in the provided text to uppercase-KENYA
LOWER(KENYA): Converts all letters in the provided text to lowercase-kenya
PROPER(kenya): Capitalizes the first letter of each word in the provided text- Kenya
TRIM(" Kenyatta University "): Removes any leading, trailing, or extra spaces between words in the provided text- KenyattaUniversity
LEFT("Mombasa",3): Returns the specified number of leftmost characters(3) from the provided text- Mom
RIGHT("Nakuru",4): Returns the specified number of rightmost characters from the provided text- Kuru
MID(text, start_num, num_chars): Returns the specified number of characters from the middle of the provided text, starting from the indicated position.

Aggregate Functions

Excel also provides a set of aggregate functions that can perform calculations on a range of cells. These functions include:

SUM(number1, [number2], ...): Adds the values in the specified range or list of numbers.
AVERAGE(number1, [number2], ...): Calculates the average (mean) of the values in the specified range or list of numbers.
MIN(number1, [number2], ...): Returns the smallest number in the specified range or list of numbers.
MAX(number1, [number2], ...): Returns the largest number in the specified range or list of numbers.
COUNT(value1, [value2], ...): Counts the number of cells that contain numeric values in the specified range or list of values.

For example, in the following data:

• Average performance score, column I, is calculated as follows:

-Sum on bonus, column K, is calculated as follows:

-Minimum work experience (years),column N, is calculated as follows:

Logical Functions

Excel's logical functions allow you to perform conditional operations and make decisions based on specific criteria. These functions include:

IF(logical_test, value_if_true, value_if_false): Returns one value if a specified condition is true, and another value if the condition is false.
For example, to promote employees with over 20 years work experience, and retain the rest in their current positions, input as follows:

AND(logical1, [logical2], ...): Returns TRUE if all of the given conditions are true, and FALSE otherwise.
For example, To identify employees with over 20 years experience AND more than 10 projects, input as follows:

OR(logical1, [logical2], ...): Returns TRUE if any of the given conditions are true, and FALSE otherwise.
For example to identify employees with over 20 years experience OR more than 10 projects, input as follows:

NOT(logical): Returns the opposite of the given logical value (TRUE becomes FALSE, and FALSE becomes TRUE).

NESTED IF: Allows for the nesting of multiple IF statements to create complex conditional logic.
Imagine you have a spreadsheet where you need to determine the grade of a student based on their test score. The grading system is as follows:

• If the score is 90 or above, the grade is "A".
• If the score is between 80 and 89 (inclusive), the grade is "B".
• If the score is between 70 and 79 (inclusive), the grade is "C".
• If the score is between 60 and 69 (inclusive), the grade is "D".
• If the score is below 60, the grade is "F".

The nested IF function works like this:

1. The first IF statement checks if the score is 90 or above. If it is, the grade is "A".
2. If the first condition is not met, the function checks if the score is between 80 and 89 (inclusive). If it is, the grade is "B".
3. If the first two conditions are not met, the function checks if the score is between 70 and 79 (inclusive). If it is, the grade is "C".
4. If the first three conditions are not met, the function checks if the score is between 60 and 69 (inclusive). If it is, the grade is "D".
5. If none of the previous conditions are met, the grade is "F".

=IF(score>=90, "A", IF(score>=80, "B", IF(score>=70, "C", IF(score>=60, "D", "F"))))

Lookup Functions

Excel's lookup functions enable you to find and retrieve data from a range or table. These functions include:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]): Looks up a value in the leftmost column of a table and returns the value in the same row from a specified column.
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]): Looks up a value in the topmost row of a table and returns the value in the same column from a specified row.
INDEX(array, row_num, [col_num]): Returns the value in a specified cell in an array.
MATCH(lookup_value, lookup_array, [match_type]): Finds the position of a value in an array that matches the specified value.
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]): Performs a flexible lookup, allowing for more advanced search options.

Conditional Math Functions

Excel's conditional math functions allow you to perform calculations based on specific criteria or conditions.
These functions include:

1. SUMIF(range, criteria, [sum_range]): Sums the values in a range that meet a single condition. Provide the range to evaluate, the criteria to match, and an optional range to sum. For example, to find the sum of the salary and bonus for employees who's salary is above Ksh.50,000, input the following:

1. SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...):
   Sums the values in a range that meet multiple conditions.
   Provide the range to sum, followed by pairs of criteria ranges and their respective criteria.

2. COUNTIF(range, criteria):
   Counts the number of cells in a range that meet a single condition.
   Provide the range to evaluate and the criteria to match.
   For example, to count the number of employees aged above 40 years:

1. COUNTIFS(range1, criteria1, [range2, criteria2], ...):
   Counts the number of cells in a range that meet multiple conditions.
   Provide the ranges to evaluate and their respective criteria, in pairs.

2. AVERAGEIF(range, criteria, [average_range]): Calculates the average of values in a range that meet a single condition.
   Provide the range to evaluate, the criteria to match, and an optional range to average.

3. AVERAGEIFS(average_range, criteria_range1, criteria1, ...): Calculates the average of values in a range that meet multiple conditions.
   Provide the range to average, followed by pairs of criteria ranges and their respective criteria.
   For example, to find the average age of married employees with a performance score above 6, input as follows:

1. MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...): Returns the largest value in a range that meets one or more conditions. Provide the range to evaluate, followed by pairs of criteria ranges and their respective criteria.

Understanding Pivot Tables in Excel

What is a Pivot Table?

A Pivot Table is a powerful data analysis tool in Excel that allows you to summarize, analyze, explore, and present your data in a concise format.
It enables you to extract meaningful insights from large datasets by organizing and aggregating data in a structured way.

Key Features of Pivot Tables

• Dynamic Data Analysis: Easily rearrange data to view it from different perspectives.
• Data Summarization: Quickly calculate sums, averages, counts, and other statistics.
• Interactive Filtering: Use filters to focus on specific data points.

How to Create a Pivot Table

1. Prepare Your Data: Ensure your data is organized in a tabular format with headers. Each column should represent a different variable.
2. Select Your Data: Click anywhere in the data range you want to analyze.
3. Insert a Pivot Table:
   • Go to the Insert tab on the Ribbon.
   • Click on PivotTable.
   • In the dialog box, confirm the data range and choose where to place the Pivot Table (new worksheet or existing worksheet).
4. Build Your Pivot Table: A new sheet will open with a PivotTable Field List on the right side.

The Four Fields of a Pivot Table

1. Filters: Allows you to filter the entire table based on specific criteria.
2. Columns: Displays data across the top of the table, defining column headers.
3. Rows: Displays data down the side of the table, defining row labels.
4. Values: Contains the data to be summarized or aggregated, such as sums or counts.

For example, the below pivot table shows the average salary per department, for male and female employees, with a filter on marital status, showing data for only those who are married:

Understanding Slicers in Excel

What are Slicers?

Slicers are visual filters in Excel that provide an intuitive way to filter data in Pivot Tables and regular tables. They allow users to quickly and easily filter data by clicking on buttons, enhancing the interactivity of data analysis.

Key Features of Slicers

• User-Friendly Interface: Slicers present filtering options in a clear, visual format, making it easy to understand which filters are applied.
• Multiple Selections: Users can select multiple items in a slicer at once, allowing for more complex filtering.
• Real-Time Filtering: Changes made through slicers immediately update the data displayed in the Pivot Table or table.

How to Create a Slicer

1. Select Your Pivot Table: Click on the Pivot Table you want to filter.
2. Insert a Slicer:
   • Go to the PivotTable Analyze tab (or Options in older versions).
   • Click on Insert Slicer.
3. Choose Fields: In the dialog box, select the fields for which you want to create slicers (e.g., Region, Salesperson).
4. Position the Slicer: After inserting, you can move and resize the slicer on your worksheet.

Using Slicers

• Filter Data: Click on the buttons within the slicer to filter the data displayed in the Pivot Table.
• Clear Filters: Use the clear filter button (typically an icon with an eraser) in the slicer to remove all filters at once. The below image shows slicers for the categories: Department, Marital Status and Education level. As peer filters applied in the slicers, the data displayed is for the HR, Finance and IT departments, as well as for those who are married:

Introduction to Charts in Excel

Charts in Excel are graphical representations of data that help visualize trends, patterns, and comparisons.
They make it easier to interpret complex data sets and communicate insights effectively.
Excel offers various chart types, each suited for different kinds of data analysis.

Comparison of Chart Types

Chart Type	Description	Best Used For
Column Chart	Displays data using vertical bars, with categories on the x-axis and values on the y-axis.	Comparing values across categories.
Bar Chart	Similar to column charts but uses horizontal bars.	Comparing values across categories, especially with long category names.
Line Chart	Represents data points connected by lines, showing trends over time.	Displaying trends or changes over time.
Pie Chart	Circular chart divided into slices, representing proportions of a whole.	Showing percentage or proportional data.
Combo Chart	Combines two or more chart types, such as line and column charts, in a single chart.	Comparing different data series with different value ranges.

Bar chart showing total bonus by education level

Column chart showing average salary by department and gender

Line chart showing average performance score by hire month

Pie chart showing employee count by remote work status

Dashboarding in Excel

What is Dashboarding?

Dashboarding in Excel refers to the process of creating a visual interface that consolidates and presents key metrics and data insights in a single view.
Dashboards are designed to provide quick access to important information, enabling users to monitor performance, track progress, and make informed decisions efficiently.

Key Features of Excel Dashboards

• Data Visualization: Dashboards utilize various charts, graphs, and tables to represent data visually, making it easier to understand complex information at a glance.
• Interactivity: Users can interact with dashboards through features like slicers, buttons, and drop-down lists, allowing for dynamic data exploration.
• Real-Time Data Updates: Dashboards can be linked to live data sources, ensuring that the information displayed is always current and relevant.

Components of an Excel Dashboard

1. Charts and Graphs: Visual representations of data (e.g., bar charts, line charts, pie charts) that highlight trends and comparisons.
2. Tables: Organized data sets that provide detailed information and support the visual elements.
3. Slicers and Filters: Tools that allow users to filter data dynamically, enabling them to focus on specific metrics or time periods eg; department, marital status, education level and gender.
4. Key Performance Indicators (KPIs): Metrics that are critical to the success of an organization, often displayed prominently on the dashboard for quick reference eg; Total salary, Total bonus, Average age, Average performance score

Conclusion

Excel is a powerful tool for data analytics, enabling users to organize, analyze, and visualize data efficiently. Its features, such as Pivot Tables and charts, facilitate quick insights and informed decision-making.