Skip to content

Excel Challenge 2 – Formatting Skills

Welcome to the second Microsoft Excel challenge. If you missed the first one, you can check it out right here!

In this article, I will give you four different questions within Excel to test your spreadsheet knowledge.

How many tasks will you be able to perform correctly? Click the link below to download the practice file to test yourself and find out!

Excel Challenge File 2

Question 1

For question 1, you must re-create the formatting you see in cell C4 and apply it to cell C6. And you may not use any form of copy and paste.

Question 1 – Solution

To complete question 1, start by selecting cell C6. Then right-click and select Format Cells.

In the dialog box, click on the Fill tab, and then click Fill Effects.

Under Shading Styles, select From Center.

Next, change Color 1 to Blue and Color 2 to White. Finally, click on Ok.

After that, navigate to the Border tab. For the Style, select the thick dashed line. Then apply that border using the Outline button.

You can then click Ok to complete the formatting.

Awesome! You can now move on to question 2.

Question 2

For question 2, your goal is to change the number formatting of all the numbers in column D so they look like their counterparts in column B.

Question 2 – Solution

For this question, you will have to change the formatting of each number one by one. Let’s say you start with the number in cell D4.

Select cell D4, right-click, and select Format Cells. Under the Number tab, select Number for the category, choose the fourth option, and click Ok.

Next, select cell D6, right-click, and Format Cells. Select the Time category, and select the fifth option. Then click on Ok.

Go ahead and select cell D8, right-click, and Format Cells. Choose Date for the category, and select the option ‘March 14, 2012.’ Then click on Ok.

 

Finally, select cell D10, right-click, and Format Cells. Select Special for the category, choose Phone Number, and click Ok.

Question 2 is now complete!

Question 3

For question 3, your task is to re-create the heat map you see on the right without using copy and paste.

Question 3 – Solution

To pull this off, start by selecting the range C6:F17.

With the range selected, go to Home Conditional Formatting > Color Scales > Green -White Color Scale.

This will automatically generate the heat map you are after.

Question 4

For question 4, your goal is to highlight the entire row (B:E) for each month where expenses surpass revenue.

Question 4 – Solution

To solve question 4, begin by selecting the range B5:E16. Next, go to Conditional Formatting > New Rule.

In the dialog box, select use a formula to determine which cells to format.

Then enter the following formula:

=$E5<0

Notice that a dollar sign is in front of the column letter E. The dollar sign ensures that each cell references the value in column E. The row number, however, is left as a relative reference. It will change for each row of the data range.

With the formula in place, go ahead and click on Format.

Under the Fill tab, select yellow for the color, and then click Ok and then Ok.

Now each row where the expenses surpass the revenue is automatically highlighted.

How Did You Do?

I hope you had fun working through Excel challenge number 2. Did you find the questions to be easy or rather hard? Let me know in the comments how you did!

Also, if you are interested in learning the skills to solve questions like this with ease, check out the Excel Total Course!

This course is designed to teach you everything you need to know about Excel. And I can promise you that what you learn in this course, you will remember for a lifetime!

Thanks so much for joining me in this week’s Excel challenge. Be sure to check back in soon for Excel challenge number 3.

Watch the Video

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top