Short Courses in Computer Software Excel No

Short Courses in Computer Software - Excel No. 4

Names 1

Defining Names 1

Assigning Names 1

Lists 2

Vlookup 2

Pivot Tables 4

Selecting data for the PivotTable 5

Names

Instead of referring to a cell or range of cells by the cell reference you can define the cell reference to be a name. This is often much easier to remember.

For example, cells B3 to B9 might contain information about employees’ wages. You could define B3:B9 to be a named range called Wage.

After you have defined a named range, you can insert cells into that range and they will be automatically included in the name.

Defining Names

To create a name;

• 
  Select the cell/s or range of cells.
  
• 
  Use one of the four following methods;
  
  1. 
     From the Formulas ribbon, in the Defined Names
     group, choose the Create from Selection command.
     
  2. 
     From the Formulas ribbon, in the Defined Names
     group, choose the Define Names command.
     
  3. 
     In the Name Box on the Formula Bar.
     
  4. 
     Right mouse click the selection and choose Define Name
     

Assigning Names

You can use another method that allows you to reference directly into the cell you wish the code entered. This way you don’t necessarily need the code on the sheet but it is still handy as a reference. You can give individual cells names all at once using Define Assign Names Using the example shown;

To define Assign Names

• 
  Select the range A1:B7
  
• 
  From the Formulas ribbon, in the Define Names group,
  
• 
  Choose the Create from Selection command.
  The Create Names from Selection window appears.
  It should have the Left Column ticked
  (if it hasn’t select it and de-select the others.)
  This means it will take the left column as the
  name for the contents of the right cell.
  I.e. B1 will now have the name ”A”
  
• 
  Click OK
  

To use Assign Names

• 
  On another sheet select the cell you want to
  use as a reference. (e.g. A1)
  
• 
  Type; =J (don’t forget the =)
  
• 
  Press Enter and the amount is now entered
  

Lists

You use a drop down list in a cell to lookup values in a table.

For example, to use the Vlookup you have to enter the code using the same entries as the first column in the table. If you don’t know what these codes are you can create a list in the cell and choose from a drop down list.

This is handy but has to be done in conjunction with another formula to get a result e.g.vlookup.

There are two stages for this,

• 
  Give the codes a name and then,
  
• 
  Create the cell list.
  

To give Table a name

If your information is on another Sheet you have to give it a name to use as a reference.

• 
  Select the codes from the list of the Data sheet
  (i.e. in our example A1 to A7)
  
• 
  Click in the Name box (on the Formula bar).
  
• 
  Type a name; (e.g. ListCode) and press Enter.
  This is now the name for the range.
  

To create a list

Next you must create the cell list.

• 
  Select the cell you want to use as a reference. (e.g. A1)
  
• 
  From the Data ribbon, in the Data Tools group, choose the Data Validation command.
  
• 
  Click the Settings tab.
  
  1. 
     In the Allow box, select List from the drop down list.
     
  2. 
     In the Source box, type the “Name” e.g; =ListCode (don’t forget the =)
     
• 
  Click OK
  

1

To create a list

Select the cell and an arrow  appears next to it.

Click the arrow to view the list.

Vlookup

Vlookup is a formula that finds a reference in the left column of a table and then returns the value in the same row from a column you specify in that table.

Syntax;

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value    is the value to be found in the first column of the table

Table_array    is the table of information in which data is looked up.

Col_index_num  is the column number in table where the matching value is stored.

Range_lookup    (Optional) is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. Approximate match is default.

Using Vlookup

For this example (shown below), we use a Data sheet (Sheet 1) to hold the table.

The lookup value is in the range A1:B7. The second column B1: B7 is the value required.

• 
  On another sheet, in the first cell A1 you could enter a letter which would match the letters in the Data sheet Column A (i.e. A to G)
  
• 
  In B1 you would enter the Vlookup formula that will compare the contents of A1 and give us a result from the table. B1 is also be formatted for currency.
  The formula would be.
  =VLOOKUP(A1,Sheet2!A1:B7,2)
  
  1. 
     If A1 contained the letter A then B1 would read $12.37
     
  2. 
     If A1 contained the letter G then B1 would read $30.61
     
     

Pivot Tables

A PivotTable is an interactive worksheet table that can summarise large amounts of existing data using the format and calculating methods you choose. When you create a PivotTable, you specify the data to use as row fields, column fields and Report fields, then the data you want summarised in the data field of the table. Excel automatically uses the Sum function for numeric data and the Count function for non-numeric data.

To set up the PivotTable

• 
  Click somewhere inside the table of data
  
• 
  From the Insert ribbon, in the Tables group, choose PivotTable
  Note the following.
  
  1. 
     The Create PivotTable dialog box, will open.
     
  2. 
     The range nominated is all of your data (if it isn’t you need to adjust it)
     
  3. 
     It has nominated a New Worksheet for the Table.
     
• 
  Click OK and note the following.
  

1. 
   Two more ribbons are added to the tabs,
   Pivot Tools - Options and Design.
   
2. 
   Both the PivotTable field list and instructions
   are displayed. (See diagram. This is a fuill page but narrowed to include all contents)
   

Selecting data for the PivotTable

You can now use the outline to select the data for the table. You do this by dragging the Heading names at the top of the PivotTable toolbar to the areas below.

To select data for the PivotTable

• 
  From the PivotTable field list, drag the field that you want to base your PivotTable on (in this example SalesPerson) to the Report Filter area. (See example following).
  Note how it “docks” to the spreadsheet.
  

1

2

3

Drag three more fields to the Row Labels area Column Labels area, and Values area and watch it build on the spreadsheet;

• 
  
  In the PivotTable Field List;
  
  • 
    The “used” fields have a tick beside them. 
    
  • 
    The fields appear in their area in the pane below. 
    
  
  Your PivotTable (on the spreadsheet)  shows;
  
  • 
    The totals for each particular heading i.e. Date and Style, for a SalesPerson (in this case All sales people).
    
  
  

To change values

• 
  Ensure you have clicked anywhere in the table.
  
• 
  In the PivotTable Field List, in one of the areas,
  (e.g. Values), click the contents (e.g. Sum of Costs)
  to produce a list.
  
• 
  Choose Value Field Settings and the dialog box opens
  
• 
  Under Summarize Value Field by, select another type (e.g. Average)
  
• 
  Click OK. The PivotTable changes.
  
• 
  You can change any field settings.
  

To change a field

• 
  Ensure you have clicked anywhere in the table.
  
• 
  In the PivotTable Field List, in an area (e.g. Column Labels)
  
• 
  Click the field (e.g. Style ) to produce a list.
  
• 
  Choose Remove Field and the field is removed
  
• 
  Drag another field from the list to the labels area.
  

Format table Design You can apply a style to the table.

• 
  From the PivotTable Tools – Design ribbon, in the
  PivotTable Styles group, click the More button to
  show a range of styles.
  
• 
  Place your cursor over the styles and watch your
  PivotTable change design.
  
• 
  When you find one you like simply click the style.