MSExcel Create a new blank workbook in Excel

MS-Excel

Create a new blank workbook in Excel ? Ans: File menu-----Choose new------workbook-----ok

Open the Meetings. xls workbook that is in My Documents Folder Ans: File menu-----Open-----Select My documents----Click on the Meetings. xls---- Click open

Save a copy of this workbook with the new name Meetings Do not change the file location Ans: File menu ------Choose Save as-----in file name section Type Meetings----Click Save

How can you save a workbook (which is currently Excel 2002 file) in a previous version of Excel? Ans: File menu-----Choose Save as-----from save as type select Microsoft Excel 97 -2002 Workbook----click Save

Save this workbook as a text file Ans: File menu-----Save as-----From save as type------ Select Text (Tab delimited)-----Click save

Close this workbook without closing the application Ans: File menu-----Select Close

Formulas

Type a formula into cell D 7 which uses to calculate the total of highlighted rang. Press Enter when you have finished Ans: in cell D 7 Type =sum(C 4: C 6) ---- Press Enter

Type a formula into cell F 8 using the appropriate function to calculate the greatest value in the highlighted range. Ans: In Cell F 8 Type: =max(F 5: F 7)----Press Enter

Enter a formula in cell D 4 using cell references that would calculate the Take home pay for Ahmed Ans: In Cell D 4 Type: = B 4 -C 4 ----Press Enter

Type a formula into cell F 8 using the AVERAGE function to calculate the average of the highlighted rang. Ans: in cell F 8 Type: =average(F 5: F 7)----Press Enter

Enter a formula in cell D 6 that would calculate the pre- tax salary for Cathy Miller Press the Enter key when you have finished Ans: in cell D 6 Type =B 6*C 6 ---- Press Enter

= min (B 3: B 9) → press Enter Type a function into cell B 10 that will calculate the smallest value in the highlighted range. Press the Enter key when you have finished.

If row 6 is deleted, which formula will then be in cell B 7 Ø= SUM(B 3: B 6) ØSUM(B 3+B 4+B 5+B 6) Ø=SUM(B 3: B 7) ØSUM=(B 3: B 6)

Which formula entered into E 2 would calculate the total cost (including delivery) of Karin garden suite and, if copied down , would automatically the total cost for the other two garden suites. Ø= sum(B 2: D 2)*B 6 Ø=sum(B 2: D 2)+$B$6 Ø=sum($B$2: $D$2)+B 6

Which formula entered into cell B 13 would calculate the number of recorded rainfall readings taken over the 4 Weeks? Ø= NUMBER(B 5: D 11) Ø= count(B 5: E 11)+ (C 5: C 11)+(D 5: D 11) Ø=COUNT(B 5: E 11) Ø=NUM(B 5: D 11)

Which TWO of the following would calculate the amount left to spend for March Ø=D 8 -D 12 Ø=D 12 -D 8 Ø=SUM(D 4: D 7)-SUM(D 10 -D 11) Ø=(D 10: D 11)-(D 4: D 7) Ø=SUM(D 10: D 11)-SUM(D 4: D 7)

If the formula in B 7 is copied to cell D 7, which formula will display in the formula bar when D 7 is selected Ø=SUM(B 3: B 5)-B 6 Ø=SUM(C 3: C 5)-C 6 Ø=SUM(D 3: D 5)-B 6 Ø=SUM(D 3: D 5)-D 6

Which formula entered into B 10 would find the heaviest rainfall in the seven days? Ø=MAXIMUM(B 3: B 9) Ø=(MAX B 3: B 9) Ø=MAXIMUM(B 3: B 10) Ø=MAX(B 3: B 9)

Which of the following formulas would calculate the Net Sales for January in this worksheet? Ø=Sum(B 2: B 11) Ø=B 6+B 11 Ø=B 6 -B 11 Ø=Sum(B 6: D 6)-Sum(B 11: D 11)

Which formula entered into B 10 would find the average amount of sunshine for March to September ØAVERAGE=B 4 Ø=(AVG)B 3: B 9 Ø=AVERAGE(B 3: B 9) Ø=AVG(B 3: B 9)

Which formula will not calculate the Net Sales for January Ø=SUM(B 2: B 5)- SUM(B 7: B 10) Ø=B 6+B 11 Ø=B 6 -B 11 Ø=(B 2+B 3+B 4+B 5)-(B 7+B 8+B 9+B 10)

Which formula entered into C 3 would calculate the total cost of Barriels for the Oil Deco company and if copied down would automatically calculate the total Cost for the other customers. Ø=B 3 * C 1 Ø=B 3 * $C$1 Ø=$B$3 * C 1

Which formula entered into B 5 will calculate the total cost of a Colonial style chair (including delivery)and if copied to C 5 will perform a similar calculation for the Metro style chair? Ø SUM($B$2: $B$4) + B 8 Ø =SUM(B 2: B 4)+$B$8 Ø= SUM(B 2: B 5)+$B$8 Ø= SUM(C 2: C$) + $C$8

Which formula entered into B 10 would find the total amount of rainfall that fell in the seven days? Ø = SUMB 3: B 9 Ø =SUM(B 3: B 9) Ø =(SUM)B 3: B 9 Ø =SUM=(B 3: B 9)

Which one of the following formulas would be appropriate to calculate the monthly surplus for September in this Worksheet? Ø= B 5 + B 11 Ø= SUM(B 5: D 5)- SUM (B 11: D 11) Ø= B 5 – B 11 Ø=Average(B 5: D 5 - Average(B 11: D 11)

IF Statement

Which formula would display 10% in cell C 2 if the customer order quantity is over 300, and 0% if not? Ø = IF(C 3>300, 10%, 0%) Ø =IF(B 2>300, 10%, 0%) Ø =IF(B 3>300, 0%, 10%) Ø =IF(B 3<300, 10%, 0%)

Which formula would display 7%in cell D 10 if the customers order value is over 10, 000 and would display 0%if not. Ø= if(D 9=10, 000, 7%, 0%) Ø= if(D 9>10000, 7%, 0%) Ø= if (D 8>10, 000, 7%, 0%

Errors

If the Enter key was pressed to accept the formula in cell B 10 , which of the following would be the result ØA# REF! error message Ø 9. 3 ØA #VALUE! Error message ØA #NAME? error message

If the Enter key was pressed to accept the formula in cell E 8, What would appear in the cell? Ø#REF! Ø 5. 000 Ø#VALUE! Ø#NAME!

If you press Enter key to accept the formula in cell E 10, which of the following would be the result Ø A#REF! error message ØA #VALUE!error message ØA #NAME!error message ØA# Circular Reference error message

Sheets

Insert a new worksheet into this workbook Ans: Insert menu -----Choose Worksheet

Change the name of the worksheet from profit to blind Ans: Format menu-----Sheet------Rename. Or Right click on the profit sheet tab -----Select rename -----Type blind

Copy the entire Accounts worksheet from this workbook into The Accounts workbook, so that it displays after sheet 1 Ans: Edit menu-----Select Move or Copy----in the To book section---- Select Accounts------in Before sheet section------Select Sheet 2 -----Check on Crate a copy ---Ok

Move the entire Accounts worksheet from this workbook Into the Accounts workbook, so that it is the first Worksheet in the workbook Ans: Edit menu-----Select Move or Copy----in the To book section---- Select Accounts------in Before sheet section------Select Sheet 1 ----- Click Ok

Move the entire Annuals worksheet into the Contracts workbook, so that it displays after the other worksheet. Ans: Select Annuals-----Edit menu -----Choose Move or Copy-----in to book section Select Contracts------in before sheet section select (move to end)------Click ok.

Delete the Sheet 2 worksheet (including its contents) from this workbook Ans: Edit menu------Choose Delete Sheet

Header & Footer

Insert the file name field into left section of the header in this worksheet Ans: View menu----Header and Footer-----Header/Footer Tab----Click on Custom Header----make click in the left section ----Click on this icon ------Click ok----Click o. K

Add a page footer with the text Confidential that will print at the bottom left of all the pages in this worksheet. Ans: View menu----Header and Footer-----Header/Footer Tab----Click on Custom Footer----make click in the left section ----Type the word Confidential ----Click ok----Click Ok

Charts

Select the range of cells A 2 to D 5. Launch the Chart Wizard, and choose a Pie Chart. Click Finish when you finish Ans: Select the range → Insert menu → select Chart → Select pie from chart type → Click finish

In this wizard show percentage and click next when you finish Ans: Click on Data Labels---in data labels section---Check percentage------Click Next

Change the background color of the chart to pink Ans: Select the Chart Area------Format menu-------Choose Selected Chart area-------Pattern Tab-------in the Area section -----Select the Pink color-------Click ok

Print the selected chart Ans: File menu ---- Choose Print-----Click Ok

Delete the selected chart from this worksheet. Ans: Edit menu----- Select Clear -----Choose All

Page setup

Change the page orientation so that this worksheet will print on a page that is taller than it is wide Ans: File menu----Choose Page setup---Page Tab----In orientation section----- Check Portrait-----Click ok

Change the page orientation so that this worksheet will print on a page that is wider than it is tall Ans: File menu------Select Page Setup----Page Tab-----in the orientation section-------Select Landscape------Click ok

Adjust the page setup so that the contents of this worksheet will fit on just one page. Ans: File menu----Choose Page setup---Page Tab----In Scaling section-----Make the following Changes ----Click ok

Change the settings for this worksheet so that the contents row 1 will print at the top of every page Ans: File menu----Choose Page setup---Sheet Tab----In Print titles section -----from ----Click on the red arrow to select the first row ----Click Ok

Change the setting for this worksheet so that the gridlines will print Ans: File menu ---- Page setup----- Select sheet tab ---check the gridlines in the print section

Use Microsoft Excel Help to access information on advanced filters? Ans: Help-----Microsoft Excel Help -----Type advanced filters------Click Search

Change the value in cell B 5 to 100 , press enter when you have finished Ans: Select cell B 5 and Type 100 , Press Enter key

Copy the selected rang and paste it into meetings worksheet Ans: Edit menu --- Choose Copy ---- Click on meetings worksheet ---Edit menu ----Choose Paste

Clear the contents of cell D 6 without deleting the cell Ans: Edit menu ----Choose Clear ------ Contents

Click here Click on the location that selects all of the cells in row 5

You wish to reverse the last change you have made to this worksheet. Choose the appropriate command to do this. Ans: Edit menu ----Choose Undo

Change the user name for this application to Ibrahim Nasr. Ans: Tool menu → Choose Options → Select General tab → in user name Type Ibrahim Nasr

Enter the value 521 in cell B 5 Click any other cell when you have finished. Ans: Select B 5 cell → Type 521→ Click any other cell

Automatically adjust the height of Row 2 to fit its tallest contents Ans: Format menu-----Select Row-------Choose Auto. Fit Or: Double click on the gray area between Row 2 and Row 3

Change the contents of cell B 2 to the word Cost. Press Enter when you have finished. Ans: Select cell B 2 → Type Cost→ Press Enter

Use the Replace tool to replace all occurrences of the name Jane Harris with Tim Snow. Ans: Edit menu→ Select Replace → Type Jane Harris in the find what → Type Tom Snow in replace with → Click Replace all

Automatically sort this list so that the highest mark displays at the top of the list. Ans: Data menu----Sort-----in Sort by section Select Mark-----Select Descending----Click ok Or: Click on this icon in the toolbar

Change the display of the numbers in the selected range from no decimal places to one decimal place. Ans: Format menu----- Choose Cell ----in the Category section-------Select Number------in Decimal places section use the arrow to choose the value 1 ----Click ok.

Apply the Pound (£) currency symbol to the selected numbers. Ans: Format menu----- Choose Cell ----in the Category section-------Select Currency------in symbol section----select £ English (United Kingdom) →ok

Format the contents of the selected cell bold. Ans: Format menu----- Choose Cell ---- Font Tab----in font style section-----select Bold---Click Ok

Copy all of the formatting from cell B 3 and automatically apply it to cell D 3. Ans: Click format painter toolbar → Click on cell D 3 from the standard

Change the orientation of the text in the selected cells to vertical (90 degrees). Ans: Format menu----- Choose Cell ---- Alignment Tab ----in in orientation area move the red point up to the first point

Delete row 3 from this work sheet Ans: Select row 3 ------ Edit menu ------Choose Delete

Drag each X to a cell in this worksheet that contains an error. X X X

Print just the selected range A 2: D 6 in this work sheet. Ans: File menu --- Choose Print ---- in print what section----- Check on selection----Click ok

Change the formatting for the selected cells so they display with commas the thousand (e. g. 10, 000) Ans: Format menu-------Select Cell------Number Tab----in the Category section---- Select Number-----Check Use 1000 Separator (, )-----Click ok

Add a border around the selected range , do not change the default line style. Ans: Format menu-----Select Cell-----Border Tab-----in the Presets section ----Select Outline------Click ok

Print this worksheet Ans: File menu-------Select Print-----in Print what section ----Select Active sheet(s)---Click ok

Use click and drag to select the range A 3: D 7 Ans: Click in cell A 3 and Drag to cell D 7

Use click-and-drag to select the range A 4: C 6 Ans: Select the cell A 4 Then Drag to C 6

Dragging the fill handle up to cell B 3 will fill B 3 with: ØEnvelopes ØPens ØPaper ØStamps

Cut the selected range and paste it into cell A 1 on the worksheet entitled Accounts in this workbook Ans: Edit menu-----Select Cut-----Click on Accounts sheet----Select Cell A 1 ------Edit menu------Select Paste

Use the Find tool to find the Title Clerks in this worksheet Ans: Edit menu-----Select Find-----in Find what section Type Clerks-------Click on Find Next

Change the alignment of the selected title Monthly sales, so that it is centered horizontally over columns Ans: Format menu-----Alignment Tab-----in the text alignment section -----in Horizontal section ----Select Center Across Selection-----Click ok

Apply the Percentage style to the selected cells. Ans: Format menu----- Select Cell-----Number Tab-----in the Category section-----Select Percentage -----Click Ok

Preview the worksheet to see how it will look when printed Ans: File menu-----Print Preview

Print 3 copies of this worksheet Ans: File menu-----Print-----in the Copies section-----Choose 3 ------Click ok

View the above worksheet at 75% of its full size Ans: View menu------ Zoom----in Magnification section-----Select 75% ----Click ok.

Insert a blank row above row 5. Ans: Select row 5 from the gray area-----Insert menu---Choose Rows

Automatically adjust the width of Column B so that its widest cell content is fully visible Ans: Select column B from the gray area-----Format menu-----Select Column------Select Auto fit Selection

Dragging the fill handle down to cell A 5 will fill A 5 with: ØWeek 3 ØWeek 5 ØWeek 4 ØWeek 7

Which one of these combinations is used to indicate that cell C 1 has an absolute reference Ø£C£ 1 Ø#C#1 Ø$C $1 Ø/C/1

Change the formatting for the selected column so that date display in this format: 04 -Mar-02 Ans: Format menu-----Select Cell----Number Tab----in the Category section------Select Date-------in Type section---Select the 14 -Mar-02 -----Click ok

Change the formatting so the selected text automatically Wraps onto a second line within the cell Ans: Format menu-----Select Cell------Alignment Tab-----in The Text Control section------Select Wrap text-------Click ok

Center align the text in the selected cells horizontally Ans: Format menu----- Select Cell-----Alignment Tab-----in the text alignment section -----Horizontal section ------Select Center -----Click ok

Freeze both the column A and row 1 titles in this worksheet Ans: Select cell B 2 ------Window menu----Select Freeze Panes

Insert a new column between column A and B in this worksheet Ans: Select Column B from the gray area-------Insert menu----Select Columns

Display the Drawing toolbar in the Microsoft Excel application window Ans: View menu-------Select Toolbars------Make check on Drawing

Copy the value that is in the selected cell C 4 and paste it Into the blank cell C 6 in the same worksheet. Ans: Edit menu------Choose Copy----Click in cell C 6 ---Edit menu-----Choose Paste

Sort this Clients list ascending in alphabetical order Ans: Data menu----Choose Sort-----in Sort by section Select Clients-----Select Ascending----Click ok Or: Click on this icon in the toolbar

Change the formatting of the selected cell to italic Ans: Format menu----- Choose Cell ---- Font Tab----in font style section-----select italic---Click Ok