Data Analysis with

MS Excel 365
Ethiopian Capital Market Authority
April 01-06, 2024
Addis Ababa
Module 2: Working with Formulas and
Functions

Apply basic formulas for calculations.

Learning Objectives: Understand and use common Excel functions.
Utilize relative and absolute cell references.

Formula construction and application

Key Skills: Utilizing functions for calculations and data manipulation
Understanding cell referencing
Formulas and Functions

Formulas: Expressions within Excel that perform calculations based on values in cells. They
typically begin with an equal sign (=) and can include cell references, numbers, and operators (+, -,
*, /).

Functions: Predefined formulas in Excel that perform specific tasks. They have a recognizable name
(e.g., SUM, AVERAGE, VLOOKUP) and often require arguments (inputs) to complete the calculation.
Functions save you time and effort compared to writing complex formulas from scratch.
What are formulas?
o Formulas are like recipes for your data! They use cell references, numbers, and operators
(+, -, *, /) to perform calculations and automate tasks. Always start a formula with an
equal sign (=).
o Examples with Basic Operations:
o Addition: =A1 + B1 (Adds the values in cells A1 and B1)
o Subtraction: =C1 - D1 (Subtracts the value in D1 from the value in C1)
o Multiplication: =E1 * F1 (Multiplies the values in cells E1 and F1)
o Division: =G1 / H1 (Divides the value in G1 by the value in H1)

o Open the file "Day 2 Demo.xlsx" and Do the Above Operations.

What are functions?
o Functions are like pre-made shortcuts in Excel. They perform specific calculations or tasks with ease.
Each function has a name (e.g., SUM, AVERAGE) and often requires arguments (inputs) within
parentheses to complete its job.
o Common Functions and Examples:
o SUM: Adds a range of numbers. =SUM(A1:A10) (Adds values in cells A1 to A10)
o AVERAGE: Calculates the average of a set of numbers. =AVERAGE(B2:B15) (Finds the
average of cells B2 to B15)
o COUNT: Counts the number of cells containing numeric values. =COUNT(C1:C20) (Counts
cells C1 to C20 with numbers)
o MIN: Finds the smallest value in a range. =MIN(D3:D8) (Returns the minimum value from D3
to D8)
o MAX: Finds the largest value in a range. =MAX(E4:E25) (Returns the maximum value from E4
to E25)
o Open the file "Day 2 Demo.xlsx" and Do the Above Operations.
Handling Errors in Calculations in Excel
365
o Sometimes, formulas might not work as expected, resulting in error messages. Don't panic! Excel provides tools to identify
and handle these errors.
o Common Errors and Meanings:
o #DIV/0: Division by zero (e.g., trying to divide a number by zero).
o #VALUE!: Incorrect data type used in a formula (e.g., trying to add text and numbers).
o #NAME?: Excel can't recognize the function name (e.g., misspelled function name).
o #REF!: Cell reference refers to a deleted cell or has an invalid reference.
o The IFERROR function is your error-handling hero! It checks for errors in a formula and displays a specific message if
an error occurs. Otherwise, it shows the calculated result.
o Example:
o =IFERROR(A1/B1, "Error: Division by Zero")
o This formula divides the value in A1 by the value in B1. If B1 contains zero, it displays "Error: Division by Zero" instead of an
error code.
o Open the file "Day 2 Demo.xlsx" and Do the Above Calculations.
Absolute vs Relative Referencing
o Formulas rely on cell references to locate and use data within your worksheet. Understanding the two main referencing styles, absolute and
relative, is crucial for accurate calculations.
o Relative References: (The Default)
o These references are relative to the cell containing the formula.
o When you copy a formula with relative references, the cell references automatically adjust based on the new location.
o Example:
o =A2 + B2 (This formula is in cell C2)
o If you copy this formula to cell C3, it will automatically change to =B3 + C3 because the references are relative to their positions.

o Absolute References: (Locked in Place)

o Use dollar signs ($) to lock down specific row and/or column references in a formula.
o Absolute references remain unchanged regardless of where you copy the formula.
o Example:
o =$A$1 + B1 (This formula is in cell C1)
o If you copy this formula to cell C2, it will stay the same =$A$1 + B2 because the reference to cell A1 is absolute.

o Open the file "Day 2 Demo.xlsx" and Do Error Handling.

Autosum and Autofill
o Excel offers built-in features like AutoSum and AutoFill to streamline your workflow and save you valuable time.
o AutoSum
o AutoSum is a magical button that automatically creates formulas to sum a range of numbers.
o How to Use It:
o Select a cell next to the numbers you want to add.
o Click the AutoSum button (Σ) on the Home tab.
o Excel will automatically generate a SUM formula that includes the selected range.
o Press Enter to confirm the calculation.

o AutoFill allows you to quickly copy and extend data or patterns across a range of cells.
o How to Use It:
o Enter data in one or more cells.
o Click and drag the small fill handle (black square) in the corner of the cell down or across to extend the pattern.
o Options: Excel can intelligently fill patterns (like dates or numbers in a sequence), or simply copy the original content

o Open the file "Day 2 Demo.xlsx" and Do Error Handling.

Flashfill
o Flash Fill is a hidden gem in Excel that automates repetitive data manipulation based on patterns you establish.
o How it Works:
o Enter a Sample: Start by entering your desired outcome in one or two cells based on a pattern you see in
your existing data.
o Flash Fill Does the Magic: Highlight the sample cell(s) and the data range you want to apply the pattern to.
Click the Flash Fill button (found in the Data tab or use the shortcut Ctrl+E).
o Flash Fill in Action (Example):
o Imagine you have a list of first names in column A and last names in column B. You want to combine them
into full names in column C.
o In cell C2, type the full name (e.g., John Smith).
o Highlight cell C2 and the data range in columns A and B.
o Click Flash Fill (or Ctrl+E).
o Excel will recognize the pattern and fill column C with complete names based on your sample in C2.
o Open the file "Day 2 Demo.xlsx" and Do Error Handling.
Named Ranges
• Think nicknames for cell ranges!
• Named ranges give a descriptive name (e.g., "SalesData") to a specific range of
cells, making formulas and navigation easier.
• Managing Named Ranges:
• Create: Select a cell range and define a name in the "Name Box" (top left corner)
or through the "Formulas" tab options.
• Edit: Modify existing names through the "Name Manager" (Formulas tab) to keep
your names accurate and organized.
• Delete: No longer need a named range? Simply delete it from the "Name
Manager."
Sorting
• Needed to organize data based on multiple criteria.
Excel lets you sort by up to three columns
simultaneously!
• Example:
o Sort a list of employees first by "Department" (A-Z), then by "Last Name" (A-Z) within each department.
o Select the data range you want to sort.
o Go to the "Data" tab and click "Sort."
o In the "Sort By" dropdown, choose the primary column (e.g., Department).
o Set the "Sort On" to "Values" and "Order" to "A to Z."
o Click "Add Level" and choose the secondary column (e.g., Last Name).
o Repeat steps 4 and 5 for sorting order (A to Z).
o Click "OK."
Sort using Custom list

o Sorting Using a Custom List:

o Sometimes, data might not be in standard alphabetical order. Use custom lists to define a specific sorting sequence.

o Example:
o Sort a list of clothing sizes (XS, S, M, L, XL) according to their actual size order.
o Go to the "Data" tab and click "Sort."
o In the "Sort By" dropdown, choose the column containing sizes.
o Click the "Order" dropdown arrow and select "Custom List."
o Create a custom list with the desired order (XS, S, M, L, XL) and click "OK."
o Click "OK" again to sort the data using your custom list.

o Let's Practice Sorting!

Filters
oWhat are Filters?
o Filters are a powerful tool in Excel that allow you to quickly focus
on specific subsets of your data based on defined criteria.

oHow do Filters Work?

o Select the data range you want to filter.
o Go to the "Data" tab and click "Filter." Dropdown arrows will appear in your header row.
o Click the dropdown arrow for a column you want to filter.
o Choose the criteria you want to apply (e.g., select specific values, filter by text).
o Only rows matching your criteria will be displayed. Click "Clear" in the filter dropdown to
remove filters and see all data again.
Unique function
o What is the UNIQUE Function?
o The UNIQUE function is a handy tool in Excel that extracts a list of all distinct
(unique) values from a range or array of data.

o How Does It Work?

o Select a blank cell where you want the unique values to appear.
o Type =UNIQUE( and then enter the cell range containing the data you want
to analyze (e.g., A1:A10).
o Press Enter.
Exercise 04: Financial Analysis for a Local Coffee Shop (45 Minutes)

• Instructions:
• Open a new workbook and enter the data provided above.
• Complete the "Total" column using formulas. (This exercise covers formulas and error handling).
§ Notice an error (#VALUE!) in cell D5 for Brownie. Investigate and fix the formula. (This exercise covers
error handling).
§ Calculate the total revenue for all products sold. (This exercise covers formulas and AutoSum).
§ Use an appropriate formula to achieve this and consider using AutoSum for efficiency.
§ Identify the top 3 best-selling coffee drinks. (This exercise is for analysis, but formulas can be used to
sort data).
§ Use a formula or sorting techniques to determine the top 3 revenue generators among the coffee drinks
(Latte, Cappuccino, Americano, Mocha, Macchiato).
§ Calculate the total cost of goods sold (COGS) if the average cost per unit of coffee is ETB 5. (This
exercise covers formulas).
§ Create a formula to calculate the COGS considering the quantity of coffee drinks sold.
 Exercise 04: Financial Analysis for a Local Coffee Shop (45 Minutes)

• Questions:
• What is the average revenue per unit sold across all products? (This exercise covers
formulas).
 Create a formula to calculate the average revenue considering both coffee drinks and bakery items.
• What is the profit margin for the coffee shop, considering a fixed monthly overhead cost of
ETB 5,000? (This exercise is for analysis, but formulas can be used for calculations).
 Use the calculated total revenue and COGS along with the fixed overhead cost to determine the
profit margin.

• Bonus Challenge:
• Use conditional formatting to highlight the top 3 best-selling coffee drinks based on revenue.
(This exercise covers conditional formatting).
Exercise 05: Real World Example
• Instructions:
Loan Disbursement by Banks in Ethiopia
• Open a workbook named "04_disbursement_banks_client_sector.xlsx".
• Compute total disbursement by bank, sector and client
• Compute growth rate of disbursement by bank, sector and client
• Compute share of disbursement by bank, sector, client

Loan Collection by Banks in Ethiopia

• Open a workbook named "03_collection_banks_ind_sector.xlsx".
• Compute total collection by bank, sector and client
• Compute growth rate of collection by bank, sector and client
• Compute share of collections by bank, sector, client
Exercise 05: Analyzing Product Sales Data (30 Minutes)
• Questions:
• What is the best-selling product (highest total sales) based on the
sorted data? (This exercise is for analysis based on sorted data).
• Apply a filter to find all clothing items (categories "Tops" and
"Bottoms") priced above ETB 100. (This exercise covers filtering).
 How many clothing items meet this criteria?
• Are there any trends you can observe in terms of sales and
pricing based on product category? (This exercise is for analysis
based on filtered and sorted data).