Excel Part - II
- Open file as read-only, or as a copy
- Save file with password protection (prevent
unauthorized viewing and/or modification)
- Workbook/worksheets/range/cells protection
and formula lock (prevent unauthorized view formula or modify formula)
- Advanced page print setup - print titles
(rows/columns to repeat for all pages), gridline, comments, row/column headers, hide error
values, etc.
- Advanced functions - mega function, array
functions, matrix functions, regression functions, financial functions, database
functions, etc.
- Advanced format - custom format, text
orientation and alignment, cells merge, indent, wrap text, shrink, strikethrough,
superscript, subscript, add unit name, border line styles and color, shading, etc.
- Advanced conditional format using formula
to setup criteria - highlight the difference between 2 rows, maximum value of a range,
etc.
- Advanced data sort, filter and data entry
validation
- Paste special - paste value, formula,
format, comments, data validation, paste as link, paste with operation (+,-,*,/), paste
skip blank, paste transpose, paste format to multiple locations
- Use of names - define/create names, use
names in formula
- Use of objects - pictures, Word Art,
AutoShape, organization chart, equations, watermark,and hyperlinks
- Use of auto format for tables
- Use of control toolbox and forms to
build user-friendly interface
- Use of formula auditing
- Use of auto-correction and advanced options
- Use of goal seek to solve simple equations
- Use of pivot table and pivot charts
(optional)
- Use of solver add-in to solve equations and
optimization based on user specified constraints (optional)
- Use of data analysis toolpak for
statistical analysis (ANOVA, correlation, covariance, regression, t-test, z-test, etc.)
(optional)
- Use of scenario (what-if) analysis
(optional)
- Use of table to conduct sensitivity studies
(optional)
- Use of data consolidation (optional)
- Use of subtotal, group and outline
(optional)
- Use of Pivot Table and Pivot chart
(optional)
- Customize Excel menu and toolbars - add
your preferred buttons on the toolbars, assign your macros to a menu item (optional)
VBA Part - II
- Debug your code
- Protect your code from unauthorized viewing
with password
- Input/output with external files
- Interact with other Office applications -
Word, Access, Outlook, PowerPoint, etc.
- Control other programs
- Import/export your VBA code and forms
- Programming shortcut keys
- Create user-friendly forms as interface for
input and output
- Create your own add-in for project/company
use
- Create your own standard menu/toolbar
- Programming using API (optional)
- Database operation using SQL (optional)
- Use of class module (optional)
|