|Total of questions: 188|
|Total of questions: 188|
|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)|
Calculate the subtotals of the turnover, for each change in Geographical Direction within the cell range A3:C19.
Replace the formulas of the cell range K5:K9 with the values of the same cells.
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:
On a new worksheet create a Pivot Table Report based on the data of the cell range Â5:F26. Display the Product field in Column Labels, the Salesman field in Row Labels and the Color and Quantity fields in the Values.
Navigate to report worksheet and group the dates which appear in the pivot table by quarters.
Insert the data of the out1.txt file located in the ΙL-ates\Excel folder on your Desktop in cell Β10.
Calculate subtotals in the column Turnover at each change in Orientation. Display the Summary below data.
Use a One-Variable Data Table to calculate in the cell range B2:B25 the VAT of the values appearing in the cell range A2:A25. The formula is provided in cell E3 and VAT percentage is displayed in the cell Ε1.
Use a Two-Variable Data Table to calculate in the cell range D7:J30 the VAT of the values appearing in the cell range C7:C30. VAT percentages are provided in the cell range D6:J6. The appropriate formula is provided in the cell B3.
Navigate to the SALES worksheet located in the IL-ates1525 workbook of the IL-ates\Excel file on your desktop. Copy and paste the data included in the cell range Β20:J20 of the SALES worksheet in the cell range A1:Ι1 of the already opened workbook as link.
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 two Scenarios with the following data:
1) Name: Min, changing cells: C5:C8, values: 5, 6, 10, 8
Display the scenario Max.
Create a scenario summary using the default values.
Remove the SubTotals.
Display the subtotal for the turnover appearing on the cell range A3:C19 . Make sure there are page breaks between groups.
Apply subtotals in the cell range A1:D201.
Create a table in the cell range C3:H13 to multiply the values displayed in the cell range B3:B13 by the quantities displayed in the cell range C2:H2.
Create a pivot table report beginning from the cell G5. The report will derive data from the cell range A1:D201 and will display the field Order date as row labels and the field Full name as column labels. Set the Sum of the field Quantity in Values area.
Enter the data of the out1.txt file located in the IL-ates\Excel folder on your Desktop to a new worksheet,
using semicolon (;) as delimiter. Save the workbook as final.xlsx to the same folder.
Navigate to the pivot table and apply grouping by Months on the column OrderDate.
Navigate to the pivot table and apply the options required to display the total of quantities (count) instead of the Sum.
Remove the field Description from the pivot table and apply the Currency format on the Values.
Modify the worksheet to display the product's total price instead of Quantity in the pivot table (Do not use a calculated field).
Apply the adjustments required in the subtotal structures so that only the total amount for each client and the Grand total are displayed.
Insert the following entries:
On the Sheet1 worksheet delete the scenario called Scenario1.
Apply a validation rule in the cell B3 to allow only decimal numbers greater than 0. Insert the text Only numbers as Error Message.
Make sure we navigate to the address www.infolearn.gr by clicking on the text appearing in the cell A1. Then delete the hyperlink of the cell A2 without deleting the text.
Activate Track Changes in the document and Highlight changes for all users and all dates. Save the document.
Reject all changes effectuated in the document by all users.
Merge the active workbook with the merge file located in the IL-ates\Excel folder on your Desktop.