AdvancedMicrosoft Excel 2007Spreadsheet Functions

Participants Workbook

Name:________________________Employee No._________________Dept:______________________

Table of Content

LESSON 1: FORMATTING31.1File types in office Excel 200731.2Use Multiple Worksheets31.3Split, Move and Remove a Split51.4Hide, Show Rows, Columns, Worksheets6

LESSON 2: FUNCTIONS AND FORMULAS82.1Absolute and Relative Cell Referencing82.2Function Syntax and Categories82.3Logical Functions102.4Mathematical Functions112.5Statistical Functions132.6Date and Time Functions132.7Lookup And Reference Functions152.8Database Functions172.9Text Functions172.10Paste Special Options18Learning Activity 1: Using Cell Referencing in Fomulae20Learning Activity 2: IF, AND, OR Function20Learning Activity 3: Nested Functions21Learning Activity 4: SUM, SUMIF Function21Learning Activity 5: ROUND, ROUND UP, ROUND DOWN Function22Learning Activity 6: COUNT, COUNTIF Function22

LESSON 3: CHARTS233.1Creating a Standard Chart233.2Formatting Charts24Learning Activity 7: Creating and Formatting a Chart26

LESSON 4: ANALYSIS274.1Pivot Table274.2Sorting a Database304.3Using Auto & Advanced Filter31Learning Activity 8: Creating PivotTables33Learning Activity 9: Sorting34Learning Activity 10: Using AutoFilter34

LESSON 5: Collaborative Editing355.1Working with Comments355.2File Sharing36Learning Activity 11: Comments37

LESSON 1: FORMATTING

1.1 File types in office Excel 2007

There are several file types you can choose from when you save a file in Excel 2007

1.1.1. Excel Workbook (*.xlsx)

Save a workbook as this file type if it does not contain macros or Microsoft Visual Basic for Applications (VBA) code. If you save a workbook as an Excel Workbook and there are any macro commands or VBA projects in the file, Excel 2007 will warn you that the macro or VBA code will be deleted from the File.

1.1.2. Excel Macro-Enabled Workbook (*.xlsm)

Save your workbook as this file type when the workbook contains macros or VBA code. If you try to save a workbook containing macros or VBA as the Excel Workbook file type, Excel will warn you against this choice.

1.1.3. Excel Template (*.xltx)

Save your workbook as this type when you need a template.

1.1.4. Excel Macro-Enabled template (*.xltm)

Save your workbook as this file type when you need a template and the workbook contains macros or VBA.

1.1.5. Excel 97 Excel 2003 Workbook (*xls)Save your workbook as this file type when you have an especially large workbook; this file type will open faster than a very large Excel Workbook will. You will still have the new Excel features with this file type, but not XML.

1.2 Use Multiple Worksheets

1.2.1 Copy and Move Worksheets

1. Open the workbook that you want to copy/move the workbook to.

2. Return to the worksheet you want to move or copy.

i. Right-click on the sheet tab.

ii. Select Move or Copy

OR

i. Select the Home tab. From the Cells group, click Format.

ii. Click Move or Copy Sheet.

3. In the To book area, select the workbook that will receive the sheets.

4. Alternatively, choose NEW to move/copy the worksheet to a new workbook.

5. In the Before sheet area, click the sheet before which you want to position the worksheet.

6. Click Create a copy to copy the worksheet instead of moving the worksheet.

7. Click [OK]

To move sheets within the current workbook, you can drag the selected worksheet along the sheet tabs.

To copy a sheet within the current workbook:

1. Press and hold the key.

2. Use your mouse to click and hold on the sheet tab.

3. Move your cursor to the desire location.

4. Release both the mouse button and the key.

1.2.2 Grouping Worksheets

When more than one worksheet is selected, the worksheets are grouped. Data entered into one sheet is entered into all sheets in the group.1.2.3 Ungroup Worksheets

Right-click any grouped worksheet and select Ungroup Sheets

OR

Click any sheet that is not grouped.

1.3 Split, Move and Remove a Split

You can view multiple distant parts of your worksheet at once by splitting the window into numerous resizable panes containing views of different parts of the worksheet.

1.3.1 Insert a split

1. To split into 2 vertical windows, click on the cell in the first row of the mid column.

To split into 2 horizontal windows, click on the cell in the first column of the mid row.

To split into 4 windows, click on the cell somewhere in the middle of the worksheet.

2. To insert a split, either

Select the View tab. From the Window group, click Split.

OR

To insert a vertical split, place your cursor on the bar to the right of the horizontal scroll bar and double-click on the bar.

1.3.2 Move a Split

1. Point your cursor on the split until the pointer changes to a split pointer.

2. To move the split up and down, click and hold your left mouse button and drag the split up and down.

3. To move the split to the left or to the right, click and hold your left mouse and drag the split left or right.

1.3.3 Remove a Split

To remove a split, you can either:

Select the View tab. From the Window group, select Remove Split.

OR

Double-click on the split.

1.4 Hide, Show Rows, Columns, Worksheets

There may be instances where you have data that you do not want others users to view. A simple way would be to hide the row, column or worksheet where the data appears.

1.4.1 Hide and Show Rows

To hide row(s), select the row(s) to be hidden and either:

Right-click on the row(s) and select Hide.

OR

On the Format menu, point to Row, and then click Hide.

To display hidden row(s), select the row above and below the hidden row(s) and either:

Right-click on the row(s) and select Unhide.

OR

On the Format menu, point to Row, and then click Unhide.

1.4.2 Hide and Show Columns

To hide column(s), select the column(s) to be hidden and either:

Right-click on the column(s) and select Hide.

OR

On the Format menu, point to Column, and then click Hide.

To display hidden column(s), select the columns in the left and right of the hidden column(s) and either:

Right-click on the column(s) and select Unhide.

OR

On the Format menu, point to Column and then click Unhide.1.4.3 Hide and Show Worksheets

To hide worksheet(s):

1. Select the worksheet(s) to be hidden.

2. On the Format menu, point to Sheet, and then click Hide.

To display hidden worksheet(s):

1. On the Format menu, point to Sheet, and then click Unhide.

2. In the Unhide sheet box, either

Double-click the name of the hidden sheet you want to display.

OR

Select the sheet name and click [OK].

LESSON 2: FUNCTIONS AND FORMULAS

2

2.1 Absolute and Relative Cell Referencing

Usually in formulas that are setup, there will be references to other cells. When the formulas are copied to another location, the cells referred to in the formulas will be changed.

Absolute Reference Cell reference is fixed Cell reference is prefix with a $

Relative Reference Cell reference changes when copied to another location No prefixes

For example, you created a formula (=A2+A3) in cell A1. When this formula is copied from A1 to say B5, the formula in B5 becomes (=B5+B6). I.E. the cell references in the formula changed relative to the location of the formula. This is termed relative referencing. Most of the time, we use relative referencing.

However, there are times we want the formula to be copied to another location; yet keeping the cells referenced in the formula the same as the original location. In this case, we require absolute referencing.

To achieve this, you will need to prefix the cell referenced in the formula with a $ sign. Taking the previous example, you will then set the formula in A1 to be =$A2+$A3. To automatically add $ symbols, press the function key .

It is possible to have a combination of absolute and relative referencing in a formula. E.g. =A1*$A2. This is termed mixed referencing. You can change the referencing by pressing until you get the reference required.

2.2 Function Syntax and Categories

A function is a formula that is already written for you and given a descriptive name. Excel offers over 350 worksheet functions. Besides worksheet functions, there are other functions that are included with adds-in such as the Analysis ToolPak.

2.2.1 Components of a Function

There are 3 components to a function:

a. The equal sign: you must begin each function with the equal sign (=)

b. The name of the function: E.g. SUM, AVERAGE

c. The arguments to the function: These are enclosed in parentheses and if there is more than one, they are separated by commas. They refer to the values on which the function operates on. Arguments must be supplied in the exact quantity and order they are specified.

In some cases, there are no arguments but the parentheses must still be specified (e.g. =today() which displays the current PC date).

2.2.2 Types of Function

Because Excel provides so many functions, it is useful to group them generally according to usage. The categories are meaningful but there can be some arbitrary classifications (especially those categorized under mathematical or statistical functions). The categories are:

Advanced Microsoft 2007 Spreadsheet Function

a.

Copyright of STATS ChipPAC Singapore 37 | Pageb. Financial Functionsc. Date & Time Functionsd. Math & Trig Functionse. Statistical Functionf. Lookup & Reference Functionsg. Database Functionsh. Text Functionsi. Logical Functionsj. Information Functions

2.2.3 Creating Functions

There are 3 methods which you can create functions:

1. Select the Home tab.

2. Click the drop-down arrow next to the AutoSum button and select More Functions

OR

1. Select the Formula tab.

2. Select the function you need from the Functions Library group.

OR

1. Select the Insert Function button on the right of the Formula Bar.

You may use the Insert Function dialogue box to search for the function you require.

You will see a Function Arguments dialogue box. This box is to allow you to enter the variables that the function needs to do the calculations.

2.3 Logical Functions

Logical functions enable Excel to help you make decisions automatically. To use the logical functions, you need to specify the criteria or condition you want evaluated. The functions will then check a cell to see if the content of the cell satisfies the criteria.

Logical functions require the criteria to be specified in such a manner that the results are black or white. In other words, the criteria must return either a TRUE or FALSE result. In order for Excel to evaluate if a criterion has been satisfied, you need to use comparison operators:

= Equal to < Less than

Not equal to >= Greater than or equal to

> Greater than 12, apples.

c. Sum_range are the actual cells to sum. The cells in sum_range are summed only if their corresponding cells in range match the criteria. If sum_range is omitted, the cells in range are summed.

2.4.2 Using the ROUND function

The ROUND worksheet function rounds a number to a specified number of digits.

=ROUND (Number, Num_digits)

a. Number is the number you want to round.

b. Num_digits specifies the number of digits to which you want to round to. Num_digits can be greater, equal or less than 0. Num_digits > 0 the number will be rounded to the specified number of decimal places. E.g. ROUND(1.256,2) returns 1.26

Num_digits = 0 the number will be rounded to the nearest integer. E.g. ROUND(645.25,0) returns 645

Num_digits < 0 the number will be rounded to the left of the decimal places. E.g. ROUND(645.25,-1) returns 650 and ROUND(645.25,-2) returns 600

2.4.3 Using the ROUNDDOWN function

The ROUNDDOWN worksheet function rounds a number down, toward zero.

=ROUNDDOWN (number, num_digits)

a. Number is the number you want to round.

b. Num_digits specifies the number of digits to which you want to round toExample: ROUNDDOWN(1.4596,2) return 1.45

2.4.4 Using the ROUNDUP function

The ROUNDUP function rounds a number up, away from 0 (zero).

=ROUNDUP (number,num_digits)

a. Number is the number you want to round.

b. Num_digits specifies the number of digits to which you want to round to

Example: ROUNDUP(1.1211,2) returns 1.13

2.5 Statistical Functions

2.5.1 Using the COUNTA Function

The COUNTA function will count the number of cells within a range that are not empty.

=COUNTA(Value1,Value2,.)

a. Value1 is the first range of cells you want to count b. Value2 is the next range of cells you want to count.

You can specify up to 30 sets of cells to include in the COUNTA function.

2.5.2 Using the COUNTBLANK Function

The COUNTBLANK worksheet function counts the empty cells in a specific range of cells.

=COUNTBLANK(Range)

Range is the range from which you want to count the blank cells.

NOTE: Cells with formulas that return (empty text) are also counted. Cells with zero values are not counted.

2.5.3 Using the COUNTIF Function

The COUNTBIF worksheet function counts the number of cells within a range that meet the given criteria.=COUNTIF(Range,Criteria)

Range is the range of cells from which you want to count cells.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be counted. For example, criteria can be expressed as 22, 22, >22, apples.

2.6 Date and Time Functions

The Date and Time functions in Excel allow you to insert current date and current time into your spreadsheet. You can also use the various functions to perform calculations using the dates in your spreadsheet.

2.6.1 Using the TODAY Function

The TODAY function allows you to insert a date that automatically updates everyday. The serial number is the date-time code used by Microsoft Excel for date and time calculations. If the cell format was General before the function was entered, the result is formatted as a date.=TODAY()

The TODAY function is one of the few functions in Excel that does not take any argument.

NOTE: Excel stores dates as a sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft excel for the Macintosh uses a different date system as its default.

2.6.2 Using the NOW Function

The NOW function calculates and returns the serial number of the current date and time. If the cell format was General before the function was entered, the result is formatted as a date.

=NOW()

The NOW function is similar to the TODAY function in that both functions do not take any arguments.

2.6.3 Using the DAY Function

The DAY function calculates and returns the day of a date represented by a serial number. The day is given as an integer, ranging from 1 to 31.

=DAY(Serial_number)

Serial_number is the date of the day you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

2.6.4 Using the MONTH Function

The MONTH function calculates and returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1(January) to 12(December).

=MONTH(Serial_number)

Serial_number is the date of the month you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

2.6.5 Using the YEAR Function

The YEAR function calculates and returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999.

=YEAR(Serial_number)

Serial_number is the date of the year you want to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

2.7 Lookup And Reference FunctionsLook up functions refer to finding values in worksheet ranges. Reference functions refer to worksheet addresses. The VLOOKUP and HLOOKUP are examples of lookup functions.

2.7.1 Using the VLookup Function

The VLOOKUP worksheet function finds a value in another column of a sorted list with a row labels. The row labels are in the leftmost column and you want to look up a value in another column based on the row labels from the same sheet or another.

=VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup)

a. Lookup_value: The value to be found in the first column of the array. It can be a value reference, or a text string.

b. Table_array: The table of information in which data is looked up. Use a reference to a range or a range name, such as Database or list. The values in the first column of table_array can be text, numbers, or logical values.

c. Col_index_num: The column number in the table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array. If col_index_num is less than 1, VLOOKUP returns the #VALUE! Error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

d. Range_lookup: This is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. That is, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

e. If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order. You can put values in ascending order by choosing the Sort command from the DATA MENU and selecting ascending.

2.7.2 Using the HLookup Function

The HLOOKUP worksheet function finds a value in another row of a sorted list with column labels. The column labels are in the topmost row and you want to look up a value in another row based on the column label.

=HLOOKUP(Lookup_value,Table_array,Row_index_num,Range_lookup)

a. Lookup_value: The value to be found in the first row of the table.

b. Table_array: The table of information in which data is looked up.

c. Row_index_num: The row number in the table_array from which the matching value must be returned. A row_index_num of 1 returns the value in the first row value in table_array. If row_index_num is less than 1, HLOOKUP returns the #VALUE! Error value; if row_index_num is greater than the number of rows in table_array, HLOOKUP returns the #REF! error value.

d. Range_lookup: This is a logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match.

VLOOKUP: Vertical (commonly used) HLOOKUP: Horizontal #N/A: NO MATCH EXACT MATCH: FALSE or 0 NEAREST/CLOSEST VALUE: TRUE or leave blank

2.8 Database Functions

A list is a worksheet range that contains data, with different variables occupying different columns headed by the variable name and different records occupying different rows. Database functions are used when you want to analyze values in a list (which is a term for a worksheet database). Most database function names start with letter D and are called Dfunctions.

Most database functions have the following arguments.

DATABASE: This is the range that contains the list. It must include row that holds the column labels.

FIELD: This is the label for the column you want to summarize. You can use the text of a column label or use a number that represents the position of the column.

CRITERIA: This is the range that has the criteria. It must include one column label and a minimum of one cell below that label.

2.9 Text Functions

Text functions allow you to manage data that has been entered in text format.

2.9.1 Using the Left Function

The LEFT function returns the first character or characters in a text string, based on the number of characters being specified. Left always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.=LEFT(Text,Num_chars)

a. Text is the text string that contains the characters being extracted.

b. Num_chars specifies the number of characters you want LEFT to extract. Num_chars must be greater than or equal to zero. If Num_chars is greater than the length of text, LEFT returns all of text. If Num_chars is omitted, it is assumed to be 1.

2.9.2 Using the Right Function

The RIGHT function returns the last character or characters in a text string, based on the number of characters being specified. Right always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.

=RIGHT(Text,Num_chars)

a. Text is the text string that contains the characters being extracted.

b. Num_chars specifies the number of characters you want RIGHT to extract. Num_chars must be greater than or equal to zero. If Num_chars is greater than the length of text, RIGHT returns all of text. If Num_chars is omitted, it is assumed to be 1.

2.10 Paste Special Options

Paste special feature enables you to narrow your choice of what to paste after you have performed a copy action. (Note: it does not work with cut.)

2.10.1 Using Paste Special

1. Select the cells you want to have special paste features.

2. Copy the data by either, press +C or Right-click-> Copy

3. Select the upper-left cell of your designated location.

4. Right-click and select the Paste Special command. The Paste Special dialogue box opens.

5. Select the appropriate Paste option.

6. Click [OK].The PASTE options within the Paste Special command:OptionDescription

ALLThis is the default option. It is the same as using the normal PASTE command.

FORMULAUse this option if you wish to copy only a formula but not the results or the formatting from the source cell.

VALUESThis option allows you to copy the results from a formula and not the formula itself.

FORMATSUse this option if you wish to copy only the formatting from a cell but not the contents.

COMMENTSAs the name implies, this option allows you to copy existing comments from one cell to another.

VALIDATIONThis option allows you to copy data validation rules defined in one cell to another cell.

ALL EXCEPT BORDERSUse this option if you wish to copy the contents and formatting of a cell but not any borders.

COLUMN WIDTHSThis option will apply column width to another cell but not the contents or the formatting

FORMULA and NUMBER FORMATSUse this option to paste only formula and all number format options but not font formatting.

VALUES and NUMBER FORMATSThis option will only paste the value and the number formatting from one cell to another.

In addition to the PASTE options, PASTE SPECIAL also contains the following operations options:OptionDescription

NONEAs the name implies, using this option means that no mathematical operation will be pasted.

ADDThis option will add together the values in the copied cells with the values in the destination cells.

SUBTRACTThis option will use the values from the destination cells and subtract that against the values in the copied cells.

MULTIPLYThis option will multiply the values in the copied cells with the values in the destination cells.

DIVIDEThis option will use the values from the destination cells and divide them against the values in the copied cells.

The SKIP BLANK option avoids replacing values in your paste area when blank cells occur in the copy area.

The TRANSPOSE option changes columns of copied data to row, and vice versa.

The PASTE LINKS options will link the pasted data to the active worksheet.

Learning Activity 1: Using Cell Referencing in Fomulae

Absolute Referencing

1. Open the workbook Advanced Excel Activities.xlsx and select the Ref Types sheet.

2. In C8 create a formula using cell references to convert the Singapore dollar amounts in A8 to US dollar.

3. Absolute the cell reference B7 and then copy the formula to C9:C11.

Mixed Referencing

1. Remain on the workbook Advanced Excel Activities.xlsx and select the Mixed Ref sheet.

2. In B4 create a formula using mixed referencing to multiply A4 against B3.

3. Copy the formula to the rest of the table.

4. Save the workbook.

Press F4 to change ref value.

Learning Activity 2: IF, AND, OR Function

IF, AND, OR Function

1. Open the workbook Advanced Excel Activities.xlsx

2. Select the YIELD sheet and in Column O12, create a function to display scrapped if FT YIELD value is equal or less than 79%. Otherwise display blank in the cells.

3. Save the workbook.

Learning Activity 3: SUM, SUMIF Function

SUM, SUMIF Function

1. Open the workbook Advanced Excel Activities.xlsx from the desktop folder Excel 2007.

2. Select the YIELD worksheet.

3. In cell H19, create SUM function to calculate the total TRACKINMAINQTY.

4. Select cell C2 and create SUMIF function that will return the TRACKINMAINQTY of those without holdcode.

5. Select cell C3 and create SUMIF function that will return the total TRACKINMAINQTY of EQPID 93K-84.

6. Save the workbook.

Learning Activity 4: ROUND, ROUND UP, ROUND DOWN Function

ROUND, ROUND UP, ROUND DOWN

1. Open the workbook Advanced Excel Activities.xlsx

2. Select the Rounding Functions sheet.

3. In cell C2, round the content of B2 to 2 decimal places.

4. In cell C4, round up the content of B4 to 2 decimal places.

5. In cell C6, round down the content of B6 to 2 decimal places.

6. In cell C8, round the content of B8 to the nearest WHOLE number.

7. Save the workbook.

Learning Activity 5: COUNT, COUNTIF Function

COUNT, COUNTIF Function

1. Open the workbook Advanced Excel Activities.xlsx

2. Select the YIELD worksheet.

3. In cell C4, insert a COUNTA function to count the total number of hold lots.

4. In cell C5, insert a COUNTBLANK function to count the total number of non-hold lots.

5. In cell C6, insert a COUNTIF function to count the total number of lots with hold code A9.

6. Save the workbook.

Learning Activity 6: Lookup Functions

VLookup Function

1. Open the workbook Advanced Excel Activities.xlsx

2. Select the Lookup worksheet.

3. In cell C3, create a VLookup that will return the Price of the Product Number in B3 against the table in E2:H11

4. Save the workbook.

LESSON 3: CHARTS

3.1 Creating a Standard Chart

3.1.1 Using the Insert tab

1. Highlight the cells (including the column headings and row labels) you want to include in a chart. If you want to highlight a non-contiguous range, highlight the first section with the mouse, and hold down the Ctrl key and click on any other cells.

2. From the Insert tab, select Column from the Charts group to create a column chart. Click on the other buttons to create other types of chart.

3.1.2 Changing Chart Type

After you have created a chart, you can easily change the chart type.

1. Select the chart that you want to change.

2. To change the chart type, either:

Click the Design tab from the Chart Tools contextual tab.

Click the [Change Chart Type] icon.

OR

Right-click on the Chart Area and select Change Chart Type.

3. From the resulting Chart Type dialogue box, select the chart you want to use.

4. Click [OK] when done.

3.2 Formatting Charts

After you have created a chart, Excel allows you to adjust the format of the chart.

3.2.1 Formatting Using the Chart Tools Tab

Excel 2007 consolidates all chart formatting options under the Chart Tool tab. This is a contextual tab that appears when you select a chart.

1. Select the chart to be modified

2. Select the Design tab.

This allows you to change the chart type, source and display of the data series, the location of the chart.

To change chart type,

a. Click on the Quick Layout group to quickly change the overall layout of the chart.

b. Click on Location group to move the chart.

3. Select the Layout tab.

This allows you to change the chart elements like chart and axis titles, data labels, use drawing tools or add text and pictures to the chart.

I. In the Labels group,

a. Click Chart Titles or Axis Titles to insert or modify the chart title, description for the various axes.

b. Click Legend to select the placement of the legend or not to display the legend at all.c. Click Data Labels to select the option to display the values of each series on the chart.

d. Click Data Tables to select the option to display the data used to plot the chart.

II. In the Axes group,

a. Click Axes to change formatting or layout of the axes.

b. Click Gridlines to select the option to display the types of gridlines of the axes on your chart.

4. To format the chart, select Format tab.

This allows you to add special effects, change line styles or add fill colors.

3.2.2 Format Text in a Chart

You can modify the font type, font size, etc. of any text on the chart. To change the formatting of text on the chart:

1. Right-click on the chart object or component that you want to modify.

2. Select Font.

3. Use the Font dialogue box and select the formatting you want to use.

4. Click [OK] when done.

Learning Activity 7: Creating and Formatting a Chart

Creating and Formatting a Chart

1. Open the workbook Advanced Excel Activities.xlsx

2. Select the Chart worksheet.

3. Add the data in cell A6:B10 to the Pie Chart

4. Create a Line-Column on 2 Axes chart using the data in A2:C10 and place the chart below the pie chart.

5. Display Data Labels for the Units series

6. Modify the Value Axis to display in thousands.

7. For Chart B, plot the Units series on a Secondary Value Axis.

8. Change the Units series to a Line Chart.

9. Add Axis Title to both Primary Value Axis and the Secondary Value Axis.

10. Modify the Primary Value Axis as follows: Minimum units set to 10,000 Major units set to 30,000

LESSON 4: ANALYSIS

4.1 Pivot Table

A Pivot table is an interactive table that you can use to quickly summarize large amounts of date. You can rotate its rows and columns to see different summaries of the source data, filter the data by displaying different pages, or display the details for areas of interest.

Use Pivot Table when you want to do the sorting, subtotaling, and totaling for you.

Rules to remembera. Purpose of the Pivot table in order to determine which fields to useb. Your list or database must contain headings or labels. This will be used by Excel to organize the layout of your Pivot tablec. There MUST NOT be any blank rows or columns in your databased. Do not insert sub-totals within the list as Excel will automatically generate sub-totals in the Pivot table. You may therefore end up double counting the datae. Do not worry about having a wrong layout. You can quickly change the layout of the Pivot table with a few mouse actions

4.1.1 Creating PivotTables

1. Open the workbook where you want to create the Pivot table report. If you are basing the report on a Microsoft Excel list or database, click a cell in the list or database

2. From the Insert tab, select PivotTable from the Tables group. Then click PivotTable

3. The Create PivotTable dialog is displayed. You can select the range of data to be analyzed or select data from an external source like Microsoft Access. Click on the option for Existing Worksheet if you want the pivot table to be in the same worksheet as the data, otherwise click New Worksheet. Then specify the starting cell reference of the location for the pivot table

4. Click [OK] to close the dialog box and following screen will appear.

4.1.2. Arranging Fields in the PivotTable

1. Select the fields to be included in the report from the PivotTable Field List task pane displayed on the right

2. Alternatively, you can drag the required field from the Field list and drop it in the Row or Column Labels text box just below the list

4.1.3 Change Summary FunctionsBy Default, Excel will summarize the data in the PivotTable with either a SUM or a COUNT function depending on the type of data you have selected.

You can change the field settings to select different functions. For example, instead of sum of Chip Quantity, you can display the count of Chip Quantity.

1. Click on the arrow beside the Sum of CHIP QUANTITY in the task pane

2. Select Field Settings

3. In the Value Field Settings dialog box displayed, choose the type of summary you want from the scroll list

4.2 Sorting a Database

4.2.1 To Sort by More Than One Column Simultaneously

1. Select a cell on a range you want to be sorted

2. To call out the Sort dialog box, either : Select the Editing group from the Home tab Select Sort & Filter and then click Custom Sort to display the Sort dialog box

OR Select the Data tab From the Sort and Filter group, click the [Sort] button and click Custom Sort

3. Select the column to sort in the Sort by box

4. Select the order you want to sort by in the Order box

5. Select the second field you want to sort by in the Then by box. Again, choose the order of sorting (Ascending or Descending) for this sub field

6. Repeat this step if you have a third field to sort by in the next Then by box. If need be, click on the [Add Level] button to increase the level of sort required

7. Click [OK] to sort the worksheet

Excel 2007 allows you to sort by up to 64 criteria

4.2.2 More Advanced Sort Options

Excel provides additional options to sort your list.

1. Select the [Options] button in the Sort dialog box

2. The following options are available in the Sort Options dialog box:

Case sensitive:Allows uppercase text to be sorted before lowercase

Orientation:Gives you the option to sort columns by using the values in the rows

4.3 Using Auto & Advanced Filter

Filtering is a quick and easy way to find and work with a subset of data in a list. A filtered list displays only the rows that meet the criteria you specify for a column. Excel provides two commands for filtering lists:

AutoFilter, which includes filter by selection, for simple criteria Advanced Filter, for more complex criteria

Unlike sorting, filtering does not rearrange a list but hides rows you do not want displayed

1. Click on the cell in the list you want to filter

2. To activate the filter, either : From the Home tab, select the Editing group Select Sort & Filter and then click Filter

Learning Activity 8: Creating PivotTables

Creating PivotTables

1. Open the workbook Advanced Excel Activities.xlsx

2. Select the List Management worksheet.

3. Use the Insert tab to create a PivotTable in a new worksheet

4. Arrange the PivotTable as follows:a. Drag the Sales Staff field to Row Labelsb. Drag the Products field to Column Labelsc. Drag the Revenue field to Values

5. Move the Sales Staff field to the Column Labels area and the Products field to the Row Labels area

6. Move the fields back

7. Select any one of the Revenue item and add a currency symbol to the numbers as follows: Display the Value Field Settings dialog box by either:a. Click the [Field Settings] button from the Active Field group on the PivotTable Tools, Design tab

OR

Right-click on any Revenue item Select Value Field Settings from the menu

OR

Click the Sum of Revenue in the Values area Select Value Field Settings from the menu

Click the [Number] button

Select either Currency or Accounting format

Click [OK] and [OK] again to complete the process

8. Double-click on any number to view the details

9. To view the details for any one club, perform the following:a. Move the Clubs field to the Report Filter

b. Select any club by clicking the drop-down arrow in the Clubs field heading in B1c. To select more than one club, click the drop down arrow in B1 and check the box next to Select Multiple itemsd. Remove the Clubs field by dragging the field heading away from the PivotTable Field List or uncheck the box next to ClubsLearning Activity 9: Sorting

Sorting

1. Open the workbook Advanced Excel Activities.xlsx

2. Select the Sorting worksheet.

3. In the worksheet, sort the data by Department, Gender and Age (Descending order)

4. Save the workbook into your folder and close when done

Learning Activity 10: Using AutoFilter

Using AutoFilter

1. Open the workbook Advanced Excel Activities.xlsx

2. Select the Filter worksheet.

3. Select any cell and activate the AutoFilter command

4. Filter for Chelsea and Hat

5. Create a customer filter to display only revenue items that are less than $6,000

6. Clear the filter when you are done

7. Filter the Clubs: Liverpool, Products: Key ring or Mug and Units: >=1500

8. Clear the filter

9. Filter for Clubs: Liverpool, Products: Calendar and Units: Between 1,000 to 2,000

LESSON 5: Collaborative Editing

1.1 Working with Comments

You can attach notes to cells by using comments

1. Click the cell that you want to add the comment

In the Review tab, select Comments group

The click [New comment]

OR

Right-click the cell and select Insert Comment

2. In the box, type the comment text. When you have finished, click outside the comment box

3. You will see a small red triangle marker on the top right-hand corner of the cell. Hover your mouse over the cell to display the comments

1.2 File Sharing

1. From the Review tab, click Share Workbook.

2. Turn on the simultaneous user feature in the Share Workbook dialog box by checking the first box in the Editing tab

Learning Activity 11: Comments

Comments

1. Open the workbook Advanced Excel Activities.xlsx

2. Select the Sales Data worksheet.

3. Show all comments

4. Hide all comments when done

5. Select cell B7 and amend the comment to Review performance next year

6. Select cell I24 and add a comment Formulae checked

7. Copy the comment in I9 to I17

8. Delete the comment in B27

9. Save the workbook when done