Excel 2010 Specialist ~ Functions & Pivots Version 1.0
This Excel advanced course is designed for proficient users of Excel who wish to expand their knowledge of building & applying functions to process and extract information from the data. Introducing Pivots and Slicers, Dynamic Named Ranges, Dependent Pick Lists are all covered in this course together with other useful tips. This manual is designed to be used in conjunction with training sessions delivered by Avon IM&T Consortium. http://nww.avon.nhs.uk/imtconsortium/Training
Avon IM&T Consortium
Contents Introduction ...........................................................................................1 How to use this Manual ........................................................................1 Further Training ....................................................................................1 1.
Chapter One – Formulae & Functions ........................................2 1.1
Text Functions .................................................................................... 2 a Concatenate ............................................................................................................ 2 b Left/Right/Mid ......................................................................................................... 4 c Proper ...................................................................................................................... 6 d Text .......................................................................................................................... 7 e Trim .......................................................................................................................... 8 f
1.2
Upper/Lower ........................................................................................................... 9
Statistical Functions ......................................................................... 10 a AverageA ............................................................................................................... 10 b AverageIF .............................................................................................................. 11 c Large ...................................................................................................................... 12 d MaxA/MinA ............................................................................................................ 13
1.3
Compatibility Function ..................................................................... 14 a Rank ....................................................................................................................... 14
1.4
Date & Time Functions ..................................................................... 15 a Networkdays ......................................................................................................... 15 b Date & Day Month Year ........................................................................................ 16 c Time & Hour Minute Second ............................................................................... 17 d Weekday ................................................................................................................ 18
1.5
Math & Trig Functions ...................................................................... 19 a RoundDown/RoundUp ......................................................................................... 19 b Subtotal ................................................................................................................. 20 c SumPoduct ........................................................................................................... 21 d INT .......................................................................................................................... 22 e MOD ....................................................................................................................... 23
1.6
Lookup & Reference ......................................................................... 24 a Index ...................................................................................................................... 24 b Indirect................................................................................................................... 25 c Match ..................................................................................................................... 26 d Offset ..................................................................................................................... 27 e Create a Dynamic Named Range ........................................................................ 28 f
1.7
Lookup, Hlookup, Vlookup .................................................................................. 29
Logical ............................................................................................... 30 2|Page
a AND ........................................................................................................................ 30 b Or ........................................................................................................................... 31 c IF ............................................................................................................................ 32
1.8
2.
3.
Absolute & Mixed Referencing ........................................................ 33
Chapter Two ~ Nesting Functions ............................................35 2.1
ROUND with SUM .............................................................................. 35
2.2
IF with AND ........................................................................................ 35
2.3
IF with OR .......................................................................................... 36
2.4
IF with IF ............................................................................................ 36
2.5
INDEX with MATCH ........................................................................... 37
2.6
VLOOKUP with MATCH .................................................................... 37
Chapter Three ~ Pivot Tables.................................................... 38 3.1
Create and Modify a Pivot Table ...................................................... 38
3.2
Adding a calculated field .................................................................. 41
3.3
PivotTable Options ........................................................................... 43
3.4
Slicers ................................................................................................ 44
3.5
Refresh the data ................................................................................ 45
3.6
Group the data .................................................................................. 45
3.7
Create a PivotChart ........................................................................... 47
3|Page
Introduction This manual has been created to support tutor-led training sessions provided by Avon IM&T Consortium. This advanced level course on Microsoft Excel 2010 consolidates existing knowledge and explores more complex data analysis functionality including combining functions, creating and editing pivot tables and displaying data in a pivotchart.
How to use this Manual Each Chapter begins with a table detailing the following:
Objectives
Notes explaining which exercise will be used if necessary
There is also a brief introduction to each new concept with illustrations to assist you with the familiarisation. Whenever you see a SmartArt graphic, this will take you step by step through a particular process, together with written instructions similar to the example below. Each blue chevron will have the name of a Tab, Group or Command to aid your understanding.
Home
• 1. Click on Tab
Font
• 2. Locate group
Bold
• 3. Select command
Further Training Training is provided for Microsoft Word, Excel, PowerPoint and Outlook. Students may choose from Introduction, Intermediate or Advanced level for each program. The training department will also provide IT problem solving support for individuals. For further details please contact Avon IM&T Consortium Training Department. Tel: 0117 900 2640 Online Bookings : ITtraining.administrator@aimtc.nhs.uk Web: http://nww.avon.nhs.uk/imtconsortium/Training/default.htm
Page |1
1.
Chapter One – Formulae & Functions
Objectives
Notes
Apply text functions to a database of clients
Exercise 1 – Text Functions
Apply statistical functions such as AVERAGEIF to extract information from data
Exercise 2 – Statistical functions
Utilise the DATE functions to perform calculations
Exercise 3 – Date/Time Functions
Employ some functions from the Math/Trig section for statistical analysis
Exercise 4 – Math/Trig Functions
Exercise 5 – Lookup & Reference Exercise 6 – Logical Functions Exercise 7 – Mixed Referencing
1.1
Text Functions a Concatenate
Description
Example
• The concatenate function allows two or more cells to be joined together • =CONCATENATE(A3,B3,C3) - this will join the three cells together
• =CONCATENATE(A3," ",C3) - this will add a space between Extension cells
• =CONCATENATE("Employee called ",A3) - this will preceed the contents of cell A3 with the words Employee called.
2|Page
3|Page
b Left/Right/Mid
Description
Example
Extension
• The LEFT function will extract a given number of characters on the left hand side of the cell contents • =LEFT(A3,1) - this will display the first character only from the cell contents • =LEFT(A3,2) - this will extract characters one and two from the text in cell A3
Example:
Description
Example
• The RIGHT function will extract a given number of characters from the right hand side of the cell contents
• =RIGHT(A1,1) - this will display the last character only from the cell contents
4|Page
Description
Example
• The MID function will extract a specified number of characters from a given start point of a cell
• =MID(A3,4,6) - this will display the six characters starting at character 4
Example
5|Page
c Proper
Description
Example
• The PROPER function will capitilize the first character of each word
• =PROPER(A3) - this will convert ,for example, sir douglas bader into Sir Douglas Bader
Example
6|Page
d Text
Description
• The TEXT function will convert the contents of the cell to text with a specified format
Example
• =TEXT(A1,"dd mm yy") - this will convert the contents of cell A1 to be displayed ,for example, 19 Sep 11
• =TEXT(A1,"0.0") - this would display the contents of cell A1 to show with one decimal place. eg 123.45 would be displayed in a text format as 123.5 Extension
Example
7|Page
e Trim
• The TRIM function will remove all trailing and leading spaces and also extra spaces between words but leave one Description
• =TRIM(A1) - this will check the contents of cell A1 for extraneous spaces Example
Example
8|Page
f
Upper/Lower
• The UPPER function will display the contents of a cell as capital letters. LOWER will display all the characters as small letters. Description
• =UPPER(A1) - this will convert A1 to capital letters. For example Tim Berners-Lee would be displayed as TIM BERNERS-LEE Example
Example
9|Page
1.2
Statistical Functions a AverageA
Description
• The AVERAGEA function will calculate the mean or average of a set of figures but will include a textual entry and assume a 0 value • =AVERAGEA(B2:B5)
Example
Extension
• If the data includes the words TRUE or FALSE, true entries will be calculated as 1 and false entries as zero.
Examples
10 | P a g e
b AverageIF
Descripti on
Example
Extension
• The AVERAGEIF function will calculate the mean or average of a set of figures if the data matches a given criteria • =AVERAGEIF(B2:B5,"East",C2:C5) • If the word that is being matched eg East is not an exact match, ie it may be displayed as Bristol East, a wildcard *East can be used to locate any occurances of the word that may be part of a phrase.
Examples
11 | P a g e
c Large
Description
• The LARGE function will calculate the placing in a list of figures
Example
• =LARGE(B2:B5,1) - this will display the highest (largest) number in the range.
Extension
• =LARGE(B2:B5,5) - this will display the fifth highest number in the range
Example
12 | P a g e
d MaxA/MinA
Description
• The MAXA function will display the highest figure in the range but will also include text and logical values. Text and False will be regarded as 0 and True as 1 • =MAXA(B2:B5)
Example
Extension
• =MINA(B2:B6) - this will display the smallest value which will be 0 if text is present
Example
13 | P a g e
1.3
Compatibility Function a Rank
Description
Example
• The RANK function will calculate where a figure is ordered in a range of figures • =RANK(B2,B2:B5,1) - the third section will display whether the order is descending (0) or ascending (not 0)
• If the order parameter is omitted, Excel assumes descending (0) Extension
Example
14 | P a g e
1.4
Date & Time Functions a Networkdays
Descripti on
Example
Extension
•The NETWORKDAY function will calculate the difference between two dates but not include weekends
•=NETWORKDAYS(A2,B2) - the first reference is the start date and the second reference is the end date
•Any other 'rest' days can be written as a list elsewhere and referenced as a third criteria
Example
15 | P a g e
b Date & Day Month Year
Descripti on
•The DATE function will compile a date from the separated components
•=DATE(A1,B1,C1) - the order of the components is YEAR,MONTH,DAY Example
Extension
•The DATE function can be used in conjunction with the individual functions YEAR, MONTH, DAY to perform calculations
Example
16 | P a g e
c Time & Hour Minute Second
Descript ion
•The TIME function constructs time from separated component parts
•=TIME(A1,B1) - the first reference is the hour part of the time and the second reference is the minute. Reference to the seconds can be added as a third Example criteria
Extension
•The TIME function can be used in conjunction with HOUR MINUTE SECOND functions to perform calculations
Example
17 | P a g e
d Weekday
•The WEEKDAY function will display the day of the week from a given date Description
Example
Extension
•=WEEKDAY(A1) - the result will display by default a number from 1 to 7 representing Sun to Sat
•A second value in the brackets can be added to alter the number representing which day
Example
If you use the Weekday function with the [return_type] argument set to 1, then you can use Excel formatting to display the weekday names instead of showing numbers. To format the weekday to show the weekday name:
Select the column containing the weekdays
Right click on this column with the mouse and select Format_Cells... from this menu
Ensure the Number tab is selected select the Category Custom
Under the Type heading, type ddd
Click OK
Note, this only works with the [return_type] set to 1. 18 | P a g e
1.5
Math & Trig Functions a RoundDown/RoundUp
Description
Example
Extension
•The ROUNDDOWN function will round a value down to set number of decimal places
•=ROUNDDOWN(B2,0) - the number in the brackets specifies the number of decimal places
•The ROUNDUP function will perform a similar function in reverse ie round up to the specified number of places
Example
19 | P a g e
b Subtotal Value
• The Subtotal function will calculate a specified function in a filtered list Description
Example
• =SUBTOTAL(3,A2:A56) - the number represents a function which is explained in the table shown here
Explanation
1
AVERAGE
2
COUNT
3
COUNTA
4
MAX
5
MIN
6
PRODUCT
7
STDEV
8
STDEVP
9
SUM
10
VAR
11
VARP
1 – li st if
2
1 – li 2 st if
The list has been filtered SUBTTOTAL is used to in this example count (3) the number of entries. This will mean that the hidden rows will not be counted which would have happened if the subtotal feature had not been used. 20 | P a g e
c SumPoduct
•The SUMPRODUCT function will multiply given ranges of corresponding values and then total the results. Description
•=SUMPRODUCT(A1:B2,D1:E2) - in the example below the sumproduct function carries out all the tasks displayed in yellow. Example
Example
21 | P a g e
d INT
•The INT function will display only the whole numbers of a calculation and not show the remainders. Description
•=INT(25/7)) - in this example the value of 3 would be returned as 25 divided by 7 is 3 remainder 4. Example
Example
22 | P a g e
e MOD
•The MOD function will display the remainder from a calculation. Description
•=MOD(25/7) - in this example the result of 4 would be displayed as 25 when divided by 7 is 3 remainder 4. Example
Example
23 | P a g e
1.6
Lookup & Reference a Index
• The INDEX function return a value or cell reference from a range. Description
• =INDEX(A1:D5,1,2) - the numbers represent firstly the row and then the column Example
Example In the example below the Index function in cell A4 returns the value 8 from row 3 and column 4 as requested.
24 | P a g e
b Indirect
• The INDIRECT function returns the value from the given textual string. Description
• =INDIRECT("A5") - the number 6 is displayed as the content of cell B3 Example
Example
25 | P a g e
c Match
Description
Example
• The MATCH function searches for a given item in a list and returns the relative position of that item • =MATCH(Balloon,A2:A5) - the function is searching for the word balloon in that range. • An optional third section is defining the "Match type". This could be -1, 0, or 1. These options are described below:
Extension
1
find the largest value less than or equal to lookup_value (the list must be in ascending order)
0
find the first value exactly equal to lookup_value. Lookup_array (the list can be in any order)
-1
find the smallest value greater than or equal to lookup_value. (the list must be in descending order)
26 | P a g e
d Offset
Description
• The OFFSET function returns a reference to a range that is offset a number of rows and columns from another range or cell
• =OFFSET(A1,1,3,1,1) Example
Start cell
Rows
Columns
=offset(A1,1,3,1,1) Height
Width
Example In this example below, OFFSET has been used to create a running total. If the SUM function is used and a row is deleted, a #REF! error message will appear but OFFSET will adapt accordingly.
27 | P a g e
e Create a Dynamic Named Range
Descripti on
Example
1 2
• The OFFSET function can be used with COUNTA to create a dynamic named range. As new items are added the range will automatically expand. • =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
• Formulas, Defined Names, Name Manager, New
• Type in a name, adapt the above formula to suit your spreadsheet
Note: Dynamic named ranges will not appear in the Name Box dropdown list. However, you can type the Excel names in the Name Box, to select the range on the worksheet.
This example will allow the user to add further rows to the range. If the user requires further columns replace the $A:$A with: COUNTA(Sheet1!$1:$1)
28 | P a g e
f
Description
Example
Lookup, Hlookup, Vlookup • The LOOKUP function will find a given value from a specified range and then return a value from a specified row or column within that range. • =HLOOKUP(C6,A1:E2,2) - the horizontal lookup function was used here because the labels for the lookup range are arranged across the page.
• The Vlookup function would be employed when the labels of the lookup range are arranged vertically. Extension
Example
29 | P a g e
1.7
Logical a AND
Descripti on
Example
Extension
• The AND function will look at two conditions and return TRUE if they both are correct or FALSE if either one are incorrect. • =AND(C7>20,D7>20)
• The AND function can contain up to 254 arguments.
Example
30 | P a g e
b Or
Description
• The OR function will look at two or more conditions and if either of them are correct will return TRUE. If neither are correct, FALSE will be returned
• =OR(A1<20,B1<1) Example
Example
31 | P a g e
c IF
Description
• The IF function makes an evaluation or logical test and performs one of two different calculations based on the result.
• =IF(A2>0,"Profit","Loss") Example
Some of the comparison operators you can use are: = Equal to (or the same as) > Greater than < Less than >= Greater than or equal to <= Less than or equal to <> Not equal to
Description
• The IF function can support further IF functions within one statement.
• =IF(A2>0,"Profit",IF(A2>-10,"Breach","OK") Example
32 | P a g e
1.8
Absolute & Mixed Referencing When autofill is employed, the cell references automatically alter (relative referencing) but there may be occurrences where you wish one of the references to remain stationary (absolute). 1 – li 2 st – if li 3 st 3 if – li st if
In this example, the formula is entered into Cell C6 and multiplied by the VAT rate in cell D2 (=C5*D2). Cell D2 is made ‘absolute’ by the addition of $ symbols before each part of the cell reference (=C5*$D$2) It is now possible to autofill cell C6 to cell D6 as the VAT rate is anchored in place (=D5*$D$2)
2 – li st if
1 – li st if
3 3 – li st if
To anchor a reference:
Typing a dollar sign before each co-ordinate (for example $C$3)
Ensure the cursor is just to the left of the reference and press the [F4] key.
Mixed Referencing You may sometimes want a cell reference to be partially absolute or mixed.
A mixed cell reference can be specified in either of the following ways:
by typing a dollar sign before the appropriate co-ordinate
or by the pressing the [F4] key a second or third time. 33 | P a g e
Pressing [F4] cycles a relative cell reference though the following permutations:
$A$1
(Column and row absolute)
A$1
(Column relative and row absolute)
$A1
(Column absolute and row relative)
A1
(Column and row relative)
Example In the example below, one formula is entered in cell B3 which can be auto filled both vertically and horizontally to fill in the cells.
34 | P a g e
2.
Chapter Two ~ Nesting Functions Nesting functions is the joining of two or more functions together in one cell to provide a more versatile solution. It works by replacing one ‘argument’ of the function with another function. In Excel 2003, up to 7 levels of nested functions were allowed but in 2010, up to 64 functions can be nested! When evaluating nested functions, Excel calculates the deepest or innermost function first and then works its way outwards.
2.1
ROUND with SUM The SUM function has been nested inside the ROUND function to ensure the result is rounded to 2 decimal places.
2.2
IF with AND The IF function can be combined with the AND function to ensure that two criteria are met to allow the TRUE option to be displayed. In this example the status of the appointment must be finished and time between booking the appointment and the appointment taking place must be more than 2 (days) in order for a ‘Breach’ to be displayed.
35 | P a g e
2.3
IF with OR When OR is used with IF, either one of the criteria must be met in order for the equation to display the TRUE value. In this example below the student must obtain 800 in any subject to be awarded the 50 points bonus.
2.4
IF with IF The IF function can be combined with more IFâ&#x20AC;&#x2122;s (nested ifâ&#x20AC;&#x2122;s) to explore complex data criteria and return TRUE or FALSE answers. In the extract below, the IF statement checks first to see if there is an appointment date entry, then to see if there is a referral date entry and if both cells are completed takes one date away from the other.
36 | P a g e
2.5
INDEX with MATCH The example below is enabling the user to type in the Assignment number of the employee into cell C8 and Excel will automatically populate the name and address fields in the cells below. Using these two functions has an advantage over using LOOKUP as the data does not have to be arranged in alphabetical order and the TRUE FALSE feature of LOOKUP does not have to be used.
2.6
VLOOKUP with MATCH Instead of specifying the exact row to return when using the lookup function, the MATCH function could be used instead to pick up a key word perhaps a column heading
=VLOOKUP(A2,A1:D5,MATCH(C9,A1:D1,0))
37 | P a g e
3.
Chapter Three ~ Pivot Tables
3.1
Create and Modify a Pivot Table A pivot table is an interactive table that summarises worksheet data according to user specifications. A PivotTable can be created either in the worksheet containing the data to be analysed or in a worksheet of its own. To create a pivot table:
With the appropriate workbook open, ensure the cursor is flashing in the middle of the data to ‘pivot’.
From the INSERT menu, select the Tables group and then PivotTable
The first dialog box is where the table range or external source is specified.
Specify the location for the PivotTable – either on the current worksheet or on a new worksheet.
The PivotTable appears on the left hand side – currently empty
The PivotTable field list appears on the right hand side
38 | P a g e
Select the data fields that are required in the PivotTable by ticking the check box.
Whichever field is selected first, the wizard will place this item as a row label.
Selecting a numeric field will automatically move this to the Values field
The filter list can be used to select in this case only certain months
The expand (+) or contract (-) symbol can hide or show the options
These options can all be hidden using the SHOW group on the PivotTable Tools tab (options)
39 | P a g e
The numbers can be formatted by selecting the Value Field Settings, Number Format
Suppose the data was required to be displayed by ‘the number of staff who worked overtime’ instead of ‘the total number of hours worked’:
The values section now displays the ‘Name’ field in the ‘Count’ format.
The ‘Hours’ field has been removed by ‘un-checking’ the appropriate field
By double clicking on the figure it will display the names of the staff.
40 | P a g e
3.2
Adding a calculated field
Select the calculations option from the Options tab on the PivotTable tools. Choose Fields, Items & Sets Select Calculated Field
Calculations
Fields, Items & Sets
Calculated Field
Name the new field Insert the Formula Insert the field into the Formula
41 | P a g e
The new field is inserted into the PivotTable Filter the data
In order to display selected data only, select the field and drop the field into the Filter area.
In this example the PivotTable is filtered by date – only April dates are being displayed
42 | P a g e
3.3
PivotTable Options Another useful area for specifying the display of data is the Options. This is located in the Options tab, PivotTable, then options.
43 | P a g e
3.4
Slicers Slicers are easy-to-use filtering components that contain a set of buttons that enable you to quickly filter the data in a PivotTable report, without the need to open drop-down lists to find the items that you want to filter. When you use a regular PivotTable report filter to filter on multiple items, the filter indicates only that multiple items are filtered, and you have to open a drop-down list to find the filtering details. However, a slicer clearly labels the filter that is applied and provides details so that you can easily understand the data that is displayed in the filtered PivotTable report.
To insert a slicer
From the Sort & Filter group, select Slicer Select the field(s) to slice The filter list is displayed Select a name from the list and the pivottable which just display that person’s information
44 | P a g e
3.5
Refresh the data If the source data is edited, the PivotTable does not automatically update and therefore the user will need to select the REFRESH option from the Options tab.
3.6
Group the data The data can be grouped by highlighting the section and then selecting GROUP from the OPTIONS tab.
To group items in a number field
Right click the field Choose Group Select the interval required Click OK
To group the items in a Date field
Right-click the Date field button. Choose Group Select the grouping option eg Months To limit the dates that are grouped, you can set a Start and End date, by typing the dates in the 'Starting at' and 'Ending at' boxes Click OK to close the dialog box.
45 | P a g e
To group the items in a Date field by week
Right-click the Date field button. Choose Group In the Grouping dialog box, select Days from the 'By' list. For 'Number of days', select 7 The week range is determined by the date in the 'Starting at' box, so adjust this if necessary. Click OK
To group the text items The Grouping dialog box isn't available if you try to group items in a text field. Instead, you can manually group the items:
Select the items that you want to group On the Ribbon's Options tab, click Group, then click Group Selection
To rename the group:
Click on the heading cells with the default name, e.g. Group1
Count Unique Items In an Excel pivot table, you may want to know how many unique patients received a vaccination instead of how many vaccinations took place. A pivot table won't calculate a unique count. However, you could add a column to the database, then add that field to the PivotTable. For example, to count the number of patients who received a vaccination, add a column to your database, with the heading 'CustItem'
In the first data row, enter a formula that refers to the patient ID (E2) and Vaccination (A2) columns. For example:
=IF(SUMPRODUCT(($E$2:$E2=E2)*($A$2:$A2=A2))>1,0,1) 46 | P a g e
Copy the formula down to all rows in the database.
Then, add the field to the data area of the Excel pivot table.
3.7
In this example there were 7 Hep B vaccinations administered to 5 individuals. Two patients had the same vaccination twice.
Create a PivotChart PivotCharts are interactive graphs where data can be filtered, grouped and selected or not as required. Although PivotCharts can be created from scratch, it may be easier to first create a PivotTable and then from there create a graph from the Table. Amendments made to the Table will then be reflected on the Chart.
Create the PivotTable
Select the PivotChart icon
Amend the PivotTable to alter the chart
Use the PivotChart tabs to alter the design and formatting
47 | P a g e