Whether you’ve landed a job interview for a role that requires fundamental Microsoft Excel skills or you’re looking to solve a real-world problem, take the How-To Geek Beginner Excel Test to double-check that you know the nuts and bolts of the popular spreadsheet software.
This is the first of a three-part series. The second (intermediate) and third (advanced) will be available soon.
Download a free copy of the How-To Geek Beginner 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.
Related
Lied On Your Resume About Excel Expertise? 8 Concepts You Need to Learn Right Now
A whistle-stop tour of Microsoft Excel’s must-knows.
Beginner Tasks
The following 15 tasks cover these areas: renaming worksheets, using Excel tables, using straightforward functions, inputting and formatting data, applying conditional formatting, finding and replacing values, and inserting and modifying charts.
Open the downloaded workbook, and complete the following tasks:
|
1 |
Rename the worksheet “Player_Database.” |
Help |
|
2 |
Format cells A1 to G21 as a purple Excel table with a header row, banded rows, and filters, and name the table “Scores.” |
Help |
|
3 |
In cell G2, use a formula to calculate the sum of the scores in cells D2, E2, and F2, and ensure the formula is duplicated to the remaining cells, down to G21. |
Help |
|
4 |
Sort the data by column G in descending order. |
Help |
|
5 |
Add a total row to row 22, and display the average overall scores to one decimal place in cell G22, and delete the text in cell A22. |
Help |
|
6 |
Create a conditional formatting rule that fills in light green all cells in column G containing total values larger than the average. |
Help |
|
7 |
Replace all instances of “ENG” in column C with “GBR” using a single command. |
Help |
|
8 |
Create a 2D clustered column chart on the same worksheet with Player_Name (column A) on the x-axis, and Total (column G) on the y-axis. |
Help |
|
9 |
Resize the chart so that it covers cells I4 to P19. |
Help |
|
10 |
Change the chart title to “Player Scores,” and add the horizontal axis title “Total Score.” |
Help |
|
11 |
Expand the table downwards one row, and add the player name “Harry” to cell A22. |
Help |
|
12 |
Type 21 into cell B22, nation SPA into cell C22, and 30 in cells D22, E22, and F22, respectively. |
|
|
13 |
Update the descending order in column G. |
Help |
|
14 |
In cell I1, type Total players, and in cell J1, use a formula that counts the number of players in column A and updates whenever players are added or removed. |
Help |
|
15 |
In cell I2, type Older than 40, and in cell J2, use a formula that counts the number of players in column A who are aged 41 or above. |
Help |
Related
The Beginner’s Guide to Excel’s Formulas and Functions
Everything you need to know about Excel’s engine room.
Source link
-
-
-
-
-
-
-
-
