Microsoft Excel COURSE CONTENTS

Course contents / Table of Contents
INTRODUCTION....................................................................................................
How To Use This Guide ............................................................................................
Objectives..................................................................................................................
Instructions ................................................................................................................
Appendices................................................................................................................
SECTION 1 ADVANCED WORKSHEET FUNCTIONS.......................................
NAMES....................................................................................................................
Defining Names..........................................................................................................
Creating Names Automatically....................................................................................
Managing Names .......................................................................................................
USING NAMES .......................................................................................................
Go To.........................................................................................................................
Names In Formulae ....................................................................................................
Applying Names .........................................................................................................
CONDITIONAL & LOGICAL FUNCTIONS...........................................................
If Statements................................................................................................................
Logical Test.................................................................................................................
Value If True / False ...................................................................................................
Nested If.....................................................................................................................
Statistical If Statements ...............................................................................................
AND, OR, NOT.........................................................................................................
And.............................................................................................................................
Or ..............................................................................................................................
Not ............................................................................................................................
LOOKUP FUNCTIONS...........................................................................................
Lookup.......................................................................................................................
Vector Lookup............................................................................................................
Hlookup......................................................................................................................
Vlookup .....................................................................................................................
Nested Lookups..........................................................................................................
OTHER USEFUL FUNCTIONS................................................................................
ISERROR....................................................................................................................
IFERROR....................................................................................................................
COUNTIF...................................................................................................................
AVERAGEIF ..............................................................................................................
AVERAGEIFS ............................................................................................................
SUMIFS.......................................................................................................................
COUNTIFS..................................................................................................................
DATA CONSOLIDATION..........................................................................................
SECTION 2 VIEWS, SCENARIOS, GOAL SEEK AND SOLVER...........................
GOAL SEEKING AND SOLVING.............................................................................
Goal Seek.....................................................................................................................
Solver ..........................................................................................................................
Solver Parameters.........................................................................................................
Constraints ...................................................................................................................
Solver And Scenario Manager.......................................................................................
Opening Scenario Manager............................................................................................
Saving Solver Solutions .................................................................................................
Reports..........................................................................................................................
VIEWS & SCENARIOS...............................................................................................
Custom Views................................................................................................................
Typical Custom View Model...........................................................................................
Defining A View..............................................................................................................
Showing A View.............................................................................................................
Scenario Manager ..........................................................................................................
Load The Scenario Manager.........................................................................................
Showing A Scenario......................................................................................................
Editing A Scenario ........................................................................................................
Deleting A Scenario ......................................................................................................
SECTION 3 USING EXCEL TO MANAGE LISTS....................................................
EXCEL LISTS,LIST TERMINOLOGY........................................................................
Row And Column Content..............................................................................................
Column Labels ...............................................................................................................
List Size And Location....................................................................................................
Miscellaneous ................................................................................................................
SORTING DATA .......................................................................................................
Custom Sorting Options ...............................................................................................
Creating A Custom Sort Order......................................................................................
ADDING SUBTOTALS TO A LIST ...........................................................................
Examining Subtotals.......................................................................................................
FILTERING A LIST.....................................................................................................
Custom Criteria .............................................................................................................
Wildcards ......................................................................................................................
Turning Off Autofilter......................................................................................................
Advanced Filtering .........................................................................................................
Set Criteria ....................................................................................................................
Entering Search Criteria .................................................................................................
Wildcards With Text Criteria .........................................................................................
Multiple Criteria ............................................................................................................
Checking The Criteria Range .........................................................................................
Calculated Criteria..........................................................................................................
Calculated Criteria Using Functions ................................................................................
Copying Filtered Data.....................................................................................................
Unique Records..............................................................................................................
LIST STATISTICS........................................................................................................
Database Functions.........................................................................................................
PIVOTTABLES.............................................................................................................
To Create A PivotChart Report From An Existing PivotTable Report ..............................
Create A Static Chart From The Data In A PivotTable Report ....................................
Delete A PivotTable Or PivotChart Report .................................................................
Create Layout For PivotTables ...................................................................................
Modifying A PivotTable ..............................................................................................
MANAGING PIVOTTABLES...................................................................................
Using Another PivotTable Report.................................................................................
Changing An Existing Report's Source Data .................................................................
Automatically Refresh Data When A Workbook Is Opened..........................................
Automatically Refresh Data At Regular Time Intervals ..................................................
Require A Password To Refresh An External Data Range ............................................
Grouping PivotTable Items...........................................................................................
Formatting A PivotTable .............................................................................................
SECTION 4 CHARTS ..............................................................................................
INTRODUCTION TO CHARTING ..............................................................................
Terminology ....................................................................................................................
Embedded Charts..........................................................................................................
Separate Chart Pages ....................................................................................................
Three Methods To Create Charts...................................................................................
Moving And Resizing Embedded Charts ........................................................................
Data Layout ..................................................................................................................
Shortcut Menu (Right Click) ..........................................................................................
Chart Types ..................................................................................................................
Default Chart Type ........................................................................................................
FORMATTING CHARTS ...........................................................................................
Design Ribbon ...............................................................................................................
Switch Rows And Columns.............................................................................................
Changing The Chart Layout ............................................................................................
Chart Styles ...................................................................................................................
Moving Chart Location...................................................................................................
Layout Ribbon ...............................................................................................................
Formatting Chart Elements..............................................................................................
Resetting Custom Formats..............................................................................................
Adding, Removing And Formatting Labels .....................................................................
Axes..............................................................................................................................
Gridlines ........................................................................................................................
Unattached Text.............................................................................................................
Format Dialog................................................................................................................
Add A Series Manually .................................................................................................
The Series Function........................................................................................................
Charting With Blocks Of Data........................................................................................
SECTION 5 TEMPLATES ........................................................................................
INTRODUCTION TO TEMPLATES ..............................................................................
Standard Templates ...........................................................................................................
Custom Templates .............................................................................................................
AutoTemplates...................................................................................................................
Opening And Editing Templates..........................................................................................
Template Properties............................................................................................................
SECTION 6 AUDITING...........................................................................................
AUDITING FEATURES .....................................................................................................
Tracers..................................................................................................................................
Comments.............................................................................................................................
SECTION 7 OTHER FORMATTING .........................................................................
FORMATTING CELLS...........................................................................................................
Themes....................................................................................................................................
Customising A Theme...............................................................................................................
Styles ......................................................................................................................................
Conditional Formatting .............................................................................................................
SECTION 8 OTHER EXCEL FEATURES.....................................................................
INSERTING, FORMATTING AND DELETING OBJECTS.................................................
Inserting A Drawing Object ......................................................................................................
SmartArt...................................................................................................................................
WordArt ..................................................................................................................................
Formatting Shapes ....................................................................................................................
QuickStyles...............................................................................................................................
Manual Formatting ....................................................................................................................
REVIEWING ...........................................................................................................................
Protecting..................................................................................................................................
Use A Shared Workbook To Collaborate...................................................................................
Share A Workbook ...................................................................................................................
PROOFING TOOLS ................................................................................................................
Spelling And Grammar ...............................................................................................................
Thesaurus..................................................................................................................................
Translation ................................................................................................................................
Show Or Hide ScreenTips ...........................................................................................................
EXCEL 2007 SPECIFICATIONS AND LIMITS ............................................................

ENTERING AND EDITING INFORMATION in MS Word

When Word 2007 is launched at the beginning of a work session, it provides a blank document ready for you to start typing. Word assigns the name “Document1” and this appears on the title bar at the top of the screen until you save the document and allocate a filename. Each time a new document is created, Word will assign this temporary name with the number of the document going up in sequence (e.g. the second document created in any one session would be “Document2” and so on). The cursor appears as a flashing vertical bar. To enter text, simply begin to type. If your cursor is at the top of the page and you wish to type text some way down the
page, double click with the mouse to anchor your cursor in the desired location. This feature is known as Click and Type.
 

Word WrapWhen you are typing in a document, Word has a feature called word wrap. This is what makes the cursor automatically jump onto a new line when the text that you are typing has filled up all the space on the previous line. Therefore the only time that you need to tell Word to begin a new line is if you want to start a new paragraph.

MicroSoft Word 2010 Screen Layouts

Word is an application that runs under the Windows graphical user interface. When launched, Word sits in its own “window” – the box that surrounds the application elements. The window can be moved, sized, closed, minimised and maximised using the features common to the Windows environment – these are listed below:

WINDOW BORDER The box that surrounds the Word screen when it is not maximized is called the window border. When the mouse is over the border, the pointer changes from a single to a double-headed arrow – clicking and dragging with this shape allows the window to be resized. TITLE BAR The coloured bar that appears at the top of the Word window. The title bar tells you which application you are using and if the workbook you are in is maximized, it will also contain the name of the workbook. If the Word window is
not maximized, by positioning the mouse over the title bar and clicking and dragging, you can move the Word window to a new location on the screen.

MAXIMISE BUTTON When working in a workbook, the Word screen contains two windows, an application window and a workbook window. You can maximise both windows to capitalise on the space you have on-screen. If you would like the window that your Word application is in to fill up the whole screen, click the outermost maximise button. You may find that the workbook you are in can still be bigger – click the inner maximise button to fill the remaining space within the Word application window.

MINIMISE BUTTON This button is very useful if you need to temporarily switch from Word into another application without closing Word down completely. Click the minimise button to shrink Word to an icon on the task bar; you will then be able to view other icons and applications you may wish to access. When you are finished and ready to continue, click the Word icon from the task bar to resume. The innermost minimise button will minimise the current workbook window.

RESTORE BUTTON This button only appears when a window is maximised. A maximized window has no border and you cannot see what is behind it. If you want to put the window back inside its border so that you can move and size it, click the restore button.CLOSE BUTTON This button is used to close a window. If you click the close button for a workbook window you close the document. The last button will close the Word application.

DIALOG BOX LAUNCHER this button launches dialog boxes specific to the part of the ribbon you see them the category will be named such as FONT, CLIPBOARD, etc
The OFFICE BUTTON is the start of Word and has many important commands and option. Such as Word settings, opening, saving, printing and closing files. This will be looked into much further later in the manual.

 

Menu's

Menu’s are no longer used in Word they have been replaced by Ribbons and will be referred to as such throughout the manual

 

Ribbons

Ribbons are tabs that show different commands with respect to what you wish to do the HOME ribbon shows basic commands of formatting.

 

The INSERT ribbon allows the insertion of any object. Just click on the named ribbon to see list of commands that you can perform by clicking on the appropriate icon.

By holding your mouse over an icon, a helpful hint will appear to show you what that command will do. Clicking will apply that command Any Icon on the ribbon with a down arrow offers other options and sometimes a dialog box.

E.G. Choosing QUICK PARTS in the TEXT group allows you to insert a field within your document or a document property adding information to your document that can be updated as the information is changed at source.

 

Dialog Box
To open a dialog box use DIALOG BOX LAUNCHER when the dialog box is open, make a choice from the various options and click OK at the bottom of the dialog box. If you wish to change your mind and close the dialog box without making a choice then click on CANCEL. The dialog box will close without any choice being applied. If you would like help while the dialog box is open then click on the “? “ in the top right hand corner this will bring up a help window that will display the relevant topics.

 

Look at a group type on the ribbon such as FONT and in the bottom right hand corner of that group you may see a small box with an arrow, clicking this is another method to call up a dialog box, this time, directly from the ribbon. Many dialog boxes may be more familiar if you have used Word before.

 

Toolbars
There are only two toolbars within the new version of Office 2007 there is the QUICK ACCESS TOOLBAR seen here next to the OFFICE BUTTON, and there is the MINI TOOLBAR

Status BarThe Status bar, across the bottom of the screen, displays different information at different times. To the left is an indicator, which will display which page you are currently on, which column you are in number of words in document. If you right click on the empty area of the status bar a menu should appear showing the various items of information that can be given on the space bar merely click on the info that you wish displayed and it will appear on the status bar.

 

 

To the right of the status bar are firstly, the different views that can be used within Word, and on the far right, the zoom control