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;
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”
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
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.
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.
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.
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.