Microsoft Excel Intermediate

DURATION

1 Day

LOCATION

Exeter – Devon, Central London, Bristol or onsite at your premises.

OVERVIEW AND OBJECTIVES

This course is designed to help you to develop your skills in using the spreadsheet tool Microsoft Excel. You will learn to use more advanced functions and create more complex formulas. You will learn to manage and link together multiple worksheets.

You will also learn to apply more advanced formatting to charts and use basic reviewing tools, such as the spelling checker and comments.

TARGET AUDIENCE

Those wanting to develop their skills in using Excel to design spreadsheets to solve basic accounting and data analysis tasks.

COURSE PREREQUISITES

Ideally, you will have completed the course “Microsoft Excel 2003 Introduction”. You should be able to use Microsoft Excel to create, edit, format, and print a spreadsheet incorporating simple formulas and charts.

SUBJECT AREAS (PLEASE NOTE, SUBJECT AREAS COVERED WILL DEPEND ON DELEGATES ABILITY AND TIME AVAILABLE)

MANIPULATING DATA, & NAMED RANGES

  • 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

FORMATTING & DISPLAY TECHNIQUES

  • Formatting tables
  • Formatting cell ranges using conditional formatting
  • Creating custom number formats

FUNCTIONS

  • COUNTA
  • COUNTIF
  • SUMIF
  • COUNTIFS
  • SUMIFS
  • IF
  • TODAY
  • AND
  • OR
  • Nestied Functions

SORTING AND QUERYING DATA

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

PIVOT TABLES

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

LINKING & CONSOLIDATING DATA

  • 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

CHARTS FORMATTING TECHNIQUES

  • 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
  • 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
  • Adding and Deleting a data series within 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)
  • Chart background colour

PROTECTION AND SECURITY

  • 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

TO BOOK

For more information or to book onto our Microsoft Excel Intermediate training course please contact us on 01392 824 022.

If there is a Microsoft course that is not listed on our site, that you are interested in attending then please let us know and we can arrange one to one training onsite with our Trainer.

Still not convinced?
We can help you!

Fill out the form below and one of our IT specialists will contact you.



Testimonials

EmailFacebookTwitterSupport PortalAME Support Tool