MOS - EXCEL Specialist
Excel Sample Questions
1. In cell J6 of the sales worksheet, enter a formula that sums the values in the total column by using existing named range.
Answer: in cell J6 add the formula =SUM(Total)
2.In Sales work Sheet delete all the comments in the columns B,C,F
Ans: Select B,C,F columns and goto Review tab Click on delete present in the Comments category.
3.In Quarter 1 worksheet,modify the heading1 style to use 18pts (Note: Accept all other default settings)
Ans: open quater1 Worksheet change the font size of heading to 18
4.Save the file as PDF file in the Document Folder.
5. In the summary worksheet change the order of evolution in cell F12 by using paranthesis so that it correctly calculates the increase in the revenue percentage from Quarter1to Quater2
Ans: Edit the Cell as shown in the figure.
7.In the partner order worksheet,Use Find and Replace to Find all the instances of 2765 in the Unit ID no and Replace them with 2762
8.In the Page orders Worksheet,set the page setup options so that only table headings are repeated in the worksheet is printed.
Ans: Add the title in the Print Titles Section.
9.In the Partner Order Worksheet,Sort the data in Desending order by order number and in ascending order by unit price.
Ans: Use sort and filter in home tab or sort in Data Tab.
10.In Order Analysis Worksheet,apply a correction to the image so that it is sharpened by 25%, and apply a paint brush Artistic effect.
11.In the sales by Region worksheet use the format painter to apply
the format from the total sales(All products) Data range to the cell
ranges D6:I9,D12:I15, and D18:I21
Ans: Select mineral water cell and
click on format painter. So, format gets copied into clipboard. Now
select the cell to copy the format.
12.In Sales By Region worksheet,Hide the columns C,L and M
Ans:Right click on the tabs to hide and select option to hide them.
13. Color codes for each of the quarter tab so that each tab has different color
Ans: Select the tab name. Right click and select the different colors for each tab.
14. In Summary Work sheet modify the comment in cell c7 so that it says Done instead of Review.
Ans: Select cell C7, Goto review tab, Click on show comments, Now change the comment.
15.Split the work sheet vertically into two separate ones.
Ans: Goto view tab, Select the option Split, now drag and drop the horizontal line to the bottom to remove the horizontal split.
16. Apply orange gradient fill data bar conditional formatting of the total rows of each of them.
Ans: select the cells Home tab -> Conditional formatting -> data bars -> orange bars.
17. In the Budget Worksheet remove the background of the image,and apply a Pencil Gray Scale Artistic Style.
Ans: in the image format tools
18. In the sales worksheet, Autofill only the format in the cell A4 through the end of the data series.
Ans: select cell A4, Click on format painter, and select the rest of the column to copy the format.
19. In the Partner order worksheet changes the hyperlink in the merged cell F1:H1 so that it links to cell B2 on the order Analysis worksheet.
Ans: Right click on cell F1:H1 Edit Hyperlink cell reference is B2 and place in document is Order analysis.
20. In the Order analysis worksheet, scale the chart so that it is 90% of height and width.
Ans: Right Click on the chart -> select format chart area -> in the dialog select size -> change the height and width to 90%.
21. Check the documents for personal information. Remove the only
comments and Document properties close the Dialog box when you are
finished.
Ans: File menu options -> check for
issues -> inspect document -> check only comments and personal
info -> inspect ->remove all -> done
22. In Summary worksheet, Copy the cell range E4:E7 and paste only the values into the cell range B8:B11
Ans: copy cells E4:E7. Paste in B8:B11 using paste only values option.
23. In sales worksheet add header that uses the owner, page number, current date format. Add a field to the left footer that automatically displays the sheet name.
Ans: In Insert -> select header and footer -> in Header select the appropriate option.
24 In sales worksheet, filter the table to show only records with a unit price less than 200 and that have at least 10000 units on order.
Ans: Apply filter Properties.
25. In the summary work sheet insert a formula in cell c4 that totals the values in the cell F12 on the other four worksheets.
Ans: Apply sum function.
26. In Quarter 1 worksheet, apply conditional formatting to the price
column so that values greater than 100 are formatted with a green fill
with dark green text and values less than 100 are formatted with a red
border
Ans: Apply filter followed by conditional formatting in the home tab.
27. In the sales work sheet, create a custom view named preview that
displays document in the page break preview at 80 %( Note: Accept all
the default settings)
Ans: Goto View tab -> custom View
-> add View Called Preview—> click on show -> now click on page
break Preview -> adjust the zoom level % to 80%.
28. In Sales worksheet, apply continuous arrow process layout to the
smart art Graphic, Change the style to polished and reverse the
direction from Right to Left.
Ans: Change the layout of smart art to continuous process and reverse the direction.
29. Merge and Right align the cells in the range B2:J2
Ans: Initially Merge and center. Then followed by right alignment of text in alignment column
30. Add Line Spark Lines in I19, I15, I21 that uses the data in the cells C9:H9, C15:H15, C21:H21
Ans: Insert line sparkline in the respective cells and select the data in the total coloumns.
31. In the sales work sheet edit the formula in the cell F4 so that
formula automatically maintains the correct cell references when copied
through cell F43.Copy the formula through F43.
Ans: Apply the same formulae in all other cells.
32. Copy Entire sales work sheet in the productcategory.xslx and
insert it between the sales and goals worksheet in the salesResult.xslx
workbook
Ans: Copy Paste.
33. Edit the formula in the cell N13 so that it correctly checks the
values in the Q2 total column against the Q2 Goals column and shows when
goals were met.
Ans: Change the formulae in the cell appropriately.
34. Set the margins of the following specifications
.5''(1.25cm) Top and Bottom
1.25''(3.25cm) Left and Right
1''(2.5cm) Header and Footer
Change the margins in the page layout .
.5''(1.25cm) Top and Bottom
1.25''(3.25cm) Left and Right
1''(2.5cm) Header and Footer
Change the margins in the page layout .
No comments:
Post a Comment