Software Solutions - Excel 2002 Skill Assessment
Please provide the following Skill Assessment Report information:
Why do we ask for you email address? Because if you have problems with your password you won't be able to open the final skill assessment report, we need more than your Firstname and Lastname to give you your password and to differentiate you from others with the same Firstname and Lastname.
Skill Set: Working with Cells and Cell Data and Managing Workbooks Skill Measurement: Insert, delete and move cells It is essential that the Excel user is able to quickly insert, delete and move the content of cells around and between worksheets. 1) To unmerge cells?
1. Right-click a cell and select Unmerge from the shortcut menu 2. Select Format>Cells and under the Alignment tab unselect Merge Cells 3. Select Format>Cells and unselect Merge Cells 4. Click the Centre icon to unmerge cells 5. Honestly don't know
Skill Measurement: Enter, edit cell data including text, numbers and formulas It is essential that the Excel user is able to quickly enter, edit all types of data including text, numbers and formulas into a worksheet. 2) To clear the content only in a cell, which option doesn't work:
1. Press the DELETE key 2. Edit>Clear>Contents from the menu 3. Format>Delete from the menu 4. Honestly don't know
Skill Measurement: Check spelling When you check spelling, Microsoft Excel checks the entire active worksheet, including cell values, cell comments, embedded charts, text boxes, buttons, and headers and footers. However, Excel does not check protected worksheets, formulas, or text that results from a formula.
3) To check the spelling in Sheet1 and Sheet5 do the following:
1. Click on Sheet1 and right-click Sheet5 and select Tools>Spelling from the menu 2. Click on Sheet1 and hold the SHIFT key and click Sheet5 and select Tools>Spelling from the menu 3. Click on Sheet1 and hold the CTRL key and click Sheet5 and select Tools>Spelling from the menu 4. Honestly don't know
Skill Measurement: Find and replace cell data and formats Excel has an extremely useful find and replace option.
4) To change all instances of the word budget in a spreadsheet to Budgets and formatted with the Red font colour use:
1. Find>Replace and the Options button 2. Edit>Replace and the Options button 3. Find>Goto and the Format button 4. Honestly don't know
Skill Measurement: Work with a subset of data by filtering lists Filtering is a quick and easy way to find and work with a subset of data in a list. A filtered list displays only the rows that meet the criteria you specify for a column.
5) If you had a list of Sales figures and you turn on the AutoFilter how do you filter the top 25 percent?
1. Sort in Descending order 2. Select (Custom..) 3. Select (Top 10...) 4. Honestly don't know
Skill Measurement: Manage workbook files and folders 6) To save a file in a new folder select:
1. File>Save As from the menu and click the Create New Folder icon 2. File>New>Folder from the menu and then File>Save As from the menu 3. Click the Create new folder icon and then File>Save As from the menu 4. Honestly don't know
Skill Measurement: Create workbooks using templates To create new workbooks that already have your preferred formatting, you can base the new workbooks on a template. A template can include formatting, styles, standardised text such as page headers and row and column labels, formulas, Visual Basic for Applications macros, and custom toolbars.
7) An Excel template has what file extension?
1. .xlt 2. .xls 3. .xtp 4. Honestly don't know
Skill Measurement: Save workbooks using different names and file formats 8) What file format does Excel not support?
1. CSV 2. MDB 3. HTM (Web Page) 4. Text 5. Honestly don't know
Skill Set: Formatting and Printing Workbooks and Modifying Workbooks Skill Measurement: Apply and modify cell formats To make text stand out, you can format all of the text in a cell or selected characters. Select the characters you want to format, and then click a button on the Formatting toolbar.
9) To rotate text 45º select ?
1. Format>Cells from the menu and the Alignment tab and set Orientation to 45º 2. Format>Cells from the menu and the Orientation tab and set Orientation to 45º 3. Format>Cell Orientation from the menu and set Orientation to 45º 4. Honestly don't know
Skill Measurement: Modify row and column settings 10) To freeze the first two columns and the first three rows select which cell before choosing Window>Freeze Panes?
1. C4 2. B3 3. Honestly don't know
Skill Measurement: Modify row and column formats 11) To set the width of columns B to D to 20 characters do the following?
1. You can't set columns to a specific number of characters wide 2. Select columns B to D and from the menu select Column>Format Width 3. Select columns B to D and from the menu select Format>Column Width 4. Select columns B to D and from the menu select Format>Column>Width 5. Honestly don't know
Skill Measurement: Apply Styles A style is a collection of formats, like bold text or background shading.
12) To apply a style to cells select?
1. Excel does not have styles, Word does 2. Format>Styles and select the style from the drop-down list 3. Click the Styles icon and select the style from the drop-down list 4. Honestly don't know
Skill Measurement: Use automated tools to format worksheets 13) Which option is not an AutoFormat?
1. Classic 1 2. List 1 3. Elegant 1 4. Honestly don't know
Skill Measurement: Modify page setup options for worksheets 14) To set a print area do the following?
1. Select the range and from the menu select File>Print and Selection 2. Select the range from the menu select File>Print Area>Set Print Area 3. Select the range and from the menu select File>Print 4. Honestly don't know
Skill Measurement: Preview and print worksheets and workbooks 15) To print Sheet1 and Sheet4 do the following:
1. Select Sheet1 and click the print icon, select Sheet4 and click the print icon 2. Click Sheet1 and hold the CTRL key and select Sheet 4 and click the print icon 3. Click Sheet1 and hold the SHIFT key and select Sheet 4 and click the print icon 4. Honestly don't know
Skill Measurement: Insert and delete worksheets A Microsoft Excel workbook is a file that contains one or more worksheets, which you can use to organise various kinds of related information. You can enter and edit data on several worksheets simultaneously and perform calculations based on data from more than one worksheet. When you create a chart, you can place the chart on the same worksheet as its related data or on a separate chart sheet. You can move from one worksheet or chart sheet to another by clicking the sheet tabs at the bottom of the workbook window. You can colour-code your sheet tabs to make them easier to recognise.
16) Which method will not insert a sheet?
1. Select Insert>Worksheet from the menu 2. SHIFT+F11 3. CTRL+N 4. Honestly don't know
Skill Measurement: Modify worksheet names and positions 17) To colour a worksheet tab do the following:
1. Right-click the sheet tab and select tab color 2. Click Format>Worksheet Color from the menu 3. Click Edit>Select All from the menu and then Format>Cells 4. Honestly don't know
Skill Measurement: Use 3-D dimensions 18) Which formula in Sheet 2 sums the range B1 to B10 and A25 in Sheet 1:
1. =SUM(SHEET2!B1:B10,SHEET2!A25) 2. =SUM(SHEET1!B1:B10,SHEET1!A25) 3. =SUM(SHEET1!(B1:B10,A25) 4. Honestly don't know
Skill Set: Creating and Revising Formulas and Creating and Modifying Graphics Skill Measurement: Create and revise formulas Formulas are equations that perform calculations on values in your worksheet. A formula starts with an equal sign (=). For example, the following formula multiplies 2 by 3 and then adds 5 to the result. =5+2*3
19) Which formula will not change the row reference only if it is filled or copied to another location?
1. =SUM($B$1:$B$10) 2. =SUM(B$1:B$10) 3. =SUM($B1:$B10) 4. Honestly don't know
Skill Measurement: Use statistical, date and time, financial, and logical functions in formulas Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. Functions can be used to perform simple or complex calculations.
20) Which function in B1 will return 10% of A1 if the value in A1 is greater than or equal to 100 else return 5%?
1. =IF(A1>=100,B1*10%,B1*5%) 2. =IF(A1>=100,A1*0.10,A1*0.05) 3. =IF(A1>=100,A1*5%,A1*10%) 4. Honestly don't know
21) To show the current date and time in a cell use the function?
1. =NOW() 2. =TODAY() 3. =DATE() 4. Honestly don't know
Skill Measurement: Create, modify, position and print charts Charts are visually appealing and make it easy for users to see comparisons, patterns, and trends in data. For instance, rather than having to analyse several columns of worksheet numbers, you can see at a glance whether sales are falling or rising over quarterly periods, or how the actual sales compare to the projected sales.
22) To modify the text in a chart legend go to which menu commands:
1. Double-click the Legend 2. Chart>Chart Options and the Legend tab 3. Chart>Source Data and the Series tab 4. Honestly don't know
Skill Measurement: Create, modify and position graphics You can add graphics to your worksheets and charts to make them more visually appealing, to create eye-catching reports, or to add emphasis. For example, you can display a logo on your worksheet, create a flowchart, and use graphics in chart data markers. You can make your worksheet interactive by using graphic objects as hyperlinks or by assigning buttons to macros.
23) To add a Venn diagram to a worksheet do the following?
1. Insert>Picture>Clip Art from the menu 2. Insert>Diagram from the menu 3. Insert>Picture>AutoShapes from the menu 4. Honestly don't know
Skill Set: Workgroup Collaboration and Importing and Exporting Data Skill Measurement: Convert worksheets into web pages Web pages often contain information that is perfect for analysis in Microsoft Excel. For example, you can analyse stock quotes in Excel using information that comes directly from a Web page. Depending on your needs, you can retrieve data that is refreshable (that is, you can update the data in Excel with the latest data on the Web page) or you can get data from a Web page and keep it static on the worksheet.
24) To view a spreadsheet as a web page do the following?
1. View>Web Page in the menu 2. File>Save as Web Page in the menu 3. File>Web Page Preview in the menu 4. Honestly don't know
Skill Measurement: Create hyperlinks A hyperlink is a link from a document that opens another page or file when you click it. The destination is frequently another Web page, but it can also be a picture, or an e-mail address, or a program. The hyperlink itself can be text or a picture.
25) Which method will not edit a hyperlink?
1. Right-click and select Edit Hyperlink 2. Select Edit>Hyperlink from the menu 3. Select Insert>Hyperlink from the menu 4. Honestly don't know
Skill Measurement: View and edit comments A comment is a note that you attach to a cell, separate from other cell content. Comments are useful as reminders to yourself, such as noting how a complex formula works, or to provide feedback to other users.
26) To print comments in a worksheet at the end of the worksheet do the following?
1. Select File>Print Comments from the menu 2. Select File>Page Setup and under the Sheet tab select Comments drop-down list 3. Select Tools>Options and under the Print tab select Comments 4. Honestly don't know
Skill Measurement: Import data to Excel By importing data, you don't have to retype the data you want to analyze in Microsoft Excel. You can also update your Excel reports and summaries automatically from the original source database whenever the database is updated with new information.
27) To import data from an Access database select which menu commands?
1. File>Import and Export and select Import Database File 2. Data>Import and Export>New Query 3. Data>Import External Data>Import Data 4. Honestly don't know
Skill Measurement: Export data from Excel 28) To Export data to a text file what menu commands would you choose?
1. File>Save As and set Files of Type to Text File 2. Data>Export and select Text File 3. Tools>Data Analysis>Export 4. Honestly don't know
Skill Measurement: Publish worksheets and workbooks to the web 29) To publish a worksheet as HTML what menu commands would you choose?:
1. Data>Export as HTML 2. File>Web Page Preview 3. File>Save as Web Page 4. Honestly don't know
Skill Set: Managing Workbooks and Formatting Numbers Skill Measurement: Create, edit and apply templates 30) To open a Spreadsheet Solutions template you would?
1. Select File>Open 2. Select File>New 3. Select File>Open Template 4. Honestly don't know
Skill Measurement: Create workspaces 31) What is a workspace file?
1. A compressed file ensuring disk space is saved 2. A file storing the Excel work environment settings 3. A collection of Excel files saved together 4. Honestly don't know
Skill Measurement: Use data consolidation 32) When consolidating data from two or more worksheets what function is not available?
1. If 2. Sum 3. StdDev 4. Honestly don't know
Skill Measurement: Create and apply custom number formats 33) To format 1000 to $1,000.00 and -1000 to -($1,000.00) and 0 to 0.00 apply which custom number format:
1. $#,###.00;-($#,###.00);0.00 2. $#,###.00;0.00;-($#,###.00) 3. Honestly don't know
Skill Measurement: Use conditional formats 34) How many conditions can you have in a single conditional format?
1. Three 2. Two 3. One 4. Honestly don't know
Skill Set: Working with Ranges and Customising Excel Skill Measurement: Use named ranges in formulas A reference identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula. With references, you can use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on other sheets in the same workbook, and to other workbooks. References to cells in other workbooks are called links.
35) To change a range name range reference select which options?
1. Edit>Named Range 2. Use the Name box 3. Insert>Name>Define from the menu 4. Honestly don't know
Skill Measurement: Use lookup and reference functions 36) To find an exact match in a Vlookup or Hlookup function the last parameter in the function must be set to?
1. True 2. False 3. Left Blank 4. Honestly don't know
Skill Measurement: Customise toolbars and menus 37) Which method is not valid; to customise a toolbar button?
1. Select Tools>Customize Toolbars and Menus in the menu 2. Select View>Toolbars>Customize 3. Select Tools>Customize in the menu 4. Honestly don't know
Skill Measurement: Create, edit and run macros If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. A macro is a series of commands and functions that are stored in a Microsoft Visual Basic module and can be run whenever you need to perform the task. For example, if you often enter long text strings in cells, you can create a macro to format those cells so that the text wraps.
38) To edit a macro which method is not valid?
1. Click the Visual Basic Editor icon on the Visual Basic toolbar 2. Click Tools>Macro>Macros>Record New Macro 3. Press ALT+F11 4. Honestly don't know
Skill Set: Auditing Worksheets and Summarising Data Skill Measurement: Audit formulas 39) Tracing dependents or precedents is done with which toolbar?
1. Formula Tracing 2. Formula Reviewing 3. Formula Auditing 4. Honestly don't know
Skill Measurement: Locate and resolve errors 40) To change error checking rules select which menu commands?
1. Tools>Options and Options tab 2. Tools>Options and Formulas tab 3. Tools>Options and Error Checking tab 4. Honestly don't know
Skill Measurement: Identify dependencies in formulas 41) The Remove All Arrows icons applies to?
1. Dependents arrows 2. Dependents and Precedents arrows 3. Precedents arrows 4. Honestly don't know
Skill Measurement: Use subtotals with lists and ranges Microsoft Excel can automatically calculate subtotal and grand total values in a list. When you insert automatic subtotals, Excel outlines the list so that you can display and hide the detail rows for each subtotal.
42) The subtotals commands is available from which menu?
1. Insert 2. Tools 3. Data 4. Honestly don't know
Skill Measurement: Define and apply filters 43) In a custom AutoFilter using AND or OR what is the maximum number of options?
1. One 2. Two 3. Three 4. Honestly don't know
Skill Measurement: Add group and outline criteria to ranges Microsoft Excel can create an outline for your data to let you show and hide levels of detail with a single mouse click. You can click the outline symbols.
44) When grouping has been applied, groups can be expanded by?
1. Clicking the ↓ button 2. Clicking the Expand button 3. Clicking the Plus button 4. Honestly don't know
Skill Measurement: Use data validation 45) To create a drop-down arrow for each cell in a range of cells that limits the entries that a user can enter into a cell apply?
1. A combo box from the forms button 2. A validation rule where the validation criteria is a list 3. Honestly don't know
Skill Measurement: Retrieve external data and create queries 46) When are you able to use the Query Wizard to create or edit a query in Excel?
1. When creating a new Web Query 2. When creating a new Database Query 3. When importing Data 4. Honestly don't know
Skill Measurement: Create extensible mark up language (XML) web queries 47) When importing data from a web page which format is not in the Options?
1. Text formatting only 2. Rich Text formatting only 3. Full HTML formatting 4. Honestly don't know
Skill Set: Analysing Data and Workgroup Collaboration Skill Measurement: Create PivotTables, PivotCharts, and PivotTable/PivotChart Reports A PivotTable report is an interactive table that quickly combines and compares large amounts of data. You can rotate its rows and columns to see different summaries of the source data, and you can display the details for areas of interest.
48) To change the layout of a PivotTable click which options on the PivotTable toolbar?
1. PivotTable and then Field Settings 2. PivotTable and then Wizard 3. PivotTable and then Table Options 4. Honestly don't know
Skill Measurement: Forecast values with what-if analysis 49) Which option is not a Trendline available in Excel?
1. Logarithmic 2. Falling Average 3. Polynomial 4. Honestly don't know
Skill Measurement: Create and display scenarios Scenarios are part of a suite of commands sometimes called what-if analysis tools. A scenario is a set of values that Microsoft Excel saves and can substitute automatically in your worksheet. You can use scenarios to forecast the outcome of a worksheet model. You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results.
50) Can you prevent changes to a scenario?
1. False 2. True 3. Honestly don't know
Skill Measurement: Modify passwords, protections, and properties Microsoft Excel provides several layers of protection to control who can access and change your Excel data.
51) To complete the protecting of cells you must Protect?
1. The Sheet 2. The Workbook 3. Honestly don't know
Skill Measurement: Create a shared workbook 52) To share an Excel Workbook and allow changes by more than one user enable Share Workbook under the Tools menu?
1. True 2. False, this is not necessary 3. Honestly don't know
Skill Measurement: Track, accept and reject changes to workbooks
Change tracking logs details about workbook changes each time you save a workbook. You can use this history to understand what changes were made, and to accept or reject revisions. This capability is particularly useful when several users edit a workbook. It's also useful when you submit a workbook to reviewers for comments, and then want to merge input into one copy, selecting which changes and comments to keep.
53) When accepting or rejecting tracked changes what options do you have?
1. Who 2. When 3. Where 4. All of the above 5. Honestly don't know
Skill Measurement: Merge Workbooks 54) When comparing and merging workbooks you are able to merge to?
1. A New Workbook 2. A New Workbook or the Current Workbook 3. The Current Workbook 4. Honestly don't know
Skill Measurement: Advanced Filtering 55) When performing an Advanced Filter you have the options to?
1. Filter in place or copy to another location 2. Filter in place 3. Copy to another location 4. Copy to another worksheet 5. Honestly don't know
