What is usually on an Excel assessment?

Excel assessments are used by employers and schools to test a person’s skills and knowledge in Microsoft Excel. Excel is one of the most widely used software programs in the workplace, and having strong Excel skills is a big advantage when looking for a job. Excel tests usually focus on the most important and commonly used features of Excel. The exact topics will vary depending on the level of the test, but most Excel assessments will include questions on formulas, functions, charts, formatting, data manipulation, and workflows. By understanding what is typically covered on an Excel exam, you can better prepare for excelling on the assessment.

Formulas

Formulas are one of the basic building blocks of Excel, and they allow users to perform calculations and work with data. An Excel test will almost always have multiple questions testing your formula knowledge. This includes questions on:

• Basic arithmetic formulas using operators like +, -, *, /
• Cell references and being able to build formulas by referencing cells
• Common functions like SUM, AVERAGE, COUNT, MAX, MIN
• Logical functions like IF statements
• VLOOKUP and HLOOKUP functions
• INDEX and MATCH functions

You may be asked to write a formula based on a described need, identify errors in an existing formula, choose the right formula for a specific calculation from a list, or interpret the results of a formula. Getting formulas correct is critical in Excel, so assessors want to thoroughly test your skills in this area.

Functions

In addition to the basic functions mentioned above, Excel has hundreds of functions across many categories like math, text, date, lookup, logical etc. Advanced Excel tests will expect you to be familiar with a wide variety of Excel functions and understand how and when to use them. Some specific functions that are commonly tested include:

• Date functions like TODAY(), NOW(), DAYS360()
• Text functions like LEFT(), RIGHT(), MID(), TRIM(), LEN()
• Statistical functions like STDEV(), STDEVP(), VAR(), VARP()
• Financial functions like PV(), FV(), RATE(), NPV()
• Logical functions like AND(), OR(), NOT(), IFERROR()
• Math functions like ROUND(), MOD(), SUBTOTAL()

You will need to know the arguments these functions take, how they work, and when it would be appropriate to use them in a formula.

Charts and Graphs

Charts allow Excel users to visualize data trends and insights. Expect Excel tests to assess your skills in creating and formatting various Excel chart types. Some typical charting skills that exams test for include:

• Creating column, bar, line and pie charts from scratch
• Choosing the right chart type based on the data
• Formatting chart elements like the chart title, axis titles, data labels, and legend
• Adjusting scale and axes
• Sorting and filtering data in charts

Advanced charting proficiency like using combo charts, trendlines, and error bars may be tested as well.

Data Manipulation

Excel is used to organize, analyze and report on data, so testing your ability to work with data is standard on Excel exams. These are some typical data manipulation tasks you should prepare for:

• Sorting and filtering data
• Removing duplicates
• Subtotaling and grouping data
• Joining data from separate tables with VLOOKUPS
• Consolidating data from multiple sheets
• Importing and exporting data
• Validating cell data
• Transposing row and column data

Excel has a wealth of tools for managing data, and you need to be comfortable using them. More advanced proficiency like Power Pivot and Power Query may be tested if the exam focuses on business intelligence.

Formatting

While Excel’s key purpose is data analysis, it is still important to present that data in a clean and readable format. Excel tests will evaluate your formatting skills with questions on:

• Font, cell, and number formatting
• Borders, cell styles, and theme formatting
• Merged cells and wrap text
• Conditional formatting like color scales and data bars

Formatting may sometimes feel cosmetic, but it greatly impacts the clarity and presentation of your data. A poorly formatted report is ineffective, so assessors want to ensure you can create clean and readable Excel worksheets.

PivotTables and PivotCharts

PivotTables are one of Excel’s most powerful features for summarizing and analyzing data. Many Excel exams test your skill in creating PivotTables and PivotCharts. Typical skills that are evaluated include:

• Building a PivotTable from a data table
• Laying out PivotTable fields in the various areas
• Using filters, slicers, and timeline controls
• Grouping data in PivotTables
• Creating calculated fields and items
• Formatting PivotTables
• Generating PivotCharts

Advanced topics like creating calculated fields or connecting to external data sources may be assessed for expert-level exams.

Workflows

In real-world situations, Excel skills need to be applied together to solve business problems. Excel assessments may test your ability to combine different functions and tools to effectively perform workflows like:

• Lookup workflows using INDEX/MATCH, VLOOKUP etc.
• Financial analysis workflows for ROI, amortization, depreciation etc.
• What-if analysis workflows using data tables and goal seek
• Reporting workflows with sorting, filtering, PivotTables, and conditional formatting.

These exams are assessing if you can stitch together Excel tools into effective business solutions.

Tables

This table provides examples of specific Excel skills tested under major feature categories.

Keyboard Shortcuts

In addition to testing your knowledge, Excel exams may also evaluate your efficiency with the program. This includes your familiarity with keyboard shortcuts for performing common tasks faster. Some shortcuts that may be tested include:

• Ctrl + C and Ctrl + V to copy/paste
• Ctrl + Z to undo
• Ctrl + F to open the Find dialog
• Ctrl + P to open the Print dialog
• Ctrl + N to create a new workbook
• Ctrl + S to save
• Ctrl + B for bold, Ctrl + I for italic
• Ctrl + A to select all
• Ctrl + arrow keys to navigate around cells quickly

Knowing shortcuts saves time and increases productivity in Excel.

Advanced Tools

For very advanced Excel exams, there may also be questions testing knowledge of advanced tools like:

• Power Pivot, to work with large datasets
• Power Query, for transforming data
• Power View, for data visualization and reporting
• Macros and VBA, for automating tasks
• What-if analysis tools
• Lookup formulas like INDEX and MATCH

Very few entry-level roles require in-depth expertise in these tools. But for analysts, consultants, and other roles relying heavily on Excel, this more advanced knowledge will be essential.

Specific Applications

In some cases, Excel tests focus on the specific business applications of Excel rather than a broad overview. For example:

• Finance – Math and financial functions, what-if analysis, financial charting, valuations, modeling, etc.
• Accounting – Summarizing and consolidating data, PivotTables, linking sheets, auditing tools, etc.
• Business Intelligence – Power Pivot, Power Query, PivotTables, external data connections, reporting, etc.
• Data Analysis – Statistical functions, trendlines, regression, T-Tests, external data connections, solver, etc.

Understanding the specific context allows you to better prepare for the Excel skills most relevant to that role or industry.

Levels of Difficulty

Excel exams can range from beginner to extremely advanced. Typically, assessments fall into levels like:

• Beginner – Basic formulas, simple functions, straightforward charts, basic data sorting and filtering.
• Intermediate – More complex functions and formulas, PivotTables, logical expressions, intermediate data tools.
• Advanced – Array formulas, advanced lookup functions, data analysis features, Power Pivot, macros.
• Expert – Obscure nested functions, custom VBA procedures, advanced data modeling, connecting external data sources.

The level of difficulty should align with the job requirements. A financial analyst role will need more advanced knowledge than an entry-level administrative assistant.

Length of Assessment

Excel exams can range from short 10-minute tests of basic abilities to extensive multi-hour assessments. Some examples:

• 10-20 minute basic skills quiz
• 30-45 minute formal written test
• 1-2 hour detailed knowledge assessment
• 3+ hour project or assignment
• Real-world worksheet mockups

Briefer tests focus on high-level abilities, while longer assessments provide a comprehensive evaluation across many skill sets. The assessment length should suit the job level and allow enough time to fully demonstrate Excel capabilities.

Question and Activity Types

Excel exam questions can take many forms beyond just standard multiple choice. Some common question types include:

• Multiple Choice – Choose the right option from a list.
• True/False – Assess whether statements are correct.
• Fill-in-the-blank – Write a short response like a formula.
• Matching – Match Excel functions to their definitions.
• Data Analysis – Interpret charts and data to answer questions.
• Hands-on Activities – Perform Excel tasks like in real workbooks.

More interactive tests like hands-on simulations better demonstrate real-world Excel skills.

Scoring and Grading

Excel exams are typically scored based on:

• Percentage of questions answered correctly
• Time taken to complete
• Accuracy of hands-on simulation activities

Performance benchmarks are often set, such as:

• Beginner: 50-60% correct
• Intermediate: 60-80% correct
• Advanced: 80-90% correct
• Expert: 90-100% correct

More difficult exams may use harsher scales. The grading approach will depend on the specific test and skills being evaluated.

Conclusion

Excel skills are critical in today’s data-driven workplace, so assessment tests allow employers to quantify and validate your Excel capabilities. By understanding what Excel knowledge and activities are commonly evaluated, you can be well-prepared to demonstrate proficiency no matter what questions are asked. Focus your preparation on mastering core Excel functionality like formulas, functions, PivotTables, charts, data analysis, and key keyboard shortcuts. With comprehensive Excel expertise and some practice with sample tests, you can excel when put to the Excel test!