Microsoft Excel - Group-and-Outline

Group and Outline 

Document outlines are a graphical method of hiding and showing the columns and rows in a worksheet, with a mouse click you can collapse or expand the view of sections of the worksheet to show either summary or detail data.

You can outline columns or rows or both, parts of the worksheet or the entire worksheet.
The outline symbols appear around the edge of the worksheet, you can show or hide the outline symbols.

An outlined worksheet only prints the visible cells, thus document summaries are easily achieved.
 An outline is applied automatically to a list when you choose, Subtotals.

Any worksheet can be outlined either automatically or manually.

Automatic Outlining: the automatic outline will follow the structure and hierarchy of the formulas that have been entered into the worksheet cells.

Manual outlines: by selecting Manual Outlining you are able to select the rows or columns that are required to be grouped (hidden).

Creating an Automatic Outline. 

1. Highlight the data that you wish to group.



2. From the Ribbon select the Data tab → Group → Auto Outline.

Creating an Manual Outline. 

1. Highlight the columns that you require to group.




2. From the Ribbon select the Data tab → Group → Group option





The display of the worksheet data (columns B – E) have  been hidden.

Select the Outline controls to re-display hidden columns.

Note: It is possible to manually group on both Rows & Columns.


Removing an Outline.

1. Highlight the column (s) that you require to Ungroup



2. From the Ribbon select the Data tab → Ungroup → Ungroup option.





Note: When removing multiple Outlines (example from Auto Outline), highlight the data and select from the Ribbon → Data tab Ungroup → Clear Outline.

Microsoft Excel - Goal Seek

Goal Seek 

Goal seeking is a method of finding a specific value for one cell by adjusting the value of another cell. Excel varies the value in a changing cell until a formula that is dependant on that cell returns a certain target result, this is used when you know the desired result of a formula but not the input value that the formula needs to determine the result.

In short, back calculation.

In  the illustration, we are doing the costing for batches of Gin & Tonic. We know that we can sell them for £8 but we cant to make a profit of £1 on each batch.


What should the measure be in order to achieve this profit?

And the answer is um, err… Worksheet calculations can be so complicated that it not possible to even guess.



Make sure that the cells that you are using are dependant on each other and use Formula Auditing with the tracer arrows if you are not sure.

Then choose the Data Tab → What if Analysis → Goal Seek.

The Set cell is the cell containing the formula where you want to force a certain result, To Value is the target result and by changing cell is one that is precedent to the Set cell.

Goal Seek will calculate the mathematical possibility of an outcome but can produce unrealistic results as you can not place constraints on the changing cell or involve other cells in the calculation.

For working out more complex problems use Excel Solver.

Microsoft Excel - Deleting a Macro

Deleting a Macro.

1. Click the Developer tab → Macros.



2. Select the Macro you wish to delete.



3. Click on the Delete button to delete the macro.

Note: If you try to delete a personal macro you will be prompted to unhide the macro first before you are able to delete it.

Microsoft Excel - Data Validation Restricting Cell Entries

Data Validation Restricting Cell Entries 

It is possible to restrict cells so that only certain values can be entered into them, by controlling what data is entered into a cell will make worksheets more reliable.

For example you may require a column to only accept numeric numbers for calculations, therefore you may wish an error message to be displayed if any other value is entered like text.

In the Example below we want to allow only a who number between 10 and 20 to be entered in column ‘A’.

1. Select column ‘A’

2. Click Data tab → Data Validation.



3. Setting: Setting the Validation criteria

Allow: Data type to be entered.

Data: additional setting.

These additional setting will change depending on what data type that you have selected.


4. Input Message:

Enter a Title for the Input Message box (optional).

Enter the Message to be displayed to the user.



5. Error Alert: display an error when the wrong data has been entered into the cell

Enter a Title for the Error Alert Message box (optional).

Enter the Error Message to be displayed to the user.

Data Validation Building a Drop-­‐Down List. 

A drop-down list could contain a list of departments that you choose from, this way a user must select from the list not typing in the data manually causing errors, for example typing a department two different ways ‘HR’ or ‘Human resources’.

1. Create a list of department to choose from. In this example type four different department using the range A1:A4.



2. Click Data tab → Data Validation.



3. Setting: Setting the Validation criteria.

Allow: List.

Source: Highlight the source (departments to choose from).



4. Input Message:

Let the user know what to type into the cell.

Enter a Title for the Input Message box (optional).

Enter the Message to be displayed to the user.



5. Error Alert: display an error when the wrong data has been entered into the cell

Enter a Title for the Error Alert Message box (optional).

Enter the Error Message to be displayed to the user.

Microsoft Excel - Creating a Shared Workbook

Creating a Shared Workbook.

It is possible to share a workbook with others when working in an networked environment.

By sharing Excel files users can:

Add columns and rows. 
Enter and modify data. 
Apply formatting. 
Track and review changes. 

 “[shared]” will be displayed in the Excel title bar to indicate that the file has been saved as an Excel shared file.

Shared workbooks work  well within teams that require joint responsibilities over the workbook data.


If users modify the same cell, Excel can keep track of changes (history), the changes can be reviewed at a later date and be accepted or rejected.

Sharing a workbook.

1. Open the workbook that you wish to share.

2. Select the Review tab.

3. Click on the Share Workbook button.



4. Select the Editing tab from the Shared Workbook dialog box.

Check the tick box, ”Allow changes by more than one user at the same time. This also allows workbook merging”.

5. Click Ok.

6. The following confirmation dialog box will be displayed

Click Ok to save you workbook. 

Modifying Shared Options. 

1. Open the shared workbook that you wish to modify the options.

2. Click on the Review tab → Share Workbook button → Advanced tab.

3. Select the time period (days) that you require to keep changes.

4. Select when changes should be saved, “when file is saved” or choose “automatically” and select the save time period.

5. Resolve conflicting changes – the latest change wins or Ask which change wins.

6. Select either or both of the include in personal view

7. Click Ok.

Microsoft Excel - Controlling the Worksheet Display

Controlling the Worksheet Display 

Multiple Windows 

Multiple windows allow you to:


Display as many windows a you want within the workbook.

In the example below we are looking at the ‘customers file’, but looking at the same time two different sheets.

Display Windows that contain separate workbooks

Copy data between sheets.

The Process: 

Open the required file.
From the Ribbon select the View tab → New Window icon.



To position the windows side by side select the View tab → Arrange All icon. The Arrange Windows dialog box is displayed.



Select the layout option required.

In this example we have selected Tiled.



Click OK.

The Windows are now displayed side-by-side.

Notice in the preview below the workbook names, Customer:2, Customers:1.

Customers1 is the original view, every time the New Window option is selected the additional view name will increase. (Customers:1, Customers:2, Customers:3 etc).

It is now possible to move your cursor between each of the views, selecting different areas in the workbook in each view.

Freeze Panes 

Freeze Panes is very useful when working with a Table, a Table can consist of thousands of rows and columns.

The problem is when you scroll down the table the headings will disappear from the top of the page.
Freeze Panes will allow you to lock the headings at the top of the page while scrolling down the table.

The Process: 

If the headings are in row 1 between columns A:G, highlight row 2 in the left margin.



From the View tab, select the Freeze Panes icon.



The titles will now stay displayed at the top of the page until the Unfreeze Panes command is selected.

Zoom Magnification. 

In some case you will need to change the zoom magnification of the worksheet.

This will either make the display of the data entered either look larger / smaller on screen.

The zoom controls will not effect the size of the data text when printed out on paper, it is for screen use only.

The Process: 

Select the Zoom icon from the View tab.



The Zoom Magnification dialog box is displayed.




Select the required magnification and click on the ok button.

Additional Zoom features. 

100%, this will restore the spreadsheet to the default screen size layout.

Zoom to Selection, highlight the data that is required to be viewed.



Select the feature, the area


highlighted will be zoomed to fit the page.

The Zoom tool can alos be accessed by using the Zoom scrollbar found at the botton right of the screen.

Microsoft Excel - Charts

Charts 

What is a Chart? 

A chart is a graphical representation of the worksheet data.

The chart is linked to the spreadsheet data therefore if the spreadsheet data changes the chart will update automatically.

Chart Terminology

Title – Identifies the overall data that is represented in the chart.

Handles – Indicate that you can resize, move, copy, or delete.

Gridlines - Horizontal lines, one for each value on the ‘Y’ axis, males  the data easier to read.

Legend – Identifies the various data series in the chart.



X-axis – The horizontal axis of a chart.

Y-axis – The vertical axis of a chart.

Data Marker – Represent the various data points in the chart. 

In this example the Data Marker are bars, as the chart is a column chart. 

Data Series – A range of data points in a chart, such as bars, columns or pie slices. 

Creating the Chart 

Highlight the data that you wish to plot. 

Select the Column chart from the Insert tab. 

In this example we have selected a 2-D column chart. 

The chart has now been created and I displayed under the original worksheet data. 

It is possible to move the chart around using the Handles located in each corner of the chart

Moving the Chart to a new Sheet 

When the default chart is created, the chart is placed on the same sheet as the data it is linked to. It is possible to move the chart to a new sheet. 

Process: 

From Chart Tools, select the Design tab → Move Chart icon. 

Select an existing or new sheet that you wish to place the chart. 

Locating Additional Chart features. 

The additional chart tools are found on three tabs located on the Chart Tools toolbar. 

Adding Additional Data to a Chart 

Once the chart is created it is possible to add additional data to the existing chart. The Process: 

Enter the new data to the Excel Spreadsheet. 

Select the chart you wish to update

Click on the Design tab under chart tools, select the Select Data icon.

The Select Data Source dialog box is displayed. 

Click on the Add button 

In the series name box, select the additional series name (heading). 

In the series value box, select the value to be included in the chart. 

Click on the OK button. 

Microsoft Excel - Auditing a Worksheet

Auditing a Worksheet 

Trace Precedents & Dependants. 

The formula auditing tools can be found on the Formulas tab on the Ribbon.

Select Formulas Tab from the Ribbon.



Formula auditing uses the terminology Precedents and dependents. Precedents are the cells that provide data to a specific cell and Dependents are the cells that depend on or are affected by the value of a specific cell.

Select the cells that contains the formula for which you want to find the precedent or dependant cells and click either the Trace Precedents or Trace Dependants buttons to display the tracer arrows.

One click shows the first level of precedents or dependents; keep clicking to trace through the original or terminus of the calculation.



Blue Arrows: are displayed to show all the cell relationships.

Red Arrows: are used if a cell contains an error value.

Black Arrows: pointing to a worksheet icon is displayed, if the cell reference is on another worksheet.

Double click on the black arrow to display the GoTo box that will display the linked worksheets.

The other workbook must be open to continue the trace.

Click on the Remove Arrows to remove all Auditing arrows.

Watch Window.

When working with large or complex worksheets, where the result of the formula is dependent on the results of another formula in another cell, you may want to monitor cell values. To do so, you can use the Watch Window. To add a cell to the Watch Window, click Watch Window




from the Formula tab in the Formula Auditing group. The Watch Window is displayed.

Click and drag to select the cells you require to watch and then click Add.

More than Two Possible Outcomes

More than two possible outcomes 

The IF function returns either a TRUE or a FALSE result, to be able to choose from more than two possible outcomes then you need to have more than one logical test; one test yields two outcomes, two tests yields three possible outcomes etc.

Combine (or nest) more than one IF function inside another to give the possibility of having more than two possible outcomes from a decision structure.  The conditional tests are evaluated in turn to return the possible outcomes.

IF(Conditional Test, True value, IF(Conditional Test, True value, False value)) etc.

For example, based on the value in cell A1; we wish our formula to return either Small, Medium or Large.

Anything up to 20 is Small, Medium is over 20 but less than 50, Large is 50 or over.
=IF(A1<=20, "Small", IF(A1>=50, "Large", "Medium"))

or

=IF(A1<=20, "Small", IF(AND(A1>20, A1<50), "Medium", "Large"))
The nesting limit in Excel is 64 levels.

Mouse Training Course Index

Contact Email

For Further Course Details Visit Our Homepage

Microsoft Accredited IT Training Provider

Microsoft Certified Training Materials

IT Training Courses delivered by Microsoft Accredited Facilitators

Microsoft MOS Test Centre London

Microsoft Excel IF Functions

IF Function

You can create "intelligent" formulas which can be used to apply sets of rules to any worksheet calculation. You make a test and then, based on the outcome to this test, you can make your calculation follow different routes. You will need to use the Comparative operators to make the test and then the Excel IF function to process the results of the test. A formula like A1>100 returns a logical value.

 

For example, if you had the value of 100 entered into cell A1, the following formula would show "Big" in the cell but if the value in the cell was changed to a value less than 100 then it would show "Small". The recalculation is automatic.

=IF(A1>=100, "Big", "Small")

The True/False outcomes can be anything you like; text, numbers, cell  references, other calculations or other conditional tests.

Examples:

If you had the value of 100 entered into cell A1, the following formula would  show "Big" in the cell but if the value in the cell was changed to a value less than 100 then it would show no entry in the cell.

=IF(A1>=100, "Big", "")

If you had the value of 100 entered into cell A1, the following formula would multiply the value in A1 by 10% but if the value in the cell was changed to a value less than 100 then it would multiply the value by 2%.

=IF(A1>=100, A1*10%, A1*2%)

A common use of these formulas is to force out the display of divide by zero errors which look ugly when the worksheet is printed. When two empty cells are divided Excel always returns the #DIV/0! (Division by Zero) error value which many people consider unsightly, the formula is fine and will operate correctly when some numbers are entered into the cells but meanwhile only the error displays. If cell C1 contained the formula expression A1/B1 the cell will display #DIV/0! while A1 and B1 are empty. To force the formula to display zero instead of #DIV/0! use the IF and ISERROR functions:

=IF(ISERROR(A1/B1), 0, A1/B1)

Please note that the first argument in the IF function requires an expression that returns a logical value. The ISERROR function returns a logical value, therefore there is no requirement to explicitly test for the value TRUE. The logical test is ISERROR(A1/B1), it is not necessary for it to be ISERROR(A1/B1)=TRUE, although the formula would still calculate correctly.

Mouse Training Course Index

Contact Email

For Further Course Details Visit Our Homepage

Microsoft Accredited IT Training Provider

Microsoft Certified Training Materials

IT Training Courses delivered by Microsoft Accredited Facilitators

Microsoft MOS Test Centre London