|Total of questions: 233|
|Total of questions: 233|
|First steps (3 questions)|
|Basic Settings (19 questions)|
|Cells (4 questions)|
|Formulas and Functions (34 questions)|
|Formatting (11 questions)|
|Charts (21 questions)|
|Special functions 1 (20 questions)|
|Special functions 2 (19 questions)|
|Images (3 questions)|
|Data (31 questions)|
|Sortings, Filters, Macros, Templates (17 questions)|
|DIPLOMA level questions (6 questions)|
|MS Excel Expert 2016, 2013, 2010, 2007, 2003 (BENCHMARK) (30 questions)|
|MS Excel Expert 2016, 2013, 2010, 2007, 2003 (ASSESSMENT) (15 questions)|
Insert a 3-D column chart in the Contest2 sheet to display the sales of the four companies for August, as they appear on the SALES worksheet. Change the color of the columns to blue, the shape of the columns to Full Pyramids (2) and the chart depth to 150.
Create two scenarios using the following data. The changing cells will be D7:D9. The first one, named Optimistic, will display:
The second, named Pessimistic, will display:
Insert the MICRONA data for the first five months on the chart appearing in the SALES worksheet.
Then copy the chart to a new Word document, so that changes to the source chart are reflected to the chart on your Word document.
Insert the data of the out1.txt file located in the ΙL-ates\Excel folder on your Desktop in cell Β10.
Protect the active worksheet of the active workbook. Make sure the user is able to change values within the cell range C4:C7 (without setting user rights in any cell range). In case a password is asked, set 1234 as password.
Sort all data of the active worksheet firstly by column data15 in ascending order then by column data08 in descending order and lastly by column data01 in descending order.
Insert the appropriate data in the cell range Α51:C52 so that if you apply an advanced filter, only entries:
Black color, pieces <50 and Total >2000 or
Red color, pieces >50 and Total <2000 are displayed. (Insert data in the provided order).
Copy the outcome in the cell range beginning from cell A60.
Change the linked archive from IL-ates1526_1 to IL-ates1526_2. Both files are located in the IL-ates\Excel file on the desktop.
Create a 2-D pie chart (not exploded pie) on the active worksheet. Accept the default settings. Each pie piece would represent the product and quantity data appearing in the 2ndhour2 worksheet of the IL-ates1527.xlsx workbook located in the IL-ates\Excel file on the desktop.
In the cell B1 of the Total worksheet insert the appropriate function to sum up the cells within the cell range A1:A10 from the FF1 worksheet through to the FF12 worksheet.
Insert in the cell B2 a function that returns the average of the respective cell range, in cell B3 for the minimum, in cell B4 for the maximum and in cell B5 for the total of cells displaying numbers, following the same pattern.
Pull out from the pie chart of the 2ndhour2 worksheet only the Motherboard and Screen pie slices.
Navigate to the IL-ates\Excel folder on the desktop and find the image brick1.gif. Set the image as filling on JAN data of the chart. Repeat for FEB data with the image brick2.gif and for MAR data with the image brick3.gif. The images should not be stretched.
Insert the appropriate function in the cell A1 to sum up values greater than or equal to 5000 in the cell range A2:A100.
Insert the appropriate function in the cell range B1:B50 that rounds the values of column A to 4 decimal places. Insert the appropriate function in the cell range C1:C50 that rounds the values of column A to 2 decimal places. Insert the appropriate function in the cell range D1:D50 that rounds the values of column A to the left of the decimal point.
In the cell A5 insert the appropriate function that joins the text of the cells A1, A2, A3, A4.
Insert the appropriate functions in the cell range G5:G10 that return the monthly payments for a loan based on regular payments and stable interest rate. Necessary data are provided in the cell range B5:F10.
Insert the appropriate function in the cell Ε3 that returns the value TRUE if the content of
Reproduce that function up to the cell Ε50. Finally, insert a function in the cell Ε1 that returns the value TRUE if the value TRUE is displayed at least once in the cell range E3:E50. (use only the AND and OR functions)
Set pages breaks and formulas to be displayed on the active worksheet.
Use the appropriate function in the cell C8 to display the value 198, if the cell C2 value is higher than or equal to 3000. Otherwise, the function returns the value 0.198.
Name the cell range E5:E9 as FullName.
Enter the appropriate function in the cell G6 to return the sum of order quantities effectuated before 02/07/2004 for the products displaying the word VIPER in their description.
Create an advanced filter to display only orders placed after 02/02/2004, by Lambropoulos Stelios, in a quantity greater than 10.
Apply the following number format on Quantities: if the entry is zero, the respective cell will display the word Zero. If the entry is greater than zero, the cell will display the word plus, a space and a five digits number. E.g. 6 will be displayed as plus 00006. If the entry is less than zero, the cell will display the word minus, a space and a five digits number. E.g. -6 will be displayed as minus 00006.
Enter a function in the cell H3 to return the maximum quantity of the set1 item for the client Lambropoulos Stelios. Then, enter the appropriate function in the cell H4 to return the minimum.
Use the countblank function in the cell B1 to count the blank cells in the cell range A1:A400.
Display the values of the cell range A1:A20 in the cell range B1:B20 using a rounded down with 2 decimal places.
Enter the appropriate function in the cell G1 to return the average of sales over 100€ effectuated by salesman Goutoudis.
Create a Clustered column Line on secondary axis chart in the Sheet1 worksheet deriving data from the cell range A1:C4.
Ιn the Sheet1 worksheet, insert the following entries:
Lambropoulos 75.00€ 109.75€
and update the pivot table in the Sheet2 worksheet.
Delete the name of the cell range insert.