Help & Contact
Skip to content
View Results Dashboard
Send Quiz Dashboard
Your email address:
Custom field 1:
If this is placed in an Excel cell, what will the answer be
In order to change cell B1 to look like cell B3, the user must have pushed which 4 buttons or used the matching shortcuts?
1, 6, 8, 9
1, 2, 4, 7
3, 6, ,7, 8
2, 3, 5, 6
1, 2, 3, 4
In the below chart, we cannot really see what is happening with the % costs as there are very small numbers and so appear at the bottom of this chart. How could we fix this?
Split the chart into 2 separate charts to be shown side by side
Add a Data Table to the chart
Switch the rows and columns around
Create a secondary axis
Add a legend to the chart
In order to sort the data below we used the following steps:
Step 1: Highlighted Cell A1 to A4
Step 2: Clicked the AZ sort button
Step 3: Told Excel to continue with the selection when asked
However, as shown below, the end result was wrong as the names and numbers got mixed up. What was wrong with the process followed
We should have put a filter onto column A first and then sorted.
We should not have clicked the AZ button but rather the SORT button
You can only sort 1 column at a time, so this cannot be done
We only highlighted column A but we should have highlight both column A and B to sort correctly
We needed to double click the AZ sort button, to sort both columns
In cell C2 we have created a formula being =VALUE(B2). What result would you expect to see when you pressed enter
Lease 1= 1 000
In cell E2 we have created a formula that works out how many days leave have been taken based on the Start date (column C) and End Date (Column D) IGNORING weekends and the public holidays shown in H2 to H5. What formula is in cell E2
Excel cannot exclude weekends and this needs to be counted off a calendar or a calendar database in an Excel sheet
In cell E2 we have created a formula that works out how many days leave have been taken based on the Start date (column C) and End Date (Column D) BUT which also includes weekend days. What formula is in cell E2
Which of the below data sets, as is, would be the most ideal to create a Pivot Table with?
In the VIEW ribbon, clicking the NEW WINDOW button will:
Create a new blank spreadsheet which you can work on
Create a new window of the existing spreadsheet, which you can arrange BUT you HAVE TO make changes on the original window only
Create a new window of the existing spreadsheet, which you CAN arrange AND make changes to either window
Opens a new window where you can write some notes and do simple calculations
open up Windows File Explorer
In cell E2 we have create a formula that averages the scores shown in B2, C2 and D2 and returns 'Above Average' if the result is bigger than 5 or 'Below Average' if it is less than or equal to 5. What formula is in cell E2
=IF((B2:D2/3>5,"Above Average","Below Average")
=IF((B2:D2>5,"Above Average","Below Average")
=IF(AVERAGE(B2:D2)>5,"Above Average","Below Average")
=IF(AVERAGEIFS(B2:D2)>5,"Above Average","Below Average")
=IF(SUM(B2:D2)>5,"Above Average","Below Average")
Below we highlighted the area A1 to B6, copied it, and then using a Paste Special tool we pasted it into A10. Which Paste Special tool did we use?
Paste Special All except borders
Paste Special Values
Paste Special Values and Number formats
Paste Special Formats
Paste Special Formulas and number formats
Below the chart column is overlapping the month name (by the red arrow). Which item in the Format Axis options can move the month names to be below the chart?
If you copy the cell in B4 to C5 (the yellow cell), what will the formula look like and what will the answer be
'=A$1' and OK
'=$A$1' and OK
'=B1' and 0
'=B$1' and 0
'=B$1' and OK
In B2 we have created a column which takes the number in A2 and changes it. Based on the numbers shown, what did we do?
Copied column A to column B and formatted the numbers with 2 decimal places
In order to split the data shown in the 'Data Preview' below into separate columns you need to?
Click on 'Other' in the delimiters and enter a | in the input box
Click on Comma in the delimiters
Click on Semi Colon in the delimiters
Click on Tab in the delimiters
Tick the 'Treat consecutive delimiters as one'
In the below spreadsheet we have used the filter arrows in cell A1 to only show rows that relate to USA. Why are we still seeing rows 13 and 14 with Australia and South Africa?
When the filter was set up it did not include the rows after row 11
We must have clicked on the number 11,12,13 and 14 in the filter to still be showing these rows
We did not finish hiding the rows and still need to hide row 11, 13 and 14
Australia and South Africa contain the letters U, S, and A so are correctly shown
We have not chosen what to filter in column B yet
In cell B5 we have used an Excel function to turn the components of each persons birthday, into a VALID Excel date (a date that can be sorted and the format changed), which we plan to copy across. What formula must be in cell B5
In cell B9 we have created a formula that multiplies to selling price with the units sold in that branch. The formula is created in a such a way that it can be copied to the rest of the yellow cells WITHOUT needing to change the formula at all. What must the formula look like in cell B9?
In order to put all the like colours together in column B (all the greens together, then yellows, then reds) you can (choose the method you would use as there could be multiple correct)
Add a new column (column E) with the the colour name as text and sort on the text
Highlight similar coloured rows and copy and paste them into a new area of the spreadsheet
Use the Data filter and show the colours together
Use the CHOOSECOLS function in a formula to group the colours together
Use Data Sort and sort by the colours
Below, in cell C3, we want to create a VLOOKUP that will find the matching student number in column F and pull the Total amount outstanding into it. It must be built so that it can be copied down to the rest of the yellow cells. The formula in cell C3 needs to be:
Below we have create the formula shown in cell B9 and B10. What will the results be?
34.0% & #N/A
#N/A & #N/A
E & D
34.0% & 45.0%
E & #N/A
If you perform a Paste Special 'Values and Number Formats'
The formats are pasted without any numbers or formulas
There is no such option to Paste Special 'Values and Number Formats'
Only the values appear with the correct number format, Other formatting and any formula in the cell are removed.
Formula and values are pasted, but all formatting is removed.
Only the values appear, with the underlying formula and ALL formatting removed
In the below Pivot Table we want to sort column A in months (Jan to Mar) and Column B in descending order based on the amount in the Sum of Bill (column C). The correct process would be:
Click on the dropdown in A1 and sort Z to A, but you cannot sort column B based on the numbers in column C, only by the text in column B
Click on the dropdown in A1, choose the sort Z to A, Click on the dropdown in B1, choose the More Sort Options, Descending based on Sum of Bill
Right click anywhere in column A and chose the Move option (Z to A), then right click anywhere in column B and chose the Move option (descending Sum of Bill)
Highlight C2 to C19 and, in the Data Ribbon choose the Sort Z to A.
Click on the dropdown in A1, choose 'More Sort Options, and choose that the sort is first by Month (name) then by Destination Network (value)
You can only 'Find' in one sheet at a time, you cannot search within all the sheets of a workbook at the same time
If you copy the cell in B2, and do a Paste Special Comments and Notes to cells B3 to B5 you will see
If you need to replace the word 'Joe Soap Inc' with 'ACME Ltd' in your whole spreadsheet, which Excel tool would you use?
Find & Replace
Column A seems to have too many spaces between the surname and first name. What did we use to make column B show only 1 space between the surname and name?
Find & Replace spaces with nothing
We need to fix this Pivot Table so that the month names fill the blank areas where the red squares are. Which screen contains the tool we will use.
In cell B5 we have created a formula that compares the Sales in A5 to cell B2 (where commissions start) and if sales are 20 000 or more it calculates the commission my multiplying the sales by the commission rate. This formula is then coped down to the rest of the yellow cells WITHOUT making any changes to the formula. What formula is in cell B5?
In the below Pivot Table we want to see more information about the destination networks used in February. In order to do this we should
Click on the dropdown button in cell A1
Double click the number in cell C3
Click on the dropdown button in cell B1
Click on the + sign in cell A3
Double click the Grand Total in cell A5
Below in cell B2 we have input a valid Excel date BUT it is not the last day of the month. What formula must be in C2 (and copied to the rest of the row) that will take the date in B2 and change it to the last day of that month one year later?
How did we got the blue bars into this list?
Conditional Formatting Data Bars
In order to REMOVE the plus and minus signs next to the A Column, you must use which button
Button 4- Ungroup
Button 2- Flash Fill
Button 5- Subtotal
Button 1- Advanced
Button 3- Data Validation
All the formulas below are the same EXCEPT for
Below the colors will change depending on what the numbers are in the cell (biggest number= dark red, smallest number= dark green). How do we do this?
Conditional formatting color scales
Overlay a XY chart over the numbers
Custom Format on the numbers
The numbers use the COLOR function in there formula
Conditional formatting data bars
In cell C4 we have created a formula that multiplies C3 with the selling price in B1. The formula is built in such a way that it can be copied to the rest of the yellow cells without changing the formula. Which formula must be in cell C4?
The $ in a formula reference e.g. =$A$1 tells Excel that
If the formula is copied and pasted into another cell it MUST NOT change the reference which comes AFTER the $
If the formula is copied and pasted into another cell it must NOT change the reference which comes BEFORE the $
Indicates that the currency to be used as the number format will be USD
Has no impact on the formula and allows you to make notes to explain the formula.
If the formula is copied and pasted into another cell it MUST change the reference which comes AFTER the $
Below in cell B2 we have a date representing 18 June 1973 (item 1) but when we try and change the format of the date to yyy/mm/dd (step 2), it does not change (see step 3 which shows what will happen when you click OK). Why is this?
You need to specify that a cell is a date format before you enter it into a cell.
The date in cell B2 is not seen as a valid Excel date (due to regional settings) so it is treated as normal text.
You cannot enter a date in the format dd/mm/yyyy .
Dates must be split by a - (e.g. 18-06-1973) and not a /
Excel cannot handle dates before the 1st January 1980.