Instructor-led training - 1 Day Advanced Excel Courses London

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Excel Power Users Training CoursesExcel Power Users Training Courses

Face to face / Virtual public schedule & onsite training. Restaurant lunch included at STL venues.

Designed for Excel 365

Advanced Excel Training in London and UK Wide.

Free manuals

We are providing a range of our course manuals free of charge.

Why not share this resource with your friends and colleagues?

Training manual sample

Below are some extracts from our Excel training manuals.

Unit 3 - The Analysis Toolpak 

In this unit, you will learn how to: 

  • Work with HISTOGRAM 

  • Calculate moving average 

  • Understand the relationship between arrays using the CORRELATION tool 

 

The Analysis Toolpak add-in is part of Excel. Add-ins are little packages of tools that more or less seamlessly integrate into the user interface of Excel. However, they require you to install them first. The following sections discuss the installation of, and some of the tools included with, the Analysis Toolpak. 

 

Histograms 

A histogram is a chart (usually a simple column chart) that takes a collection of measurements and plots the number of measurements (called the frequency) that fall within each of several intervals (called bins). 

This is a graphical way of displaying the frequency of variables. An example may be to have student exam scores, then a range of score values, and then the number of frequency of the variables in that range. 

Note: Class or Bin is the range of the variables 

 

Generating Random Numbers 

The built-in random cell number function, RAND, generates a uniform distribution of random real numbers from 0 to 1. In other words, all values from 0 to 1 share the same probability of being returned by a set of formulas based on the RAND function. Because the sample is relatively small, the distribution is by no means perfectly uniform.  

Nevertheless, repeated tests demonstrate that the RAND function doesn't favour any position within its spectrum of distribution.  

Fixing random numbers 

The RAND function is one of Excel's volatile functions-that is, it recalculates every time the worksheet recalculates, which happens every time you make an entry in a cell. If you want to generate a set of random numbers and then 'freeze' them, select all the RAND formulas in your worksheet, and press Ctrl+C to copy them. Then click the Paste button on the Home tab on the Ribbon, and click Paste Values to replace the volatile formulas with fixed values. Or, instead of using the RAND function, use the Random Number Generation tool (described next), which produces constants instead of formulas. 

 The Random Number Generation tool creates sets of random numbers that are not uniformly distributed.  

 

Calculating Moving Averages 

A moving average is a forecasting technique that simplifies trend analysis by smoothing fluctuations that occur in measurements taken over time.  

These fluctuations can be caused by random noise that is often a by-product of the measurement technique. For example, measurements of the height of a growing child will vary with the accuracy of the ruler and whether the child is standing straight or slouching.  

You can take a series of measurements, however, and smooth them over time, resulting in a curve that reflects the child's actual growth rate.  

Fluctuations in measurements can result from other temporary conditions that introduce bias. Monthly sales, for example, might vary with the number of working days in the month or the absence of a star salesperson who takes a holiday. 

Correlations 

Overview 

How are monthly stock returns for Microsoft, GE, Intel, GM, and Cisco related? 

Trend curves are a great help in understanding how two variables are related. Often, however, we need to understand how more than two variables are related.  

Looking at the correlation between any pair of variables can provide insights into how multiple variables move up and down in value together. 

The correlation (usually denoted by r) between two variables (call them x and y) is a unit-free measure of the strength of the linear relationship between x and y 

The correlation between any two variables is always between –1 and +1. Although the exact formula used to compute the correlation between two variables isn’tvery important, being able to interpret the correlation between the variables is. 

A correlation near +1 means that xand yhave a strong positive linear relationship. That is, when x is larger than average, y tends to be larger than average, and when x is smaller than average, y also tends to be smaller than average.  

When a straight line is applied to the data, there will be a straight line with a positive slope that does a good job of fitting the points. As an example, for the data shown below (x=units produced and y=monthly productioncost), xand yhave a correlation of +0.90. 

 

Summary of Correlation 

 

Determining the Correlation Between Data 

Correlation is a measure of the relationship between two or more sets of data. For example, if you have monthly figures for advertising expenses and sales, you might wonder whether they’re related. That is, do higher advertising expenses lead to more sales? 

To determine this, you need to calculate the correlation coefficient. The coefficient is a number between –1 and 1 that has the following properties: 

 

Correlation Coefficient Interpretation 

  • 1 The two sets of data are perfectly and positively correlated.  
    For example, a 10% increase in advertising produces a 10% increase in sales. 

  • Between 0 and 1The two sets of data are positively correlated (an increase in advertising leads to an increase in sales). 
    The higher the number, the higher the correlation is between the data. 

  • 0 There is no correlation between the data. 

  • Between 0 and –1The two sets of data are negatively correlated (an increase in advertising leads to a decrease in sales). The lower the number is, the more negatively correlated the data is. 

  • –1The data sets have a perfect negative correlation. 
    For example, a 10% increase in advertising leads to a 10% decrease in sales (and, presumably, a new advertising department). 

 

Unit 4 - Form Field Control Introduction 

In this unit, you will learn how to: 

  • Add form controls to a worksheet 

  • Create Group Box and Option Buttons 

  • Understand the form controls 

 

You can create forms within Excel without the need to learn VBA (Visual Basic for Applications) programming. Form Controls are created using standard Excel tools from the Developer ribbon. 

Form Controls can be used to enable quick and easy data inputYou can design them to print out or use onlineForms Controls consist of such as objects as option buttons, tick boxes, and drop-down lists.   

Controls can be used to validatedata and limit user inputThe value selected in the control is returned to the spreadsheet allowing you to then use this information to display text, values, or run calculations on them 

 

Adding Controls 

To add a control to your worksheet, simply click the relevant icon on the formsdrop-down, and by clicking and dragging, draw the control onto the sheet. 

Once placed on the screen, you will see that it has handles at each edge and corner, allowing you to resize it as required. 

Understanding Controls and How They Work 

All controls must have a “cell linkThis can be any cell on the worksheet (that would generally be hidden away) that is used to store the value returned by the controlFor example, if cell B2 is used as the cell link for a tick box, the value TRUE or FALSE would be returned to cell B2 depending on whether or not the box is ticked. 

Once there is a value in B2 (in this example, either True or False), you can then use that result for a calculation, function, etc. 

e.g. 

=IF(B2=True,”Yes I will attend”,”No I won’t be attending”) 

Alternatively, if you are using a Spinner or Scrollbar to change values from 10 to 100, and using C3 as the cell link, you could use the value returned in a calculation. 

e.g. 

=C3*60 

=C3*10 

=C3*100 

 

All controls have their own properties, which will be covered in this guideAll controls have the ‘Cell Link’ property, which needs to be set if the result is to be used further within the spreadsheet. 

 

 

Group Box and Option Buttons 

 

From the Insert > Form Controls, click . 

Draw a Group Box onto the spreadsheetHighlight the heading and rename it to “April Fixtures”. 

 

Simply highlight the heading and overtypeAlternatively, just delete to have no heading and just a box. 

 

 

From the Insert > Form Controls, click . 

 

Draw the Option Button inside the Group BoxHighlight the text and type “Tottenham Hotspurs”. 

 

 

 

Add 5 more Option Buttons, and rename them “Arsenal”, “Man Utd”, “Liverpool”, “Chelsea”, and “Aston Villa”. 

Resize the Group Box if necessary (by clicking one of the borders and using the handles), to fit in the other Option Buttons. 

 

 

 

Click on the CONTROL tab.  What you can see here is the properties of the Option Button control. 

 

ShapeClick in the CELL LINK box, then click an empty cell behind in the spreadsheetIn the example below, cell F1 has been selected. 

 

 

 

Then click OK. 

 

 

 

List Box 

Single Selection 

A list box is used for displaying a list of itemsThis list is controlled by values or text within a range of cells on your spreadsheet. Dependent on the control’s properties, the user can select one or multiple choices from the list. 

Combo Box 

The Combo Box is basically adrop-down listThe input range, similarly to the List Box, is taken from a range from within the spreadsheet. 

The combo box is a text box with adrop-down arrow, listing items, allowing the user to select one of theseThe index number of the item is returned to the cell link and the value backinto the text box. 

Scrollbar 

The scrollbar can be used either horizontally or vertically on the spreadsheetIt is used to increase or decrease values, and the page change option (clicking anywhere in the centre of the scrollbar instead of the arrows at each end) allows faster and larger value jumps. 

Spinner 

The Spinner works almost exactly the same way as the Scrollbar, except that you don’t have the Page Change propertyYou simply use the up and down arrows to increase and decrease values. 

 

 

Thanks. Your download will begin shortly.

Please help us

Share or create a link to this manual today!

Just follow these simple instructions...


Server loaded in 0.61 secs.

✓ w3speedster