Ford and Lord International Pty Ltd

Master Your Microsoft Excel Skills
Free Course + Free Certification

Versatile Teaching

High Standards

Bright Future

Microsoft Excel

For Data Analytics

How to enrol and get free certification:


Both the Excel and Python courses are conducted through an open admission system. This means no formal enrolment is required. You can complete either of these courses at your own pace, in your own time. All you need to do is complete the Excel or Python (or both) video courses via the website. All assignments, tasks, projects, and quizzes will be available on the website by June 30, 2024. Please watch all the videos and ensure you are confident with the concepts taught.


Please prepare assignments, tasks, complete projects, and reflections, but keep them in a folder on your computer. Once you are confident that you have learned all the concepts and completed all the assignments, reflections, and tasks, please contact us via email at admin@fordandlord.com to indicate that you are ready for the exam. We will then send you a link to a Google Classroom page with the code. You will need to join the classroom and submit all the assignments, projects, and course reflections at once.


We will check and mark all the coursework. If you have achieved satisfactory results, we will invite you for an interview via an online meeting. During the interview, we will test your knowledge, and if you are successful, we will issue a certificate of completion.

Course Outline - Microsoft Excel for Data Analytics

Week-1

Excel Fundamentals - Introduction To MS-Excel

Assignment-1

After watching the video introduction about navigation, cell addresses, formula bar, and the Excel interface, reflect on what you have learned and how it can help you in your future work with Excel. Your assignment should address the following:

  1. Understanding the Interface: Describe the main components of the Excel interface covered in the video. How do these components contribute to your ability to work efficiently in Excel?
  2. Cell Addresses and Navigation: Explain what cell addresses are and how they are used in Excel. Reflect on the strategies for navigating through a spreadsheet that were discussed in the video. How can these skills improve your workflow?
  3. Using the Formula Bar: Discuss the importance of the formula bar in Excel. How does it help when working with data and formulas?
  4. Practical Application: Provide an example of how you can apply the concepts from the video to a real-world scenario or task. Describe the specific Excel functions or features you would use and why.

Your reflection should be approximately 500 words in length. Be sure to provide specific examples from the video and your own experiences to support your points. Aim to be clear and concise in your writing.

Instructions for Preparing and Submitting the Assignments

  1. Prepare Your Assignment: Complete all the assignments as outlined in the course materials. Organize your work in a dedicated folder on your computer.
  2. Review Course Materials: Ensure you have watched all the videos and completed the associated exercises and tasks to gain a comprehensive understanding of the subject matter.
  3. Prepare for Submission: Once you have finished all the assignments and reviewed the course content, you will be ready for the next steps.
  4. Contact Us: Reach out to us via the email address provided below when you are ready to proceed to the online test. We will respond with a link to our Google Classroom page.
  5. Join the Class: Use the provided link to join the Google Classroom. This platform will serve as the central hub for submitting all of your assignments in one go.
  6. Submit Your Assignments: Upload your completed assignments through Google Classroom. We will review your submissions to ensure they meet the established standards.
  7. Online Test Invitation: If your assignments are approved, you will receive an invitation to participate in an online test or interview.
  8. Certification: Upon successfully passing the online test, you will be awarded a certificate of completion.

Week-2

Cells In Excel

Week-2.1

Basic formatting, fonts, borders, fill colours

Week-3

Formulas and Functions (Part 1)

Week-4

Formulas and Functions (Part 2)

Week-5

Intermediate Excel Skills - Data Management

Week-6

Advanced Functions

Week-7

Important Tips and Tricks - 1

Week-8

Important Tips and Tricks - 2, Custom Formatting, Charts and Graphs

Week-9

Working with multiple sheets in EXCEL - PivotTables (Part 1)

Week-10

Numbers that can drive you crazy - Creating a simple payroll - PivotTables (Part 2)

Week-11

Simple payroll - Calculating overtime, =IF() logical function

Week-11.1

Data Analysis with Excel

Week-12

Analyzing, Reverse Engineering. Absolute References - Excel

Week-13

Simple payroll - Calculating Tax in Excel

Week-14

Lesson 14: LARGE(), SMALL(), MATCH() and INDEX() functions in Excel

Week-14.1

Lesson 14.1: LARGE(), SMALL(), MATCH() and INDEX() functions in Excel

Week-15

Lesson 15 : Copy values and ROUND() function in Excel

Week-16

Transpose() and Conditional and Logical Operations

Card image cap
Introduction To Microsoft Excel.

Click On Below Button For Learning New Topic.

Watch Video Assignment 1

Excel for Data Analyst: Assignment No. 1

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 1 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Assignment 1

After watching the video introduction about navigation, cell addresses, formula bar, and the Excel interface, reflect on what you have learned and how it can help you in your future work with Excel. Your assignment should address the following:

  1. Understanding the Interface: Describe the main components of the Excel interface covered in the video. How do these components contribute to your ability to work efficiently in Excel?
  2. Cell Addresses and Navigation: Explain what cell addresses are and how they are used in Excel. Reflect on the strategies for navigating through a spreadsheet that were discussed in the video. How can these skills improve your workflow?
  3. Using the Formula Bar: Discuss the importance of the formula bar in Excel. How does it help you when working with data and formulas?
  4. Practical Application: Provide an example of how you can apply the concepts from the video to a real-world scenario or task. Describe the specific Excel functions or features you would use and why.

Your reflection should be approximately 500 words in length. Be sure to provide specific examples from the video and your own experiences to support your points. Aim to be clear and concise in your writing.

Note:

Submit your assignment in document format with your introduction and answers to the assignment questions.

Card image cap
Cells In Microsoft Excel.

Click On Below Button For Learning New Topic.

Watch Video Assignment 2

Excel for Data Analyst: Assignment No. 2

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 3 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Define the following terms in one or two lines: 30 Marks
    • Tabs
    • Ribbon
    • Groups
    • Name Box
    • Formula Bar
    • Status Bar
  2. How cells can be formatted and how many formatting are there in the numbers group of the Home tab? 25 Marks
  3. Define Data Bars in conditional formatting and their common uses. 15 Marks
  4. Submit an Excel file of .xls or .xlsx extension in which you create daily income, expense, profit, and loss data with the sum function and every column formatted. Additionally, show that profit amount with data bars in the next column. 25 Marks

Note:

You have to submit two files:


  • In the first file, you will explain Task 1, 2, and 3. This file will have a .doc or .docx extension.

  • In the second file, you will create a weekly budget of income, expense, and profit with data bars in a .xls or .xlsx extension.
Card image cap
Basic formatting, fonts, borders, fill colours

Click On Below Button For Learning New Topic.

Watch Video Assignment 2.1

Excel for Data Analyst: Assignment No. 2.1

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 3 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Define the following purposes of basic formatting in a single paragraph: 50 Marks
    • How data formatting helps in Professional Looking of data?
    • How data formatting helps users to read it easily?
    • How data formatting helps users in Data Visualization?
    • How data formatting helps in highlighting key information?
    • How data formatting helps in time-saving for the user?

    10 Marks for every correctly briefed paragraph.

  2. Submit an Excel file of .xls or .xlsx extension in which you created daily income, expense, profit, and loss data sheet file in your previous assignment with a new sheet having the same data with basic formatting, fonts, coloring, alignment, borders, and center across selection 45 Marks

    • 10 Marks for submitting xlsx or xls file.
    • 7 Marks for every applied formatting.

Note:

You have to submit two files:


  • In the first file, you will explain Task 1 in a single file having a .doc or .docx extension.

  • In the second file, you will apply font, color, alignment, border, and center across selection formatting, and create a weekly budget of income, expense, and profit with data bars in a .xls or .xlsx extension.
Card image cap
Formulas And Functions Part-1.

Click On Below Button For Learning New Topic.

Watch Video Assignment 3

Excel for Data Analyst: Assignment No. 3

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 3 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Create a data set with columns: Name, D.O.B., Salary, Gender, Employer ID, and Country 05 Marks
    • Data set must have 30 rows.
  2. Then Calculate with help of Excel functions:
    Calculation Marks
    Sum of Salaries 12 Marks
    Total number of Employees 12 Marks
    Male Employees 12 Marks
    Female Employees 12 Marks
    Avg Salary 12 Marks
    Median Salary 12 Marks
    Maximum Salary 07 Marks
    Minimum Salary 07 Marks

Note:

You have to submit a .xls or .xlsx file:

  • In this assignment, you have to fill in your introductory lines first, then create the sheet.

  • Every function should be applied correctly to the specified range. Otherwise, marks will be deducted.

  • Remaining 04 Marks will be of your way of presentation a data.
Card image cap
Important Formulas And Functions Part-2.

Click On Below Button For Learning New Topic.

Watch Video Assignment 4

Excel for Data Analyst: Assignment No. 4

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 4 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Describe two different uses of the Format Painter and how to apply it in two different ways? 20 Marks
  2. Briefly describe five different uses of the fill handle. 25 Marks
  3. Briefly describe three different uses of filters and how to apply them. 30 Marks
  4. Submit the Excel file you created in the last assignment, with the age of each employee calculated using the DATEDIF() function, referencing the D.O.B. column. Apply a filter to the data using any option. 20 Marks

Note:

You have to submit 02 files:

  • The first file should be in a document format and include your introduction followed by the first three tasks of this assignment.

  • The second file should be in .xls or .xlsx format and include your introduction, followed by the data set with the DATEDIF() function and applied filters.
Card image cap
Intermediate Excel Skills - Data Management.

Click On Below Button For Learning New Topic.

Watch Video Assignment 5

Excel for Data Analyst: Assignment No. 5

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 5 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Describe 11 different data formats in Excel in only one line each. 55 Marks
  2. Create a duplicate sheet of your previously created data set and apply the following: 40 Marks
    • Currency format to the currency column
    • Date format as ‘dd-mmm-yyyy’
    • Insert two columns to the right side of the Name column with the column names ‘First Name’ and ‘Last Name’
    • Use Flash Fill to populate these columns with the first and last names respectively

Note:

You have to submit 02 files:

  • The first file should be in document format containing your introduction and the first task of this assignment where you define 11 different data formats used in Excel. 5 Marks for each correctly described data format.

  • The second file should be in .xls or .xlsx format containing your introduction and two sheets with the same data set. The second sheet should have the modifications as required in task 2 of this assignment. 10 Marks for each data format applied and 10 Marks for Flash Fill in both the First and Last Name columns.
Card image cap
Advanced Functions In MicroSoft Excel.

Click On Below Button For Learning New Topic.

Watch Video
Card image cap
1 - Important Tips And Tricks - In MicroSoft Excel.

Click On Below Button For Learning New Topic.

Watch Video Assignment 7

Excel for Data Analyst: Assignment No. 7

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 7 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Describe concatenation in one paragraph and identify where concatenation is used in this video with examples. 20 Marks
  2. Define three uses of Find and Replace in Excel with examples. 30 Marks
  3. In your previously created data, replicate a sheet with the name of Assignment 7 and: 10 Marks
    • Find the age of employees with the help of the YEARFRAC() function. 15 Marks
    • Replace the first 25 employees' IDs starting with AU (e.g., E0001 to AU0001) using Find and Replace. 10 Marks
    • Replace the last five employees' IDs starting with CA (e.g., E0001 to CA0001) using Find and Replace. 10 Marks

Note:

You have to submit 02 files:

  • The first file should be in document format containing your introduction and the first two tasks of this assignment.

  • The second file should be in .xls or .xlsx format containing your introduction and a replicated sheet with the same data set, including the tasks defined above.
Card image cap
2 - Important Tips , Tricks | Custom Formatting + Charts And Graphs.

Click On Below Button For Learning New Topic.

Watch Video Assignment 8

Excel for Data Analyst: Assignment No. 8

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 8 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Describe any five uses of custom formatting with examples. 50 Marks
  2. In your previously created data, replicate a sheet with the name of Assignment 8 and: 10 Marks
    • Insert a row or two above the data and give a heading that covers all columns. The heading should be center-aligned using any option. 10 Marks
    • Create a column for Phone Numbers and apply custom formatting for phone numbers so that when you enter only the number, the country code will appear for all Australian employees. 10 Marks
    • Apply custom formatting for the phone numbers of the last five employees according to their country with their dialing code. 15 Marks

Note:

You have to submit 02 files:

  • The first file should be in document format containing your introduction and the first task of this assignment.

  • The second file should be in .xls or .xlsx format containing your introduction and one replicated sheet with the same data set and tasks defined as above.
Card image cap
Working With Multiple Sheets In Excel - PivotTables Part 1.

Click On Below Button For Learning New Topic.

Watch Video Assignment 9

Excel for Data Analyst: Assignment No. 9

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 9 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Describe any three benefits of working with multiple sheets. 30 Marks
  2. Create a new workbook named ‘Assignment 9’ and: 10 Marks
    • Create 7 sheets in this workbook named them with days name from ‘Monday’ to ‘Sunday’ then apply method of working on multiple sheets as discussed in this lesson. 10 Marks
    • Create a sales data of employees working in a furniture store with columns, ‘S.No’, ‘Name’, ‘Total Sales Amount’ try using the same method of working in multiple sheets but with different sales amounts. 10 Marks
  3. Create an 8th sheet with the name ‘Summary’ and Calculate: 35 Marks
    • Total weekly Sale.
    • Max sale of every day.
    • Min sale of every day.

Note:

You have to submit 02 files:

  • The first file should be in document format containing your introduction and the first task of this assignment.

  • The second file should be in .xls or .xlsx format containing your introduction in a new workbook and the tasks required in task 2 of this assignment.
Card image cap
Creating A Simple Payroll - PivotTables Part 2.

Click On Below Button For Learning New Topic.

Watch Video Assignment 10

Excel for Data Analyst: Assignment No. 10

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 10 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Create a new sheet in your previous assignment file. 10 Marks
  2. Create a payroll for every employee in this sheet. 25 Marks
    • Columns should include Employee ID, Name, Hours Worked, Per Hour Rate, Pay
  3. Calculate pay for every employee using the function explained in the lesson or any other method of your choice. 20 Marks
  4. Keep in mind that time should be in HH:MM format and in a single cell. Convert this into a calculated integer value in the next column for ease of calculation. 40 Marks

Note:

You have to submit 01 file:

  • The file should be in .xls or .xlsx format containing your introduction and both tasks as required in this assignment.
Card image cap
Simple Payroll - Calculating Overtime, =if() Logical Function.

Click On Below Button For Learning New Topic.

Watch Video Assignment 11

Excel for Data Analyst: Assignment No. 11

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 11 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Describe any three uses of entering data in a cell using an apostrophe: 30 Marks
  2. Define LEFT, RIGHT, and FIND function in one paragraph with examples: 30 Marks
  3. In this assignment, you need to calculate the overtime of employees who worked more than 7 hours and 21 minutes in the same sheet of your last assignment. Then calculate the total salary adding overtime to the salary in a new column. 35 Marks

Note:

You have to submit 02 files:

  • The 1st file should be in document format having your introduction and the first two tasks of this assignment.

  • The 2nd file should be in .xls or .xlsx format containing your introduction in the workbook and the tasks required in task 3 of this assignment.
Card image cap
Data Analysis With Microsoft Excel.

Click On Below Button For Learning New Topic.

Coming Soon
Card image cap
Analyzing, Reverse Engineering. Absolute References.

Click On Below Button For Learning New Topic.

Watch Video Assignment 12

Excel for Data Analyst: Assignment No. 12

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 12 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Define relative reference and absolute reference with examples: 30 Marks
  2. In your previously created payroll sheet:
    • Take relative reference from the employee sheet of Employee ID and Employee Name. 20 Marks
    • In an empty cell type Per Hour Rate and in the next cell type that rate. Now take absolute reference from this cell to calculate salary and overtime. 45 Marks

Note:

You have to submit 02 files:

  • The 1st file should be in document format having your introduction and the first task of this assignment.

  • The 2nd file should be in .xls or .xlsx format containing your introduction in the workbook and the tasks required in task 2 of this assignment.
Card image cap
Simple payroll - Calculating Tax In Excel.

Click On Below Button For Learning New Topic.

Watch Video Assignment 13

Excel for Data Analyst: Assignment No. 13

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 13 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Create a new worksheet with previous assignment named ‘Assignment 13’ and: 10 Marks
  2. In an empty cell type Tax and in the next cell type value of tax then calculate Tax from everyone’s salary in the next column where you calculated salary but with help of absolute reference from Tax value cell. 40 Marks
  3. In the next column calculate the net salary of every employee by deducting Tax from their salaries. 20 Marks
  4. Calculate Min, Max, Average, and Median Salary in the same sheet. 25 Marks

Note:

You have to submit 01 file:

  • The file should be in .xls or .xlsx format having your introduction in the worksheet and create tasks that are required in this assignment.
Card image cap
LARGE(), SMALL(), MATCH() and INDEX() Functions In Excel.

Click On Below Button For Learning New Topic.

Watch Video
Card image cap
Detailed Discussion LARGE(), MATCH() and INDEX() In Excel

Click On Below Button For Learning New Topic.

Watch Video Assignment 14.1

Excel for Data Analyst: Assignment No. 14.1

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 14.1 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Describe LARGE(), SMALL(), INDEX(), and MATCH() functions with examples: 40 Marks
  2. Differentiate between LARGE() vs MAX() and SMALL() vs MIN() functions: 20 Marks
  3. Create a new worksheet with previous assignment named ‘Assignment 14’ and: 05 Marks
  4. In the previous assignment you calculated every employee salary with the same per hour rate, but it could not be the case in most cases. Now you have to calculate with a different per hour rate for every employee. In the per hour rate column change per hour rate and calculate salary, tax, and net salary accordingly: 10 Marks
  5. Calculate Top 5 Earners: 05 Marks
  6. Calculate Least 5 Earners: 05 Marks
  7. Match the salaries with the names of employees of every Top 5 and Least 5 Earners using INDEX and MATCH functions: 10 Marks

Note:

You have to submit 02 files:

  • 1st file will be a document having an introduction and the first two tasks of this assignment.

  • 2nd file should be in .xls or .xlsx format having your introduction in the worksheet and the 3rd tasks of this assignment.
Card image cap
Copy values and round() function in Excel.

Click On Below Button For Learning New Topic.

Watch Video
Card image cap
Transpose() and Conditional and Logical Operations.

Click On Below Button For Learning New Topic.

Watch Video Assignment 16

Excel for Data Analyst: Assignment No. 16

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 16 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Describe five different uses of Special Paste used in Excel: 20 Marks
  2. Describe ROUND() and TRANSPOSE() functions with examples: 20 Marks
  3. Create a new workbook with name ‘Class Record’ name sheet1 ‘Record’ and second sheet ‘Result Card’: 15 Marks
  4. In Record sheet, name columns, S.No, Name, Roll No., Science, Maths, English, Social Studies, History and type 10 students record in these columns: 10 Marks
  5. In Result Card sheet create result card of every student with the help of TRANSPOSE() function as shown in lesson 16: 10 Marks
  6. Calculate in Result Card sheet, Total Marks, Marks Obtained, Percentage, and Grade (Calculate Grade using IFS() function): 10 Marks
  7. Use relative and obsolete reference in Result Card sheet for student name, student roll no., subject names, subject marks: 10 Marks

Note:

You have to submit 02 files:

  • 1st file will be a document having an introduction and the first 2 tasks of this assignment.

  • 2nd file should be in .xls or .xlsx format having your introduction in the worksheet and 3rd tasks of this assignment.
Card image cap
Power of Match() and Index() in MS Excel

Click On Below Button For Learning New Topic.

Watch Video
Card image cap
count(), counta(), unique(), countif(), sumif()

Click On Below Button For Learning New Topic.

Watch Video
Card image cap
Aggregation using list with sumif()

Click On Below Button For Learning New Topic.

Watch Video
Card image cap
Sales by customer, Analyzing Data sumif()

Click On Below Button For Learning New Topic.

Watch Video Assignment 17.2

Excel for Data Analyst: Assignment No. 17.2

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 17.2 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Explain Data Validation five different uses and examples: 20 Marks
  2. Describe UNIQUE() and SUMIF(), SUMIFS() functions with examples: 15 Marks
  3. Create a new workbook with name ‘Sales’, name sheet1 ‘Sales’ and name columns, Invoice No, Item, Category, Quantity, Amount and enter 15 records with 8 different invoices: 25 Marks
  4. Now calculate Total amount of day sale, Total Quantity sold, then calculate Number of invoices generated that day, Quantity of different brand item sold, Amount generated per brand: 15 Marks
  5. Create any two dimensional graph having Amount in its Y axis and Brand in its X axis (for this you need to calculate it first): 20 Marks

Note:

You have to submit 02 files:

  • 1st file will be a document having an introduction and the first 2 tasks of this assignment.

  • 2nd file should be in .xls or .xlsx format having your introduction in the worksheet and 3rd tasks of this assignment.
Card image cap
sumifs() in MS Excel

Click On Below Button For Learning New Topic.

Watch Video
Card image cap
2nd example sumifs() in MS Excel

Click On Below Button For Learning New Topic.

Watch Video Assignment 17.4

Excel for Data Analyst: Assignment No. 17.4

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 17.4 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. In previous assignment you created a workbook with name ‘Sales’, in this assignment you need to data validation in this sheet to find out, amount received from different category and then brand for example in this case category ‘Shoes’ and brand ‘Hyderabadi’: 25 Marks
  2. Insert a new column with sales record table and name it ‘Single Item’, then applying if condition find out which item sold in single quantity with showing Yes for single quantity sold and No for more: 25 Marks
  3. Create a table in which calculate revenue generated by single item sold and revenue generated by more than single item sold by brand using SUMIFS function as shown in the lesson: 20 Marks
  4. Apply Data formatting techniques to your calculated data so it looks visually clear and easy to understand: 25 Marks

Note:

You have to submit 01 file:


  • File should be in .xls or .xlsx format having your introduction in worksheet and tasks of this assignment.
Card image cap
Eomonth(), char(row()), and randbetween() in Excel

Click On Below Button For Learning New Topic.

Watch Video Assignment 18

Excel for Data Analyst: Assignment No. 18

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 18 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Describe EOMONTH(), CHAR(), ROW(), and RANDBETWEEN() functions with examples: 35 Marks
  2. Create a new workbook with name ‘Assignment 18’: 10 Marks
  3. In this sheet create a six months sales record with columns Product, January to June, and Total: 10 Marks
  4. In this table enter 16 products manually and then you are not allowed to enter any value manually. You are assigned to use RANDBETWEEN Function for monthly sales and EOMONTH function for months and AUTOSUM for calculating sum of sales per month and total: 40 Marks

Note:

You have to submit 02 files:


  • 1st file should be in document format having your introduction and contain description of task 1 of this assignment.

  • 2nd file should be in .xls or .xlsx format having your introduction in worksheet and task 2 of this assignment.
Card image cap
filter(), choose(), and sort() in Excel

Click On Below Button For Learning New Topic.

Watch Video Assignment 19

Excel for Data Analyst: Assignment No. 19

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 19 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Describe FILTER(), CHOOSE(), SORT(), and OFFSET() functions with examples: 35 Marks
  2. From lesson 14 assignment in which you had created a list of employees with tax and salary calculated, create a new worksheet in it and name it ‘Assignment 19’: 10 Marks
  3. With the help of this record you need to create sorted list from higher to lower salary with the help of functions discussed not with filter options: 10 Marks
  4. Create a list of Top 5 Earners from this record but with the help of FILTER, CHOOSE, and LARGE function: 15 Marks
  5. Create a list of Least 5 Earners from this record but with the help of FILTER, CHOOSE, and SMALL function: 15 Marks

Note:

You have to submit 02 files:


  • 1st file should be in document format having your introduction and contain description of task 1 of this assignment.

  • 2nd file should be in .xls or .xlsx format having your introduction in worksheet and task 2 of this assignment.
Card image cap
Generating Quarterly Report in Excel

Click On Below Button For Learning New Topic.

Watch Video
Card image cap
Quarterly Report with roundup(month()) functions

Click On Below Button For Learning New Topic.

Watch Video Assignment 20.1

Excel for Data Analyst: Assignment No. 20.1

Total Marks 100
Your assignment should start like this 05 Marks
Assignment No 20.1 Introduction of Assignment
Submitted By Your Name
Email address abc@yahoo.com
Submission Date 01-01-2024

Instructions

  1. Describe ROUNDUP(), ROUNDDOWN(), and MONTH() functions with examples: 30 Marks
  2. Create a sheet of per week sales record for the year of 2023 for the same store in which you created earlier, so it will be records you can create a record using RANDBETWEEN() function. Then in the next column create a ‘Quarter’ column in which you separate sales in four quarters of year according to weekly dates using INT, and MONTH function as shown in the lesson.: 20 Marks
  3. In the next column again calculate four quarters of year with the help of ROUNDUP(), function.: 10 Marks
  4. Now as you calculated four quarters of year, calculate sales per quarter according to brand using SUMIF() function.: 10 Marks
  5. Create a chart of sales per quarter according to brand record which displays this record and visualize the sales in graphical interface organized with the sales per week and sales per quarter record.: 25 Marks

Note:

You have to submit 02 files:


  • 1st file should be in document format having your introduction and contain description of task 1 of this assignment.

  • 2nd file should be in .xls or .xlsx format having your introduction in worksheet and task 2 of this assignment.
Card image cap
VLOOKUP(), HLOOKUP() & XLOOKUP()

Click On Below Button For Learning New Topic.

Watch Video
Card image cap
XLOOKUP(), VLOOKUP(), FILTER(), MATCH() & INDEX()

Click On Below Button For Learning New Topic.

Watch Video