Microsoft Office Course - MS Excel

This course is for the those who wants to learn the concepts of Microsoft Office Excel computer application. Students will begin to learn the functions and capabilities of Microsoft Excel with emphasis on the integration of Microsoft Office software to solve business problems.

At the end of the course the participant will be able to:

• Explore the new and enhanced Microsoft Office Excel environment
• Organize data in Excel worksheets using enhanced tables and table formats
• Analyze Excel data by applying enhanced conditional formatting
• Generate specific information using the sort and filter options
• Present Excel data using enhanced charts and illustrations
• Work with the enhanced options of PivotTables and PivotCharts for conducting selective analysis.
• Maintain an Excel Database
• Sorting and Filtering Data
• Using Workbooks for Larger Project

• Why are selection techniques important?
• Selecting a cell
• Selecting a range of connecting cells
• Selecting a range of non-connecting cells
• Selecting the entire worksheet
• Selecting a row
• Selecting a range of connecting rows
• Selecting a range of non-connected rows
• Selecting a column
• Selecting a range of connecting column
• Selecting a range of non-connecting columns
• Inserting rows into a worksheet
• Inserting columns into a worksheet
• Deleting rows within a worksheet
• Deleting columns within a worksheet
• Modifying column widths
• Modifying column widths using ‘drag and drop’
• Automatically resizing the column width to fit contents
• Modifying row heights
• Copying the cell or range contents
• Deleting cell contents
• Moving the contents of a cell or range
• Editing cell content
• Undo and Redo
• AutoFill
• Sorting a cell range
• Searching and replacing data

• Switching between worksheets
• Renaming a worksheet
• Inserting a new worksheet
• Deleting a worksheet
• Copying a worksheet within a workbook
• Moving a worksheet within a workbook
• Copying or moving worksheets between workbooks

• Font type
• Font size
• Bold, italic, underline formatting
• Cell border formatting
• Formatting the background colour
• Formatting the font colour
• Aligning contents in a cell range
• Centering a title over a cell range
• Cell orientation
• Text wrapping
• Format painter
• Number formatting
• Decimal point display
• Comma formatting
• Currency symbol
• Date styles
• Percentages
• Freezing row and column titles

• Creating formulas
• Easy way to create formulas
• Copying formulas
• Operators
• Formula error messages
• Relative cell referencing within in formulas
• Absolute cell referencing within formulas
• What is a function?
• Common functions
• Sum function
• Average function
• Max function
• Min function
• Count function
• What are ‘IF functions’?
• Using the IF function

• Inserting a column chart
• Inserting a line chart
• Inserting a bar chart
• Inserting a pie chart
• Resizing a chart
• Deleting a chart
• Chart title or labels
• Chart background colour
• Changing a column, bar, line or pie slice colours
• Changing the chart type
• Modifying Charts using the Layout tab
• Copying and moving charts within a worksheet
• Copying and moving charts between worksheets
• Copying and moving charts between workbooks

• Modifying basic options
• Minimizing the Ribbon
• AutoCorrect options

• Worksheet margins
• Worksheet orientation
• Worksheet page size
• Headers and Footers
• Header and footer fields
• Scaling your worksheet to fit a page(s)
• Visually check your calculations
• Gridline display when printing
• Printing titles on every page when printing
• Printing the Excel row and column headings
• Spell checking
• Previewing a worksheet
• Comparing Workbooks side by side
• Zooming the view Printing options

• Paste Special
• Transposing data
• Importing text and delimiting by space, comma or tab
• What does naming a cell range mean?
• Rules for naming cells and ranges
• Naming cell range(s) in a worksheet
• Named ranges within formulas
• Navigating through workbooks using named ranges
• Creating named ranges automatically based on cell values
• Deleting named cells/ranges
• Creating subtotals
• Removing subtotals

• Using templates
• Creating templates
• Opening and editing templates

• Formatting tables
• Formatting tables using table styles
• Formatting cell ranges using conditional formatting
• Creating custom number formats
• Freezing the top row
• Freezing the first column
• Freezing the top row and the first column at the same time
• Hiding and un-hiding rows
• Hiding and un-hiding columns
• Hiding and un-hiding worksheets

• Sorting internal Excel databases
• Custom sort options
• Using AutoFilter to query data
• Multiple queries
• Removing filters
• Top 10 AutoFilter
• Filtering unique records
• Advanced Filter

• Linking individual cells within a worksheet
• Linking charts to data within a worksheet
• Linking a cell range on one worksheet to another worksheet (within the
• same workbook)
• Linking data on one worksheet to a chart in another worksheet (within
• the same workbook)
• Linking data from one workbook to another
• Linking a chart from one workbook to another
• Copying data from Excel into a Word document
• Linking data from Excel into a Word document
• Copying a chart from Excel into a Word document
• Linking a chart from Excel into a Word document
• Consolidating data over several worksheets or worksheet pages

• Changing the angle of pie chart slices
• Formatting the chart axis fonts
• Formatting the chart axis scales
• Formatting the chart axis text orientation
• Creating and positioning a chart title
• Re-positioning a chart legend
• Re-positioning chart data labels
• Exploding the segments within a pie chart
• Deleting a data series within a chart
• Adding a data series to a chart
• Modifying the chart type for a defined data series
• Widening the gap between columns / bars within a 2-D chart
• Inserting an image into a 2D chart (as a background)
• Inserting an image into a 2D chart (to format a column or bar of data)

• Specifying a password for opening a workbook
• Using the “read-only recommended” option
• Removing a password from an Excel workbook
• Protecting a worksheet or worksheet elements
• Removing workbook protection
• Allowing selective editing of a protected worksheet

• Excel 2010 Functions
• Getting help about using a particular function
• TODAY, DAY, MONTH, YEAR, SUMIF, ROUND, COUNT, COUNTA,COUNTIF, PROPER, UPPER, LOWER, CONCATENATE, FV, NPV,PMT, PV, RATE, HLOOKUP, VLOOKUP, IF, AND, OR, ISERROR,DSUM, DMIN, DMAX, DCOUNT
• Nested functions

• Using a one input Data Table command
• Using a two input data table command

• Creating a PivotTable
• Dropping data into the Pivot Table
• Modifying data and refreshing the Pivot Table
• Grouping data within a Pivot table

• Scenario Manager – an example
• Showing a scenario
• Viewing an alternative scenario
• Create a scenario summary

• Tracing precedent cells
• Tracing the dependants of a cell
• Displaying all formulas within a worksheet
• Adding comments
• Displaying comments
• Removing comments
• Editing comments

• Displaying the Developer tab
• Recording and running macros
• Customizing the Quick Access Toolbar
• Changing the Quick Toolbar Macro icon
• Removing a macro icon from the quick access toolbar