How to Pass Excel Assessment Test For Job Applications - Step by Step Tutorial with XLSX work files

Online Training for Everyone

21 Jun 201919:48

EducationalLearning

32 Likes 10 Comments

TLDRThis tutorial video guides viewers through a 20-step Excel assessment test often used by employment agencies to evaluate Excel skills. The instructor demonstrates how to extend columns, calculate and sum expenses, create borders, format as currency, and use formulas for averages and totals. Additionally, the video covers tasks such as changing page orientation, fitting tables to a single page, centering headers, spell checking, renaming sheets, creating charts, adjusting column widths, modifying font sizes, merging and centering text, and forecasting costs. The presenter also encourages downloading a sample file for hands-on learning and provides tips for navigating Excel functions.

Takeaways

😀 The video provides a step-by-step guide to completing an Excel assessment test, which is often used by employment agencies to validate Excel skills.
📊 The test includes 20 steps, starting with extending column B to fit the 'Total Caused by Month' value, and ending with forecasting the least cost for the third quarter.
📝 Understanding the data is crucial; the script describes a table of business expenses categorized by different items and months.
🔢 The SUM formula is used multiple times to calculate total costs by month and average costs, highlighting its versatility in Excel.
🖌️ Formatting is important; the script mentions formatting data as currency and changing the alignment of column D to right to improve readability.
🔗 Borders are added around the table to define its scope and improve the presentation of the data.
📁 The script instructs viewers to save the file in the Documents folder, emphasizing the importance of proper file management.
🖥️ Page orientation is changed to landscape and the worksheet is fit into a single page for printout, showing attention to detail for presentation purposes.
🔠 The script includes a step for spellchecking the document, ensuring the accuracy and professionalism of the final output.
📊 A column chart is created to visualize the expenses for the first quarter, demonstrating the use of Excel's charting capabilities.
📈 The final step involves forecasting costs for the third quarter, illustrating the application of Excel in making predictions based on past data trends.

Q & A

What is the main purpose of the video?
-The main purpose of the video is to guide viewers step-by-step on how to pass an Excel assessment test often required by employment agencies to validate Excel skills.
What is the first step in the Excel assessment test according to the video?
-The first step is to extend column B so that the value of 'total cost by month' fits into the column.
How does the video suggest calculating the total cost of expenses by month?
-The video suggests using the SUM formula to calculate the total cost of expenses by month.
What is the third step in the Excel assessment test?
-The third step is to create a border around the table containing the business expenses.
Which formula is used to calculate the average cost of expenses?
-The AVERAGE formula is used to calculate the average cost of expenses.
How can you change the alignment of column D to the right?
-To change the alignment of column D to the right, select column D and click the 'Align Right' button.
What steps are involved in saving the file to the Documents folder?
-Click on the File tab, select 'Save As,' click 'Browse,' choose the Documents folder, and click the Save button.
How do you change the page orientation to landscape in Excel?
-To change the page orientation to landscape, go to the Page Layout tab, click on Orientation, and select Landscape.
How can you fit the work table into a single page for printout?
-Define the print area by selecting the area of the work table, then go to Page Layout, click Print Area, and select Set Print Area. Verify the print area by checking the Print Preview.
What is the final step in the Excel assessment test in the video?
-The final step is to forecast lease costs for the third quarter by calculating the Q3 total, assuming the lease costs increase by $20 per month.

Outlines

00:00

🧑‍💻 Introduction and Overview of the Excel Assessment Test

This paragraph introduces the video, explaining that it will guide viewers through passing an Excel assessment test often required by employment agencies. The video will cover 20 steps, using a practice document on the left and a list of steps on the right. Viewers are encouraged to download a sample file from the website to follow along. The video begins with extending column B to fit the total cost by month and marking the step as completed.

05:01

📊 Calculating Total and Average Costs

This paragraph covers steps two to seven. Step two involves calculating the total cost of expenses by month using the SUM formula. Step three requires creating a border around the table, while step four involves formatting data as currency. Step five uses the SUM formula to calculate total costs horizontally, and step six uses the AVERAGE formula to calculate average costs without including the total cost. Step seven aligns the text in column D to the right.

10:02

📝 Calculating Quarterly Costs and Saving the File

This paragraph covers steps eight to ten. Step eight involves calculating quarterly costs for Q1 and Q2 using the SUM formula. Step nine instructs on saving the file to the Documents folder. Step ten changes the page orientation to landscape mode.

15:03

📄 Fitting Work Table into a Single Page and Centering Table Header

This paragraph covers steps eleven to thirteen. Step eleven involves fitting the work table into a single page for printout by setting the print area. Step twelve centers the table header values, and step thirteen checks the document for spelling errors.

📝 Renaming Sheets and Adding a New Worksheet

This paragraph covers steps fourteen to sixteen. Step fourteen involves renaming Sheet1 to 'Business Expenses.' Step fifteen adds a new worksheet. Step sixteen creates a column chart to show expenses for the first quarter and ensures it fits into the print area.

🔠 Adjusting Column Width and Formatting Headings

This paragraph covers steps seventeen to nineteen. Step seventeen adjusts the width of columns I and J so the content fits. Step eighteen involves bolding all headings and changing the font size to 12 points. Step nineteen merges and centers the table heading 'Business Expenses.'

🔮 Forecasting Lease Costs and Conclusion

This paragraph covers step twenty, which forecasts lease costs for the third quarter by calculating the Q3 total based on the assumption of a $20 monthly increase. The video concludes by encouraging viewers to like, share, check out additional resources in the video description, and subscribe to the YouTube channel and email list for more content.

Mindmap

Keywords

💡Excel Assessment Test

An Excel Assessment Test is a method used by employment agencies to evaluate the proficiency of job applicants in using Microsoft Excel. In the video, the presenter guides viewers through a specific test with 20 steps, demonstrating how to complete each task effectively. The test in the script involves various Excel functionalities such as extending columns, calculating totals, and formatting data.

💡Column Extension

Column extension refers to the process of widening a column in an Excel spreadsheet to accommodate larger data or labels. In the context of the video, the presenter instructs viewers to extend column B to fit the 'total caused by month' value, illustrating the basic yet essential task of adjusting column widths for better data presentation.

💡Sum Formula

The Sum Formula in Excel is used to calculate the total of a range of cells. The video demonstrates multiple ways to apply this formula, such as manually typing it, using the sum button, or extending an existing formula to calculate the total cost of expenses by month vertically and horizontally across the spreadsheet.

💡Borders

Borders in Excel are lines that can be added around cells to create a defined area, often used for visual separation or to highlight specific data. The script mentions creating borders around the table to organize and distinguish the expenses data, which is a common practice for enhancing readability and structure in spreadsheets.

💡Currency Formatting

Currency formatting in Excel is a way to present numerical values in a monetary format, typically with a currency symbol and two decimal places. The video script describes formatting the expense values as currency, which is crucial for financial data representation and ensures consistency in how monetary values are displayed.

💡Average Formula

The Average Formula in Excel calculates the mean of a set of numbers, which is useful for determining the typical value within a data set. In the script, the presenter uses this formula to calculate the average cost for office supplies, excluding the total cost by month, to provide an insight into the typical expense per item.

💡Alignment

Alignment in Excel refers to the positioning of text or data within a cell, which can be left, right, center, or justified. The video script instructs viewers to change the alignment of column D to right, which is an example of how alignment can be adjusted to improve the presentation of data, especially for dates or monetary figures.

💡Quarterly Costs

Quarterly Costs represent the total expenses aggregated over a three-month period within a fiscal quarter. The script describes calculating the costs for Q1 and Q2 by summing the total costs by month, which is a common financial analysis technique for understanding business expenses over time.

💡Page Orientation

Page Orientation in Excel determines the direction of the document's layout, either portrait (vertical) or landscape (horizontal). The video script includes a step to change the page orientation to landscape, which is often done to fit wider tables or charts onto a printed page.

💡Print Area

The Print Area in Excel is a defined range of cells that are set to be printed when the document is sent to a printer. The script describes setting a print area to ensure that the entire business expenses table fits onto a single page for a clean and concise printout.

💡Chart Creation

Chart Creation in Excel involves selecting data and using the insert chart function to visualize it in graph form. The video script details creating a column chart for the first quarter expenses, which helps in quickly understanding the distribution and magnitude of expenses during that period.

💡Forecasting

Forecasting in the context of Excel and financial analysis involves predicting future values based on past data trends. The script's final step involves forecasting the least cost for the third quarter by assuming a continued increase from the second quarter, demonstrating a basic forecasting method.

Highlights

Introduction to the importance of passing Excel assessment tests for employment purposes.

Overview of the video: step-by-step completion of a 20-step Excel assessment test.

Step 1: Extending column B to fit 'Total Cost by Month'.

Explanation of the data table consisting of business expenses categorized by items and months.

Step 2: Using the SUM formula to calculate total costs of expenses by month.

Three methods to use the SUM formula: typing manually, using the SUM button, and copying & pasting values.

Step 3: Creating a border around the entire expenses table.

Step 4: Formatting data values as currency using the Accounting Number Format.

Step 5: Calculating total costs using the SUM formula horizontally for rows.

Step 6: Calculating average costs using the AVERAGE formula and excluding total costs from the calculation.

Step 7: Changing alignment in column D to right alignment.

Step 8: Calculating quarterly costs for Q1 and Q2 using the SUM formula for relevant months.

Step 9: Saving the file to the Documents folder.

Step 10: Changing page orientation to landscape.

Step 11: Fitting the work table into a single page for printout by defining the print area.

Step 12: Centering table header values.

Step 13: Spellchecking the document using the Review tab.

Step 14: Renaming 'Sheet1' to 'Business Expenses'.

Step 15: Adding a new worksheet.

Step 16: Creating a column chart to show expenses for the first quarter.

Step 17: Changing the width of columns I and J to fit content.

Step 18: Bold all headings and change the font size to 12 points.

Step 19: Merging and centering the table heading 'Business Expenses'.

Step 20: Forecasting lease cost for the third quarter by calculating Q3 total based on previous increase trends.

Encouragement to like, share, and subscribe for more content, and join the email list for updates.

Transcripts

Browse More Related Video

Tutorial on Rivera Engineering excel project on Mac in excel

Excel Module 2: Rivera Engineering

Excel Module 1 Project 2: Retail Pro

Excel for Beginners - The Complete Course

Excel Test for Job Interview: Excel Formulas & Functions Questions and Answers

Excel Module 2 SAM End of Module Project 1 | NP_EX19_EOM2-1 | First Mittagong Community Bank