EXCEL PROJECT

EXCEL TEMPLATES PRACTICE

INSTRUCTOR
Michael Liew has worked in the IT industry for more than 15 years in several areas like Data Analyst, Senior Programmer, or Technical Analyst. He is based in the UK and holds a Bachelor Degree in Computer Science.

DESCRIPTION
The goal of these four mini projects is to practice Excel building templates with applications in the real world. Creating templates is a fantastic exercise to improve your Excel skills and boost your confidence with the spreadsheet. Not only you will have implement and combine conditional statements and functions like VLOOKUP or SUMIFS, but also you will practice how to edit spreadsheets with a professional look and feel.
The advantage of this type of projects is that you gain experience with Excel since you will have to solve the same regular problems any analyst encounters when working with Excel. These assignments ask the student to provide a solution to a requirement or problem. You task is to decide and implement what functions and formulas are the most suitable to get the best possible solution.
The templates to build are the following:
1) Invoice generator: given a spreadsheet with 20 000 invoices, you will be requested to create a template to retrieve and display in a professional manner the information of any invoice based in just one field.
2) Budget: build a tool to provide quotes to clients of a car garage.
3) Dashboard: create a tool to retrieve employees sales figures from a database and display them in a graph.
4) Metrics breakdown: given a sheet with several items metrics across 66 columns, build a summary table to display metrics of a selected item during specific months.

The four assignments have a guidelines section where each template assignment is broken down into short exercises. This way, you create the template step by step solving one problem at a time.

WHAT YOU WILL PRACTICE:
1) Find, match and retrieving values from different sheets.
2) Editing the spreadsheet with a professional layout.
3) Nested functions (ie vlookup + conditionals).
4) Checkboxes.
5) Real applications of the following functions: VLOOKKUP, SUMIF,INDEX/MATCH, IFERROR, etc.
6) Create charts, insert images, Drop-down lists.
6) Text functions.

REQUIREMENTS
This project requires a minimum knowledge of Excel. Students must have attended an intermediate course of Excel.
The spreadsheets included in the project have been done with the latest Excel version. It is then compatible with Excel 2007, Excel 2010, Excel 2013 and Excel 2016.

DOWNLOAD
You will receive a protected zip folder and its password  in the email confirmation after purchase.
The zip file is made up of 3 PDFs and 8 Excel files:
– PDF with the instructions, including the assignment description, guidelines and the expected outcome.
– PDF with an appendix. Explanations on how Excels formulas work.
– PDF with the  solutions (34 pages). It includes detailed explanations about how to build every template and how functions    work.
– Excel files with all problems solved.

Picture

CONTACT
If you need additional information, do not hesitate to contact us.