Advanced Excel TutorialsExcel Assessment TestExcel Formulas and FunctionsExcel Interview QuestionsExcel Test for Interview Candidates

Excel Pivot Table Tutorial For Beginners

A Microsoft Excel test is a pre-employment hiring assessment that employers use during the recruitment process to check how proficient candidates are with Microsoft Excel. Spreadsheets allow users to input and analyse data efficiently, and are used in all kinds of office jobs; as such, Microsoft Excel Assessment Tests are becoming increasingly common. In this post we will learn how to get prepared for Microsoft Excel Pre Employment Test by looking at Top 50 Excel Test for Job Interview Questions and Answers. We are also going to look at how companies conduct excel skills test, how employers test for Excel skills, review sample questions asked as part of Excel Portion of Job Interview and look at the resources to get prepared for each test the fastest way possible.

 

Why Employers Test Job Candidates?

Assessing a job candidate’s Microsoft Excel proficiency is an important step in making the right hire. The intermediate Microsoft Excel skills test is helpful in learning if a job candidate understands how to manipulate the many functions, tools, and formulas of Excel to present extensive information, identify key trends, or calculate financial and numerical data.

Microsoft Excel is a complex business application which allows companies to record, track, and analyze data and perform very complex modelling and calculations, essential for measuring company performance, maximizing return on investment, and defining goals. Since Microsoft Excel is used in a lot of organizations, employers would like to make sure that candidates has solid Excel skills and experience using the tool. A new employee who can comfortably navigate is prepared to immediately contribute by producing meaningful, data-driven spreadsheets, reports, and graphs to best serve company needs.

 

How Employers Test for Excel Skills?

There are multiple providers that conduct the tests and each one of the tests is a little different. Most of the time employers use below providers to conduct the tests

  • Indeed.com
  • IKM
  • SkillCheck
  • TotalTesting

Because Microsoft Excel is so popular and useful, companies are trying to evaluate candidates by conducting pre-employment assessment test. An Excel Interview Test is a screening process employers use as part of the candidate employment interview to test a potential candidate on their knowledge and proficiency of Microsoft Excel.

Each employer might use a slightly different variation of the Excel test. The Excel Assessment Test typically broken down into a multiple-choice section and an interactive portion. Some tests are timed but some of them do not have a limit on how long applicant can take to answer the questions, but there might be exceptions.

 

Excel Skill Assessments through questions

There are different types of questions typically being presented during Excel Test :

  • Multiple Choice Questions
  • Multiple Answer Questions
  • True/False Questions

 

Interactive Test Simulators

It is rare that only one type of question is used during the test – most of the time questions are part of the large pool and testing software randomly selects certain number of questions from the pool and presents them to the candidate.

 

Hands-On Excel Skill Assessment

Sometimes companies also conduct hands on Excel Job Interview Test when they provide candidates with sample data file as ask them to perform certain tasks with the data, based on instructions provided.

 

Excel Skills Tested as Part of Test

During Excel Interview employer assesses candidate’s ability to use Microsoft Excel's user interface quickly and efficiently to access the right features and perform simple tasks in the application. It validates fundamental knowledge from determining the best way to open a recently closed Workbook to using the correct formula to perform automatic calculations. Candidates are required to answer true/false,  multiple-choice questions and multiple answer questions, based on Excel spreadsheets, charts and tables filled with sample data

Below is the list of topics candidates are tested for:

  • Follow the correct steps to apply formatting, use formulas or accomplish certain things in spreadsheet
  • Apply styles, use simple fill patterns and apply formatting
  • Identify the proper keyboard shortcuts to accomplish tasks more quickly.
  • Choose the right formula and cell references to automatically calculate values.
  • Perform basic tasks like hiding rows, preventing cell overflow, and pasting data.

Since each question comes with a strict time limit (one minute or less), the test will also measure a candidate’s ability to quickly format, isolate and manipulate data under a significant amount of pressure without any outside help.

 

Hands On Abilities Evaluated

  • Ability to edit Excel worksheets and workbook by identifying common functions, applying basic keyboard shortcuts, and manipulating columns and rows
  • Ability to apply sorting and filtering options as well as statistical functions to explore and summarize values
  • Ability to customize the look and feel of a Excel workbook using cell formatting tools

 

Complexity Levels of Excel Employment Assessment Tests

Microsoft Excel harbor very basic calculations to very advanced data processing and analysis that requires in-depth knowledge of every tools of Excel. Depending on the position candidate is  applying for you are likely to face a pre-employment Excel test with varying level of difficulty. Microsoft Excel Employment Assessment tests can be categorized into at least three levels of difficulty:

  • Microsoft Excel Basics TestThe basic test will evaluate your skills performing basic Excel functions. This can include anything from printing, formatting cells, inserting tables, and so on. If you have used Excel in the past or familiar with other similar applications you should be able to refresh your skills with the quick tutorial, practice sample excel interview questions to pass assessment test. Basic Excel Test for Interview Candidates is a screening process for entry-level or experienced candidates, including administrative assistants, retail store managers, Sales Representatives, Marketing Representatives, Accountants, Entry level Analysts, or any other position that requires using Microsoft Excel.
  • Intermediate Microsoft Excel test: This test requires more than basic knowledge but is not as complex as the advanced level test. The intermediate Microsoft Excel skills test presents candidates with a series of timed questions designed to assess their knowledge of Microsoft Excel. For passing this test, you will need to study and practice in advance using different tools and resources and ideally have practical experience of solving problems with Microsoft Excel. The Intermediate Excel Test is typically offered to evaluate skills of Business Analysts, Project Managers, Architects and other professionals, that may be responsible for complex decision making.
  • Advanced Microsoft Excel test: This test is designed for job that require to use Excel in a more complex manner. The test will evaluate your skills with pivot tables, creating macros, filtering, and functions such as IF, IFS, VLOOKUP, SUMIFS, VBA and other advanced concepts used in Microsoft Excel. An advanced Excel user would need to have advanced formula skills. He would have a skill and confidence to make his own formula to adapt to any situation. Typically, this test is offered to Senior Professionals, Executives, Financial Analysts, Scientists and other professionals that may need to use advanced features of Microsoft Excel.

What Employers are Looking For?

Microsoft Excel is a comprehensive tool that allows businesses to record, track, and analyze data essential for measuring company performance, maximizing return on investment, and defining goals – when used to its fullest potential. A new employee who can comfortably navigate Excel is prepared to immediately contribute by producing meaningful, data-driven spreadsheets, reports, and graphs to best serve company needs.

Assessing a job candidate’s Microsoft Excel proficiency is an important step in making the right hire. The Beginners Microsoft Excel skills test is helpful in learning if a job candidate understands how to manipulate the many functions, tools, and formulas of Excel to present extensive information, identify key trends, or calculate financial and numerical data.

Microsoft Excel assessment test helps predict a job candidate’s ability to:

  • Enter sales figures and properly apply formulas to generate sales totals by date, representative, product or region.
  • Conditionally format cells with the goal of highlighting specific dates, values, or ranges.
  • Create bar graphs and pie charts from large datasets to illustrate critical company data, performance metrics, and outlook.

Tips for Passing Your Excel Job Test!

TIP #1 – What are the Smartest Ways to Prepare for Assessment Test:

  • Use outlines from Test provider (Indeed.com, SkillsCheck, IKM) and review their sample questions
  • Take Online Training Courses, if available
  • Download and read Assessment Test Books and eBooks
  • Download and Use Practice Tests

TIP #2 – What to do during the test:

  • Try to schedule test in the morning, when you have high levels of energy
  • Get a good sleep before the test.
  • Do not take a test if you are tired
  • Read Question carefully (ideally more than once)
  • Answer easy questions first (if you have a choice)
  • This would allow you to leave harder questions for the end, but you will get easy answers in
  • Validate your answer with more than one method (i.e. Common sense, Manual Calculations, Use Calculator etc.)

TIP #3 – How to Pass Assessment Test Today:

  • Be prepared for Assessment Test at Home
    • You might be monitored and timed
  • Anticipate questions based on the job position
  • Research and practice before the test
  • Reflect after the test and take notes
    • Use these notes to get ready for next test
  • Improve your Skills in Between Tests by taking Practice tests and timing yourself

Pivot Table in Excel for Beginners

By name it might seem scary at first when starting with Pivot Table but it’s one of the very amazing features of the Excel which let you dissect a data properly whether it be Sum, Average, Product, Charts and much more.

Today we are going to start teaching you about Pivot Tables and its many applications but don’t worry we are not going to overwhelm you as we are going to look at the Pivot Table from the eyes of a beginner.

Let’s start simple and create our very first Pivot Table.

Creating a Pivot Table

Pivot Table with Fixed Range on a New Sheet

On the below screenshot you can see our sample data which contains sales reps across various Regions.

So, in order to create the Pivot Table, you have to select the data set including the headers and then go the Insert tab and from tables group select the option Pivot Table. Refer to below screenshot to understand more.

Now once you do that you will see the following window

From here we have to observe a couple of things as we can see it is showing our selected range which is 'Sales Data'!$A$1:$G$44 against Table/Range: option and then at the bottom its showing where do you want to place the Pivot Table and now one thing to note is that Pivot Table is dynamic so you need to give it more space as it expands so let’s for now put it on a different sheet.

And once you click OK you will be transferred to a newly created sheet which will look something like below

Now you can observe from the image that we are indeed on a new sheet and you can see our Pivot Table begins from cell A3 and on the very left you can see an option called PivotTable Fields. We can go in-depth about it when we explore it further.

Pivot Table with a Table or an Existing Sheet.

Now let’s explore creating Pivot Tables with an Excel Table. To clear your doubts, we can also create Pivot Tables with Fixed Range on existing sheets and I choose this example to kill two birds with one stone.

So, what are the benefits of creating a Pivot Table with an Excel Table, well the biggest one is that we can increase/decrease the data in the Excel Table and the Pivot Table will update based on the inputs.

Let’s first convert our Range to an Excel Table in two simple steps.

  • Select the entire range of your Excel Data
  • Go to the Insert tab of the Excel Ribbon and then from Tables group select Table and you will be greeted with an option like in the below image.
  • Hit OK as we have headers on our data and that’s it, your data is now converted to a Table and you can see that Excel will make some format changes on its own which you change based on your requirements

Now we are going to create a Pivot Table on a sheet named Analysis which is a blank sheet we have inserted on our spreadsheet.

So again, like the last time select the entire data and repeat the process by going to Insert Ribbon and selecting the option PivotTable and you will be greeted with a window that looks something like this.

Please note that table name between ours and your example might differ.

Now you can see against the Table/Range option it is showing us the table name which in our case is Table1.

Now we are going to choose the option Existing Worksheet and create our Pivot Table on the worksheet named “Analysis” on cell A2 and so select the option and then we can enter the location but the better and much smarter way is clicking the upward arrow and then navigating to sheet where you want to place the Pivot Table. Once you hit the arrow you will see a window like below.

Then simply switch to the sheet you want to place the Pivot Table and select the cell and the window then will show content like something in the below image, based on the sheet which in our case is named “Analysis” and we have selected cell A2.

Once you hit enter you will go back to the original Create PivotTable window and from there you hit okay and we have done it. You can see from below image that we have created a Pivot Table on an existing sheet named analysis and it starts from cell A2

Pivot Table with Fixed Range on a New Sheet

On the below screenshot you can see our sample data which contains sales reps across various Regions.

So, in order to create the Pivot Table, you have to select the data set including the headers and then go the Insert tab and from tables group select the option Pivot Table. Refer to below screenshot to understand more.

Now once you do that you will see the following window

From here we have to observe a couple of things as we can see it is showing our selected range which is 'Sales Data'!$A$1:$G$44 against Table/Range: option and then at the bottom its showing where do you want to place the Pivot Table and now one thing to note is that Pivot Table is dynamic so you need to give it more space as it expands so let’s for now put it on a different sheet.

And once you click OK you will be transferred to a newly created sheet which will look something like below

Now you can observe from the image that we are indeed on a new sheet and you can see our Pivot Table begins from cell A3 and on the very left you can see an option called PivotTable Fields. We can go in-depth about it when we explore it further.

Pivot Table with a Table or an Existing Sheet.

Now let’s explore creating Pivot Tables with an Excel Table. To clear your doubts, we can also create Pivot Tables with Fixed Range on existing sheets and I choose this example to kill two birds with one stone.

So, what are the benefits of creating a Pivot Table with an Excel Table, well the biggest one is that we can increase/decrease the data in the Excel Table and the Pivot Table will update based on the inputs.

Let’s first convert our Range to an Excel Table in two simple steps.

  • Select the entire range of your Excel Data
  • Go to the Insert tab of the Excel Ribbon and then from Tables group select Table and you will be greeted with an option like in the below image.
  • Hit Ok as we have headers on our data and that’s it, your data is now converted to a table and you can see that Excel will make some format changes on its own which you can then change based on your requirements

Now we are going to create a Pivot Table on a sheet named Analysis which is a blank sheet we have inserted on our spreadsheet.

So again, like the last time select the entire data and repeat the process by going to Insert Ribbon and selecting the option PivotTable and you will be greeted with a window that looks something like this.

Please note that table name between ours and your example might differ.

Now you can see against the Table/Range option it is showing us the table name which in our case is Table1.

Now we are going to choose the option Existing Worksheet and create our Pivot Table on the worksheet named “Analysis” on cell A2 and so select the option and then we can enter the location but the better and much smarter way is clicking the upward arrow and then navigating to sheet where you want to place the Pivot Table. Once you hit the arrow you will see a window like below.

Then simply switch to the sheet you want to place the Pivot Table and select the cell and window will show content like something in the below image, based on the sheet which in our case is named “Analysis” and we have selected cell A2.

Once you hit enter you will go back to the original Create PivotTable window and from there you hit okay and we have done it. You can see from below image that we have created a Pivot Table on an existing sheet named analysis and it starts from cell A2

Understanding Pivot Table

Now as we discussed in our intro a Pivot Table is an amazing tool to summaries the data and we are going to see how a Pivot Table functions and what role everything plays in it from the eyes of a beginner.

Pivot Table Fields

PivotTable fields are essentially just columns of data we have in our range so as you can see in the image below it shows us the fields and they are just columns of data in our Table which you can see in the next image to that.

And that is why you have to keep your field names unique and must have header for each column when you create a Pivot Table.

Pivot Table Areas

There are 4 kind of areas when it comes to Pivot Tables and in each area, we add fields to make our Pivot Table and we can either simply select and drag the field or right click on the field and add it to any area.

The four areas determine what we see in the Pivot Table. Let’s see what each area does on its own.

Filters

As the name suggest a filter area adds any field to filter the data by so for instance you add any field from which you want to see data for only particular area and that is where filter comes in handy.

Columns

Again, as the name implies any field you add it to the column section will show up as a column on the Pivot Table.

Rows

As the name implies again, this is for adding any field to the Pivot Table on a row.

Values

Finally, values area is the area where most of the Pivot Table magic happens and you can use this field to calculate following.

  1. Sum
  2. Count
  3. Average
  4. Max
  5. Min
  6. Product
  7. Count Numbers
  8. StdDev
  9. StdDevp
  10. Var
  11. Varp

Applications from Pivot Table

Okay now let’s have a look at some examples of how we can extract summary of the data from the Pivot Table.

Example 1

Now based on our data lets find out the number of units of an item sold based on a particular region.

For doing that we are going to do following.

  • Drag or Add the region field to filter area.
  • Drag or Add the item field to the row area.
  • Drag or Add the units field to the values area.

Once we have done that, area on the Pivot Table will look below

And our Pivot Table will now look like following

As you see just by following simple steps, we can get the sales unit by items summed up by using few simple steps.

Now since we wanted to see a particular region, we can simply select the region for which we want to see the sale from the filter we created on our Pivot Table.

So, as we can see from image above our filter is in cell B1. Once you select the filter you will see following.

We can select any region from the options and we are going to opt for central and see the changes on the figures. Observe below the image for the change in figures when we do the same.

Also note that you can additionally sort and filter using the Pivot Table and for instance in the picture above you can sort and filter by clicking the option on the Row Labels column and sort and filter as you like.

Example 2

We are going to look at another example to understand it better and this time we are going to find out the average unit cost of an item based on the region Central.

So, we are going to do clear all ticks from our existing Pivot Table fields and that should bring us back to zero with a clear Pivot Table.

Now we will do following

  • Drag or Add Region field to filter area
  • Drag or Add Item field to Rows area
  • Drag or Add Unit Cost field to the Values area

Now our Pivot Table and fields should look like something below

You can see that the value field is assuming we want sum of unit cost but that is not useful to us as we want average so in order to change we are going to right click on the “Sum of Unit Cost” option and select the option Value Field Setting. After that you will see the the window like below and select the average option from the list and hit OK.

Now you should have average values showing on the Pivot Table like the image below but you also have grand total option which is useless in this context so lets remove that.

Click anywhere inside the Pivot Table and now you should see PivotTable Tools on Excel Ribbon and from there select the option Design. Have a look at the image below to understand it better.

The second option is the Layout group is Grand Totals from there select the option Off for Rows and Column and the Grand Total should disappear and once you select the region Central from the filter, we should have the desired results and we can format the cost to only show two decimal places to make it better and it should look like the figure below.

Pivot Charts

Pivot Charts are another amazing feature of the Pivot Table, they are interactive chart that work on the basis of the data and fields selected on the Pivot Table.

Let’s insert a simple chart using the Pivot Table we created in the Example 2

Creating a Pivot Chart

In order to that we have to first click anywhere inside the Pivot Table and then go the Insert option on the Excel Ribbon and from the charts group select the option Pivot Chart and once we do that, we will see the window like on the below image.

After that once we will select the Clustered Column chart and select Ok and we will see a chart based on the options we have selected will now be shown on the sheet like in the image below

The best part about this is that the chart will change based on the option we select on the Pivot Table so for instant let’s change the region to East from filters. You can see in the image below the data along with the chart has been updated to show now the values form the East Region.

Question/Answers

Question 1

In order to create the Pivot Table, one must have following information present in all columns of the data source.

  1. Numbers
  2. Text
  3. Column Headers
  4. Column Footers

The Answer is option C Column Headers, A Pivot Table cannot be created without any headers missing in the source data.

Question 2

Which of the following statement is correct about the Pivot Table?

  1. A Pivot Table cannot be used to create a chart on the sheet.
  2. A Pivot Table cannot be created on an existing sheet.
  3. Both
  4. Neither

The Answer is D as a Pivot Table can be used to create a Pivot Chart and a Pivot Table can be created on an Existing sheet.

Question 3

Which of these areas are available on a Pivot Table?

  1. Filters, Columns, Footer, Header
  2. Filters, Rows, Footer, Values
  3. Filters, Rows, Column, Values
  4. Neither

The right answer is column C.

Question 4

Using the Data given create a Region filter and find out the total units sold in the region west

The correct answer is

  1. 2121
  2. 691
  3. 231
  4. 241

The correct answer is 231. You can find the result based on the Pivot Table based on the image below.

Question 5

Using the data sheet given create a Pivot Table to find out the average cost of item binder accurate to 2 decimals in the west Region.

The correct Answer is

  1. 19.99
  2. 11.49
  3. 8.19
  4. 11.54

The correct answer is A. Refer to the Pivot Table in the image below to see the correct answer

About the Author

Vadim Mikhailenko, MBA

Vadim lives in Milwaukee, Wisconsin and works as an Information Technology Consultant, Coach and Educator. Vadim is very passionate about Milwaukee community and teaches classes to underprivileged students at local community college to help them get employed. Throughout his consulting career Vadim studied a lot of tools and technologies and learned hiring process by helping organizations to hire candidates and also supported students to get prepared and to pass interview and assessment tests.

Vadim loves to share his knowledge with and enjoys teaching  new skills to help you get hired for your dream job.

Next Step: Download PDF EBook

Top 50 Excel Assessment Test Questions and Answers

Top 50 Excel Assessment Test Questions and Answers PDF EBook teaches you everything you need to get ready for Basic and Intermediate Microsoft Excel Interview and prepare for Excel Assessment Test. You will practice most frequently asked questions on the exam and will learn what to do when you arrive at the exam testing center,  how to manage your time when taking the exam, tips to manage your time during assessment test, tricks for answer questions in different formats and much much more.

Download PDF EBook