Whether you've recently taken a Microsoft Excel course or you want to check that you're up-to-date with the program's latest capabilities, take the How-To Geek Advanced Excel Test and see how you fare!
Download a free copy of the How-To Geek Advanced 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.
Do You Need Math Skills to Be Good at Microsoft Excel?
Math skills can be useful in Excel—but are they essential?
Advanced Tasks
The following 16 tasks cover these areas: importing data from an online source, using PowerQuery Editor to manipulate data, creating advanced conditional formatting rules, using advanced functions, inserting and using checkboxes, naming ranges, and implementing and using data validation rules. The instructions combine beginner-level, intermediate-level, and advanced-level tasks.
You must have an internet connection to complete these tasks.
Open the downloaded workbook, and with cell A1 in the worksheet named Start_Here selected, complete the following tasks:
|
1 |
Use the Get Data tool to import the RSSSF list of footballers with 500 or more goals from Wikipedia. |
|
|
2 |
Transform the data in the PowerQuery Editor in the following ways: (a) Remove the Rank column. (b) Change all player names to block capitals. (c) Split the Career Span column into two columns, where the first shows the year each player began playing, and the second shows the last year the player played. (d) Rename these two columns "Career Start" and "Last Played," respectively. (e) Change the number format of the Last Played column to whole numbers, and replace errors with zeros. |
|
|
3 |
Load the table onto a new worksheet, and close the PowerQuery Editor. Rename the new worksheet "Footballers," and make it the right-most worksheet tab. |
|
|
4 |
Rename the table "Scorer_Database." |
|
|
5 |
Using the Find And Replace tool, replace any zeros in column F with a formula that updates automatically to show the current year. |
|
|
6 |
Expand the table to column G, rename the column "Still Playing," and add a checkbox to cell G2. |
|
|
7 |
Staying in cell G2, create a formula that checks the box if the value in cell F2 is a formula. |
|
|
8 |
Apply the checkbox and the formula to the remaining cells in column G. |
|
|
9 |
Insert a new column between the Player and Goals columns, and call it "Name." |
|
|
10 |
In cell B2, insert a formula that switches the players' first names and last names, and separates them with a comma and a space. In cases where the player's name contains one word only, the formula must duplicate that name. |
|
|
11 |
Fix the data in column B so that it no longer references column A, and delete column A. |
|
|
12 |
Create a conditional formatting rule that applies a yellow fill to the entire row of the table for any footballer whose goal ratio is greater than 1. |
|
|
13 |
Head to Sheet2. In cell I1, type Nation, and in cell J1, type Players. |
|
|
14 |
Name the range in column C "Nation" using the name box. |
|
|
15 |
In cell I2, create a data validation drop-down list that lists the nations in column C, and select "SPA" from this list. |
|
|
16 |
In cell J2, use a formula that lists all the players whose nation corresponds with the value in cell I2. |
Download a free copy of the How-To Geek Advanced 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.
