Training Centre: Kandivali (W): Shankar Lane: 76666 77286| M.G.

ROAD: 76666 55334

Adv. Excel
 Paper Pattern
Q1. State True or False (Tick Appropriate Choice): (Marks: 10)
Q2. Multiple Choice Question (Write the Correct option number in Right Answer): (Marks: 30)
Q3. Fill the Blanks: (Marks: 10)
 State True or False (Tick Appropriate Choice)

Sr.No. Question True False

When you create a formula that contains a function, the Insert Function dialog box helps
1 F
you enter worksheet functions
2 In Excel, The delete and clear commands perform the same function. T
3 Different cells with in a row can have different heights. F
4 You can have a different header and footer on each sheet of a workbook. F
Excel moves decimal point one place to the left on clicking Decrease Decimal button on
5 T
Formatting toolbar.
6 You cannot turn off gridlines. F
7 In Excel, pressing [Ctrl] + [Spacebar] select the entire row. F
8 3-D charts have a category (x) axis a value (y) axis and a third (z) axis T
9 When you copy a formula in Excel, relative cell references do not change T
10 Pie chart can represent multiple series of data. T
11 By default cells in Microsoft Excel is unlocked. T
12 Data can be imported from Notepad, Database and Webpage T
13 There are three types of LOOKUP functions, LOOKUP, VLOOKUP and CLOOKUP F
14 By applying Range Name for list data could ease run any Function T
Conditional Formatting is a formatting technique that applies a specified format to a cell
15 T
or range based upon a set of criteria
16 VLOOKUP is one of function in Database Function F
17 Sorting can be done in Ascending and Descending format F
18 Recorded Macro can be assigned to a button T
View allows you to save different display and print setting and impose them quickly and
19 T
easily on the worksheet at any time
20 You can create Pivot table either within same data source worksheet or new worksheet. T
21 A cell has a name that reflects the respective column number and row letter. T
22 To select an entire column, click the row heading F
23 IF function can be nested with another IF function. T
24 To underline your text Ctrl + u is used in Excel. T
25 The DSUM function is =DSUM(database, field, criteria). T
26 SUM and SUMIF are same function F
27 The function to sum a range is =SUM(A1:A10) T
28 =MAX(10,12,14) is 10 F
29 An Excel Workbook is a collection of Charts. F
30 Comments put in the Cells are called Cell Tip. T
31 Macros are “run” or executed from the Review tab. F

Career Counselling For Any Course

Ashish Maru: 9920092773
 Training Centre: Kandivali (W): Shankar Lane: 76666 77286| M.G.ROAD: 76666 55334

32 ABS() returns the absolute value of a number, a number with its sign. F
33 The NAME MANAGER is used to get the desired named ranges in a sheet T
34 =MIN(10,12,14) is 10 T
35 SUM and AVERAGE are same function F
36 Table and Range are same F
37 The symbol for multiplying in excel is *. T
38 Ctrl + D Shortcut key in Excel will open the font dialog box F
39 Shift + F11 key is used to add a new Excel worksheet T
40 F12 is the keyboard shortcut for creating a chart from the selected cell range. F
If you type =NOW in a cell, Excel will generate and display an error message rather than
41 T
a date.
42 PMT function calculates your monthly mortgage payment T
43 =SQRT(81) is 9 T

 Multiple Choice Question (Write the Correct option number in Right Answer):

Sr. Right
Question Option1 Option2 Option3 Option4
No Answer
Which of the following
1 formulas is not entered =10+50 =B7*B1 =B7+14 =7B+14 4
correctly?
It can contain
many sheets You have to
Each excel file is called a It can contain text It can be
2 including work hard to 3
workbook because and data modified
worksheets and create it
chart sheets
When the formula bar is The Edit Formula The Cancel
3 The Enter button All of these 4
activated, you can see button button
Which of the following is not
4 an option in the spelling Edit Ignore Ignore all Change 1
dialog box?
Which language is used to
5 Visual basic C Visual C ++ Java 1
create macros in Excel?
Which functioning Excel
6 checks whether a condition SUM IF Count Average 2
is true or not?
To move to the previous
7 Alt + PgUp Ctrl + PgUp Ctrl + PgDn Shift + Tab 2
worksheet, press
When numbers are typed
Center
8 inside a excel cell, the Left-aligned Right aligned Justified 1
aligned
default alignment is:
The Greater Than sign (>) is
9 Arithmetic Logical Conditional Greater 4
an example of ___operator.
The process of arranging the
items of a column in some
10 Arranging Auto fill Sorting Filtering 3
sequence or order is known
as :

Career Counselling For Any Course

Ashish Maru: 9920092773
 Training Centre: Kandivali (W): Shankar Lane: 76666 77286| M.G.ROAD: 76666 55334

When you create a chart on

Embedded
11 a separate sheet in the same Chart sheet View sheet View chart 1
chart
workbook it is called
Control data Import data Insert data from
12 What is Data Validation? All of these 1
entered by user from system File
The resulting
Multiply the
The Paste Special command Cell values of a Formatting
13 selection by a 1
lets you copy and paste comments formula instead of options
copied value
the actual formula
Use the Wrap
Start typing in the Use the Shrink to
How to fix long text in a Text option in
cell and press Fit option in the
14 single cell with multiple Home All of these 2
Enter key to start Home Alignment
lines? Alignment
another line Tab
Tab
What does COUNTA () Counts cells counts Counts
15 counts empty cells 4
function do? having alphabets selected cells nonempty cells
What is the shortcut key to
16 Ctrl + H Ctrl + R Ctrl + 9 Ctrl + - 3
hide entire row?
Click on
Adding? symbol at Worksheet Double click on
Worksheet
Worksheet can be renamed the end of tab by worksheet tab
17 cannot be 3
by filename while Holding CTRL and type new
renamed
saving workbook Key and type name
new name
Further
There is an Further changes in
If the Cell B1 contains the There is a relative changes in
absolute Value of A1 will
18 formula =A$1, which of the reference to Cell value of B1 1
reference to not affect the
following statements is true A1 will affect the
Cell A1 value of B1
value of A1
The _______ function
19 returns the reference INDEX MATCH INDIRECT None of these 3
specified by a text string
The output of
20 TRUE FALSE Cannot Say None of These 2
=AND(1>2,2>3) is
The output of formula
21 1 2 3 4 3
=COLUMNS(E4:G4) is
What term describes
22 explanatory text attached to Context Callout Comment Dialog 3
a cell?
A value used in a formula
23 that does not change is Constant Cell address Variable Static 1
called a?
To create an interactive
Pivot Table for the web, you Pivot Table Pivot Table
24 HTML Pivot Table Report 1
use a Microsoft Office Web Field List List
component called?
Using Paste Special
command which of the
25 Formulas Validation Formats All of above 4
following you can paste
selectively

Career Counselling For Any Course

Ashish Maru: 9920092773
 Training Centre: Kandivali (W): Shankar Lane: 76666 77286| M.G.ROAD: 76666 55334

The _____ function returns

26 the day of the month, a DATE DAY DATEVALUE None of These 2
number from 1 to 31
You could summarize an Prepared in Prepared in
Prepared in Excel
27 existing report to a Pivot PowerPoint table Word table All of these 3
table layout
Table if data layout layout
What is short cut key to
28 Ctrl H Ctrl K Ctrl C Ctrl J 2
make Hyperlink?
You can ________ data from
each separate worksheet
29 into a master worksheet to Concatenate Consolidate Containing Filtering 2
summarize and report
results.
What is the shortcut key to
30 replace a data with another Ctrl + H Ctrl + Shift + R Ctrl + R All of above 1
in sheet?
Select the sheet
Select the
then choose
Select the sheet, sheet, then
Which of the following Home
then choose Page choose
31 action removes a sheet from Format none of these 3
Layout Home
workbook? Sheet Hide &
Delete Sheet Delete
Unhide
Delete Sheet
Hide Sheet
What is the shortcut key to
32 insert new sheet in current F11 Alt+F11 Ctrl+F11 Shift+F11 4
workbook?
Data layout can be change
33 True False I Don’t know None of these 1
by using Paste Link option
What is the answer for this
formula? =if(A1>B1, "Good",
34 Good Not Good Very Good None of these 2
"Not Good") ---> A1=90 ,
B1=90
Protect Sheet and Protect
35 Workbook option can be View Review Data References 2
found in View Ribbon
Greater than Less than and
36 Logical Operator for > = is Greater than Equal 2
and equal equal
Shortcut key to transfer
37 chart to a new spreadsheet F11 F12 F10 F9 1
is
What type of chart is useful
38 for comparing values over Pie Chart Column Chart Line Chart Dot Graph 2
categories?
Which of the following is not
39 an option of the spelling Ignore Ignore All Edit Change 2
dialog box?
MS-Excel uses the _____
40 Function when creating a Average Count Both of 1 and 2 Table 4
data table

Career Counselling For Any Course

Ashish Maru: 9920092773
 Training Centre: Kandivali (W): Shankar Lane: 76666 77286| M.G.ROAD: 76666 55334

In MS-Excel which functions

will calculate the number of
41 Workday Date Network day Weekday 3
workdays between
6/9/2018 and 8/12/2018
What MS-Excel feature can
Auto
42 you use if you want to work Auto Filter Data Form All of these 3
Complete
with one record at a time?
In MS-Excel value used in a
43 formula that does not Cell address Constant Function None of these 2
change is called a
In MS-Excel which elements
of worksheet can be
44 Contents Objects Scenario All of these 4
protected from accidental
modification?
In MS-Excel which of the
45 following is an absolute cell !A!1 $A$1 #a#1 None of these 2
reference?
In MS-Excel which is used to
46 Solver Goal seek Scenario Manager All of these 4
perform what if analysis?
What is the short cut key to
47 replace a data with another Ctrl + H Ctrl + Shift + R Ctrl + R All of these 1
in sheet?
Which of the following you
48 can paste selectively using Formats Validation Formulas All of these 4
Paste Special command?
Long text can be broken
down into many lines within Conditional
49 Justify Wrap Text None of these 2
a cell. You can do this Formatting
through
In MS-Excel the view that
puts a blue a blue line Page Break
50 Print Preview Normal View None of these 3
around each page that Preview
would be printed is the
Adds up cell values Adds all the Returns a subtotal
In MS-Excel what does
51 based on a numbers in a in a list or Both 1 and 2 1
SUMIF function do
condition range of cells database
Which function in Excel
52 checks whether a condition SUM IF Count Average 2
is true or not?
Ctrl + F1 is the shortcut to 3
53 Hide Ribbon Show Ribbon Both of These None of These

Ctrl + F2 is used to open the Print Dialog

54 Save Dialog Box Cancel Dialog Box None of These 2
Box
Ctrl + F3 is used to get the Worksheet Workbook
55 Name Manager None of These 3
Manager Manager
56 The output of =CHAR(65) is A a AA Aa 1

Career Counselling For Any Course

Ashish Maru: 9920092773
 Training Centre: Kandivali (W): Shankar Lane: 76666 77286| M.G.ROAD: 76666 55334

The missing parameter in

57 the formula =PV(rate, PV NPV PMT IPMT 1
nper,___,[fv],[type]) is
The output of formula
58 10 12 14 16 2
=AVERAGE(10,12,14) is
With which of the following $ =
59 / * 4
all formulas in excel starts?
Which of the following
keyboard shortcut can be
60 F11 F10 F4 F2 1
used for creating a chart
from the selected cells?
Which among following is Word
61 Graphic program Presentation Spreadsheet 4
associated with excel? Processor
In Paste Special dialog box,
62 which of the following Divide Add Subtract Square root 4
option is not available?
To manually update the
Pivot Table Field List, users
63 Inform Revise Update Not Sure 3
need to click the ______
button.
Opening a Closing a
64 Ctrl + F4 is used for Both of These None of These 2
Workbook Workbook
Select a particular Select a
65 Ctrl + A is used for particular Select All Cells None of These 3
Cell Range
66 Ctrl + F1 is the shortcut to Hide Ribbon Show Ribbon Both of These None of These 3
How many recent files you
67 can display on File menu at 3 7 9 15 4
maximum?
Maximize a Minimize a
68 Ctrl + F9 is used for Both of These None of These 2
Workbook Workbook
Maximize a Minimize a
69 Ctrl + F10 is used for Both of These None of These 3
Workbook Workbook
“Qtr 1, Qtr 2, Qtr 3”is an
70 Formula Function Series Syntax 3
example of a
____________ function
71 rounds a number down to ROUND INT ABS VALUE 2
the nearest integer
The output of formula
72 9 15 7 6 3
=ROWS(E9:E15) is
towards the towards the towards the
On Excel screen, Tab scroll towards the top
73 top right bottom right bottom left 4
buttons are: left corner
corner corner corner

Career Counselling For Any Course

Ashish Maru: 9920092773
 Training Centre: Kandivali (W): Shankar Lane: 76666 77286| M.G.ROAD: 76666 55334

 Fill the Blanks:

1. In Excel, the intersection of row and column is known as Cell.
2. F11 Keyboard shortcut is used for creating a chart from the selected cells.
3. A Graphical view of data is called Smart art in Excel.
4. To select multiple non-adjacent cells in a worksheet you will click them holding Ctrl Key.
5. Large Function will return the largest value in the selected range of cells.
6. “New Comment” option can be found under Review tab.
7. ABS Function returns the absolute value of a number, a number without a sign.
8. The formula of PMT is : =PMT(Rate, Nper,-Pv)=PMT
9. You can quickly change the appearance of your work by choosing Format as Table from the Home tab.
10. The accounting style shows negative numbers in Red.
11. To remove sheet from workbook choose Hide Sheet option from Home tab.
12. The Fill feature of MS Excel quickly completes a series of data.
13. The Convert function converts Miles to kilometers.
14. A group of cells is called a Range.
15. Pie type of chart present Data in a piece of slice.
16. Concatenate Command combines several cells into one cell occupying the same screen space.
17. Bottom left is the default alignment for values and dates in a cell.
18. The shortcut key to edit a cell is F2.
19. A Graphical view of data is called Chart in Excel.
20. A quick way to return to a specific area of a worksheet is to type in the Name Box.
21. Formulas in Excel start with =.
22. Area formed by Intersection of row and a column in a table is known as Cell.
23. To select multiple non-adjacent cells in a worksheet you will click them holding Ctrl Key.
24. The process of identifying specific rows and columns so that so that certain columns and rows are always
visible on the screen is called Define Name.
25. Formulas are entered in the worksheet cell and must begin by =.
26. Right is the default alignment for values and dates in a cell.
27. To remove sheet from workbook choose Delete option from Home tab.
28. / is a symbol of divide in Excel.
29. The Auto fill/Series feature of MS Excel quickly completes a series of data.
30. The box on the chart that contains the name of each individual record is called the Data labels.
31. The formula of MAX is: =MAX(value1, value2, value3…..)
32. Ctrl+V is the shortcut for Paste.
33. Pressing the F5 function key opens the Go to dialog box.
34. If you press Enter the cell accepts your typing as its contents.
35. A function inside another function is called a Nested function.
36. The accounting style shows negative numbers in Red Column.
37. The extension of MS Excel File is .XLSX
38. The Software which contains rows and columns is called Spread Sheet.

Career Counselling For Any Course

Ashish Maru: 9920092773
 1

ADVANCE EXCCEL SOLUTION SEPTEMBER- 2023

I. State True or False (Tick Appropriate Choice) (Marks: 10)

Sr. No. Question True False

1 =MIN (10,12,14) is 10 True

2 Formula palette is used to copy all cells. True

In Excel 2013, you can use the TRANSPOSE function to change the orientation of data True
3
from rows to columns, or vice versa.
Excel 2013 provides the ability to create custom views to display specific sets of data True
4
and formatting.
Excel 2013 provides the ability to create and customize pivot tables for data analysis and True
5
reporting.
In Excel 2013, you can use the AVERAGE function to calculate the average of a range True
6
of cells.
7 Excel 2013 does not support the use of charts and graphs to visually represent data. False

8 Excel 2013 does not support the creation of data validation rules to restrict input values. False

PivotTables can be used in Excel 2013 to summarize and analyze large amounts of True
9
data.
10 Excel 2013 does not support the use of macros for automating tasks. False

1
MBTB/GBTB/RSTIL/DTP/ NOV-2023 Career Counselling: Ashish Sir: 9920092773
II. Multiple Choice Question (Write the Correct option number in Right Answer (Marks: 30)

Right
Sr. No Question Option1 Option2 Option3 Option4
Answer

Which of the following is

1 not a valid function in VLOOKUP SUMIF AVERAGEIF MAXIFS 4
Excel 2013?
Which ribbon in Excel
2013 contains the 1
2 Home Insert Page Layout Formulas
commands for formatting
cells?
Copying and
Which of the following is Typing
pasting from Importing a Sending an 4
3 not a valid way to enter directly into
another text file email to Excel
data in Excel 2013? a cell
application
What is the shortcut key
for opening the Format Ctrl + Shift + Ctrl + Shift + 1
4 Ctrl + 1 Ctrl + F
Cells dialog box in Excel F 1
2013?

5 Screenshot SmartArt Table None Of This 1

What Is The
Name Of This Icon?
Getting data from a cell
6 located in a different Accessing Referencing Updating Functioning 2
worksheet is called?

The output of formula 3

7 9 15 7 6
=ROWS(E9:E15) is

The output of None of 2

8 TRUE FALSE Cannot Say
=AND(1>2,2>3) is These

Select a Select a
Select All None of 3
9 Ctrl + A is used for particular particular
Cells These
Cell Range
Which function in Excel
10 checks whether a SUM IF Count Average 3
condition is true or not ?

In MS-Excel which is
Scenario 4
11 used to perform what if Solver Goal seek All of these
Manager
analysis?
Which of the following
you can paste 4
12 Formats Validation Formulas All of these
selectively using Paste
Special command?
Which functioning Excel
13 tells how many numeric NUM COUNT SUM CHKNUM 2
entries are there ?
Long text can be broken
down into many lines Conditional None of 2
14 Justify Wrap Text
within a cell. You can do Formatting these
this through

2
MBTB/GBTB/RSTIL/DTP/ NOV-2023 Career Counselling: Ashish Sir: 9920092773
 A value used in a
15 formula that does not Constant Cell address Variable Static 1
change is called a?

III. Fill the Blanks (Marks: 10)

1. The Conditional Formatting_ feature in Excel 2013 allows you to highlight cells that meet specific

criteria using different colors or formatting styles.

2. The shortcut key combination to save an Excel workbook is CTRL + S .

3. The Freeze Pane feature in Excel 2013 allows you to freeze rows or columns so that they remain visible

when scrolling through a large worksheet.

4. To quickly navigate to the last cell in a worksheet in Excel 2013, you can press Ctrl + End / Ctrl +  .

5. To merge two or more cells into a single cell in Excel 2013, you can use the Merge & Center feature.

6. In Excel 2013, the = Min function is used to find the minimum value in a range of cells.

7. To format numbers as currency in Excel 2013, you can apply the Number format to the cells.

8. The Track Changes feature in Excel 2013 allows you to track changes made to a workbook and

accept or reject them.

9. Ctrl+V is the shortcut for Paste _.

10. “New Comment” option can be found under Review tab.

3
MBTB/GBTB/RSTIL/DTP/ NOV-2023 Career Counselling: Ashish Sir: 9920092773
 ADVANCE EXCCEL SOLUTION NOVEMBER- 2023

I. State True or False (Tick Appropriate Choice) (Marks: 10)

Sr. No. Question True False

You can create Pivot table either within same data source worksheet or new worksheet. True
1
True
2 Recorded Macro can be assigned to a button
View tabs allows you to save different display and print setting and impose them quickly True
3
and easily on the worksheet at any time
True
4 In order to perform VLOOKUP, the reference table must be in the same file.
True
5 Comments put in the Cells are called Cell Tip.
True
6 You can adjust print area with manual page breaks.
False
7 There are three types of LOOKUP functions, LOOKUP, VLOOKUP and CLOOKUP
True
8 If you forget the File Open password, you can retry 3 times.
False
9 F12 is the keyboard shortcut for creating a chart from the selected cell range.
In Excel 2013, you can use the TRANSPOSE function to change the orientation of data True
10
from rows to columns, or vice versa.

1
MBTB/GBTB/RSTIL/DTP/ NOV-2023 Career Counselling: Ashish Sir: 9920092773
 II. Multiple Choice Question (Write the Correct option number in Right Answer (Marks: 30)

Right
Sr. No Question Option1 Option2 Option3 Option4
Answer

Using the
Using the
Goal Seek Using the Data
What-If Using the
Which of the following is feature to Analysis
Analysis CONCATEN 4
not a valid method for determine ToolPak to
1 feature to ATE function
data analysis in Excel input values perform
explore to merge text
2013? to achieve a statistical
different strings
desired analysis
scenarios
outcome
In Excel 2013, which
function would you use to DATEVALU
2 DAY MONTH YEAR 1
convert a text string to a E
date format?
Using the
Protect Using the Disabling the
Which of the following is Locking cells
Sheet Hide Formula Formula Bar
not a valid method for using the 3
3 feature to feature to to prevent
protecting cells in Excel Format Cells
prevent hide formulas editing of
2013? dialog box
changes to from users formulas
specific cells
In MS-Excel the view
that puts a blue a blue
Print Page Break 3
4 line around each page Normal View None of these
Preview Preview
that would be printed is
the
3 type of error alerts in
5 data validation are as Stop Information Critical Warning 3
below except

6 Column Link Win/Loss Comments 2

----- Which
Icon Is This?
Which of the following
7 formulas is not entered =10+50 =B7*B1 =B7+14 =7B+14 4
correctly?

What is short cut key to 2

8
make hyperlink? Ctrl H Ctrl K Ctrl C Ctrl J

What means of this ! Name of Absolute Name of 1

9 Name of Cell
symbol in Excel ? Worksheet Reference Workbook

Adds up cell Returns a

Adds all the
In MS-Excel what does values subtotal in a 4
10 numbers in a Both 1 and 2
SUMIF function do based on a list or
condition range of cells database
What type of chart is
11 useful for comparing Pie Chart Column Chart Line Chart Dot Graph 2
values over categories?
The function
12 returns the reference INDEX MATCH INDIRECT None of these 1
specified by a text string

2
MBTB/GBTB/RSTIL/DTP/ NOV-2023 Career Counselling: Ashish Sir: 9920092773
 Which of the following
keyboard shortcut can be 1
13 F11 F10 F4 F2
used for creating a chart
from the selected cells?

Opening a Closing a None of 2

14 Ctrl + F4 is used for Both of These
Workbook Workbook These

What is the short cut

15 key to replace a data Ctrl + H Ctrl + Shift + R Ctrl + R All of these 1
with another in sheet?

III. Fill the Blanks (Marks: 10)

1. / Is a symbol of divide in Excel.

2. A group of cells is called a Range.

3. The Number function converts Miles to kilometers.

4. Formulas are entered in the worksheet cell and must begin by =

5. ABS function returns the absolute value of a number, a number without a sign.

6. The box on the chart that contains the name of each individual record is called the name-box .

7. Pie-Chart type of chart present Data in a piece of slice

8. To select multiple non-adjacent cells in a worksheet you will click them holding CTRL Key

9. In Excel 2013, the Days function is used to calculate the number of days between two dates.

10. To apply the same formatting to multiple cells in Excel 2013, you can use the Conditional Formating feature.

3
MBTB/GBTB/RSTIL/DTP/ NOV-2023 Career Counselling: Ashish Sir: 9920092773