Whether you've recently taken a Microsoft Excel course or you want to verify that your knowledge of the program is current, try out the How-To Geek Advanced Excel Test and find out how well you do!

This is the third in a three-part series. The first installment is the beginner skills test, and the second one is the intermediate skills test.

Posts Advanced Tasks ----------------

The following 16 tasks include these areas: importing data from an online source, manipulating data using PowerQuery Editor, creating advanced conditional formatting rules, applying advanced functions, inserting and working with checkboxes, naming ranges, and setting up and utilizing data validation rules. These instructions combine beginner-level, intermediate-level, and advanced-level tasks.

1 Use the Get Data feature to import the RSSSF list of footballers who have scored 500 or more goals from Wikipedia. Help 2 In the PowerQuery Editor, transform the data as follows: (a) Remove the Rank column. (b) Convert all player names to uppercase. (c) Split the Career Span column into two separate columns—one showing the year each player started playing, and the other indicating the last year they played. (d) Name these new columns "Career Start" and "Last Played," respectively. (e) Format the Last Played column as whole numbers, and replace any errors with zeros. Help 3 Load the transformed table onto a new worksheet, then close the PowerQuery Editor. Rename the new worksheet "Footballers," positioning it as the right-most tab. Help 4 Rename the table "Scorer\_Database." Help 5 Using the Find And Replace function, substitute any zeros in column F with a formula that dynamically updates to display the current year. Help 6 Add a new column G, label it "Still Playing," and insert a checkbox into cell G2. Help 7 While remaining in cell G2, write a formula that checks the box if the value in cell F2 contains a formula. Help 8 Apply both the checkbox and its associated formula to the rest of the cells in column G. 9 Insert a new column between the Player and Goals columns, and name it "Name." Help 10 In cell B2, create a formula that swaps the players' first and last names, separating them with a comma and a space. For players whose names consist of only one word, the formula should duplicate that name. Help 11 Edit the data in column B so that it no longer refers to column A, then delete column A entirely. 12 Create a conditional formatting rule that fills the entire row of any footballer with a goal ratio greater than 1 with yellow. Help 13 Go to Sheet2. In cell I1, type Nation, and in cell J1, type Players. 14 Use the name box to assign the name "Nation" to the range in column C. Help 15 In cell I2, set up a data validation drop-down list containing the nations listed in column C, and choose "SPA" from this list. Help 16 In cell J2, use a formula to list all players whose nation matches the value in cell I2. Help Download a free copy of the How-To Geek Advanced Excel Test Solutions PDF and a sample Excel file containing completed versions of all tasks. After opening these links, click the download button located in the top-right corner of the window. Keep in mind that the solutions provided may not be the only way to solve the problems.

The above is the detailed content of Prove Your Real-World Microsoft Excel Skills With the How-To Geek Test (Advanced). For more information, please follow other related articles on the PHP Chinese website!