Financial Data Using Advanced Excel

Microsoft Excel is a spreadsheet application developed by Microsoft for Microsoft Windows and Mac OS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms, especially since version 5 in 1993, and it has replaced Lotus 1-2-3 as the industry standard for spreadsheets. Excel forms part of Microsoft Office.

Microsoft Excel has the basic features of all spreadsheets, using a grid of cells arranged in numbered rows and letter-named columns to organize data manipulations like arithmetic operations. It has a battery of supplied functions to answer statistical, engineering and financial needs. In addition, it can display data as line graphs, histograms and charts, and with a very limited three-dimensional graphical display. It allows sectioning of data to view its dependencies on various factors for different perspectives (using pivot tables and the scenario manager). It has a programming aspect, Visual Basic for Applications, allowing the user to employ a wide variety of numerical methods, for example, for solving differential equations of mathematical physics, and then reporting the results back to the spreadsheet.

It also has a variety of interactive features allowing user interfaces that can completely hide the spreadsheet from the user, so the spreadsheet presents itself as a so-called application, or decision support system (DSS), via a custom-designed user interface, for example, a stock analyzer, or in general, as a design tool that asks the user questions and provides answers and reports. In a more elaborate realization, an Excel application can automatically poll external databases and measuring instruments using an update schedule, analyze the results, make a Word report or PowerPoint slide show, and e-mail these presentations on a regular basis to a list of participants.

MODULE 1: SECURITY AND PROOFING

SECURITY FEATURES

• Working with Security Features

PROTECTING EXCEL FILES

• Specifying a password for opening a workbook
• Specifying a password for saving a workbook
• Removing a Workbook Password
• Using the Read-only option
• Creating backup files automatically

WORKSHEET PROTECTION

• Protecting Worksheets
• Un-Protecting Worksheets
• Protecting Cells

WORKING WITH DIGITAL SIGNATURES

• Using Digital Signatures
• Signing a File
• Removing a digital signature from a file

CHECKING SPELLING

• Using the Spell Checker

USING DATA VALIDATION

• Setting data validation
• Creating the Input Message
• Displaying an Input Message
• Creating the Error Alert
• Displaying an Error Alert

USING TEXT TO SPEECH

• Reading back a group of cells
• Using Speak On Enter
• Turning off Speak On Enter

DOCUMENTATION

• Working with Comments
• Adding a Comment
• Displaying a Comment
• Editing a Comment
• Deleting a Comment

MODULE 2: ANALYZING DATA

WHAT-IF ANALYSIS

• Using What-If Analysis

GOAL SEEK

• Using Goal Seek
• Applying Goal Seek

DATA TABLES

• Using Data Tables
• Creating a one-variable Data Table
• Creating a two-variable Data Table
• Speeding up calculations with Data Tables

SCENARIO MANAGER

• Using Scenario Manager
• Adding a Scenario
• Showing a Scenario
• Deleting a Scenario
• Editing an existing Scenario
• Summarizing Scenarios

SOLVER

• Using Solver
• Installing Solver
• Applying Solver
• Changing  a Constraint
• Deleting a Constraint

MODULE 3: TEMPLATES AND STYLES

WORKING WITH TEMPLATES

• Using Templates
• Creating a Template
• Applying Templates

WORKING WITH STYLES

• Using Styles
• Creating a Style
• Applying a Style
• Editing a Style
• Deleting a Style
• Copying Styles from another Workbook

MODULE 4: COLLABORATION AND CONSOLIDATION

SHARING WORKBOOKS

• Using Shared Workbooks
• Allowing Workbook Sharing
• Customizing Workbook Sharing Options
• Comparing and Merging Shared Workbooks

SENDING WORKBOOKS

• Sending Workbooks for Review
• Sending a Worksheet as Attachment
• Routing a Workbook
• Routing a Workbook to the next recipient

CONSOLIDATION

• Consolidating data over several Worksheets
• Consolidating selected rows and columns from several Worksheets

MODULE 5: IMPORTING DATA

IMPORTING DATA FROM EXTERNAL SOURCES

• Using Database Terminology
• Importing Data into Excel

 IMPORTING TEXT FILES

• Importing text files into Excel
• Importing text using the Text Import Wizard
• Refreshing data from imported text files

USING MICROSOFT QUERY

• Adding a Data Source
• Creating a Query

QUERYING DATA FROM THE WEB

• Using Web Queries
• Accessing the New Web Query dialog box
• Creating a new Web Query
• Saving a Web Query
• Setting Web Query options
• Running a saved Web Query
• Refreshing a Web Query

REFRESHING DATA

• Refreshing external data without losing the formatting
• Refreshing external data automatically

MODULE 6: MACROS AND CUSTOM CONTROLS

WORKING WITH MACROS

• Using Macros
• Recording a Macro
• Assigning a shortcut key to your Macro

RUNNING MACROS

• Running a Macro using the main menu
• Running a Macro using a shortcut key
• Running a Macro using a button

ADDING CUSTOM CONTROLS

• Working with Buttons
• Creating a new Button
• Renaming a Button
• Formatting Buttons
• Linking an existing Macro to a Button
• Deleting a Button

MODULE 7: CUSTOMIZING EXCEL 2010

CONFIGURING EXCEL 2010 OPTIONS

• Customizing Excel Options
• Customizing View Options
• Customizing Calculation Options
• Customizing Edit Options
• Customizing General Options
• Customizing Transition Options
• Customizing Custom Lists Options
• Customizing Chart Options
• Customizing Color Options
• Customizing International Options
• Customizing Save Options
• Customizing Error Checking Options
• Customizing Spelling Options
• Customizing Security Options

CUSTOMIZING THE EXCEL ENVIRONMENT

• Displaying Toolbars using the Customize dialog box
• Creating a Custom Toolbar
• Adding a Menu to a Toolbar
• Attaching a Toolbar
• Deleting a Custom Toolbar
• Resetting a Menu
• Resetting a Toolbar

WORKING WITH WORKSPACES

• Using Workspace files
• Saving your Workspace