Excel 2007  Excel 2010  Excel 2013 Martin Green's Excel Training
|   Home   |   Excel   |   Access   |   Word   |   Outlook   |   VBA   |   eBooks   |   Handouts   |   Site Index   |   RSS   |
Microsoft Excel Training

Excel Training Home

Download Excel Short Course Details (PDF)

Download Excel VBA Short Course Details (PDF)

NEW Short Courses in Excel VBA

Free Excel Training Resources

Free Excel Training Handouts

Free Step-by-Step Exercises

 
Half-day courses in Excel for up to 8 people for just £275.
  
Get Excel and Access Training for up to 8 people for just £425.
  
Get 1-to-1 Access and Excel training for just £275.

Short Courses in Microsoft Excel

Published: 10 April 2014
Author: Martin Green

Aimed primarily at business users, these half-day courses each focus on a specific area of expertise in Microsoft Excel. They apply to all current version of Excel (2007, 2010 and 2013). If you are still using Excel 2003 (or earlier!) I can still help you although, owing to differences between versions, the content might vary a little.

The following short courses are currently available. More courses will be added to the list. If you can't see what you need, contact me and I will create a tailored course to suit your exact requirements. Click on the name of the course to see details of the course content.

Click here to download full details of these Short Courses in Microsoft Excel in PDF format.

If you are interested in learning Excel Macro Programming with VBA click here to see my Short Courses in Excel VBA.

Calculating for New Users – Calculating 101

Everything you need to know to get started with calculating in Excel. Every Excel user should be familiar with all these topics if they are to work confidently with calculations. This course teaches all the basic rules of calculating in Excel.

  • The Rules of Calculating: It is important to understand how Excel thinks so that you get the results you expect, and discover exactly which calculations you can perform in Excel.
  • The AutoSum Tool: It can do a lot more than just add-up. Find out how it works and how it can save you time and ensure accuracy in your workbooks.
  • Working with Percentages: What is the difference between typing a percent sign and using the percent format? How to calculate percentages manually.
  • Working with Dates: How to add and subtract days, find the difference between dates and discover some useful date functions.
  • Using the Fill Handle: Save time and guarantee accuracy by letting Excel do the work of copying formulas.
  • Relative and Absolute Cell References: You have probably seen dollar signs in formulas but what do they mean and why are they so important?
  • Named Ranges: How to name a range of cells and use names in formulas. How names can simplify formula writing and help prevent errors.
  • All About Cell References: How to refer to cells on different worksheets and in different workbooks.
  • Introduction to Functions: How to use the Paste Function tool and get access to the hundreds of ready-made calculations that Excel can perform.

To find out how book this course click here.

^ top

Calculating for Power Users - Special Functions

Within Excel’s vast collection of built-in functions are many that can help users with their everyday tasks, if only they knew about them and how to use them. This course introduces many of the special functions most valuable to the business user.

  • Date Functions: Working with dates often causes problems. This collection of functions, including DAY, MONTH, YEAR, WEEKDAY, DATE, DATEVALUE, and DATEDIF, help you extract important information from dates so that you can more easily analyse your data.
  • Text Functions: Extracting information from text values can be difficult and time-consuming. Functions like LEFT, RIGHT, MID, FIND, LEN, REPLACE and TRIM make the job of working with text simpler and more reliable.
  • Conditional Functions: Sometimes we need our calculations to behave intelligently and make decisions. The functions IF, AND and OR let the user apply conditions to their calculations.
  • Array Formulas: these let you perform calculations you can’t do with regular Excel Functions. They also ensure consistency and prevent accidental changes. We also take a look at the SUMPRODUCT function.

To find out how book this course click here.

^ top

Data Analysis – Organising Data

Often the data you need to analyse is stored in different places. Your first task is to bring together your various datasets so that you can work with them at one time. This course teaches you what tools are present in Microsoft Excel to help you achieve this task quickly and efficiently:

  • The Consolidate Tool: This tool helps you bring together similar sets of data from different worksheets or workbooks, at the same time summarising the values.
  • External Data Sources: Excel makes it easy to gather data from a variety of different sources such as a text file, a database such as Microsoft Access, a server database such as SQL Server and even a web page.
  • Lookup Functions: The range of lookup functions in Excel: VLOOKUP, HLOOKUP, INDEX, MATCH and OFFSET help you organise your data, saving time and increasing accuracy, by setting up associations between datasets in different locations.

To find out how book this course click here.

^ top

Data Analysis – Tools and Functions

This course introduces users to the various tools and functions included in Microsoft Excel for the analysis of Business Data. The following topics are covered:

  • Conditional Functions: Functions such as SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, and AVERAGEIFS used to extract information from data using predefined conditions. The IF function allows you to build on your data using simple or complex decisions.
  • Summarizing Functions: The MAX and MIN functions are used to determine the range of values in the data and in calculating ratios; SUBTOTAL, STDEV and VAR are used for statistical analysis of the variation in a data set; CORREL compares two sets of data.
  • Custom Sorting: Organise data using multiple levels of sorting; create Custom Lists and use them as custom sorting criteria.
  • The Subtotal Tool: Use this time-saving tool to organise and summarise large sets of data.

To find out how book this course click here.

^ top

Data Analysis – PivotTables and PivotCharts

The PivotTable is the single most powerful and flexible tool you have for analysing your business data. Once learned it is easy to use can handle almost any kind of data in volumes from a few dozen rows to hundreds of thousands. This course deals exclusively with the theory and practice of analysing data with PivotTables and PivotCharts, exploring:

  • PivotTables: These dynamic tables summarise single sets of data or analyse multiple data sets by plotting them against each other in a variety of configurations, whilst at the same time performing a range of different types of calculation.
  • PivotCharts: These use the analytical power of PivotTables to create graphic representations of data making use of the full range of Excel’s powerful charting tools.
  • Slicers: Create an interactive dashboard by adding these flexible filtering tools.

To find out how book this course click here.

^ top

What-If Analysis and Basic Forecasting

An important aspect of decision-making in business is the ability to predict future performance on the basis of known results, and to calculate what the consequences would be if circumstances change. Excel provides several useful tools to help with Forecasting and What-if Analysis.

  • Scenario Manager: Assign different values to variables within your calculations and use this tool to save them as scenarios to recall or create a scenario report.
  • Goal Seek: This tool calculates the required value of an input variable to achieve a result you specify in your calculation.
  • Data Tables: Save time and guarantee accuracy by using this tool to create a table of results from the same original calculation with a selection of values for one or two input variables.
  • Plot Trends: Use the Series command to create Linear or Exponential trends from existing data on a worksheet or add Trend Lines to charts to display forecasts graphically.

To find out how book this course click here.

^ top

Building Better Charts

Excel’s charting tools are powerful and easy to use but most users don’t make use of the wealth of design and formatting options that Excel offers. This course helps users create great looking charts that show their data to the best advantage. Topics include:

  • Selecting the Data: It sounds easy but deciding exactly which and how much data should be included has a strong influence in the effectiveness of a chart.
  • Choosing the Right Chart Type: It isn’t always obvious which kind of chart to use. A few simple rules will help you decide whether to use a Column, Bar, Line or Pie or maybe one of the many other types of chart on offer.
  • Dealing with Multiple Series: When to use a Stacked Columns or add a second axis to the chart.
  • Plotting Large Amounts of Data: How Scatter Charts and Bubble Charts can help make sense out of difficult data.
  • Ensuring Consistency of Style: How to save time by saving your chart layouts as templates and tips for quickly duplicating a chart’s design with a simple Copy and Paste.

To find out how book this course click here.

^ top

Auditing Worksheets and Workbook Security

In a business environment users often share workbooks or create workbooks for others to use. It is easy to assume that a workbook contains no mistakes. Excel offers a number of tools to help you check the accuracy of a spreadsheet and apply protection to prevent unwanted changes.

  • Data Validation: Apply rules to cells to control the data that is entered. Applied to existing data this tool can display which cells do not conform to the rules you apply.
  • Conditional Formatting: Use colours and icons to give a visual indication of the values in cells.
  • Formula Auditing: Visually trace the cells that contribute to a formula, or to see which calculations depend on the value of a specific cell. How to use the Evaluate Formula tool.
  • Showing and Hiding Formulas: How to quickly view all the formulas on a worksheet, and how to hide calculations from view when required.
  • Restricting User Access: Lock the cells that you want to keep safe from accidental or intentional changes. Specifying what a user can do with a workbook and protect your rules with a password.
  • Managing the Metadata: How to use the Document Panel to view and edit a workbook’s hidden properties.

To find out how book this course click here.

^ top

 

  ©2000-2015 Martin Green martin@fontstuff.com All rights reserved