WorksheetsExcel Advanced
Total questions: 17
Worksheet time: 17mins
GoTo Special can be used to
find blank cells
copy range with hidden cells
find specific format of cells
find nonblank cells
Conditional formatting can format cells that
have a specific value
have a specific text
are blank
are nonblank
have errors
You can conditional format cells based on their values and use
Data Bars
2-Color Scale
3-Color Scale
5-Color Scale
Icon Sets
Custom formula that specifies Conditional Formatting rule returns
a number
a text
a logical value (True/False)
What are limitations of VLOOKUP?
cannot look left
cannot lookup 2 columns
cannot look both directions (rows and columns)
cannot find an approximate match
Working on dates you can
apply date functions
apply arithmetic operations
apply text functions
PROPER function converts string to
lower case
upper case
first letter of a word to lower and others to upper case
first letter of a word to upper and others to lower case
What's the difference between ISERR and ISERROR?
ISERROR treats #N/A as an error value
ISERR treats #N/A as an error value
ISERROR does not treats #REF! as an error value
ISERR does not treat #REF! as an error value
You can import a data into excel from?
a text file
the web
a database
the PowerPoint
the SharePoint
What are the options to refresh a data?
on demand
on specific time (i.e. on 12:00 AM)
on file open
by time interval (i.e. by 60 min)
What function can be used to summarize pivot table data?
Count
Sum
Average
Len
Min
What are the options to filter PivotTable?
Slicer
AutoFilter
Filter field
What are the options to sort data in PivotTable?
descending order
ascending order
manually
custom sort
Advanced sorting allows us to sort the data
By cell color
By font color
By icons
From left to right
From right to left
What Wildcards are available in Excel
?
%
*
~
What type of charts may be used to create a Combo Chart
Bar Chart
Column Chart
Line Chart
Area Chart
Which of the following statements are true for an Array Function
It allows to reduce excel file size
Overwriting a single cell of multicell formula is impossible
To run the array formula, you always have to press Ctrl+Alt+Delete
Array formula cannot return a single result
