Www.core-ct.state.ct.us



Notes to the Intermediate and Advanced Excel Class for EPM Users

Presented to DOT March 17 and 24, 2011

Table of Contents

Relative and Absolute references and Named Ranges 2

What is a relative reference? 2

What is an absolute reference? 2

What is a Named Range? 2

Advantages of using Named Ranges 3

Rules for Named Ranges 3

Using the Name Box 3

To Create a Named Range 4

Functions 4

COUNTIF 4

SUMIF 5

VLOOKUP 5

SUBTOTAL 7

Sorting 8

Subtotaling 9

Import 12

Pivot Tables 13

Background Information 13

Basic Pivot Information 14

Modifying Data 15

Formatting 15

Advanced Filter to find a subset of data 17

Advanced Filter to find unique values 18

Database Functions 19

SUMPRODUCT 20

Array Formulae 22

Relative and Absolute references and Named Ranges

What is a relative reference?

A relative reference occurs when a cell is used in a formula without locking its position using the $ symbol. A relative reference means that Excel will change where the cell your formula is pointing to if you copy or expand the cell containing the formula.

For example, =A1

When you copy this formula to another cell, Excel automatically adjusts the cell reference to refer to different cells relative to the position of the formula.

When you enter a formula, you enter a specific cell reference (for example, in cell C1 you type =A1*B1) Behind the scenes, Excel translates those references into positions relative to the result cell (C1). So, the formula =A1+B1 is translated as =TwoCellsToTheLeft * OneCellToTheLeft. You copy down the formula, Excel uses its translation to generate results.

What is an absolute reference?

An absolute (or locked) reference occurs when a cell is entered in a formula using the $ symbol.

For example:

=$A$1 - Locks the field

=A$1 - Locks the row

=$A1 - Locks the column

Each component of the cell reference can either be defined as absolute or relative. Highlighting the cell and pressing the F4 key repeatedly will cycle you through the absolute reference scenarios. An absolute reference means that the referenced cell will not change when you change the position of the cell with the formula.

A named range is an example of an absolute reference. The major difference is that a named range may referenced easily from anywhere in a spreadsheet simply by typing the range name in a formula or by pressing F3. F3 displays the list of named ranges you have created.

What is a Named Range?

o A named range is a short text description that can be used instead of the cell address to refer to individual cells or ranges of cells.

o Providing cells with actual descriptive names can be used to simplify formulas and make them much more user friendly.

o Descriptive names are also a lot easier to remember that actual cell addresses.

o You can also give descriptive names to constant values as well as formulas.

o Any named ranges that are created are workbook specific and can only be used in the workbook they are created in.

o You can also use the shortcut key (F3) to display the (Insert > Name > Paste) dialog box when editing a cell.

o Named ranges can be given to cells containing both numerical and text values.

Advantages of using Named Ranges

In addition to providing an alternative to repeatedly typing in cell addresses and cell ranges, using named ranges have several other advantages.

o They improve readability and make your formulas much easier to understand meaning there is less chance of errors.

o Moving or shifting cells that have a named range means that the formulas are adjusted automatically. There is no need to worry about whether the formulas use absolute or relative references.

o Inserting and deleting cells, rows or columns will not change the location of your named ranges. Moving cells, rows or columns will though.

o Typing a descriptive name is much easier than remembering a specific cell address, therefore simplifying your formulas.

o Allows you to define Named Constants which are single, frequently used values.

Rules for Named Ranges

o A named range can be up to 255 characters long and can contain letters, numbers, periods and underscores (no spaces or special punctuation characters).

o Named ranges are not case sensitive and they can contain both upper and lower case letters. They cannot resemble any actual cell addresses such as "B3" or "AA12".

o All named ranges must begin with a letter, an underscore "_" or a backslash "\".

o Named ranges can include numbers but cannot include any spaces.

o You cannot use any symbols except for an underscore and a full stop”.”. It is possible to include a question mark as long as it is not the first character.

o Named ranges can be just single letters with the exception of the letters R and C.

o When you add a named range it is the cell that is named and not the cell contents.

o By default named ranges are created as absolute references.

o It is possible for a cell (or range) to have more than one named range so typing a new name using the Name Box will not change the named range but will create a new one.

Using the Name Box

o The Name Box is basically a shortcut for creating and inserting named ranges.

o A more comprehensive method is to use the (Insert > Name) submenu it is very useful for moving to different parts of a worksheet.

o The drop-down list to the right of the Name Box allows you to find and select the named ranges.

o You can quickly insert named ranges into your formulas by pressing F3 while editing in the formula bar.

o If you highlight a range of cells and this corresponds exactly to a named range then the name is displayed in the Name Box.

To Create a Named Range

o Ctl-Shift-arrow key (down usually) to highlight the range you want to define (include the header)

o Ctl-Shift-F3 to open the Create Names dialog box

o Click OK

o Click the dropdown arrow from the Name Box to see the result (the Create Names dialog box will modify by adding underscores to any header that has two or more words in their title)

o [pic]

For more information, go to:

Functions

COUNTIF

Syntax

=COUNTIF(RangeOfThingsToCount, WhatToCount)

Example: =COUNTIF(A1:A45,”11000”)

What to count can be

¤ Cell Reference (e.g., A1)

¤ “Word(s)”

¤ formula – “>=100”

Allows the use of Wildcards

=COUNTIF(range, “A*”) will find all things starting with A

=COUNTIF(range, “*A*”) will find all things with A in it

=COUNTIF(range, “??A*”) will all things with A in the third position followed by anything.

* = any number of spaces

? = one space

Excel 2007, 2010 has COUNTIFS for use with multiple criteria (AND criteria only)

SUMIF

Syntax

=SUMIF(RangeToBeExamined,CriteriaToMatch,RangeToTotal)

Example: =SUMIF(A1:A45,”11000”,B1:B45)

Range to be examined – a column

Criteria to match can be:

¤ Cell Reference (e.g., A1)

¤ “Word(s)”

¤ formula – “>=100”

Range to Total – a column

Note: Excel 2007 has SUMIFS for multiple criteria (AND criteria only)

VLOOKUP

The VLOOKUP function scans down the row headings at the side of a table to find a specified item. When it is found, it then scans across the row to pick a cell entry based on its column designation in the table. It is made up of four parts: (1) The item to find; (2) a lookup table of two or more columns where the “item to find” makes up the left-most column and information to be returned makes up the columns to the right; (3) a number (usually 2 or greater) that indicates the column where the information to be returned is located; (4) TRUE or FALSE where FALSE indicates an exact match is required and TRUE indicates that the list is sorted and the match will be exact or the next lowest value from the “item to find.”

=VLOOKUP(ItemToFind,RangeTo LookIn,ColumnToPickFrom,SortedOrUnsorted)

Example: =VLOOKUP(A1,E19:F66,2,FALSE)

A1 = Item to Find

E19:F66 = The Range to look in to find the item This can be named range (highlight the table, in the Name Box give it a name, press Enter. If you decide to keep just the cell reference (e.g., E19:F66) make sure to lock the reference using the F4 key.

2 = Once the item is found select the item in this column. This is the ‘nth’ column in the table

FALSE = An exact match is required (Assumes unsorted, but the list may be sorted)

TRUE would indicate an exact match is not required. Finds an exact value or the next lowest value. (Assumes sorted, and the function will fail if the reference list is unsorted)

[pic]

Use VLOOKUP to match one item with an associated item.

[pic]

VLOOKUP Troubleshooting

Formula is Displayed Instead of Calculating a Value in Excel

When you type your formula into a cell, and you see the formula instead of a value, it means one of two things:

You are viewing formulas. Just hit Ctrl+` (that's the accent mark near your Esc key on your keyboard). Most people call it CTL-~ (tilde)

Your cells are formatted as text.

Highlight the affected cells

Ctl-1 to open the Format Cells dialog box

Select General from the Number tab

Click OK

In the formula bar, copy and paste your formula.

Press Enter

SUBTOTAL

Syntax

=Subtotal(method,range1,range2,…,range_n)

Method is the subtotal that you'd like to create.

It can be one of the values listed below.

Range1,range2…,range_n are the ranges of cells that you want to subtotal.

|Value |Calculation |Method Description |

|1 |AVERAGE |Adds all entries and then divides by the number of entries |

|2 |COUNT |Counts the number of entries containing numbers |

|3 |COUNTA |Counts the number of entries that are not blank (includes text entries) |

|4 |MAX |Reports the highest number of all the entries |

|5 |MIN |Reports the lowest number of all the entries |

|6 |PRODUCT |Multiplies all the entries together |

|7 |STDEV |Computes the standard deviation, assuming the selection is a sample of the entire population |

|8 |STDEVP |Computes the standard deviation, assuming the selection is the entire population |

|9 |SUM |Adds all entries together |

|10 |VAR |Computes the variance, assuming the selection is a sample of the entire population |

|11 |VARP |Computes the variance, assuming the selection is the entire population |

SUBTOTAL

SUBTOTAL allows the user to subtotal a table of data at one or more levels by sum, count, or other defined method. It is required that the table of data being subtotaled is first sorted so that it is in the order the user wants to subtotal.

[pic]

Sorting

Sorting is accomplished first by placing the cursor anywhere inside the table of data (Excel will determine the boundaries of the table).

Navigation: Data > Sort. The Sort dialog box opens. (In Excel 97-2003 you are allowed 3 sort levels)

[pic]

[pic]

Repeat this action for all numbers that are being treated as text.

Once the data is sorted, it may be subtotaled. Place the cursor inside the table.

Subtotaling

Navigation: Data > Subtotal. The Subtotal dialog box displays.

The data to be subtotaled must match the sort criteria. In the example, the sort criteria was Fund – SID – Acct.

[pic]

One the user clicks OK, Excel creates the Subtotal and adds a summary control area to the left of the spreadsheet. This allows the user to control which summary is displayed.

[pic]

All that is required to add a different subtotal method (e.g., Count) while retaining the original subtotal is to repeat the subtotal process, select the new method, and uncheck the “Replace current subtotals” checkbox. Click OK.

Additionally, the same applies to adding the same subtotal method to different data. Select the next column in the sort hierarchy; uncheck the “Replace current subtotals” checkbox. Click OK.

[pic]

When an additional subtotal layer is added, the Summary Control will expand by the additional layer. This allows the user to control the level of detail that is displayed.

[pic]

In this example:

Summary 1 will show just the grand total

[pic]

Summary 2 will show just the Fund subtotal. In this example, it also shows the SID subtotals to two funds. This is because there are no changes at the SID level.

[pic]

Summary 3 shows the Summary 2 subtotals and also the Summary 3 subtotals (in this example, Fund and SID)

[pic]

Summary 4 shows all of the detail along with the related subtotals.

To remove all of the subtotals, navigate to the Subtotal dialog box and click the Remove All icon.

Import

Not all Excel spreadsheets are created by entering data. Sometimes, the data already exists and all that is required is to import it so it can be analyzed. To make the process easier, Excel has a ‘wizard’ that walks the user through the steps. The data can be either in a CSV (comma separated value) or TXT (text) format.

Navigation: Data > Import External Data > Import Data.

The Select Data Sources dialog box displays. Navigate to the file location of the data to be imported; click on the file; click Open. The Text Import Wizard dialog box displays.

Step 1: Choose the file type that describes your data

Delimited: These are characters that act as field separators (This is the default setting and is true of CSV and most TXT files).

Fixed Width: The user determines where the field separation occurs.

Click Next

Step 2: Select the correct delimiter or determine the column widths

The default is Tab, but most CSV and TXT files use a comma separator. Generally, the instruction will be to uncheck the Tab checkbox and check the comma checkbox. Depending on the nature of the list being imported the user can select his own defined delimiter by checking the Other checkbox and typing a delimiter (example, @ if the list is email addresses). The only rule is that the delimiter must apply to all the rows.

Click Next

Step 3: Column Formatting. Excel wants to interpret the data the way it looks. If something looks like text (words, mixed words and numbers) Excel wants to treat it as text. If something looks like a number (numbers, numbers with leading zeros) it wants to treat it as a number. If it looks like a date then Excel wants to treat it as a date. If this is true, then the user doesn’t have to do anything and can leave the formats as General. However, this isn’t always true.

Click the column to be formatted and select the format.

General: Excel will determine how the data should be interpreted.

Text: (Vendor, Voucher, some fields text-others numbers) Used on any column where it is important to retain leading zeros.

Date: For fields that have a number format that should be formatted as a date. (example: the number 40000 formatted as a date represents 7/6/2009).

Do not import column: Column will not be imported.

Click Finish. The Import Data dialog displays. This is used to determine where on the spreadsheet the import will begin. The default is cell A1. This can be changed.

Click OK. Excel will import the data.

Pivot Tables

Background Information

Pivot tables are a way of summarizing tabular data by use of subtotals and other calculations where the user can choose the display parameters. In this way, large tables of data can be organized so that it can be easily reviewed and relationships identified that might otherwise be hard to see.

Create a Pivot Table (Excel 97-2003)

Place the cursor anywhere in the table to be pivoted.

Navigation: Data > PivotTable and PivotChart Report. The PivotTable and PivotChart Wizard displays.

Step 1: Microsoft Excel List or Database and PivotTable are selected by default. Click Next.

Step 2: So long as the cursor has been placed inside the table, Excel will determine the border using the marching ants as an outline. If this is correct, click Next.

Step 3: New Worksheet is selected by default. There are two links on this page (Layout and Options ~ these will be discussed later). Click Finish. The pivot table is created and the PivotTable Field List is displayed. The pivot table is drag and drop to add or remove items.

The following terms are used in PivotTable reports:

o Field: The header at the top of a column in a List (data source table).

o Item: Numeric data or text in the Field column.

o Data: An area detailing the data in the lower part of the PivotTable report, including columns with numeric data.

o Row Field: A field that is positioned as a row in the lower left of the PivotTable report.

o Column Field: A field that is positioned as a column in the row above the data in the PivotTable report.

o Page Field: A field that is positioned in the upper left of the PivotTable report.

Example of a Data Cube

Once constructed, the data has three dimensions

¤ X Axis

¤ Y Axis

¤ Drill Down to details

Basic Pivot Information

Pivot Wizard (part 3) and Layout button (right click > wizard > layout)

[pic] Data: shows the results you want to see

[pic] Column: shows results of each value in its own column

[pic] Row: shows results of each value in its own row

[pic] Page: allows you to filter the values of the table based on the values you select.

Options button (right click > wizard > options)

[pic] Allows you to personalize how the data will look.

Blank table: Fields are drag and drop

[pic] Format the Data field (comma format)

[pic] Reorder the fields: click and drag field to the left (box, I-beam)

View the underlying data (Drill down)

[pic] Double-click the field of interest

Filtering

[pic] Drop down for Row and column – multiple filter options

[pic] Page: Single Option

[pic] Page: Pivot Table > Show Pages (creates a pivot table for each Page topic)

Modifying Data

Update the Data [pic]

[pic] Change Source Table then go back to the pivot and click the exclamation. If you add a column, do so inside the table. This way you don’t have to redefine the table.

Calculated Fields

[pic] Pivot Table > Formulas > Calculated Fields

[pic] Name: Overhead?

[pic] Select Field from list > Insert field

[pic] Insert calculation: *.05

[pic] This creates an additional field that can be filtered

Grouping Data (usually used for dates)

[pic] Right Click (header) > Group and Show Detail > Group (Group dialog opens)

[pic] You can select single or combinations (eg, month, quarter, year)

[pic] This creates an additional field that can be filtered

Ad hoc Grouping

[pic] Mouse over the boundary of the multi-column or multi-row until your mouse pointer looks like a right pointing arrow, then click. All the data that meets the criteria will be highlighted.

Add the Sum Of field twice: change one to Count of

[pic] Right click > Field Settings > Select Count

[pic] Right Click > Field Settings > Name (rename field)

Formatting

Pivot Chart

[pic] Right click > Pivot Chart

Format Report

[pic] Single Data Column (a true reformat)

[pic] Multi Data column (Format in place – it makes the pivot data look prettier)

Show Pages

Pivot Menu > Show Pages > Select Item > Click OK

Showing percents

[pic] Field Setting > Number > Percentage > OK > Options > Show Data As (dropdown) > % of Total > OK

Hiding and Showing Detail

[pic] Double click the row or column item of interest (not the header). The Show Detail dialog box opens. Select the detail you want to show, and click OK. The item selected becomes a new column or row. Drag back to the Pivot Table Field List once it is no longer needed.

Other Menus

[pic] Double click the Row, Column or Data header to display the Pivot Table Field dialog box. Your choices are

Subtotals (Auto, Custom, None)

Advanced (Auto Sort, Top 10)

Layout (Display and Print options)

Number (if a date field

Pivot Menu

[pic] Right click the menu bar and click the PivotTable entry. By dragging the PivotTable menu to the master menu, the pivot table menu can be anchored so it is always visible when needed.

Pivot Charts

[pic] Not all pivoted data will lend itself to charting. By clicking the PivotTable dropdown menu and selecting PivotChart (second line), the user can experiment with chart presentation.

Advanced Filter to find a subset of data

In practice, the Advanced Filter behaves most like a database function (see the Database Functions section for a full explanation). It asks explicitly for a data source and a separate Criteria area. The Field of interest is contained in the Criteria area.

Notes:

o AND/OR: Criteria on the same row constitute the AND criteria. Criteria on different rows constitute the OR criteria. = is assumed. Other operators include: ,

o If you are using the same field name twice as an AND operator then you must use to make the advanced filter work properly.

o Use copy and paste for your criteria headings. You can type, but the spelling has to be exact.

o You only need to use specific criteria headings. You do not need to include the entire header row.

o You can specify the columns that you want to extract. If you want to extract all columns, you can leave the extract range empty.

o You can use the wildcards ~, *, and ?

o To find specific text use the equal sign. For example, ICE will find ice, ice cream, ice tea, etc. Using =”=ice” will find only those entries where the answer is ice.

Procedure

1. Highlight and copy the header row. Place the cursor where you want the criteria to appear and Paste. Note: The criteria only needs to include those column headings you want to use as criteria. If you are not using the copy and paste feature then you must type criteria exactly the same way as it appears in the header data field (spelling only ~ it does not need to be formatted the same). This becomes the criteria area.

2. Place the cursor in the data area.

3. Navigation: Data > Filter > Advanced Filter (the Advanced Filter dialog box displays)

4. Check the Copy To Another Location radio button (preferred). The default Filter the List, IN-Place radio button will hide the rows that don’t meet the criteria. Copy to Another Location allows the user to copy the subset easily to another workbook or spreadsheet.

5. List Range: Self selected so long as the cursor is placed in the data area. Otherwise, click the lookup and select the data area (including the headers). Click the lookup to return to the Advanced Filter dialog.

6. Criteria Range: Click the lookup and select the criteria area (include the header fields and the criteria fields (see the picture below).

7. Copy To: Click the lookup. Select where you want the unique values to display.

o If you select just the one cell then the filter will return the same number of columns as are displayed in the data area.

o To return selected columns then you must establish beforehand those column headings to be returned. Select the column headers you want returned and either type or copy them to the Copy To location. Using the Copy To lookup, select those headers. Click the lookup to return to the dialog

8. Click OK.

[pic]

Example as Described

Advanced Filter to find unique values

1. The Unique values feature is a subset of the filtering operation. Otherwise, it is set up in exactly the same way as any other filter.

2. Navigation: Data > Filter > Advanced Filter (the Advanced Filter dialog box displays)

3. Check the Copy To Another Location radio button (preferred)

4. List Range: Click the lookup. Highlight the column of data that contains your data of interest. You must include the column header.

5. Criteria Range: Ignore

6. Copy To: Click the lookup. Select where you want the unique values to display.

7. Check the Unique Records Only checkbox.

8. Click OK.

Note: If you are trying to find unique values from a calculated field, Excel does not like to work with calculated functions (e.g., =SUM) because all it sees is the formula. To solve this problem, you must copy the range and, using the Paste Special feature, paste "just values". This leaves the data while removing the calculation.

Also, this process assumes there will be a header row, so if you don’t include the header row Excel will show the first unique value twice.

Database Functions

[pic]

=DSUM($A$10:$J$50,4,A6:J8)

=DSUM(DataSource,FieldofInterest,Criteria)

DataSource – The Data Source is the entire table of information you need to examine, including the field names at the top of the columns.

FieldofInterest – The Field of Interest is where the answer located. It can be entered on the formula as the column header (“Amount”), column number (4), or field reference (D10).

Criteria - The Criteria is made up of two types of information. The first set of information is the name, or names, of the Fields(s) to be used as the basis for selecting the records. In this example it is the column header. The second set of information is the actual field value(s) or record(s), which are to be selected.

All database functions have the same syntax: Data Source, Field of Interest, Criteria. It is important to understand that while you do not need to duplicate the column headings as was done for the example above, copying the header row insures that the spelling is correct.

This example uses two rows below the Criteria header for the actual criteria. The syntax is: Row1 AND criteria 1 AND criteria 2 AND … Criteria N

OR

Row2 AND criteria 1 AND criteria 2 AND … Criteria N

To include multiple AND criteria for one column header, add that column header to end of the row and type the additional criteria. Be sure to include the additional header and criteria in the formula.

SUMPRODUCT

SUMPRODUCT improves on SUMIF by allowing the use of multiple ranges or additional criteria. SUMPRODUCT works using the Array principle but does not require being array entered. It is different from other Functions in that the ranges are separated either by an asterisk or a plus instead of a comma. Normal algebraic signs apply to the ranges. For example, a Range could be equal to, less than, greater than, etc. to a value or other field reference.

The formula is written:

=SUMPRODUCT((Range1)*(Range2)*…(Range29)*The Range To Sum)

where:

* means AND

+ means OR

Note: Range sizes must be the same for each range. If you are using a prompt field instead of a field within the range then the formats must be the same.

In Excel 2007 SUMIFS performs the same operation but was specifically designed to be a function rather than being adapted from an Array.

If you do not include “The Range To Sum” then the answer will be returned as a Count.

Example:

[pic]

=SUMPRODUCT((dept=”OSC15910”)*(fund=”11000”)*(acct=”46200”)*amount)

The columns (dept, fund, acct, amount) are named ranges.

The algebraic symbol is a qualifier (e.g., =, >, =, ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download