Short Courses in Computer Software - Excel No. 2

Count Functions.....................................................................................................2

COUNT.............................................................................................................2

COUNTA...........................................................................................................2

COUNTBLANK..................................................................................................2

COUNTIF..........................................................................................................2

Charts and Graphs.................................................................................................2

Creating Charts...................................................................................................2

Working with Charts............................................................................................3

Shortcut Menu.................................................................................................3

Choosing a Chart Type........................................................................................3

Chart Tools ribbons.............................................................................................3

Design Ribbon.................................................................................................3

Layout Ribbon................................................................................................4

Format Ribbon................................................................................................4

Home Ribbon...................................................................................................4

Using Multiple Sheets.............................................................................................4

3-D References................................................................................................4

Copying Worksheets........................................................................................5

Working on Multiple sheets.............................................................................5

Viewing multiple sheets..................................................................................5

Viewing multiple workbooks............................................................................5

Hide Zeros.......................................................................................................5

Custom Formats.....................................................................................................6

Custom Date Format.......................................................................................6

Leading Zeros..................................................................................................6

Custom Hours..................................................................................................7

Custom Numbers.............................................................................................7

Count FunctionsAt times you may need to count entries of a particular range.

COUNTCounts numbers only in a list. Syntax: COUNT(Value1, Value2, ...)

COUNTACounts the number of Alpha Numeric entries values in a list. Counts anything, except blank cells. Syntax: COUNTA(Value1, Value2, ...)

COUNTBLANKCounts the number of blank cells. Syntax : COUNTBLANK(range)

COUNTIFCounts the number of cells within a range which meets the given criteria

Syntax: COUNTIF(range, criteria)

Example1: COUNTIF(C12:C25,6) - would count all the number 6’s in the range.

Example2: COUNTIF(C12:C25,”Alpha”) - would count all the cells with the word “Alpha” contained in the range or list. Text must have the “Quotation” marks around it.

Charts and GraphsCreating ChartsExcel has a wide range of charts from which you can choose. You can also add legends, x and y axis titles and headings to the charts, change the colours and patterns. You can do this as the chart is being created through the Chart Wizard or change and add them after the chart has been created, by using the Menus.

You can create an embedded chart or a separate chart as a “chart” sheet. In today’s exercises you will create both types of charts. Whichever type of chart you create, the chart is automatically linked to the data so if you change the data, the chart adjusts to reflect this change.

To create a chart

Select the data you wish to chart.

From the Insert ribbon, in the Charts group, choose the Chart type. Or click the Dialog Box Launcher

Unlike earlier versions of Excel, there is no Chart Wizard. Changes to the Wizard are done after it is created.

Working with ChartsWhen the Chart is created you can change any of the original features using the following tools.

When the chart is selected the Chart Tools Design ribbon appears. This allows you to change the Chart itself.

You can change an element of the chart, (area/control/object) by selecting each individual element and use the ribbons or you can use the Shortcut menu (Right Mouse click) to access most of the changes.

Some of these changes are not very obvious. Changes are directly linked to which object you select.

To make changes to Charts:

Depending what you want to do, there are three different methods to make changes to Charts in Excel, these include:

Right Mouse Shortcut Menu.

The Chart Tools ribbons. (there are 3)

The Home Ribbon.

Double Mouse Click will bring up the Chart Tools ribbons.

Shortcut Menu.Right Mouse Menu

Clicking with the Right Mouse button on an area/control/object will bring up a shortcut menu for the selected item.

Select the Format option from the shortcut menu.

The Format dialog box will then appear.

Choosing a Chart TypeTo present your information clearly it is important to select the appropriate chart type for your data. Here is a guide to choosing the most appropriate chart to display your data.

Chart Type Chart Function

Column and Bar Charts Shows contrast between quantities

Line Chart Shows the relationship of one variable to another

Pie Chart Compares parts of a whole

Scatter Chart Compares pairs of values

Area Chart Shows the trend of values

Chart Tools ribbons. There are 3 ribbons that you can use to modify your chart.

Design RibbonThe Chart Tools - Design ribbon allows you to change the shape, design and some minor aspects of the Chart.

Layout RibbonThe Chart Tools - Layout ribbon allows you to change how the graph looks.

Format Ribbon

The Chart Tools – Format ribbon allows you to change the text and shapes on

the graph.

Home Ribbon. Using the Home ribbon allows limited changes to style colours and Text formats

Using Multiple Sheets3-D ReferencesIn the first lesson we linked information from one sheet to another. A 3-D reference is a cell reference that spans two or more sheets in the one workbook. Using 3-D references you can perform calculations on the data in more than one sheet at a time. When using 3-D references it is best to make sure you have set out each worksheet identically. That way you can be sure the cells you wish to use in the formula are in the identical position on each worksheet. To do this it is best to use a template and/or styles.

Examples of a 3-D reference would be:

=Sheet1!C8+Sheet2!F8 OR

=SUM(Sheet1:Sheet6!$C$8:$F$8) OR

=SUM(Sheet1!F8,Sheet2!C9,Sheet3!G4)

You can use 3-D references with the following functions: Sum, CountA, Average, Max, Min, Product, StDev, Var, VarP and Count.

Copying Worksheets.If you need to copy worksheets there are a few ways you can do it, generally involving Copy and Paste. A simpler way is to use the Tabs

To copy using Tabs.

Select the tab of the sheet you wish to copy.

Click and hold the mouse button down. The cursor has a symbol of a “sheet” attached.

Press and hold the Ctrl key, on the Keyboard.Note the “sheet” has a “+” sign on it.

Drag to the new position and let go. The new sheet will have the same name plus a number eg Sheet 1 (2)

Working on Multiple sheetsYou can insert certain functions (such as Paste) and apply formatting on more than one sheet at a time by selecting all the sheets and inserting the function.

To choose multiple sheets.

Select the first tab

Hold the Ctrl key and select the other sheet tabs required.

Apply the function and/or formatting to the sheet on the screen. This will put the function on all selected sheets.

To de-select all the sheets, click onto a spare sheet tab OR right click on one of the sheet tabs and click on Ungroup Sheets.

Viewing multiple sheetsNormally, if you wanted to see information on another sheet, you would go back and forth to the sheets using the sheet tabs. You can view more than one sheet at a time to save using the tabs.

To view multiple sheets.

From the View tab in the Window group, choose New Window. This gives you a second window to view your information.

From the View tab in the Window group, choose Arrange All then choose Tiled and click OK. You can now see both on the same screen, which allows you to use tabs independently to view/copy/change information.

Viewing multiple workbooksYou can use the same method to view different workbooks.

To view multiple workbooks.

Make sure both workbooks are open.

From the View tab in the Window group, choose Arrange All and then choose Tiled and click OK. You can now see both workbooks on the same screen, which allows you to use workbooks independently to view/copy/change information.

Hide ZerosCopying formulas that have no results, gives you a Zero value. You can hide these zeros on the sheet.

To Hide Zeros on the whole sheet

Open the File tab,

Click the Options button (at the bottom)

Select the Advanced category .

Scroll down to the Display options for this worksheet group .

De-select the Show a Zero ...

Click OK and the zeros have gone from the selected sheets.

Click onto a spare sheet tab to de-select all the sheets

To Hide Zeros in individual cells

Select the cell/s that want to hide zeros in.

From the Home ribbon, in the Cells group, click Format, and then click Format Cells.

In the Category list, click Custom.

In the Type box, type 0;-0;;@

Click OK

Custom FormatsYou can create your own Custom Number Format that doesn’t exist in the standard formats. You have used this in Intermediate 1 to hide the Cell contents. An example of this would be a date format showing the actual day.

3

21

2

Custom Date FormatTo create a Custom Format.

Select the cell required.

From the Home, ribbon, open Number Dialog box launcher.

Choose the Number sheet, under Category: choose Custom

In the Type box , select the entry.It will probably say “General” as in the diagram.

Type a date format, e.g. dd/mm/yyyy

Click OK and note the result.

To delete a custom Format;

From the Home, ribbon, open Number Dialog box launcher.

Choose the Number sheet, under Category: choose Custom Under Type, select the entry. Click the Delete button (on the Keyboard).

Note only user defined custom formats can be deleted.

Leading ZerosSometimes you may want the number of a cell to show with leading zeros. To do this you simply apply a Custom Format of Zeros (000) to the cell.

When you enter a number it will have the zeros automatically installed.

E.g. enter 1 read 001, 12 reads 012, 414190196 reads 04190196 etc.

Custom HoursThe problem with hours in Excel is that it only counts to 24 hrs then starts again.

In the following example, The formula in B4 is =SUM(B2:B4) and the result is only 19hrs.

The same formula in D4 gives the result of 39hrs. B4 is formatted by default as h:mm the formula in D4 is [h]:mm. The square brackets around the hrs allow it to total more than 24hrs.

Custom Numbers When numbers are entered in Excel they are automatically formatted as General but in a number format. You can change the format to Number which allows you to format Negative numbers to make them stand out.

In the following example, The formula in B4 is =SUM(B2:B4) and the result is a negative number. Also by default a negative number appears in black (automatic) colour.

You can also show this number as Red, without the negative sign or Red, with the negative sign.

1

43

2

A B C D

1 12:00 12:00

2 14:00 14:00

3 13:00 13:00

4 Total: 15:00 Total: 39:00

A B

1 -1

2 5

3 -6

4 Total: -2