INTRODUCTION TO EXCEL FOR

DATA ANALYSIS

D’SON ICT SOLUTIONS & ACADEMY

+234813157393
1
Introduction to Excel for Data Analysis
This lesson introduces students to the basic features, terminology, and structure of Microsoft
Excel. It sets a solid foundation for using Excel in data analysis by explaining its components,
purpose, and practical usage.

Lesson Objectives:

By the end of this lesson, students will be able to:

● Understand what Microsoft Excel is and its uses.

● Navigate the Excel interface.

● Define and identify core Excel terminologies.

● Understand Excel limitations.

● Apply basic operations using cells, ranges, and formulas.

What is Excel?

🔎 Explanation:

Excel is a spreadsheet program developed by Microsoft. It enables users to store, organize,

analyze, and visualize data using rows and columns.

1|Page
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 Why Excel for Data Analysis?

📌 Key Benefits:

● Stores large volumes of data in structured tables.

● Offers built-in functions and formulas for calculations.

● Visualizes data using charts and graphs.

● Encourages collaboration with features like co-authoring and comments.

💡 Practical Tip:

Use Excel when working with structured tabular data (like sales records, customer lists, etc.),
especially when rapid analysis and visualization are needed.

Limitations of Excel

⚠️ Important to Know:

● Maximum Rows: 1,048,576

● Maximum Columns: 16,384 (Column XFD)

● Dependent on system memory—too many open workbooks or formulas may cause

slowness.

2|Page
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
💡 Best Practice:

Use Excel for moderate-sized data sets. For larger datasets (e.g., millions of records), consider
using Power BI or databases like SQL.

Excel Interface Overview

Familiarize yourself with the main components of the Excel interface:

Component Description

Ribbon Toolbar with commands like Insert, Formulas, Data, Review, etc.

Workbook The entire Excel file (e.g., SalesReport.xlsx).

Worksheet A single tab within a workbook (like a page inside a book).

Formula Bar Shows the content or formula of the selected cell.

Cells The building blocks of a worksheet; where data is entered.

3|Page
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
🖼️ Activity:

● Open Excel.

● Explore tabs like Home, Insert, and Formulas.

● Click on different cells and watch the Formula Bar reflect their content.

Key Definitions and Terms

📘 Workbook

A file containing one or more worksheets. Think of it as the container for your entire project.

Example: “SalesData.xlsx” could contain multiple sheets like "Q1 Sales", "Q2 Sales", etc.
4|Page
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
📘 Worksheet

The tab inside the workbook, consisting of rows (numbers) and columns (letters).

Example: “January Budget” sheet inside “Household.xlsx”.

📘 Cell

The intersection of a column and a row (e.g., B4). Each cell stores data.

Activity: Click cell A1. Type: Welcome to Excel.

📘 Cell Reference

The unique identifier of a cell, combining column letter and row number.

Example: The top-left cell is A1; the 3rd column and 2nd row cell is C2.

5|Page
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
📘 Active Cell

The currently selected cell, highlighted by a green border.

Try This: Use arrow keys or mouse to move to a different cell and observe the border
change.

📘 Range

A group of two or more cells, either adjacent or non-adjacent.

Example: A1:A5 or A1, C1, E1.

Try This: Select a range like B2:B10 and highlight it.

6|Page
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
📘 Formula

A formula performs calculations or logical operations.

Syntax: All formulas start with an equal sign =

Example:

=A1 + B1
=SUM(A1:A10)
=AVERAGE(B2:B6)

📘 Formula Bar

Located above the worksheet, next to the fx symbol. It displays or edits formulas and cell
values.

Activity:

● Type =5+5 in a cell.

● Press Enter.

● Observe the result in the cell and the formula in the Formula Bar.

7|Page
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 Loading Workbooks in Excel

Lesson Objective:

● Learn how to open existing Excel workbooks.

● Understand how to navigate worksheets.

● Practice selecting data in a column and interpreting row count.

8|Page
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 Background:

Being able to load and explore a workbook is a foundational skill before you clean, sort, or
analyze the data.

Scenario:

You are given a historical stock price dataset for Google (GOOG). The data is saved in an Excel
workbook named:

1_1_loading_workbooks.xlsx

Inside this workbook is a worksheet called:

Google Monthly Stock Price

Step-by-Step Instructions:

✅ Step 1: Open the Workbook

1. Open Excel.

2. Go to the top menu and select File → Open.

3. Navigate to the folder named Workbooks.

4. Select the file: 1_1_loading_workbooks.xlsx.

5. Click Open.

9|Page
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
✅ Step 2: Access the Worksheet

● Once the file opens, locate and click the tab labeled:

Google Monthly Stock Price

This worksheet contains the monthly stock prices of Google.

✅ Step 3: Select Column A

● Click on the letter “A” at the top of the first column. This highlights all the values in
Column A (including the header).

● This column likely contains dates of stock price entries.

10 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 Step 4: Count the Rows in Column A

After selecting Column A, look at the bottom of the Excel window (in the status bar). Excel
may show a count of the selected rows, or you can use this method:

1. Scroll to the bottom of the data in Column A using the scroll bar.

2. Note the last row number that contains a value (including the header row).

📌 Expected Outcome:

The number of rows selected in Column A (including the header) is:

✅ 121

This means:

● 120 data entries (rows of stock prices)

● 1 header row (e.g., “Date”)

11 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 Working with Data

🎯 Objective:

Learn how to manually input data, copy data from another workbook, format columns, and
improve spreadsheet readability using a real-life dataset.

🧩 STEP 1: Open the Required Workbook

Workbook to Load:
Open 1_2_working_with_data.xlsx from the Workbooks folder on your local computer.

✅ This file will be your main working file for the rest of the exercise.

📄 STEP 2: Create New Worksheets

👉 Task:

Create two worksheets and rename them.

📝 Instructions:

1. At the bottom of Excel, click the “+” sign twice to add two new sheets.

2. Rename the first sheet to Manual Input.

3. Rename the second sheet to Pasted Data.

To rename a sheet, simply double-click the tab name and type your new name.

12 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
✍️ STEP 3: Enter Data Manually

Worksheet: Manual Input

👉 Task:

Manually enter the following data starting from cell A1:

📝 Instructions:

● Click on the Manual Input worksheet.

● Type “Name” in cell A1, “Location” in B1, and “Orders Placed” in C1.

● Continue entering the values in rows 2–4 as shown above.

✅ This helps you get comfortable with keyboard data entry and cell selection.

📋 STEP 4: Paste External Data

Worksheet: Pasted Data

👉 Task:

13 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
Copy data from another Excel file and paste it as values.

📝 Instructions:

1. Open the copying_data.xlsx file from the Datasets folder.

2. Select columns A to K (use Shift + Ctr + arrow key, or mouse drag).

3. Press Ctrl + C (Copy).

4. Go to your Pasted Data worksheet in 1_2_working_with_data.xlsx.

5. Click cell A1 and use Paste Special → Values.

To paste as values:
Right-click → Paste Special → Choose Values or use shortcut: Alt + E + S +
V + Enter

🧹 STEP 5: Clean and Format the Data

👉 Task:

Format the pasted data to improve readability.

📝 Instructions:

1. Set column width:

○ Select all columns (A to K).

○ Go to the Home tab on the top ribbon.

14 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 ○ In the Cells group, click on Format.

○ From the dropdown, select Column Width…

○ In the dialog box that appears, enter a value (e.g., 15) and click OK.
2. Fix column headers:

○ Ensure the first letter is uppercase, and the rest are lowercase.

○ Example: Change movie title to Movie title.

✅ These formatting steps improve visual clarity and prepare your data for
analysis.

🎬 STEP 6: Answer the Question

Q: What was the runtime for the movie "The Shawshank Redemption" in
minutes?

📝 Instructions:

1. In the Pasted Data sheet, locate the Movie Title column.

2. Scroll or use Ctrl + F and search for “The Shawshank Redemption”.

3. Look in the Runtime column (likely one of the columns labeled as such).

4. Record the runtime in minutes.

✅ Answer: The runtime for "The Shawshank Redemption" is 142 minutes (may
vary slightly depending on the dataset version).

15 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
Creating Your First Formula

🎯 Objective:

Learn how to create and copy Excel formulas to perform arithmetic operations, and use basic
functions to summarize data.

📂 Step 1: Load the Excel File

Workbook to Load:
Open 1_3_creating_your_first_formula.xlsx from the Workbooks folder on your
local computer.

📄 Step 2: Navigate to the Right Sheet

👉 Task:

Go to the sheet labeled Google Monthly Stock Price.

📝 Instructions:

● At the bottom of the Excel window, click on the Google Monthly Stock Price tab to
open it.

📊 Step 3: Insert a New Column for Formula

16 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
👉 Task:

Create a column titled Open vs Close next to the Volume column.

📝 Instructions:

1. Locate the column named Volume.

2. Select the next empty column to the right.

3. In the first cell type Open vs Close.

✅ This new column will calculate the difference between the Close and Open
stock prices.

🧮 Step 4: Write the First Formula

👉 Task:

Subtract Open price from Close price.

📝 Instructions:

1. Go to the first data row (likely row 2).

2. In cell G2, enter the following formula:

= E2 - B2

● E2 is the Close price.

17 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 ● B2 is the Open price.

✅ This gives the price difference for the first month.

🔁 Step 5: Copy Formula to All Rows

👉 Task:

Copy the formula from cell G2 to all rows down to row 121.

📝 Instructions:

● Click cell G2.

● Hover over the bottom-right corner of the cell until you see a small + (plus) sign.

● Double-click the plus sign OR drag it down to cell G121.

This will apply the same calculation for all 120 months of stock data.

🎨 Step 6: Highlight the Entire Column

👉 Task:

Make it easier to read the data in Open vs Close.

18 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
📝 Instructions:

● Select cells G2 to G121.

● You may apply a color fill or bold font to highlight the calculated values.

📈 Step 7: Calculate the Average Difference

👉 Task:

Find the average of all the values in the Open vs Close column.

📝 Instructions:

1. Click an empty cell below the data or in any blank area (e.g., G122).

2. Enter this formula:

=ROUND(AVERAGE(G2:G121), 2)

✅ This calculates the average difference and rounds it to two decimal places.

📌 Final Answer:

Average Difference between Close and Open prices = 0.62

19 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
Working with Tables

🎯 Lesson Objectives:

By the end of this lesson, learners will:

● Understand what Excel Tables are.

● Learn the benefits of converting data into tables.

● Explore table structure (Header Row, Body, Totals Row).

● Apply formatting, sorting, and filtering within tables.

● Use structured references for better data management.

Introduction: What Are Tables in Excel?

In Excel, even though your data might look like a table (rows and columns), it is not a true
Excel table unless you explicitly format it as one.

❗ Why this matters:

● Excel doesn't treat standard data ranges as related unless it's turned into a table.

● Tables help group data logically, making it easier to analyze and manage.

20 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 ✅ To create a table: Select your data ➜ go to the Insert tab ➜ click Table ➜
make sure "My table has headers" is checked, or use Ctr +T short cut key
combination.

Key Benefits of Using Tables

✅ Structured References

● Instead of referencing cells like B2, you can reference Sales[Amount].

● Easier to read, especially in formulas (e.g., =SUM(Sales[Amount])).

✅ Dynamic Ranges

● Tables automatically expand as you add or remove rows or columns.

✅ Data Validation & Integrity

● Apply rules to ensure only valid data is entered (e.g., restrict to dates, numbers only).

21 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 ● Promotes consistent and clean data entry.

Formatting Your Table

Go to Table Design tab when your table is selected for these options.

Formatting Cells in Tables

You can also apply cell-level formatting inside your table to customize:

● Fonts (bold, italic, size, color)

● Alignment (left, center, right)

● Data types (date, number, currency)

22 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 ● Conditional formatting (highlight values >1000, top 10%, etc.)

● Cell styles and themes (preset designs for professional-looking sheets)

Filtering and Sorting Table Data

📍 Why use filters?

In large datasets (e.g., 10,000+ rows), filtering helps you focus only on the data you need.

🔍 Filtering Features:

● Show only rows with specific values (e.g., product = "Laptop")

● Apply comparison filters: greater than, less than, equals

● Use Top N filters: e.g., top 5 highest sales

📊 Sorting Options:

● Alphabetical (A–Z, Z–A)

● Numerical (Smallest to Largest)

● By Date/Time (Newest to Oldest)

● Sort by formatting (cell color, font color, icon sets)

✅ To sort or filter: click the drop-down arrow on the column header in your table.

23 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
Working with Structured Cell References

🎯 Lesson Objectives:

By the end of this lesson, learners will:

● Understand what structured cell references are and how to use them.

● Learn to add new calculated columns in Excel tables using structured references.

● Learn to change column data types to support analysis.

● Calculate delivery time in days using date fields.

Scenario: Adventure Works Sales Data

Adventure Works is a global retail company selling bikes, parts, and accessories. You're
working as a Data Analyst helping management better understand sales data from 2017.

What Are Structured References?

In Excel tables, instead of using regular cell references like C2 - A2, you can use structured
references, which refer to column names directly.

24 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
✅ Benefits of Structured References:

● Easy to read and understand.

● Automatically adjusts as the table grows or shrinks.

● Cleaner formulas that are easier to maintain.

Step-by-Step Guide: Add a New Column to Calculate Delivery

Time

🧩 Step 1: Open the Workbook

● Open 1_4_working_with_structured_references.xlsx from your Workbooks

folder.

🧩 Step 2: Insert a New Column

● Click on the column header next to DeliveryDueDate.

● Right-click and select Insert Table Column to the Right.

● Name the new column OrderToDelivery.

25 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
🧩 Step 3: Enter a Formula Using Structured References

● In the first row of the OrderToDelivery column, type this formula:

=[@DeliveryDueDate] - [@OrderDate]

This uses structured references to subtract the OrderDate from the DeliveryDueDate for each
row.

If Excel displays a date, that’s because it's assuming the result is a date. We
need to fix the data type.

Step 4: Convert the Result to a Number (Days)

● Select the OrderToDelivery column.

● On the Home tab, go to the Number format dropdown.

● Change the format from Date to Number.

Now the column will display the number of days it takes for each order to be delivered.

Step 5: Calculate the Average Delivery Time

To find the average delivery time:

● Select any cell below the table or in the Totals Row (if enabled).

26 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 ● Use the formula:

=AVERAGE(Sales[OrderToDelivery])

✅ This will calculate the average number of days it takes for Adventure Works to
deliver an order.

📊 Interpretation:

The result is 6 days, meaning that It takes about 6 days on average for Adventure Works to
deliver once a customer has placed an order.

Summary Table

Task Action

Add new column Insert table column and name it OrderToDelivery

Enter formula Use =[@DeliveryDueDate] - [@OrderDate]

Convert to number Change column data type from Date to Number

Use structured reference in Structured references improve clarity and adapt

formula dynamically

Calculate average delivery time Use =AVERAGE(Sales[OrderToDelivery])

27 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
Filtering and Sorting Data

🎯 Lesson Objectives:

By the end of this lesson, learners will be able to:

● Filter data based on date conditions.

● Sort data based on one or more columns.

● Use filters and sorts to support analysis and reporting.

Scenario: Adventure Works Sales Data

As an Analyst supporting Adventure Works, your task is to help identify sales made within
September 2017 and organize them based on customer country and order date.

Step-by-Step: Filter Data to a Specific Date Range

📁 Step 1: Open the Dataset

● Open the file: 1_5_filtering_data.xlsx from your Workbooks folder.

● Navigate to the Sales worksheet.

🔍 Step 2: Apply a Filter to Show September 2017 Only

28 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 1. Click anywhere inside the sales data table.

2. Go to the Data tab.

3. Click Filter (if not already active).

4. Click the dropdown arrow on the OrderDate column.

5. Uncheck Select all and check only September.

6. Click OK.

✅ The table now shows only sales orders made in September 2017.

Step-by-Step: Sort by Multiple Columns

🔁 Step 3: Sort the Filtered Data

You will now sort the filtered data using two levels of priority:

1. First Sort: CustomerCountry in A to Z order (alphabetically).

2. Second Sort: OrderDate in Oldest to Newest.

How to Apply Multi-Level Sort:

1. Select any cell in the filtered data.

2. Go to the Data tab.

3. Click on Sort (not the A-Z or Z-A button).

29 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 4. In the Sort dialog box:

○ Under Sort by, choose CustomerCountry, Order A to Z.

○ Click Add Level.

○ Then by OrderDate, Order Oldest to Newest.

5. Click OK.

✅ Your table is now filtered to September 2017 and sorted by country, then by date.

Check the First Row After Filtering and Sorting

After applying the filters and sort steps:

The first visible order number is:

✅SO44323

30 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 Formatting Tables for Data Integrity

🎯 Lesson Objectives:

By the end of this lesson, learners will:

● Understand why formatting matters in data analysis.

● Format columns to ensure data integrity.

● Apply formatting styles like Currency, Number, and Text properly.

Scenario: Adventure Works Sales Data

You are continuing your work as an Analyst for Adventure Works. This time, your focus is to
apply consistent formatting to the sales table to improve clarity and ensure data types are
correctly interpreted in Excel.

Step-by-Step: Open the Workbook

● Open the file: 1_6_formatting_tables.xlsx from the Workbooks folder.

● Go to the Sales worksheet.

31 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 Remove Existing Filters

To view the entire dataset:

1. Go to the Data tab.

2. Click the Filter button to remove any filters.

3. You should now see all rows in the Sales table.

Sort the Table

1. Click anywhere in the table.

2. Go to the Data tab.

3. Click Sort.

4. Sort by OrderNo in ascending order (A to Z).

5. Click OK.

Apply Formatting

💰 Format as Currency

For financial clarity, format these columns:

32 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 ● ItemCost

● ItemPrice

Steps:

1. Select the entire column.

2. Go to the Home tab.

3. In the Number group, select Currency from the dropdown.

🔢 Format as Number (0 Decimal Places)

These are numeric columns that don’t require decimal points:

● SalesOrderLineKey

● OrderQuantity

● OrderToDelivery

Steps:

1. Select each column.

2. Go to the Home tab.

3. Choose Number format.

33 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 4. Set Decimal Places to 0 in the format options.

🔤 Format All Other Columns as Text

To avoid issues like Excel interpreting codes or numbers incorrectly (e.g., removing leading
zeros), format all remaining columns as Text.

Steps:

1. Select each non-financial, non-numeric column.

2. Go to Home > Number format dropdown.

3. Select Text.

How Many Columns Were Formatted as Text?

After applying all the correct formatting:

✅ Answer: 10 columns were formatted as Text.

34 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 Managing and Formatting Data

🎯 Lesson Objectives

By the end of this lesson, students will be able to:

● Manage data effectively using named ranges, subtotals, and data validation.

● Apply formatting to enhance data presentation and clarity.

● Use conditional formatting to identify patterns and trends visually.

1. Introduction

Excel is a powerful tool for managing, organizing, and formatting data. This lesson explores best
practices and built-in features that make data management efficient and reliable.

2. Ways to Manage Data in Excel

Excel stores data in tabular format by default (rows and columns), which makes it easy to
manage even manually entered data. Several features can enhance this process:

35 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 3. Named Ranges

Definition: A named range is a descriptive label assigned to a specific group of cells.

✅ Benefits:

● Makes formulas easier to read and manage.

● Allows quick navigation and editing.

● Simplifies calculations across worksheets.

🔧 How to Use:

● Select the cells.

● Go to the Formulas tab → Click Define Name.

● Use the Name Manager to edit or delete ranges.

➕ 4. Subtotals

Definition: Subtotals are automated calculations (sum, count, average, etc.) applied to grouped
data within a column.

✅ Benefits:

● Helps detect errors or outliers.

36 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 ● Provides quick summaries.

● Automatically includes grand totals.

🔧 How to Use:

● Select your data.

● Go to the Data tab → Click Subtotal.

● Choose the column and operation (e.g., Sum by Region).

✅ 5. Data Validation

Definition: Data validation restricts the type of data or values users can enter into a cell.

✅ Benefits:

● Prevents data entry errors.

● Guides users with input messages.

● Displays custom error messages for invalid inputs.

🔧 How to Use:

● Select the cell range.

● Go to the Data tab → Click Data Validation.

37 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 ● Set criteria (e.g., Whole numbers between 1 and 100).

6. Formatting Data

Proper formatting improves readability, accuracy, and presentation.

Key Formatting Actions:

● Change font styles and sizes.

● Apply borders to sheets or selected cells.

● Format cells based on data types (Currency, Date, Text).

7. Custom Formats

Custom formats help you display data exactly the way you want.

🔧 How to Use:

● Right-click the cell → Choose Format Cells.

● Under the Number tab → Select Custom to define formats (e.g., #,##0.00 for
thousands separator with 2 decimal places).

38 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 8. Conditional Formatting

Definition: Conditional formatting allows you to apply formatting rules based on specific
conditions.

✅ Benefits:

● Highlights trends and outliers.

● Adds visual meaning to numeric values.

● Makes reports more interactive and insightful.

Features:

● Color scales for ranges.

● Data bars to visualize magnitude.

● Icon sets for alerts or status.

🔧 How to Use:

● Go to Home tab → Click Conditional Formatting.

● Choose rules or use New Rule.

● Manage or delete rules via Manage Rules.

39 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 Summary Table

Feature Purpose Key Menu Path

Named Ranges Simplify formulas Formulas → Name Manager

Subtotals Summarize grouped data Data → Subtotal

Data Validation Control input values Data → Data Validation

Formatting Data Improve readability & clarity Home → Format Cells

Custom Formats Set personalized display Format Cells → Custom

formats

Conditional Formatting Highlight patterns or trends Home → Conditional Formatting

Naming Ranges

🎯 Lesson Objective

To learn how to define and use named ranges in Excel for simplifying formulas and enhancing
readability, especially when dealing with large datasets.

40 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 1. What Are Named Ranges?

Named ranges allow you to assign meaningful names to specific groups of cells (such as a
column of prices or quantities) so that you can easily reference them in formulas instead of
using traditional cell addresses (like E2:E1202).

✅ Benefits of Named Ranges:

● Easier to read and understand formulas.

● Minimizes errors in cell referencing.

● Reusable across sheets and formulas.

● Speeds up your workflow.

2. Practical Activity: Naming Ranges

Using the provided workbook: 2_1_naming_ranges.xlsx

🔧 Steps:

1. Open the file: 2_1_naming_ranges.xlsx from the Workbooks folder.

2. Select the ItemPrice values:

○ Highlight cells from E2 to E1202 (excluding the header).

○ Go to the Formulas tab → Click Define Name.

41 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 ○ Name the range: item_price.

3. Name the OrderQuantity range:

○ Select C2 to C1202.

○ Define the name: quantity.

➗ 3. Calculating Order Line Price

We now calculate a derived column based on the named ranges.

🧮 Steps:

1. In cell R1, enter the header: OrderLinePrice.

In cell R2, enter the formula:

=item_price * quantity
2. Press Enter, then copy the formula down to row 1202 to apply it for all data rows.

4. Finding the Average

To find the average Order Line Price across the entire dataset:

42 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
In an empty cell (e.g., R1204), enter the formula:

=AVERAGE(R2:R1202)
1. Result (rounded to 2 decimal places):
3216.59 (based on your uploaded file).

Using Subtotals to Analyze Data

🎯 Lesson Objective

To understand how to use Excel’s Subtotal feature to perform grouped summary

calculations—specifically average delivery times by country—without manually writing
formulas.

1. What Are Subtotals in Excel?

The Subtotal feature in Excel allows you to automatically calculate grouped totals (Sum,
Average, Count, etc.) for data that is sorted by a specific column.

This is useful for:

● Getting a quick summary of numerical values.

● Understanding patterns by category.

● Avoiding manual calculation errors.

43 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 2. Practical Exercise: Subtotal by Country

Using file: 2_2_sub_totals.xlsx

🔧 Steps to Follow:

🛠️ Step 1: Prepare the Dataset

● Open 2_2_sub_totals.xlsx.

● Delete the OrderLinePrice column if it exists (from the previous exercise).

● Duplicate the worksheet (right-click the sheet tab → "Move or Copy" → check Create a
copy).

● Rename the new sheet as: Sales Subtotals.

🔠 Step 2: Sort Data by Country

● Add filters to your table (use Home → Sort & Filter → Filter).

● Click the dropdown on the CustomerCountry column.

● Sort A to Z to ensure countries are grouped correctly for subtotaling.

44 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
➕ Step 3: Apply the Subtotal Feature

● Go to the Data tab → Subtotal.

● Use the following settings:

○ At each change in: CustomerCountry

○ Use function: Average

○ Add subtotal to: OrderToDelivery

○ ✅ Check Summary below data

This inserts subtotal rows after each country with the average number of days
from order to delivery.

3. Interpreting the Result

Once Subtotals are added, notice the numbers 1, 2, 3 on the top-left corner of the worksheet.
These control data grouping levels:

● Click 2 to collapse and only show subtotal rows (country-wise averages).

● Click 3 to expand and show all data with subtotals.

● Click 1 to show only the grand total.

45 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
✅ Question: What is the average order-to-delivery time for customers in
Germany?

Answer: 6 days

This value is shown on the subtotal row for Germany when collapsed to Level 2 view.

Custom Formatting and Data Validation

🎯 Lesson Objective

To understand how to:

● Apply custom formatting for monetary values in Excel.

● Use data validation to enforce data integrity in a worksheet.

1. Custom Formatting in Excel

Custom formatting allows you to define how values are displayed—beyond Excel's default
options.

🛠️ Step-by-Step: Format Monetary Columns

✅ Step 1: Clean Up Workbook

● Delete the Sales Subtotals worksheet if it still exists.

● Format the Sales worksheet as a table:

46 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 ○ Select the entire dataset.

○ Go to Insert → Table or use Ctrl + T.

💰 Step 2: Format the ItemCost Column

● Highlight the ItemCost column.

● Open the Format Cells dialog (Ctrl + 1).

● Go to the Custom tab.

● In the Type field, enter: #,### $

○ This applies a thousand separator, 0 decimal places, and a dollar sign after
the number.

○ Example format: 1,000 $

💰 Step 3: Format the ItemPrice Column

● Repeat the same steps and apply the same custom format #,### $.

2. Data Validation in Excel

Data validation helps control what values users can enter into specific cells. This improves
data quality and prevents input errors.

47 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
🛠️ Step-by-Step: Validate the OrderQuantity Column

⚙️ Step 1: Select Column

● Highlight the OrderQuantity column.

⚙️ Step 2: Apply Validation Rules

● Go to Data tab → Data Validation.

● Set:

○ Allow: Whole number

○ Data: Greater than

○ Minimum: 0

⚠️ Step 3: Add Error Alert

● Go to the Error Alert tab.

● Customize an alert message like:

"Invalid entry. Order quantity must be greater than zero."

48 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
❓ Test Your Validation

Question: If you entered the value 0 in cell C1203, would you get an error?

✅ Answer: Yes

Because the validation rule requires the number to be greater than 0, entering 0 violates the
rule and will trigger the error message.

Aggregate and Arithmetic Operations

🎯 Lesson Objectives

By the end of this lesson, students will be able to:

● Understand the order of operations (PEDMAS) in Excel.

● Use aggregate functions (like AVERAGE, SUM, MIN, MAX, and COUNT) to summarize
data.

● Apply functions with cell ranges, column references, or structured table references.

1. Order of Operations (PEDMAS)

Before performing any arithmetic in Excel, it's important to understand the sequence in which
operations occur.

49 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
✅ The PEDMAS Rule:

Symbol Operation Example

P Parentheses (2+3)*4 =
20

E Exponents 2^3 = 8

D Division 10/2 = 5

M Multiplication 5*2 = 10

A Addition 5+3 = 8

S Subtraction 5-2 = 3

Division, multiplication, addition, and subtraction are done left to right when they
appear together without parentheses.

📌 Example Calculation:

Expression:

=20*2 + 10/2*4 - 20

Follow the order:

1. Parentheses first (if any)

2. Exponents next (not used here)

3. Then multiplication/division left to right

50 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 4. Then addition/subtraction left to right

2. Arithmetic in Excel

Excel uses the PEDMAS rule just like manual calculations but also incorporates:

● Reference Operators (: for ranges)

● Concatenation (&)

● Negation (-)

● Logical comparisons (=, <, >)

Most calculations in Excel start with the = symbol.

3. Introduction to Functions in Excel

Functions are built-in formulas that automate tasks such as mathematical, logical, and text-
based operations.

🔧 Function Categories:

● Math & Trigonometry: SUM, ROUND, PRODUCT

● Statistical: AVERAGE, COUNT, MIN, MAX

51 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 ● Logical: IF, AND, OR

● Text: LEFT, RIGHT, LEN

● Date & Time: TODAY, NOW, DATEDIF

4. What Are Aggregate Functions?

Aggregate functions are used to summarize groups of values into a single output. They are
helpful in identifying trends, understanding distributions, and performing quick analysis.

Common Aggregate Functions:

Function Description Example

SUM() Adds all values in a range =SUM(B2:B11)

AVERAGE Calculates the arithmetic mean =AVERAGE(B2:B

() 11)

MIN() Finds the smallest value =MIN(B2:B11)

MAX() Finds the largest value =MAX(B2:B11)

COUNT() Counts the number of numeric =COUNT(B2:B11

values )

52 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 5. Example: Student Test Scores

Let's say you're a lecturer and want to analyze student test scores.

📍 Data Range: B2:B11 contains 10 test scores

🧮 Using AVERAGE:

=AVERAGE(B2:B11)

● This returns the mean score.

● Result: 74.2

🔁 Alternative ways to reference:

● Whole column: =AVERAGE(B:B)

● Structured table column: =AVERAGE(Table1[Test Score])

➕ 6. More Aggregate Functions with the Same Range

● Minimum Score: =MIN(B2:B11)

● Maximum Score: =MAX(B2:B11)

● Number of Students: =COUNT(B2:B11)

53 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 ● Total Score: =SUM(B2:B11)

These functions help you understand performance distributions at a glance.

Summary Table

Function Description Sample Formula

AVERAGE Mean value =AVERAGE(B2:B

11)

MIN Lowest value =MIN(B2:B11)

MAX Highest value =MAX(B2:B11)

COUNT Number of records =COUNT(B2:B11

)

SUM Total of values =SUM(B2:B11)

Excel Lesson Note: Summarizing Sales Using

Aggregate Functions

🎯 Lesson Objectives

In this lesson, you will learn how to:

54 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 ● Use aggregate functions like MIN, MAX, AVERAGE, and SUM.

● Format values as currency.

● Calculate profit-related metrics from sales data.

1. Setting Up Your Summary Section

Open the Excel file: 2_4_summarizing_sales.xlsx.

Navigate to a new summary section starting from cell S2, and input the following labels:

Cell Description

S2 "Lowest Item Price"

S3 "Highest Item Price"

S4 "Average Item Price"

S5 "Total Sales"

S6 "Total Profit"

S7 "Profit Margin Ratio"

This will help organize your calculations and make your worksheet easier to understand.

55 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
➗ 2. Calculating Aggregate Values

Now enter formulas in column T to calculate the required values based on the dataset:

🟢 T2 – Lowest Item Price

=MIN(Sales[ItemPrice])

🟢 T3 – Highest Item Price

=MAX(Sales[ItemPrice])

🟢 T4 – Average Item Price

=AVERAGE(Sales[ItemPrice])

Formatting Tip:
Select cells T2 to T4, right-click → Format Cells → Choose Currency with 2 decimal places.

Final Result

Question: What is the average item price across all sales?

✅ Correct Answer: $3216.59

This value appears in cell T4 after using the AVERAGE() function.

Summary Table

56 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 Metric Function Used Example Formula

Lowest Price MIN() =MIN(Sales[ItemPrice])

Highest Price MAX() =MAX(Sales[ItemPrice])

Average Price AVERAGE() =AVERAGE(Sales[ItemPri

ce])

Total Sales SUM() =SUM(Sales[OrderLinePr

ice])

Total Profit SUM() =SUM(Sales[Profit])

Profit Margin Ratio Manual Division =T6/T5

Calculating Sales and Profit

🎯 Learning Objectives

By the end of this lesson, you should be able to:

● Use Excel aggregate functions (SUM)

● Combine arithmetic and functions to calculate profit

● Derive profit margin ratios

● Apply appropriate currency and percentage formatting

57 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 1. Calculating Total Sales

👉 In Cell T5:

You need to calculate the total sales.

Since each order has a quantity of 1, ItemPrice alone represents the revenue for each
transaction.

Formula:

=SUM(Sales[ItemPrice])

✅ Formatting:

● Right-click the cell → Format Cells → Currency → 0 Decimal Places

● Result displays in dollar ($) format.

2. Calculating Total Profit

👉 In Cell T6:

Calculate Total Profit using the formula:

Total Profit = Total Sales − Total Cost

Since quantity = 1 for all rows, cost is simply the sum of the ItemCost column.

Formula:

=SUM(Sales[ItemPrice]) - SUM(Sales[ItemCost])
58 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
✅ Formatting:

● Apply Currency with 0 Decimal Places.

3. Calculating Profit Margin Ratio

👉 In Cell T7:

Calculate Profit Margin Ratio using the formula:

Profit Margin Ratio = Total Profit ÷ Total Sales

Formula:

=T6/T5

✅ Formatting:

● Format as Percentage with 0 Decimal Places.

✅ Final Result:

Question:
What is the profit margin ratio?

Answer: 40%

59 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
Interpretation:

If your profit margin is 25%, it means:

“For every $100 in sales, the business keeps $25 as profit after covering all
costs.”

Summary Table:

Cell Description Formula Format

T5 Total Sales =SUM(Sales[ItemPrice]) Currency ($)

T6 Total Profit =SUM(Sales[ItemPrice]) - Currency ($)

SUM(Sales[ItemCost])

T7 Profit Margin Ratio =T6/T5 Percentage

%

60 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 Other Functions in Excel

🎯 Learning Objectives

By the end of this lesson, you will:

● Understand the variety of Excel function categories

● Be able to apply Text, Date & Time, Maths & Trig, and Statistical functions

● Know practical use cases for each function in real data analysis

Excel Function Categories

Excel has 12 function categories:

● In Scope (This course):

○ Text

○ Date & Time

○ Maths & Trig

○ Statistical

○ Logical

○ Lookup & Reference

61 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 ● Out of Scope:

○ Financial

○ Cube

○ Engineering

○ Web

○ Information

○ Compatibility

Excel functions can work with:

● Cell references (A2, B5)

● Table references (Sales[Amount])

● Static values ("Hello", 100, etc.)

Text Functions

📍 a. LEFT(text, num_chars)

Returns the first n characters from a text string.

Example:

62 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
=LEFT("DataCamp", 4) → "Data"

📍 b. RIGHT(text, num_chars)

Returns the last n characters from a string.

Example:

=RIGHT("DataCamp", 4) → "Camp"

📍 c. UPPER(text) / LOWER(text)

● UPPER converts text to uppercase.

● LOWER converts text to lowercase.

Example:

=UPPER("excel") → "EXCEL"
=LOWER("EXCEL") → "excel"

Date & Time Functions

📍 a. DATE(year, month, day)

Combines separate year, month, and day into one date.

Example:
63 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
=DATE(2025, 6, 19) → 19-Jun-2025

📍 b. DAY(date) / YEAR(date)

Extract the day or year from a given date.

Example:

=DAY("2025-06-19") → 19
=YEAR("2025-06-19") → 2025

Maths & Trigonometry Functions

📍 a. ROUND(number, num_digits)

Rounds a number to a given number of digits.

Example:

=ROUND(-1.475, 2) → -1.48

📍 b. ROUNDUP / ROUNDDOWN

● ROUNDUP: Always rounds up.

● ROUNDDOWN: Always rounds down.

64 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
📍 c. ABS(number)

Returns the absolute value (i.e., removes negative sign).

Example:

=ABS(-2) → 2

📍 d. SUM(range)

Adds all numbers in the specified range.

Example:

=SUM(A1:A5)

Statistical Functions

Already covered:

● AVERAGE(range)

● COUNT(range)

● MIN(range)

● MAX(range)

These are part of the Statistical category. More advanced statistical functions will be covered in
future lessons.

65 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
📝 Summary Table:

Category Function Purpose

Text LEFT, RIGHT Extract characters from text

Text UPPER, LOWER Change case of text

Date & Time DATE, DAY Create and extract date

components

Maths & Trig ROUND, ABS Round and transform numbers

Statistical AVERAGE, SUM Analyze data using aggregates

Product Profiles

🎯 Learning Objectives

By the end of this lesson, you will be able to:

● Extract partial data from existing columns using text functions

● Format column values using case transformation functions

● Create unique identifiers from existing strings

66 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 Context: Why This Matters

In data analytics, data is not always clean or formatted the way we want. We often need to:

● Transform text (e.g., change lowercase to uppercase)

● Extract parts of a value (e.g., ID or code)

● Create new fields (e.g., order identifiers) for sorting or joining with other tables

This lesson focuses on preparing a clean and structured product profile table using Excel
formulas.

Steps to Complete the Task

✅ 1. Load the Workbook

File: 3_1_product_profiles.xlsx
Open the workbook in Excel from your Workbooks folder.

✅ 2. Create a New Column: Country (Uppercase)

Objective: Convert all values in the CustomerCountry column to uppercase.

Steps:

1. Insert a new column between columns L and M.

2. Name the new column Country.

67 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 3. In the first row of the new column (assume row 2 if headers are in row 1), enter the
formula:

=UPPER(L2)

4. Drag the formula down to apply it to all rows.

📖 Function Used:

● UPPER(text)
Converts all letters in a text string to uppercase.

✅ 3. Create a New Column: OrderID (Last 5 Digits)

Objective: Extract the last 5 characters from each Order Number to create a simplified ID.

Steps:

1. Insert a new column between columns A and B.

2. Name the new column OrderID.

3. In the first row of the column, enter the formula:

=RIGHT(A2, 5)

4. Format the column as General (not Text or Number).

68 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
📖 Function Used:

● RIGHT(text, num_chars)
Returns the last n characters in a text string.

🔍 Check Result

Question: For OrderID 43698, what is the name of the customer?

✅ Correct Answer: Rachael Martinez

You can use the Filter tool or manually check the OrderID column to match 43698, then read
the corresponding CustomerName.

🧠 Key Takeaways

Concept Function Purpose

Text to UPPER() Standardizes country names

Uppercase

Extract from Text RIGHT() Creates short-form IDs from long codes

Column Insertion Right-click > Insert Adds new field next to related data

These operations help clean, structure, and prepare your data for efficient reporting and future
analysis.

69 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 Profit Performance

🎯 Learning Objectives

By the end of this lesson, you will:

● Extract year and month from a date using Excel functions

● Prepare your data for time-based analysis

● Perform filtering to calculate conditional averages

Context: Why This Matters

In business analysis, time-based reporting is critical. Stakeholders often need to:

● Compare performance by month or year

● Understand seasonal trends

● Group data over time periods for reporting

To do this effectively, we use date functions to extract time components from full date values.

70 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 Steps to Complete the Task

Workbook: 3_2_profit_performance.xlsx
Open the file in Excel from your Workbooks folder.

✅ 1. Add a Column: OrderYear

Goal: Extract the year from the OrderDate column.

Steps:

1. Insert a column next to OrderDate.

2. Name it OrderYear.

3. In the first row (assuming headers are in row 1), enter the formula:

=YEAR([@OrderDate])

4. Format the column as Number with 0 decimal places.

✅ 2. Add a Column: OrderMonth

Goal: Extract the month number (1 = Jan, 9 = Sep, etc.) from the same OrderDate.

Steps:

1. Insert a column next to OrderYear.

71 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 2. Name it OrderMonth.

3. In the first cell, enter:

=MONTH([@OrderDate])

4. Format this column as Number with 0 decimal places.

🔍 3. Analyze Average Cost for September Orders

Goal: Calculate the average item cost for orders placed in September.

Steps:

1. Filter the OrderMonth column to show only 9 (September).

2. Use Excel’s SUBTOTAL and select average.

✅ Answer (from dataset):

Average Item Cost in September: 1908.

72 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 Excel Lesson Note: Formatting with Functions

🎯 Learning Objectives

By the end of this lesson, learners will:

● Understand the role of rounding functions in Excel.

● Use ROUNDUP() and ROUND() to clean up and present numeric data more clearly.

● Format summary statistics to make reports easier to read.

Context: Why This Matters

When presenting sales or financial data, it’s important to:

● Round values to a meaningful precision.

● Avoid clutter caused by unnecessary decimal places.

● Ensure readability and professional presentation of numerical data.

Excel provides functions like ROUNDUP(), ROUNDDOWN(), and ROUND() to achieve this.

73 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 Steps to Complete the Task

Workbook: 3_3_formatting_with_functions.xlsx
Open it from the Workbooks folder if not already loaded.

✅ 1. Unfilter Your Data

Before proceeding:

● Remove any filters applied in previous exercises:

○ Go to the Data tab and click Clear in the Sort & Filter group.

✅ 2. Locate the Summary Statistics

Look for the following existing values (assumed to be in column T, starting from row 2):

Row Description Cell

2 Lowest Item Price X2

3 Highest Item Price X3

4 Average Item Price X4

74 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
✅ 3. Apply Rounding Functions

Target Function Formula Example Cell

Round up the lowest item price to 0 digits ROUNDUP(X2, In cell Y2

0)

Round up the highest item price to 0 ROUNDUP(X3, In cell Y3

digits 0)

Round the average item price to 1 digit ROUND(X4, In cell Y4

1)

Tip: After entering formulas, press Enter, and format cells as Currency (optional) to
maintain clarity.

Functions Used

Function Description

ROUNDUP(number, Rounds a number up to the nearest integer or decimal

num_digits) place

ROUND(number, Rounds a number normally (up or down) to given precision

num_digits)

Business Use Case

● Use ROUNDUP when prices or costs should never be underestimated (e.g., financial
proposals).

75 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds
 ● Use ROUND when you want to present data in a balanced, readable format.

● These practices make dashboards, reports, and summaries cleaner and more
effective for decision-making.

✅ Final Question:

What is the lowest item price when rounded up?

→ Check the result in Y2 and format as needed.
Example Answer: 700

76 | P a g e
D’SON ICT SOLUTIONS & ACADEMY - Building Digital Future and Empowering Innovative Minds