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.
To create a name;
Select the cell/s or range of cells.
Use one of the four following methods;
From the Formulas ribbon, in the Defined Names group, choose the Create from Selection command.
From the Formulas ribbon, in the Defined Names group, choose the Define Names command.
In the Name Box on the Formula Bar.
Right mouse click the selection and choose Define Name
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;
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”
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
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 DataTools group, choose the DataValidation command.
Click the Settings tab.
In the Allow box, select List from the drop down list.
In the Source box, type the “Name” e.g; =ListCode (don’t forget the =)
To create a list
Select the cell and an arrow appears next to it.
Click the arrow to view the list.
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.
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.
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.
If A1 contained the letter A then B1 would read $12.37
If A1 contained the letter G then B1 would read $30.61
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.
From the Insert ribbon, in the Tables group, choose PivotTable Note the following.
The Create PivotTable dialog box, will open.
The range nominated is all of your data (if it isn’t you need to adjust it)
It has nominated a New Worksheet for the Table.
Click OK and note the following.
Two more ribbons are added to the tabs,
Pivot Tools - Options and Design.
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.
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).