Names - Home | Western Sydney University



Short Courses in Computer Software - Excel No. 4 TOC \o "1-3" \h \z \u Names PAGEREF _Toc367100232 \h 1Defining Names PAGEREF _Toc367100233 \h 1Assigning Names PAGEREF _Toc367100234 \h 1Lists PAGEREF _Toc367100235 \h 2Vlookup PAGEREF _Toc367100236 \h 3Pivot Tables PAGEREF _Toc367100237 \h 4Selecting data for the PivotTable PAGEREF _Toc367100238 \h 5NamesInstead 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 NamesTo create a name;41483901223300Select 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. 418969717833500From 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 NameAssigning NamesYou 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 4522470152400From the Formulas ribbon, in the Define Names group, 3318510809625Choose 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 NamesOn 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 enteredListsYou 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 nameIf your information is on another Sheet you have to give it a name to use as a reference.439928036195Select 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 listNext 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.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 =) Click OK 6591303365500407860515176511334073587630To create a listSelect the cell and an arrow appears next to it.Click the arrow to view the list.VlookupVlookup 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 tableTable_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 VlookupFor 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)If A1 contained the letter A then B1 would read $12.374354830456565262890450215If A1 contained the letter G then B1 would read $30.61Pivot TablesA 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 PivotTable5195870698500Click somewhere inside the table of dataFrom 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.4552950190500Click 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)10706104699000Selecting data for the PivotTableYou 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 PivotTableFrom 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. 611505624840123123Drag three more fields to the Row Labels area Column Labels area, and Values area and watch it build on the spreadsheet; 4692653776980In 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). 00In 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 values451294588265Ensure 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 opensUnder Summarize Value Field by, select another type (e.g. Average) 324040512573000Click OK. The PivotTable changes.You can change any field settings.To change a field4867275161290Ensure 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 removedDrag another field from the list to the labels area. Format table DesignYou can apply a style to the table. 532828510795From 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. ................
................

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

Google Online Preview   Download