Required Microsoft Excel Skills
Microsoft Excel
The skills list for Microsoft Excel includes almost all skills associated with the Core level of MOUS (Microsoft Office User Specialist) certification as well as selected skills associated with the Expert level. [The numbering corresponds to the MOUS skills as listed by Microsoft.]
Core skills in Excel:
1. Working with cells and cell data
1.1 Insert, delete and move cells Insert and delete cells, columns and rows, center and merge cells
1.2 Enter and edit cell data including text, numbers, and formulas Enter cell data, use Format Painter, clear contents, formatting and comments, enter basic formulas
1.3 Check spelling Check spelling
1.4 Find and replace cell data and formats Use Find, Replace, GoTo
1.5 Work with a subset of data by filtering lists Activate, use, and turn off AutoFilter
2. Managing Workbooks
2.1 Manage workbook files and folders Open, Close, Save, Save in new folder
2.2 Create workbooks using templates Create and use templates
2.3 Save workbooks using different names and file formats Use Save As to change name, type, and location of workbook
3. Formatting and Printing Worksheets
3.1 Apply and modify cell formats Apply and modify cell formats (currency, number, font color, font size, bold, italic, underline, borders, patterns, background color)
3.2 Modify row and column settings Insert, delete, hide and unhide rows and columns; freeze and unfreeze rows and columns
3.3 Modify row and column formats Change row height and column width, use AutoFit, change orientation of text
3.4 Apply styles Apply existing styles
3.5 Use automated tools to format worksheets Use AutoFormat
3.6 Modify Page Setup options Use Page Setup to change page orientation, add page numbers and dates to headers and footers, change margins and other printing options
3.7 Preview and print worksheets and workbooks Set print area, use Page Preview and Print
4. Modifying Workbooks
4.1 Insert and delete worksheets Insert and delete worksheets
4.2 Modify worksheet names and positions Move, copy, and rename worksheets
4.3 Use 3-D references Use 3-D references in formulas.
5. Creating and Revising Formulas
5.1 Create and revise formulas Create and revise formulas, use relative and absolute references
5.2 Use statistical, date and time, in formulas Use Insert Function or toolbar to insert and edit statistical, date and time, financial, and logical functions in formulas. [Prior to program entry, you should familiarize yourself with at least the following statistical functions: AVERAGE, STDEV, MIN, MAX, and COUNT. The Statistics module of the residency will also require the use of a number of the other statistical functions in Excel, such as NORMDIST, NORMSDIST, NORMSINV, CONFIDENCE, CORREL, FREQUENCY, QUARTILE, RANK, PROB, TINV, TDIST, TTEST, and ZTEST. These are covered in Excel help as well at the Statistics text to be used. While mastery of these latter functions is not expected prior to program entry, it wouldn't hurt to read the Excel help description of them.]
6. Creating and Modifying Graphics
6.1 Create, modify, position and print charts Use Chart Wizard to create and modify charts and chart position [You should be able to create and modify the following types: Pie, Scatter, Column, Line]
6.2 Create, modify and position graphics Add Clip Art, Word Art, Picture to inset, modify, and position graphics (recommended).
7. Workgroup Collaboration
7.2 Create hyperlinks Insert and modify hyperlinks
7.3 View and edit comments Insert, delete, format, and edit comments
Expert skills in Excel:
1. Importing and Exporting Data
1.1 Import data to Excel Import data
1.2 Export data from Excel Export worksheet as text file, or other Excel version, convert to Access database (recommended).
3. Formatting Numbers
3.1 Create and apply custom number formats Create and apply custom number formats
3.2 Use conditional formats Apply and modify conditional formatting
4. Working with Ranges
4.1 Use named ranges in formulas Create names for ranges, use named ranges in formulas (recommended).
4.2 Use Lookup and Reference functions Use Lookup and Reference functions (recommended).
5. Customizing Excel
5.1 Customize toolbars and menus Customize toolbars and menus (recommended).
5.2 Create, edit, and run macros Record, edit, and run macros (recommended).
6. Auditing Worksheets
6.1 Audit formulas Use Formula Auditing to check for errors, Trace Precedents, Remove Precedents.
6.2 Locate and resolve errors Use Formula Auditing to Error Checking and Evaluate Formulas (recommended).
6.3 Identify dependencies in formulas Identify dependencies in formulas.
7. Summarizing Data
7.1 Use subtotals with lists and ranges Use subtotals with lists and ranges (recommended).
7.2 Define and apply filters Define and apply filters (recommended).
7.3 Add group and outline criteria to ranges Add group and outline criteria to ranges (recommended).
7.4 Use data validation Create and modify data validation (recommended).
8. Analyzing Data
8.1 Create PivotTables, PivotCharts, and PivotTable/PivotChart Reports Create and modify PivotTables, PivotCharts, and PivotTable/PivotChart reports (recommended).
8.2 Forecast values with what-if analysis Use Solver, Goal Seek, and Forecast function, add and remove trend-lines in charts (recommended).
8.3 Create and display scenarios Use Report Manager to create and modify reports, create and apply scenarios (recommended).