Excel Practice Exercises PDF with Answers
To solve the following exercises, you will need to know about the SUM , AVERAGE , IF , VLOOKUP , INDEX , MATCH , ROUNDUP , UNIQUE , COUNTIF , LEFT , SEARCH , MID , RIGHT , LEN , FIND , SUBSTITUTE , AND , and SUMIF functions and the Data Bars feature of Excel. You can use Excel 2010 or later, except for the UNIQUE function, which is only available in Excel 2021 .
Download the Practice Files
You can download the PDF and Excel files by filling out this form:
Eleven Practice Exercises with Solutions.pdf [/wpsm_box] [wpsm_box type="download" float="none" textalign="left"] Eleven Practice Exercises.xlsx [/wpsm_box] -->
Problem Overview
Here is a snapshot of the first two problems. The solutions to all problems are given in a separate sheet.
- Exercise 01 – Class Performance Evaluation . You will find these values –
- The total number for each student,
- Their average on those subjects,
- Based on the average score, you will return a GPA. For GPA calculation, less than 60 is B and higher is A .
- You need to find the employee salary in the lookup table on the right side.
- Exercise 03 – Lookup Values (Any Direction) .
- Here your task is the same as the second task. However, this time the lookup range is on the right side. Therefore, you cannot use the VLOOKUP function here.
- You will need to round the sales generated values in this exercise.
- You will need to add the first name and last name.
- Your task is to create a Data Bar for the salary values and hide the salary values.
- Firstly, you need to find the unique values in a list of names.
- Then, you will find how many times that value occurred in that lis
- You need to separate the three parts of a name from a given list.
- You will need to find the total sales for a particular country.
- Your objective is to ensure that users cannot type less than 0 in a column.
- Your target is to determine whether a date is between two dates or not.
Here is a screenshot of the solutions to the first two problems.
What is ExcelDemy?
Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio
You really help me alot thanks
Hello, Aleem!
Thanks for your appreciation. stay in touch with ExcelDemy .
Regards ExcelDemy
Thanks a lot
Hello please give more excel questions and answers for practice
Hello David ,
Here are some article you will find more exercises. Excel Practice Test & Quiz Sample Excel File with Employee Data for Practice Advanced Excel Exercises with Solutions PDF
Dear Rashmi ,
You are most welcome.
This is very helpful. You are God send
Thanks for your appreciation.
One of the best materials I have been looking for on the internet. I wish you give us more examples rich in Excel with VBA well explained like the pdf file.
Hello Angufibo David ,
Thank you so much for your kind words! I’m glad you found the material helpful. We’re always working to provide more detailed and practical examples, especially with Excel and VBA. If you have any specific topics you’d like to see covered, feel free to share!
One of the best materials I have been looking for on the internet. This is very helpful.
Hello Floyd ,
Thanks for your appreciation, it means a lot to us. I’m glad to hear that you found this material so helpful. It’s great knowing the guide provided exactly what you were looking for. If you need further guidance or have any specific questions, don’t hesitate to reach out—happy to help! Keep exploring Excel with ExcelDemy!
Hi. Unable to download this practice file. Please mail me
Hello Dr. Karumuri Sri Rama Murthy ,
You can download the Excel and PDF files free of cost just by providing your valid email address. To get the files go to the “ Download the Practice Files ” section of this post and enter your email address. Then check your email immediately after to get the download links.
Best Regards ExcelDemy
Leave a reply Cancel reply
ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.
See Our Reviews at
- User Reviews
- List of Services
- Service Pricing
- Create Basic Excel Pivot Tables
- Excel Formulas and Functions
- Excel Charts and SmartArt Graphics
- Advanced Excel Training
- Data Analysis Excel for Beginners
Advanced Excel Exercises with Solutions PDF
Practice And Learn Excel Online For Free
Here you can practice dozens of Excel exercises with full solutions and explanations. If you are new to excel, please check out our Excel Basics Tutorial!
The explanations can be found in the Answer tab in each of the exercises. Have fun 🙂
New! Excel Self Assessment – Test your Excel skills and understand what to learn next to be even better!
- Basic Math and Percentages Exercise
SUM Function
- SUM Tutorial and Practice
- SUM Function – Exercise 1
- SUM Function – Exercise 2
- SUM Function – Exercise 3
COUNT, COUNTA & COUNTBLANK Functions
- COUNT Tutorial and Practice
- COUNTA Tutorial and Practice
- COUNT & COUNTA Functions – Exercise 1
- COUNT & COUNTA Functions – Exercise 2
- COUNT, COUNTA & COUNTBLANK Functions – Exercise 3
AVERAGE Function
- AVERAGE Tutorial and Practice
- Average Function – Exercise 1
- Average Function – Exercise 2
- Average Function – Exercise 3
MIN/MAX Function
- MIN Tutorial and Practice
- MAX Tutorial and Practice
- MIN/MAX Function (combined with AVERAGE)
- MIN/MAX Function (combined with IF)
IF Function
- IF Tutorial and Practice
- IF Function – Exercise 1
- IF Function – Exercise 2
- IF Function – Exercise 3
- IF Function – Exercise 4
Nested IF Function
- Nested IF Tutorial and Practice
- Nested IF – Exercise 1
VLOOKUP Function
- VLOOKUP Tutorial and Practice
- VLOOKUP Function – Exercise 1
- VLOOKUP Function – Exercise 2
- VLOOKUP with Approximate Match – Exercise 3
HLOOKUP Function
- HLOOKUP Function – Exercise 1
SUMIF Function
- SUMIF Tutorial and Practice
- SUMIF Function – Exercise 1
- SUMIF Function – Exercise 2
COUNTIF Function
- COUNTIF Tutorial and Practice
- COUNTIF Function – Exercise 1
- COUNTIF Function – Exercise 2
SUMIFS Function
- SUMIFS Tutorial and Practice
- SUMIFS Function – Exercise 1
- SUMIFS Function – Exercise 2
COUNTIFS Function
- COUNTIFS Tutorial and Practice
- COUNTIFS Function – Exercise 1
- COUNTIFS Function – Exercise 2
Pivot Table
- Pivot tables Tutorial and Practice
- Pivot Table basics – Exercise 1
Practice Excel Exams and Tests
Want to test yourself? check out our Excel Practice Tests Page
Terms and Conditions - Privacy Policy
Excel tutorial
Assignment problem in excel.
Use the solver in Excel to find the assignment of persons to tasks that minimizes the total cost.
Formulate the Model
The model we are going to solve looks as follows in Excel.
1. To formulate this assignment problem , answer the following three questions.
a. What are the decisions to be made? For this problem, we need Excel to find out which person to assign to which task (Yes=1, No=0). For example, if we assign Person 1 to Task 1, cell C10 equals 1. If not, cell C10 equals 0.
b. What are the constraints on these decisions? Each person can only do one task (Supply=1). Each task only needs one person (Demand=1).
c. What is the overall measure of performance for these decisions? The overall measure of performance is the total cost of the assignment, so the objective is to minimize this quantity.
2. To make the model easier to understand, create the following named ranges .
3. Insert the following functions.
Explanation: The SUM functions calculate the number of tasks assigned to a person and the number of persons assigned to a task. Total Cost equals the sumproduct of Cost and Assignment.
Trial and Error
With this formulation, it becomes easy to analyze any trial solution.
For example, if we assign Person 1 to Task 1, Person 2 to task 2 and Person 3 to Task 3, Tasks Assigned equals Supply and Persons Assigned equals Demand. This solution has a total cost of 147.
It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.
Solve the Model
To find the optimal solution, execute the following steps.
1. On the Data tab, in the Analyze group, click Solver.
Note: can't find the Solver button? Click here to load the Solver add-in .
Enter the solver parameters (read on). The result should be consistent with the picture below.
You have the choice of typing the range names or clicking on the cells in the spreadsheet.
2. Enter TotalCost for the Objective.
3. Click Min.
4. Enter Assignment for the Changing Variable Cells.
5. Click Add to enter the following constraint.
Note: binary variables are either 0 or 1.
6. Click Add to enter the following constraint.
7. Click Add to enter the following constraint.
8. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.
9. Finally, click Solve.
The optimal solution:
Conclusion: it is optimal to assign Person 1 to task 2, Person 2 to Task 3 and Person 3 to Task 1. This solution gives the minimum cost of 129. All constraints are satisfied.
Learn more, it's easy
- Transportation Problem
- Assignment Problem
- Shortest Path Problem
- Maximum Flow Problem
- Capital Investment
- Sensitivity Analysis
- System of Linear Equations
Download Excel File
- assignment-problem.xlsx
- Analysis ToolPak
Follow Excel Easy
Become an Excel Pro
- 300 Examples
Assignment Problem • © 2010-2024 Excel is Awesome, we'll show you: Introduction • Basics • Functions • Data Analysis • VBA
Excel Exercises helps regular people learn Excel as quickly as possible.
Our bite-sized, interactive lessons make excel skills easy to learn and remember..
The Fun Way to Learn Excel Skills
Welcome to the fun, hands-on way to learn Excel! My name is Jake and I'm known as a spreadsheet wizard around my office, but it wasn't a fast or easy process to get here. I watched several hours worth of Excel training videos, but found I didn't really master a formula until I actually got my hands dirty and used the formula at work. It took me a few years of working in finance and consulting- using Excel nearly every day- to internalize all the keyboard shortcuts and functions. I started wondering why there wasn't an easier and faster way to master Excel.
Like I mentioned before, I spent hours watching Excel training videos, but they were honestly kind of boring. I'd find myself zoning out and would need to rewind and re-watch each video two or three times. And without immediately putting the techniques from the videos into practice, I'd forget the techniques right after watching the video.
Excel Practice For Busy People
Practice Excel the right way - in small, bite-sized, engaging lessons, rather than falling asleep reading a lengthy article or daydreaming through a long video. This is Excel practice for those who learn by doing, not by watching. There's a reason thousands of people are choosing to get their Excel practice from Excel Exercises: because it works. Each lesson is designed to keep you engaged and entertained while walking you through new concepts, so that you can't advance if you're not absorbing the information. That's just one of the ways Excel Exercises helps you learn more efficiently and maximize your practice time.
Excel Exercises Solves the "Boring Video" Problem
We walk you through all the Excel functions that you need to know, forcing you to type through practice exercises to get hands-on and commit them to memory. You'll also practice keyboard shortcuts on your own keyboard to build muscle memory and get faster at manipulating spreadsheets. Through hands-on repetition and smart skills targeting, I've distilled all the skills I've learned from years of working with Excel to a program that can be completed in a matter of days.
Have Fun While You Learn With Excel Exercises
These practice exercises aren't just engaging; they are actually fun. Score points by answering questions correctly and advance through the levels as you learn, rather than relying on boring memorization. You'll start by practicing some easy skills and work your way up to practicing more advanced techniques. By gradually introducing new concepts for you to practice, we make it easy to learn all the techniques you need to become an Excel master. Whether you're searching for easy Excel practice exercises or more advanced formula practice, Excel Exercises offers a fun learning experience for all skill levels - it doesn't even feel like learning!
Thousands of people have already used Excel Exercises to practice Excel skills and advance their careers. Solid Excel skills are critical for most finance, accounting, consulting, and other data-oriented jobs. And let's be honest - if your shortcut game is on point you'll impress anyone watching over your shoulder. Learn Excel the fun way today and get your career moving in the right direction.
Online Excel Practice
Excel Exercises lets you practice Excel online, so you can learn functions and keyboard shortcuts right in your browser. While we do have example projects and real practice spreadsheets you can download if you'd like, you can also stick to our online Excel simulator for Excel practice that's 100% online.
Use Excel Exercises to Excel-erate Your Career
Almost any job in Finance, Accounting, Data Science, Consulting, or any other quantitative industry will require you to use spreadsheets. By mastering Excel you can give yourself an edge by completing your work faster and better than your peers. Many jobs today also require an Excel test as part of the application and interview process. Whether you're applying for an internship or you're already an executive, Excel mastery is a tangible and noticeable skill that can help you get the offer, earn more money, and make yourself indispensable.
Today's job market demands strong spreadsheet skills like never before. Everything from simple data entry to advanced data analysis will require proficiency with Excel in order to pass the interview process and perform with the speed and quality necessary to thrive in your new job. Luckily, there are now more resources than ever available to help you learn Excel online. Excel Exercises is the first web-based Excel practice resource that simulates real Excel practice exercises right in your browser. Let Excel Exercises be your new secret weapon to master Excel and get a new job or move up in your current role.
Hands-on Exercises
Most people can't learn new skills simply by watching. There's a reason you hear that the best way to learn Excel is just to use it at work for a few years. But when you don't have years to learn Excel, your best option is the curated simulations offered by Excel Exercises. We walk you through new skills and let you write the actual formulas and tap out the keyboard shortcuts to build muscle memory and learn by doing. By focusing on one skill at a time, you'll get enough reps to commit the skill to memory before moving on.
Excel Exercises for Beginners and Experts
Whether you already have some Excel experience or you've never written a "sum" function in your life, this site will teach you the tools to stand out at work as the Excel Master. It starts easy with simple functions to build a solid foundation. Then it becomes more challenging as the lessons incorporate new concepts, shortcuts, and advanced functions to build your skills, boost your efficiency, and expand on what you thought was possible with Excel. Whether you're a beginner or advanced, there is always a new Excel skill you can learn.
Excel Tutorial: How To Make An Assignment Tracker In Excel
Introduction.
Are you struggling to keep track of your assignments? In this Excel tutorial, we will guide you through the process of creating an assignment tracker in Excel. Whether you're a student or a professional, keeping track of your tasks and deadlines is crucial for staying organized and on top of your workload. Using Excel for tracking assignments offers a dynamic and customizable solution for managing your to-do list effectively.
Key Takeaways
- Creating an assignment tracker in Excel is a valuable tool for staying organized and on top of your workload.
- Understanding the basics of Excel, such as layout and functions, is crucial for effectively using the software for tracking assignments.
- Setting up the spreadsheet with properly named and formatted columns is essential for efficient assignment tracking.
- Utilizing Excel's sorting, filtering, formulas, and functions can greatly streamline the process of organizing and analyzing assignment data.
- Visual representations, such as charts and graphs, are effective for visually tracking and analyzing assignment data.
Understanding the basics of Excel
Microsoft Excel is a powerful tool for organizing, analyzing, and presenting data. In this chapter, we will discuss the layout and basic functions of Excel, as well as how to navigate through the software and use basic commands.
Excel is organized into a grid of cells, with each cell identified by a unique address composed of the column letter and row number (e.g., A1, B2, C3, etc.). The main components of the Excel interface include the Ribbon, which contains all the commands and tools necessary for working with your data; the Formula Bar, where you can enter and edit formulas and data; and the Worksheet Area, where you can input, manipulate, and visualize your data.
To navigate through Excel, you can use the mouse to click on different cells, columns, and rows. You can also use the arrow keys on your keyboard to move around. Basic commands include formatting cells, entering data, creating formulas, and using functions. You can also customize your spreadsheet using features such as conditional formatting, sorting, and filtering.
Setting up the spreadsheet
When it comes to tracking assignments, Excel is a great tool to efficiently organize and manage your tasks. Here's how you can set up a simple yet effective assignment tracker in Excel.
A. Create a new spreadsheet in Excel
Before you can start tracking your assignments, you'll need to create a new spreadsheet in Excel. Open Excel and select "Blank workbook" to start with a clean slate.
B. Name and format the columns for the assignment tracker
Once you have your new spreadsheet ready, it's time to set up the columns for your assignment tracker. Here's how you can name and format the columns:
- Assignment Name: Create a column for the names of the assignments you need to track. This will be the main identifier for each task.
- Due Date: Next, create a column for the due dates of each assignment. This will help you prioritize tasks based on their deadlines.
- Status: Create a column to track the status of each assignment, such as "Not Started," "In Progress," or "Completed." This will give you a quick overview of where each task stands.
- Priority: Add a column for the priority level of each assignment, such as "High," "Medium," or "Low." This will help you focus on the most important tasks first.
- Notes: Lastly, create a column for any additional notes or details related to each assignment. This can include instructions, resources, or any other relevant information.
By naming and formatting the columns in this way, you'll have a clear and organized structure for tracking your assignments in Excel.
Data entry and organization
When creating an assignment tracker in Excel, it is essential to input the necessary data for each assignment and utilize Excel's sorting and filtering functions to organize the data effectively.
- Start by creating a table in Excel with columns for assignment name, due date, status, priority, and any other relevant information.
- Enter the data for each assignment into the respective columns, ensuring accuracy and consistency.
- Consider adding additional columns for tracking progress, notes, or any other details that may be helpful for managing assignments.
- Once the data is entered, utilize Excel's sorting function to arrange the assignments based on due date, priority, or any other relevant criteria.
- Use filtering to display specific subsets of assignments, such as those with a particular status or priority level, to focus on specific tasks.
- Explore the use of conditional formatting to visually highlight important information, such as approaching due dates or overdue assignments.
Using formulas and functions
When creating an assignment tracker in Excel, it is essential to understand how to use formulas and functions to calculate and analyze the data effectively. By utilizing these tools, you can automate the process of tracking progress and gaining valuable insights into your assignments. In this chapter, we will explore the basic formulas and functions that are vital for building an efficient assignment tracker.
Introduce basic formulas for calculating assignment progress
- SUM: The SUM function is fundamental for adding up the scores or completion status of each assignment. It allows you to quickly calculate the total progress made on all assignments.
- COUNT: The COUNT function is useful for counting the number of completed assignments or the total number of assignments in the tracker. This can give you a clear idea of the workload and completion rate.
- IF: The IF function is a powerful tool for setting conditions based on the progress of assignments. You can use it to categorize assignments as "completed" or "incomplete" based on specific criteria.
Utilize functions such as SUM, COUNT, and IF for tracking and analyzing data
- SUM: To track the overall progress of assignments, you can use the SUM function to add up the scores or completion status of each assignment. This will give you a comprehensive view of the total progress made.
- COUNT: By utilizing the COUNT function, you can analyze the number of completed assignments and the total number of assignments in the tracker. This can help you assess completion rates and workload effectively.
- IF: The IF function can be used to set conditions for tracking and analyzing data. For example, you can use it to categorize assignments as "completed" or "incomplete" based on certain criteria, allowing for easy analysis and decision-making.
Creating visual representations
When it comes to tracking assignments in Excel, making use of visual representations can greatly enhance the effectiveness of your tracker. Visual representations such as charts and graphs can provide a quick and easy way to understand and analyze the data.
Excel offers a wide range of chart and graph features that allow users to create visually appealing representations of their assignment data. Whether it's a simple bar chart or a more complex pie chart, Excel provides the tools to effectively visualize the progress and status of assignments.
Visualizing data is important for tracking assignments as it allows for quick and easy interpretation of the information. Instead of sifting through rows and columns of raw data, charts and graphs provide a clear and concise overview of the assignment status, deadlines, and progress. This visual representation can help users identify patterns, trends, and potential issues more efficiently.
In conclusion, creating an assignment tracker in Excel is a practical and efficient way to stay organized and on top of your workload. By following the key points we've discussed, including setting up a table, using formulas for automatic calculations, and utilizing conditional formatting for visual cues, you can easily keep track of all your assignments in one place.
Using Excel for organizing and tracking assignments offers numerous benefits, such as the ability to customize columns and rows to fit your specific needs, easily update and rearrange data, and generate insightful charts and graphs for progress analysis. Its user-friendly interface and powerful features make it an indispensable tool for students, professionals, and anyone looking to stay on top of their tasks.
Immediate Download
MAC & PC Compatible
Free Email Support
Related aticles
The Benefits of Excel Dashboards for Data Analysts
Unlock the Power of Real-Time Data Visualization with Excel Dashboards
Unlocking the Potential of Excel's Data Dashboard
Unleashing the Benefits of a Dashboard with Maximum Impact in Excel
Exploring Data Easily and Securely: Essential Features for Excel Dashboards
Unlock the Benefits of Real-Time Dashboard Updates in Excel
Unleashing the Power of Excel Dashboards
Understanding the Benefits and Challenges of Excel Dashboard Design and Development
Leverage Your Data with Excel Dashboards
Crafting the Perfect Dashboard for Excel
An Introduction to Excel Dashboards
How to Create an Effective Excel Dashboard
- Choosing a selection results in a full page refresh.
COMMENTS
Intermediate Microsoft Excel Worksheet: Practice 1. Objectives: The Learner will be able to: Enter data into a Spreadsheet. Use AutoFill with labels, data, and formulas. Format Cell Borders and Contents. Calculate the total across the rows. Calculate the total for each column. Use Conditional Formatting.
List of Numbers in Excel – Generate sequential or custom patterns of numbers for your Excel projects. ABC List in Excel – Easily create alphabetical lists from A-Z for indexing, sorting, and categorization. Days of the Week in Excel – A quick list of the days of the week, essential for scheduling and planning.
Download our 100% fre e Excel Practice Workbook. The workbook contains 50+ automatically graded exercises. Each exercise is preceeded by corresponding lessons and examples. Download.
Exercise 04 – Rounding Values. You will need to round the sales generated values in this exercise. Exercise 05 – Joining Two Strings. You will need to add the first name and last name. Exercise 06 – Conditional Formatting. Your task is to create a Data Bar for the salary values and hide the salary values. Exercise 07 – Counting Unique ...
If you are new to excel, please check out our Excel Basics Tutorial! The explanations can be found in the Answer tab in each of the exercises. Have fun. New! Excel Self Assessment – Test your Excel skills and understand what to learn next to be even better!
The model we are going to solve looks as follows in Excel. 1. To formulate this assignment problem, answer the following three questions. a. What are the decisions to be made? For this problem, we need Excel to find out which person to assign to which task (Yes=1, No=0). For example, if we assign Person 1 to Task 1, cell C10 equals 1. If not ...
To open a new Excel workbook, start by opening Microsoft Excel on your computer. Once the program is open, you can either click on "File" in the top left corner and select "New" to create a new workbook or use the keyboard shortcut Ctrl + N. This will open a new blank spreadsheet for you to work with. B. Discuss the options for formatting the ...
Excel Exercises Solves the "Boring Video" Problem. We walk you through all the Excel functions that you need to know, forcing you to type through practice exercises to get hands-on and commit them to memory. You'll also practice keyboard shortcuts on your own keyboard to build muscle memory and get faster at manipulating spreadsheets.
Free online Excel training for beginners and experts (all skill levels). 3 lessons (30 minutes total), free assignments & much more.
A. Input the necessary data for each assignment. Start by creating a table in Excel with columns for assignment name, due date, status, priority, and any other relevant information. Enter the data for each assignment into the respective columns, ensuring accuracy and consistency. Consider adding additional columns for tracking progress, notes ...