SPREADSHEET II

Advanced Features and tools of spreadsheet

Activity 10.1: Understanding the advanced features in spreadsheet

In groups:

1. Study the above graph and identify those features that make it unique from the graphs you created while in spreadsheet I.

2. State the function of each feature identified.

3. Copy and complete the table shown below about the features of the graph above

Activity 10.2: Inserting a Column Graph

To quickly review, a column graph is a type of bar graph that compares data using vertical bars. Every column graph has four main parts: title, labels, scale, and categories. Graphs are a perfect way to display your data in a manner that’s easy to read and understand.

In Groups

1. Open Excel and save it as “Marks”

2. Enter the data in the worksheet starting from cell A2

Hint:  To open Excel, go to start →All Programs → MS Office → Microsoft Excel

To add a column graph

1. Highlight / select the Names Column from A2:A10

2. Then Hold down ctrl key and select Biology marks column that is E2:E10

3. Select the Insert tab in the toolbar at the top of the screen.

1. Click on the Column button in the Charts group and then select a chart from the drop down menu.

2. In this example, we have selected the first column chart (called Clustered Column) in the 2-D Column section.

3. A column graph will appear like the one below.

Adding a title to a graph

Activity 10.3: Adding a title to a graph

In Groups

Follow these steps to insert and edit chart titles in Excel

1. Click anywhere on the chart you want to modify.

2. On the Chart Tools Layout tab, click the Chart Title buttonin the Labels group.

1. A list of options appears:

• None: This choice means you don’t want to display a title. Also use this option to remove a chart title you don’t want.
• Centered Overlay Title: Centers the title over the chart but retains the existing size of the chart.
• Above Chart: Centers the title over the chart but adds room at the top so the title doesn’t interfere with the chart itself.

2. Make a selection from the Chart Title drop-down list. For example “Above Chart”

3. A box with the words Chart Title appears on the chart.

4. Double-click the new text box that says “Chart Title” and drag across the words Chart Title.

5. Type the desired title.

(Optional) To format a title, select it and, on the Chart Tools Layout tab, click Chart Title →More Title Options, and customize to your heart’s content.

The Format Chart Title dialog box appears. Click any tab and choose any formatting options you want to apply to the title. Click Close when you’re done.

Adding axes (axis) titles to a graph

Activity 10.4: Adding axes (axis) titles to a graph

In Groups

1. Click anywhere in the chart to show the chart tools button on the ribbon.

2. Click Layout tab > Axis Titles, point to Primary Horizontal Axis Title or Primary Vertical Axis Title, and then click the axis title option you want.

3. In the Title text box, type a title for the axis.

4. To start a new line in the title, press Enter.

Activity 10.5: Inserting a Line Graph and a Pie chart

In Groups

1. Use the same data and procedures to create a line graph and a pie chart

2. Add titles and axis titles where appropriate

3. Share your findings

Creating a combo chart

A combo (or combination chart) is a chart that plots multiple sets of data using two different chart types. A typical combo chart uses a line and a column. Use them to visually highlight the differences between different sets of data.

Activity 10.5: Creating a combo chart

In groups

1. Select the Cells, 3 columns i.e. Names, Physics and Biology Marks, Select the cells containing the headings and numbers to be included in the chart

Hint: Remember to hold down ctrl key as you select other columns with data

Insert a Column Chart

1. Click the Insert tab on the Ribbon

2. Click Column

3. Click the type of column chart required – in this example 2-D Column has been selected

On The Column Chart Change One of the Bars (to a Line)

1. Right click on one of the bars that represent the series to be changed (to a line) and select Change Series Chart Type from the menu

2. Select the type of chart required for this data series

3. Click OK

Display the Scale for the Line on Secondary Axis (Optional)

8. In the chart, right click the line

9. Select Format Data Series

10. In the Series Options, select Secondary Axis

Use of varying scales on graph axes.

8. The Secondary Axis appears to the right of your chart

9. Provide appropriate titles and axis titles to your graph

10. Format your graph with different background and designs

Activity 10:6 Formatting charts and graphs

In groups:

1. Click anywhere in the chart. This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Format tab, in the Current Selection group,

2. Click the arrow next to the Chart Elements box, and then select the chart element that you want to format. For example chart area

3. To format the bar appearance, right click on the bar choose Format Data series and then under Fill button choose your designs.

Activity 10.7: Move or resize a chart

To move a chart, drag it to the location that you want.

Move a chart to another worksheet

Click anywhere in the chart.

This displays the Chart Tools option on the ribbon

Under Chart Tools, on the Design tab, in the Location group, click Move Chart.

Do one of the following:

To move the chart to a new worksheet, click New sheet, and then in the New sheet box, type a name for the worksheet.

To move the chart as an object in another worksheet, click Object in, and then in the Object in box, select the worksheet in which you want to place the chart.

Tip: You can also place a chart in another chart sheet.

1. To change the size and scaling of the chart, on the Format tab, in the Size group, click the Dialog Box Launcher                      next to Size, and then do the following:

2. In the Format Chart Area dialog box, click the Size tab.

1. Under Size and rotate, in the Height and Width boxes, enter the numbers you want.

2. Under Scale, in the Height and Width boxes, enter the numbers you want.

Activity 10.8: Adding Data Labels to Pie Charts

1. Select the data you will create a pie chart based on, click Insert > Insert Pie or Doughnut Chart > Pie. …

2. Then a pie chart is created. …

3. Now the corresponding values are displayed in the pie slices. …

4. Right click any slice on your chart, and select Format Data Labels… in the context menu.

1. On the Format Data Labels pane, select either the Value or Percentage box, or both as in the following example.

2. Percentages will be calculated by Excel automatically with the entire pie representing 100%.

Data Filtering

Data filtering is the process of choosing a smaller part of your data set and using that subset for viewing or analysis. Filtering is generally (but not always) temporary – the complete data set is kept, but only part of it is used for the calculation.

Activity 10.9: Data Filtering

In groups

1. Enter data in Excel

2. Select any cell within the range.

3. Select Data > Filter.

1. Select the column header arrow.

1. Select Text Filters or Number Filters, and then select a comparison, like between.

2. Enter the filter criteria and select OK.

For example enter Greater than 70 in Mathematics column

Activity 10.9: Data Filtering

In groups

1. Enter data below in Excel save it as Data.

1. Add a new column “Total” and calculate for Total marks

2. Add a new column “Average” and calculate for Average marks

3. Filter out those who scored Biology marks in the range “Between” 65 – 80?

4. Represent the Names and filtered biology marks on a pie chart

5. Include a title and percentage values on your pie-chart

6. Present your results

Reference using absolute/ relative/mixed referencing

A worksheet in Excel is made up of cells. These cells can be referenced by specifying the row value and the column value.

For example, A1 would refer to the first row (specified as 1) and the first column (specified as A). Similarly, B3 would be the third row and second column.

The power of Excel lies in the fact that you can use these cell references in other cells when creating formulas.

Relative reference

By default, a cell reference is a relative reference, which means that the reference is relative to the location of the cell. If, for example, you refer to cell A2 from cell C2, you are actually referring to a cell that is two columns to the left (C minus A)—in the same row (2). When you copy a formula that contains a relative cell reference, that reference in the formula will change.

As an example, if you copy the formula =B4*C4 from cell D4 to D5, the formula in D5 adjusts to the right by one column and becomes =B5*C5.

Absolute reference

If you want to maintain the original cell reference in this example when you copy it, you make the cell reference absolute by preceding the columns (B and C) and row (2) with a dollar sign ($). Then, when you copy the formula =$B$4*$C$4 from D4 to D5, the formula stays exactly the same.

Mixed reference

Less often, you may want to mixed absolute and relative cell references by preceding either the column or the row value with a dollar sign—which fixes either the column or the row (for example, $B4 or C$4).

To change the type of cell reference:

In groups

1. Select the cell that contains the formula.

2. In the formula bar                       , select the reference that you want to change.

3. Press F4 to switch between the reference types.

Working with advanced functions

While in spreadsheet I you looked at simple functions like sum, average, min, max and count, in this chapter we shall use more functions like IF, Sumif, Countif, Rank, Vlookup and Hlookup.

Using the IF Function

The Microsoft Excel IF function returns one value if the condition is TRUE, or another value if the condition is FALSE. The IF function is a built-in function in Excel that is categorized as a Logical Function.

Syntax

The syntax for the IF function in Microsoft Excel is:

Parameters or Arguments

condition

The value that you want to test.

value_if_true

It is the value that is returned if condition evaluates to TRUE.

value_if_false

Optional. It is the value that is returned if condition evaluates to FALSE.

Returns

The IF function returns value_if_true when the condition is TRUE.

The IF function returns value_if_false when the condition is FALSE.

The IF function returns FALSE if the value_if_false parameter is omitted and the condition is FALSE.

Activity 10.10: Using the IF Function

In groups

1. Open excel

2. Type the following data

1. In a new column and calculate Average marks

2. Add a new column “Remarks”.

3. Use the table below to assign for remarks with the help of IF Function basing on Average marks column.

1. Procedure

1. Click in cell G3

2. Type =IF(G3>=75,”Excellent”,IF(G3>=60,”Good”,IF(G3>=50,”Average”,”Improve”)))

3. Press Enter Key and auto fill

Using the SUMIF Function

The SUMIF function is a worksheet function that adds all numbers in a range of cells based on one criteria (for example, is equal to 2000). The SUMIF function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel.

Let’s explore how to use SUMIF as a worksheet function in Microsoft Excel.

Syntax

The syntax for the SUMIF function in Microsoft Excel is:

Activity 10.11: Using the SUMIF Function

In groups

1. Enter the data below in a worksheet

1. In cell F2, use the SUMIF Function to add all values of the Year 2000 given the criteria in cell D2.

Procedure

1. Click in cell F2

2. Type =SUMIF(A2:A6,D2,C2:C6)

3. Press Enter Key

Range is  A2:A6

Criteria is  D2 ie the figure “2000”

Sum_range is  C2:C6

Meaning to add up all values in year 2000, one must use a sumif function, select the rangethat contains different years, assign a (criteria– (this can be written in a cell of typed in the function)) and also select the range to be added (Sum_range).

1. In cell E4, use the SUMIF Function to add all values that are less than 100.

Procedure

1. Click in cell E4

2. Type =SUMIF(C2:C6,”<100″)

3. Press Enter Key

Using the COUNTIF Function

The Microsoft Excel COUNTIF function counts the number of cells in a range, that meets a given criteria. The COUNTIF function is a built-in function in Excel that is categorized as a Statistical Function. It can be used as a worksheet function (WS) in Excel.

Syntax

The syntax for the COUNTIF function in Microsoft Excel is:

Parameters or Arguments

range

The range of cells that you want to count based on the criteria.

criteria

The criteria used to determine which cells to count.

Returns

The COUNTIF function returns a numeric value.

Activity 10.12: Using the COUNTIF Function

In groups

1. Open excel

2. Enter the Data below and save it as “Data_Counted”

1. In cell C12 use the COUNTIF Function to return how many got 80 and above

Procedure

ü Click in Cell C12

ü Type =COUNTIF(C3:C10,”>=80″)

ü Press Enter Key

Using the RANK Function

The Microsoft Excel RANK function returns the rank of a number within a set of numbers.

The RANK function is a built-in function in Excel that is categorized as a Statistical Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the RANK function can be entered as part of a formula in a cell of a worksheet.

Syntax

The syntax for the RANK function in Microsoft Excel is:

Parameters or Arguments

number

The number to find the rank for.

array

A range or array of numbers to use for ranking purposes.

order

Optional. It specifies how to rank the numbers.

Returns

The RANK function returns a numeric value.

Activity 10.13: Using the RANK Function

In groups

1. Type the data below in a worksheet

2. Use it to calculate Position using Rank Function in a way that those value with high numbers are ranked as 1, 2 etc.(Descending Order)

1. Procedure

1. Click in cell D2

Type =RANK(C2,C2:C6,0)

1. Press Enter Key

2. Auto – fill to the rest of the cells below.

3. Use the same procedure and rank the values in Ascending Order.

Using the VLOOKUP Function

The VLOOKUP function performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position.

The VLOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel.

Syntax

The syntax for the VLOOKUP function in Microsoft Excel is:

Parameters or Arguments

value

The value to search for in the first column of the table.

table

Two or more columns of data that is sorted in ascending order.

index_number

The column number in table from which the matching value must be returned. The first column is 1.

approximate_match

Optional. Enter FALSE to find an exact match. Enter TRUE to find an approximate match. If this parameter is omitted, TRUE is the default.

Returns

The VLOOKUP function returns any datatype such as a string, numeric, date, etc. If you specify FALSE for the approximate_match parameter and no exact match is found, then the VLOOKUP function will return #N/A.

If you specify TRUE for the approximate_match parameter and no exact match is found, then the next smaller value is returned.

If index_number is less than 1, the VLOOKUP function will return #VALUE!.

If index_number is greater than the number of columns in table, the VLOOKUP function will return #REF!.

Activity 10.14: Using the VLOOKUP Function

In groups

1. Open excel

2. Enter the Data below and save it as “Data_Looked”

1. Add a column for Average Marks and compute for Average Marks

2. Start from Cell C12 enter this table as well

1. Add a new column for Remarks and use VLOOKUP Function to compute for Remarks basing on Average Marks and with the help of the array table.

Procedure

1. Click in Cell G3

2. Type =VLOOKUP(F3, highlight the range C13:D16, 2, TRUE)

3. Press Enter Key

4. Use the Auto fill handle to get the rest of the cells below.

Using the HLOOKUP Function

The Microsoft Excel HLOOKUP function performs a horizontal lookup by searching for a value in the top row of the table and returning the value in the same column based on the index_number.

The HLOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the HLOOKUP function can be entered as part of a formula in a cell of a worksheet.

Syntax

The syntax for the HLOOKUP function in Microsoft Excel is:

Parameters or Arguments

value

The value to search for in the first row of the table.

table

Two or more rows of data that is sorted in ascending order.

index_number

The row number in table from which the matching value must be returned. The first row is 1.

approximate_match

Optional. Enter FALSE to find an exact match. Enter TRUE to find an approximate match. If this parameter is omitted, TRUE is the default.

Returns

The HLOOKUP function returns any datatype such as a string, numeric, date, etc.

If you enter FALSE for the approximate_match parameter and no exact match is found, then the HLOOKUP function will return #N/A.

If you specify TRUE for the approximate_match parameter and no exact match is found, then the next smaller value is returned.

If index_number is less than 1, the HLOOKUP function will return #VALUE!.

If index_number is greater than the number of columns in table, the HLOOKUP function will return #REF!.

Activity 10.15: Using the HLOOKUP Function

In groups

1. Open excel

2. Enter the Data below and save it as “Looking”

1. Add a column for Average Marks and compute for Average Marks

TIP: To calculate average use =AVERAGE (B3:E3)

2. Start from Cell C12 enter this table as well

1. Add a new column for Remarks and use HLOOKUP Function to compute for Remarks basing on Average Marks and with the help of the array table.

Procedure

1. Click in Cell G3

2. Type =HLOOKUP(F3,C12:F13,2,1)

Absolute and relative cell references in VLOOKUP and HLOOKUP formulas

If you are writing a formula for a single cell, you may not worry about the proper use of relative and absolute cell references, either one will do.

Copying a formula to multiple cells is a different story. In essence:

You should always fix table_array by using absolute cell references with the dollar sign ($) like $B$1:$I$2.

· In the above formula, we use absolute cell references ($B$1:$I$2) in table_array because it should remain constant when the formula is copied to other cells.

· Due to the clever use of cell references, our Vlookup and Hlookup formulas works perfectly for multiple cells

Switch From Cell Values To View Formulas.

As soon as you type a formula in Excel and hit enter, it would return the calculated result, and the formula would disappear. That’s how it’s supposed to work.

But what if you want to show formulas in the cells and not the calculated values;

Activity 10.16: Switching From Cell Values to View Formulas.

Method 1

In groups

1. Click on the ‘Formulas’ Tab in the ribbon.

2. In the Formula Auditing group, click on the Show Formulas option.

Method 2

In some cases, you may have a lot of worksheets and you want to show the formulas in all the worksheets in the workbook.

In groups

Here are the steps that will show the formulas in all the worksheets in Excel:

Activity 10.17: How to Print Formulas in Excel

In groups

1. Go to Formula tab.

2. Click on the Show Formula option.

3. Go to File –> Print.

Common Excel Errors and How to Fix Them

Every function has a syntax and this must be properly complied with, and if any deviation is observed in entering the syntax then there will be an Excel error.

Understanding Excel errors are important same as we understand the functions. These displayed errors tell us a lot of things. With the proper understanding about the Excel errors, one can easily solve those errors.

Activity 10.19: Common Excel Errors and How to Fix Them

The #### Error

In groups

Type the information below, reduce column C so that cell C2 shows the error

This one is easy, it means that there is not enough space in the cell to display the value.

Solution

To fix it, simply increase the size of the cell i.e Column c for the values to fit in.

The #NAME? Error

In groups,

In cell C5, type =SUMM(C2:C4) this will return an error as seen below

This error appears when you are using a function that doesn’t exist, which probably means that you made a spelling mistake.

Solution

So proofread the function name to find the mistake, and fix it! =SUM(C2:C4)

The #DIV/0! Error

In groups

Use the same data above, replace the value in cell D4 (3) with 0. Then in cell E4, type =C4/D4, press enter. You will have #DIV/0! As the answer.

Do you know that it’s impossible to divide a number by zero? And when you try to do just that in Excel, you get the #DIV/0! Error.

Solution

If that happens do not divide by zero. That is you can now replace 0 with 3 again.

The #NULL! Error

In groups

1. Use the data below

2. Enter the word Total in cell A6, then try calculating for total by use of a formula for example type =(C2+C3 C4) Press Enter.

3. You will get the #NULL! As the result.

This usually happens when you have an error in the syntax of your formula. Maybe you forgot a commas or a plus sign somewhere.

Solution

The only way to fix that is to have a close look at your formula to see what’s wrong. This can be possible by double clicking in #NULL in cell C6, then add a plus symbol between C3 and C4 so that your formula looks like =(C2+C3+C4)

The #REF! Error

In groups

1. Enter the Data below

1. In column F add a heading Total and Calculate total which is Value x QTY.

2. Deliberately delete the QTY Column, the total column will be filled with #REF!

I don’t like seeing this error, it means that a reference (cell or range) you are using is not valid. That can happen when you delete a column, or past stuff on top of data.

Solution

There’s no easy fix, usually the best way is to start the formula from scratch to make sure you didn’t delete important data.

The #VALUE! Error

In groups,

1. Open Excel and enter the data below

2. In Cell F2, type =C2*D2, instead of =C2*E2 then press Enter

3. The cell will be filled with #VALUE!

This is a classic mistake. This happens when your formula includes cells with different data types, like if you try to multiply a number by a text.

Solution

So check all the cells/range in your formula to find which cells should not be there, and remove them. Meaning you type the correct formula as =C2*E2

Conclusion on Excel Errors

With all this information you can be quicker to debug your spreadsheet because you know exactly what the problem is

Here’s a quick recap of all the errors we saw with their meaning:

1. #### not enough space to display the value.

2. #NAME? the function does not exist.

3. #DIV/0! trying to divide by zero.

4. #NULL! there is a syntax error.

5. #REF! some cells do not exist anymore.

6. #VALUE!cells have different types of data.

Adding Headers and footers in a worksheet

Headers and footers are displayed only in Page Layout view, Print Preview, and on printed pages. You can also use the Page Setup dialog box if you want to insert headers or footers for more than one worksheet at a time. For other sheet types, such as chart sheets, or charts, you can insert headers and footers only by using the Page Setup dialog box.

Activity 10.20: Adding Headers and footers in a worksheet

In groups

1. Click on the Insert tab, in the Text group

2. Click Header & Footer.

Setting print area in a worksheet

A print area is one or more ranges of cells that you designate to print when you don’t want to print the entire worksheet. When you print a worksheet after defining a print area, only the print area is printed. You can add cells to expand the print area as needed, and you can clear the print area to print the entire worksheet.

A worksheet can have multiple print areas. Each print area will print as a separate page.

Activity 10.22: Setting print area

In groups;

Set one or more print areas

1. On the worksheet, select the cells that you want to define as the print area.

To see all the print areas to make sure they’re the ones you want, click View > Page Break Preview in the Workbook Views group. When you save your workbook, the print area is saved too.

Add cells to an existing print area

You can enlarge the print area by adding adjacent cells. If you add cells that aren’t adjacent to the print area, Excel creates a new print area for those cells.

1. On the worksheet, select the cells that you want to add to the existing print area.

When you save your workbook, the print area is saved as well.

Clear a print area

Note: If your worksheet contains multiple print areas, clearing a print area removes all the print areas on your worksheet.

1. Click anywhere on the worksheet for which you want to clear the print area.

2. On the Page Layout tab, in the Page Setup group, click Clear Print Area.

Printing Worksheets

If you have multiple worksheets in your workbook, you’ll need to decide if you want to print the entire workbook or specific worksheets. Excel gives you the option to Print Active Sheets. A worksheet is considered active if it is selected.

Activity 23: Printing active sheets

1. Select the worksheets you want to print. To print multiple worksheets, click the first worksheet, hold down the Ctrl key, then click the other worksheets you want to select.

Chapter Summary

In this chapter you have learnt;

(a) How to create and format graphs and charts

(b) Data Filtering

(c) Reference using absolute/ relative/mixed referencing

(a) Working with advanced functions like IF, Sumif, Countif, Vlookup, Hlookup And Rank.

(a) Toggle between formula and value views.

(b) Excel errors and fixing them

(c) Printing a worksheet