Font size
WorksheetsExcel Basic
Total questions: 34
Worksheet time: 20mins
-
(
What number format is used for to get this kind of cell [$ 1.234,00]?
General
Accounting/Currency
Number
Text
Where can we find quick analysis on selected cells (sum,average,min/max etc.)?
Status bar
Formula bar
Name box
Ribbon
When we want to look two sheets from the same workbook in separate windows, what options do we use?
Open another instance of the same workbook (double click)
Use Split option on View tab
Page Break View
Use New Window + Arrange options in View tab
How do we freeze all cells above Row 4 and left of Column D (with Row 4 and Column D NOT freezed)?
Select Cell "E5" and click Freeze Panes
Click Freeze Top Row and Freeze Top Column
Select Cell "D4" and click Freeze Panes
Hide Rows 1-4 and A-C
How should we type Custom Number format to create a number with meter unit next to it. I.E. [ 1,000.00 cm ]
#,##0.00 "cm"
0 "cm"
#,##0.00 cm
0 cm
What option do we use to remove all text and colors from selected cells at the same time?
Clear Formats
Delete on keyboard
Clear All
Clear Contents
To format cells like this, which Conditional formatting should we use?
Color Scales
Highlight Cells Rules:
Greater Than
Less Than
Between
Top/bottom Rules
Data bars
To format 25 cells with the biggest value in some range, what Conditional formatting should we use?
Top/bottom Rules
- Above Average
Highlight Cells Rules
- Greater than
Top/Bottom Rules
- Top 10 items
Top/Bottom Rules
- Top 10%
When using Table Object (Format as Table), we can summarize columns on the bottom of the table using next option:
Header Row
Total Row
Summarize with PivotTable
Last Column
What is the proper typing of IF function to check if Cell A1 equals "TEST"?
Return values are "Correct", "Not Correct". (Comma = Semicolon)
IF(A1, "TEST", "Correct", "Not Correct" )
IF(A1 = "TEST", Correct, Not Correct)
IF(A1 = TEST, "Correct", "Not Correct")
IF(A1 = "TEST", "Correct", "Not Correct")
IF the date in cell A2 is before 02/10/2023 result is "Old", if not, result is "New". Type a formula
IF(A2 < 02/10/2023, Old, New)
IF(A2 <= 02/10/2023, "Old", "New")
IF(A2 < "02/10/2023", "Old", "New")
IF(A2 = BEFORE(02/10/2023), "Old", "New")
Count cells in column "A" that are equal to "ROAD"?
COUNTIF(A:A, "ROAD")
COUNT(A:A = "ROAD")
COUNTIF("A:A", ROAD)
COUNT("A:A" = "ROAD")
Sum Amounts in Column "C" for "Ground" (column "A") & "International" (column "B") lines
SUMIFS(C:C, A:A, "Ground", B:B, "International")
SUMIFS(A:A, Ground, B:B, International, C:C)
SUMIF(A:A = "Ground", B:B = "International", C:C)
SUMIF(C:C, A:A = "Ground", B:B = "International")
When we want to Find & Replace cells in our sheet that contain only "-" and skip the ones that contain "-" inside the text of the cell (I.E. "DE-AU"), which option do we use?
Match Case
Look in "Values"
Match entire cell contents
Use apostrophes '-'
To create our own sorting we use?
Custom sort > Custom list
Text filters > Custom Filter
Insert new column and add numerical values, then sort by that column
Cut and insert rows
If we want to count distinct values in our Pivot table, we should use?
Remove duplicates
Check "Add this data to the Data Model"
Uncheck "Add this data to the Data Model"
Use filters
In which PivotTable field we put fields that we want to calculate?
Filters
Rows
Columns
Values
How can we expand our data range for PivotTable without losing current Pivot layout? (multiple correct answers)
Change Data Source > Expand selection
Add new column to the end > Refresh Data
Insert new column into existing data > Refresh Data
Create new PivotTable
We summarize values in PivotTable using next steps:
Insert value field in Rows > Click Field settings >
Chose summarize by
Insert value field in Values >
Click Value Field Settings >
Choose summarize by
Insert value field in Columns > Click Field settings >
Chose summarize by
Use SUM function
To show rows next to each other instead of one bellow the other we use: (multiple answers)
Repeat Item Labels
Show in Compact Form
Remove Subtotals
Show items in Tabular Form
Which option did we use in picture to get Grand totals on bottom?
Off for Rows and Columns
On for Rows and Columns
On for Rows Only
On for Columns Only
What do we use Calculated fields for, and what do we use Calculated Items for?
Calculated Fields: Using values from table (or previously created Calculated Fields) to create new measures
Calculated Items: Using values from table (or previously created Calculated Fields) to create new measures
Calculated Fields: Using dimensions from a single textual field (column) to create new dimension
Calculated Items: Using dimensions from a single textual field (column) to create new dimension
How we call 2 advanced filters which are used for better visual representation?
Splitter
Timeline
Slicer
Date picker
What does "Report Connections" option do to a Filter (Slicer/Timeline)?
Send feedback to Microsoft
Makes Connection between different Excels
Allows to use one Slicer/Timeline to filter multiple Pivots
Filters different fields in table
Correct VLOOKUP use:
VLOOKUP(A2, B:C, 2, 0)
VLOOKUP(A2, B:C, 3, 0)
VLOOKUP(A:A, B:C, 2, 0)
VLOOKUP(A2, B2, 2, 0)
Benefits of XLOOKUP functionality
Smaller formula
"IF not found" option
Independent of column positions
Time saving
What function is substitute to "Text to Column" option?
TEXTJOIN
TEXTAFTER
TEXTSPLIT
TEXTBEFORE
What can be used to get "PULSE" from "BP-PULSE"?
FIND("-",text)
RIGHT(text,5)
MID(text,4,5)
LEFT(text,3)
If we want to separate Text to Columns on ";" we will select delimiter type?
Tab
Semicolon
Comma
Space
We have text "Carrier - Name, Region" And we want to have three different columns
1. Carrier, 2. Name, 3. Region
What delimiter options should we select?
Comma
Semicolon
Other = "-"
Tab
To inform user how should he enter the value in selected field with data validation (before entry), we should use next option:
Data Validation > Input Message
Data Validation > Error Alert
Right Click > New Comment
Right Click > New Note
What data validation criteria we use to make drop down menu?
Any value
Custom
Decimal
List
