Data Validation & Multiple-level Interactive
If you want to type the optional data in Data Validation - Settings - Source section, you need to separate them by:
A. , (Comma) B. ; (Semicolons)
A. , (Comma)
Which function can be used to remove blank space before/after data in cell A1?
A. =Clip(A1)
B. =Trim(A1)
C. =Adorn(A1)
D. =Dislodge(A1)
B. =Trim(A1)
The hot key to change between absolute references and relative references.
A. F7
B. F4
C. F5
D. F12
B. F4
Which menu bar include PivotTable function?
A Home
B Insert
C Page Layout
B Insert
Which menu bar include Macro function?
A. Home
B. Review
C. Formula
D. View
D. View
How to operate to create a dropdown list for us to select valid data?
Data Validation - choose "List"
Which function can be used to find out same data in different columns?
A. Data Bars → Between
B. Data Bars → Duplicate Values
C. Highlight Cells Rules → Between
D. Highlight Cells Rules → Duplicate Values
D. Highlight Cells Rules → Duplicate Values
Please select the correct matching for the composition of VLookup formula.
A. FALSE - 0; TRUE - 1
B. FALSE - 1; TRUE - 0
A. FALSE - 0; TRUE - 1
Which hot key we can use to select all data quickly?(Extend Selection from the first cell to the last cell of Excel)
Ctrl + Shift + ↓
Which shortcut can be used to open Visual Basic studio to design your codes?
ALT+F11
The result of below formula:
=Indirect(A1)
A B C D E F
1 B1 B2 B3 B4 B5 B6
2 C1 C2 C3 C4 C5 C6
3 A1 A2 A3 A4 A5 A6
B2
If you want to set format using 'New Formatting Rule' under Conditional Formatting, to implement that highlighting entire row when showing "pending" in column E. Which of the following formulas is true?
A. =$E1="pending"
B. =E$1="pending"
C. =E1="pending"
D. =$E$1="pending"
A. =$E1="pending"
Under VLookup, what's the meaning of TRUE and FALSE?
TRUE: Approximate match
FALSE: Exact match
How many parts were included in a PivotTable? What's their name?
3 parts
1.PivotTable 2.Chooese Fields 3.Drag Fields
Which shortcut can be used to call up Macro function to run a Macro?
ALT+F8
Set Data Validation per below request:
List: QD; HD; JN
Show Input Message: Note - Input Location
Stop when user enters invalid data
Choose the WRONG description of Conditional Formatting:
A. You can remove duplicated data by using function under Highlight Cells Rules
B. 'Top/Bottom Rules' is to highlight data trends via icon
C. Blank space will impact the results
D. You can clear all rules one time
B. 'Top/Bottom Rules' is to highlight data trends via icon
Please explain the meaning of VLOOKUP standard formula.
=VLLOKUP(lookup value, table array, col index num,{range lookup})
There are four blank field under drag fields, what are their name?
Report Filter
Column Labels
Row Labels
Values
You can access the macro code from any workbook on your system if the Macro was stored in:
A. Personal Macro Workbook
B. This Workbook
C. New Workbook
A
Set Multiple-level Interactive per below request:
QD: D2; G5; C2
HD: 15; 32; 46
JN: D; F1; F2
Client provides a VIN #list, request us to compare the provided with system. Please complete effciently.
1. Compare column C & D and find the VIN# discrepancy(NOTE: delete duplicate data in one column and remove all space to make sure we got correct result)
2. Clear all format in whole worksheets
Please use the VLookup function to fill all the Eff Date.
With the source document, please create a PivotTable per below request:
1. Create PivotTable on a new sheet
2. Show each task's actual tracked time.
1. Record a Macro in workbook Case_1
a) Highlight Active Title with Green
b) Add border for all used cells
2. Run this Macro in workbook Case_2