About Workshop
The starter level topics covered in this course provide the learner with the skills and knowledge necessary to create workbooks in Microsoft Excel. It covers creating a new workbook, adding data, editing data, working with formulas, printing and charting. On the intermediate level, the course extends the learner's basic knowledge of Excel and provides the learner with skills and knowledge to produce more effective and productive workbooks. It covers formulas and function techniques, more intricate formatting, setting complex printing options, using intricate charting features, and working more effectively with existing worksheets and workbooks. Later on theadvance level focuses on the more advanced features of Excel 2007. Advanced analysis tools, especially pivot tables, Solver, outlining and summarising are overed, as are some key automation features such as Macros.
Learning Outcomes:
Work within the basic excel environment
create, open and navigate within workbooks and worksheets
make changes to data in a workbook
understand, create and work with formulas and functions used to perform calculations
understand and use formula cell referencing to create more complex formulas
understand and work with ranges in a worksheet
copy and paste data in excel
use font formatting techniques to greatly enhance the look of a worksheet
align the contents of cells in a number of ways
format rows and columns in a worksheet
understand and use the number formatting features in excel
print your workbook data
create effective charts in microsoft excel
use the fill operations available to fill a data series
move the contents of cells and ranges within and betweenworkbooks
use a range of formula techniques
use a range of logical functions
apply a range of number formatting techniques to data
apply conditional formatting to ranges in a worksheet
apply borders to cells and ranges
work with various elements of a worksheet
use a range of techniques to work with worksheets
use a range of find and replace techniques
apply a variety of page setup techniques
sort data in a list in a worksheet
filter data in a table
use a range of techniques to enhance charts
apply formatting techniques to text on charts
use a range of lookup and reference functions
modify excel options
create and use labels and names in a workbook
protect data in worksheets and workbooks
summarise data using subtotals and relative range naming
use data linking to create more efficient workbooks
use the data consolidation feature to combine data from
several workbooks into one
understand and create simple pivottables
construct and operate pivottables using some of the more
advanced techniques
use goal seeking to determine the values required to reach a desired result
group cells and use outlines to manipulate the worksheet
use solver to solve more complex and intricate problems
create recorded macros in excel
use the macro recorder to create a variety of macros
Workshop topics to be covered:
Getting To Know Excel
● Starting Excel ● The Excel Screen ● Using The Ribbon ● Using Key Tip Badges ● Minimising The Ribbon ● Using Shortcut Menus ● Launching Dialog Boxes ● Customizing The Quick Access Toolbar ● The Office Button ● Using The Office Button ● The Status Bar ● Customizing The Status Bar
Start Working with Workbooks
● Understanding Workbooks ● Using The Blank Workbook Template ● Typing Text Into A Worksheet ● The Save As Dialog Box ● Saving A New Workbook ● Typing Numbers Into A Worksheet ● Typing Simple Formulas In A Worksheet ● Easy Formulas ● Typing Dates In A Worksheet ● Easy Formatting ● Checking Spelling In A Worksheet ● Making And Saving Changes
● Printing a Worksheet ● Safely Closing a Workbook
Working With Workbooks
● Opening An Existing Workbook ● Moving About A Worksheet ● Moving About A Workbook
● Going To A Specific Location ● The Open Dialog Box
Editing In A Workbook
● Understanding Data Editing ● Overwriting Cells Contents ● Editing Longer Cell Entries ● Editing Formulas ● Editing Functions ● Clearing A Cell ● Deleting In A Worksheet ● Undoing And Redoing Operations
Formulas And Functions
● Understanding Formulas ● Creating Formulas That Add ● Creating Formulas That Subtract ● Formulas That Multiply And Divide ● Understanding Functions ● Using The SUM Function To Add ● Summing Non-Contiguous Ranges ● Calculating An Average ● Finding A Maximum Value ● Finding A Minimum Value ● More Complex Formulas ● What If Formulas
Formula Referencing
● Absolute Versus Relative Referencing ● Relative Formulas ● Problems With Relative Formulas ● Creating Absolute References ● Creating Mixed References Selecting Ranges ● Understanding Ranges ● Selecting Ranges ● Selecting Non-Contiguous Ranges ● Using Special Selection Techniques ● Selecting Larger Ranges ● Selecting Rows ● Selecting Columns ● Viewing Range Calculations ● Creating An Input Range
Copying Excel Data
● Understanding Copying In Excel ● Using Fill For Quick Copying ● Copying From Cell To Another ● Copying From One Cell To A Range ● Copying From One Range To Another ● Copying Relative Formulas ● Copying To A Non-Contiguous Range ● Copying To Another Worksheet ● Copying To Another Workbook
Font Formatting
● Understanding Font Formatting ● Working With Live Preview ● Changing Fonts ● Changing Font Size ● Growing And Shrinking Fonts ● Making Cells Bold ● Italicising Text ● Underlining Text ● Changing Font Colours● Changing Background Colors ● Using The Format Painter ● Applying Strikethrough ● Subscripting Text ● Superscripting Text
Cell Alignment
● Understanding Cell Alignment ● Aligning Right ● Aligning To The Centre ● Aligning Left ● Aligning Top ● Aligning Bottom ● Aligning To The Middle ● Rotating Text ● Indenting Cells ● Wrapping And Merging Text ● Merging And Centering ● Merging Cells ● Unmerging Cells
Row And Column Formatting
● Approximating Column Widths ● Setting Precise Column Widths ● Setting The Default Column Width ● Approximating Row Height ● Setting Precise Row Heights ● Hiding Rows And Columns ● Unhiding Rows And Columns
Number Formatting
● Understanding Number Formatting ● Applying General Formatting ● Formatting As Currency ● Formatting Percentages ● Formatting As Fractions ● Formatting As Dates ● Using The Thousands Separator ● Increasing And Decreasing Decimals Printing ● Understanding Printing ● Previewing Before You Print ● Performing A Quick Print ● Selecting A Printer ● Printing A Range ● Printing An Entire Workbook ● Specifying The Number Of Copies ● The Print Dialog Box
Creating Charts
● Understanding The Charting Process ● Choosing The Chart Type ● Creating A New Chart ● Working With An Embedded Chart ● Resizing A Chart ● Dragging A Chart ● Printing An Embedded Chart ● Creating A Chart Sheet ● Changing The Chart Type ● Changing The Chart Layout ● Changing The Chart Style ● Printing A Chart Sheet ● Embedding A Chart Into A Worksheet ● Deleting A Chart
Filling Data
● Filling A Series ● Filling A Growth Series ● Filling A Series Backwards ● Filling Using Options ● Creating A Custom Fill List ● Modifying A Custom Fill List ● Deleting A Custom Fill List
Moving Data
● Understanding Moving In Excel ● Moving Cells And Ranges ● Moving Data To Other ● Worksheets ● Moving Data To Other Workbooks
Formula Techniques
● Scoping A Formula ● Developing A Nested Function ● Creating Nested Functions ● Editing Nested Functions ● Copying Nested Functions ● Using Concatenation ● Switching To Manual ● Recalculation ● Forcing A Recalculation ● Pasting Values From Formulas ● Pasting Formulas As Pictures
Logical Functions
● Understanding Logical Functions ● Using IF To Display Text ● Using IF To Calculate Values ● Nesting IF Functions ● Using IF ERROR ● Using TRUE And FALSE ● Using AND ● Using OR ● Using NOT
Number Formatting Techniques
● Using Alternate Currencies ● Formatting Dates ● Formatting Time ● Creating Custom Formats
Conditional Formatting
● Understanding Conditional ● Formatting ● Formatting Cells Containing ● Values ● Clearing Conditional Formatting ● More Cell Formatting Options ● Top Ten Items ● More Top And Bottom ● Formatting Options ● Working With Data Bars ● Working With Colour Scales ● Working With Icon Sets
Applying Borders
● Understanding Borders ● Applying A Border To A Cell ● Applying A Border To A Range ● Applying A Bottom Border ● Applying Top And Bottom ● Borders ● Removing Borders ● The More Borders Option ● Using The More Borders Option
Working With A Worksheet
● Understanding Worksheets ● Changing Worksheet Views ● Worksheet Zooming ● Viewing The Formula Bar ● Viewing The Gridlines ● Viewing The Ruler ● Inserting Cells ● Deleting Cells ● Inserting Columns ● Inserting Rows ● Deleting Rows And Columns ● Switching Between Worksheets
Worksheet Techniques
● Inserting And Deleting ● Worksheets ● Copying A Worksheet ● Renaming A Worksheet ● Moving A Worksheet ● Hiding A Worksheet ● Unhiding A Worksheet ● Copying A Worksheet To Another ● Workbook ● Moving A Worksheet To Another ● Workbook ● Changing Worksheet Tab Colours ● Grouping Worksheets ● Hiding Rows And Columns ● Unhiding Rows And Columns ● Freezing Rows And Columns ● Splitting Windows
Finding And Replacing
● Understanding Find And Replace ● Operations ● Finding Text ● Finding Cell References In ● Formulas ● Replacing Values ● Using Replace To Change ● Formulas ● Replacing Within A Range ● Finding Formats ● Finding Constants Using Go To ● Special ● Finding Formulas Using Go To ● Special ● Finding The Current Region ● Finding The Last Cell
Page Setup
● Understanding Page Layout ● Using Built In Margins ● Setting Custom Margins ● Changing Margins By Dragging ●Centring On A Page ● Changing Orientation ● Specifying The Paper Size ● Setting The Print Area ● Clearing The Print Area ● Inserting Page Breaks ● Using Page Break Preview ● Removing Page Breaks ● Setting A Background ● Clearing The Background ● Settings Rows As Repeating Print ● Titles ● Clearing Print Titles ● Printing Gridlines ● Printing Headings ● Scaling To A Percentage ● Fit To A Specific Number Of ● Pages <● Strategies For Printing Larger ● Worksheets
Sorting Data
● Understanding Sorting ● Performing An Alphabetical Sort ● Performing A Numerical Sort ● Sorting On More Than One ● Column ● Sorting Numbered Lists ● Sorting By Rows
Filtering Data
● Understanding Filtering ● Applying And Using A Filter● Clearing A Filter● Creating Compound Filters ● Multiple Value Filters ● Creating Custom Filters ● Using Wildcards
Charting Techniques
● Understanding Chart Layout ● Elements ● Adding A Chart Title ● Adding Axes Titles ● Positioning The Legend ● Showing Data Labels ● Showing A Data Table ● Modifying The Axes ● Showing Gridlines ● Formatting The Plot Area ● Adding A Trendline ● Adding Error Bars ● Adding A Text Box To A Chart ● Drawing Shapes In A Chart
Chart Text Formatting
● Understanding Chart Text ● Formatting ● Using Font Formatting And ● Alignment ● Using WordArt Styles ● Changing Text Fill ● Changing Text Outline ● Changing Text Effects
Lookup Functions
● Understanding Data Lookup Functions ● Using CHOOSE ● Using VLOOKUP ● Using VLOOKUP For Exact Matches ● Using HLOOKUP ● Using INDEX ● Using MATCH ● Understanding Reference Functions ● Using ROW And ROWS ● Using COLUMN And COLUMNS ● Using ADDRESS ● Using INDIRECT ● Using OFFSET
Setting Excel Options
● Understanding Excel Options ●Personalising Excel ● Setting The Default Font ● Setting Formula Options ● Understanding Save Options ● Setting Save Options ● Setting The Default File Location ● Setting Advanced Options
Chart Object Formatting
● Understanding Chart Object Formatting ● Selecting Chart Elements ● Using Shape Styles To Format Objects ● Changing Column Colour ● Changing Pie Slice Colour ● Changing Bar Colours ● Changing Chart Line Colours ● Using Shape Effects ● Filling The Chart Area And The Plot Area ● Filling The Background ● The Format Dialog Box ● Using The Format Dialog Box ● Using Themes
Labels And Names
● Understanding Labels And Names ● Creating Names Using Text Labels ● Using Names In New Formulas ● Applying Names To Existing Formulas ● Creating Names Using The Names Box ● Using Names To Select Ranges ● Pasting Names Into Formulas ● Creating Names For Constants ● Creating Names From A Selection ● Scoping Names To The Worksheet ● Using The Name Manager ● Documenting Range Names
Protecting Data
● Understanding Data Protection ● Providing Total Access To Cells ● Protecting A Worksheet ● Working With A Protected Worksheet ● Disabling Worksheet Protection ● Providing Restricted Access To Cells ● Password Protecting A Workbook ● Opening A Password Protected Workbook ● Removing A Password From A Workbook
SummarisingAndSubtotalling
● Creating Subtotals ● Using ASubtotalled Worksheet ● Creating Nested Subtotals ● Using Subtotals With AutoFilter ● Installing and Using The Conditional Sum Wizard ● Creating Relative Names For Subtotals ● Using Relative Names For Subtotals
Data Linking
● Understanding Data Linking ● Linking Between Worksheets ● Linking Between Workbooks ● Updating Links Between Workbooks
Data Consolidation
● Understanding Data Consolidation ● Consolidating With Identical Layouts ● Creating An Outlined Consolidation ● Consolidating With Different Layouts
PivotTables
● Understanding Pivot Tables ● Creating A PivotTable Shell ● Dropping Fields Into A PivotTable ● Filtering A PivotTable ● Clearing A Report Filter ● Switching PivotTable Labels ● Formatting A PivotTable
PivotTable Techniques
● Using Compound Fields ● Counting In A PivotTable ● Formatting PivotTable Values ● Working With PivotTable Grand Totals ● Working With PivotTable SubTotals ● Finding The Percentage Of Total ● Finding The Difference From ● Grouping In PivotTables ● Creating Running Totals ● Creating Calculated Fields ● Providing Custom Names ● Creating Calculated Items
PivotCharts
● Creating A PivotChart Shell ● Dragging Fields For The PivotChart ● Changing The PivotChart Type ● Using The PivotChart Filter Pane ● Moving PivotCharts To Chart Sheets
Goal Seeking
● Goal Seek Components ● Using Goal Seek
Grouping And Outlining
● Understanding Grouping And Outlining ● Creating An Automatic Outline ● Working With An Outline ● Creating A Manual Group ● Grouping By Columns
Solver
● Understanding How Solver Works ● Setting Solver Parameters ● Adding Solver Constraints ● Performing The Solver Operation ● Running Solver Reports ● Refining Solver Answers
Recorded Macros
● Understanding Excel Macros ● Setting Macro Security ● Saving A Document As Macro Enabled ● Recording A Simple Macro ● Running A Recorded Macro ● Relative Cell References ● Running A Macro With Relative References ● Viewing A Macro ● Editing A Macro ● Assigning A Macro To The Toolbar ● Running A Macro From The Toolbar ● Assigning A Keyboard Shortcut To A Macro ● Deleting A Macro ● Copying A Macro ● Tips For Developing Macros
Recorder Workshop
● Preparing Data For An Application ● Recording A Summation Macro ● Recording Consolidations ● Recording Divisional Macros ● Testing Macros ● Creating Objects To Run Macros ● Assigning A Macro To An Object
![](Templates/"../images/bodyedge_tr.gif"width="2"){kind=small}
![](Templates/"../images/bodyedge_tl.gif"width="2"){kind=small}
