Whether you've earned a data-related job promotion or recently learned some new Microsoft Excel skills, take the How-To Geek Intermediate Excel Test to put your expertise under the microscope!
Download a free copy of the How-To Geek Intermediate Excel Test workbook. After you click the link, you'll find the download button in the top-right corner of your screen. You can download the solutions and a sample file via the links at the bottom of this article.
Intermediate Tasks
The following 14 tasks cover these areas: modifying the Excel window, using visualization tools, working with Excel tables, using intermediate-level functions, duplicating formatting, and inserting and modifying PivotTables. The instructions combine beginner-level and intermediate-level tasks.
Open the downloaded workbook, and complete the following tasks:
|
1 |
Display the Quick Access Toolbar at the top of the Excel window. |
|
|
2 |
Add "Focus Cell" to the Quick Access Toolbar, and turn it on. |
|
|
3 |
Format cells A1 to C21 as a blue Excel table with a header row, banded rows, and filters, and rename the table "Student_Scores." |
|
|
4 |
Use a formula to assign a random score between 30 and 100 to each student in column B, and fix the result. |
|
|
5 |
Using the lookup table, generate a formula to assign a grade to each student in column C based on their test scores. |
|
|
6 |
Add another student called "Izzy" to the formatted table in row 22, and add her score of 71. Ensure the resulting grade is correct. |
|
|
7 |
Extend the formatted table to column D, and add the column header "Status" to cell D1. |
|
|
8 |
Using the lookup table, generate a formula to return the students' statuses in column D based on their grades in column C. |
|
|
9 |
Duplicate the formatting of cells F2 to H10 in cells F12 to H19. |
|
|
10 |
In cells G13 to G19, use a formula to calculate the average student score for each grade, and display the results to one decimal place. |
|
|
11 |
In cells H13 to H19, use a formula to count the number of students who have achieved each grade. |
|
|
12 |
In cells G22 to G25, use a formula to count the number of students that fall into each status. |
|
|
13 |
Duplicate the formatting of cells F12 to H19 in cells F21 to G25, ensuring the values in cells G22 to G25 are displayed as whole numbers. |
|
|
14 |
Create a PivotTable in a separate worksheet that has students' names in column A and their scores in column B. Rename the worksheet "Pivot_Test," and remove the Grand Totals row. |
Download a free copy of the How-To Geek Intermediate Excel Test Solutions PDF and a sample Excel file with all the tasks completed. After you open these links, click the download button in the top-right corner of the window. The solutions shown may not be the only way to solve the problems.
