My.bridgew.edu



Import DataOpen Excel and select a Blank workbookClick on the Data tab and then select From Text (or Get External Data) depending on version.Select a .csv file to importIf you need Data, most reports in Argos have an option to export your results as a .csv file, so pick a report with Banner ID to follow along with this guideDelimited or fixed widthI can see that the first row contains titles so let’s check My data has headersThe data is delimited but let’s look at fixed, click NextPlease note this document isn’t fixed width, but I just wanted to reference in case anyone has a fixed width file to importDrag first line over to the end of the data in your first column Click in the text box at the end of each new column to create additional data breaksSelect back and change to Delimited then click NextLooking at the data I see that it’s delimited by a Comma, so let’s go ahead and check the Comma box and you can see your data is now alignedTo the right you see a box called Text qualifier with a double quoteIf you click the back button you can see some data is encapsulated in double quotes.This is a common practice when receiving data because sometimes the fields will have a comma in the data and that will throw off our delimiterNext and NextBy default everything is General which if we leave for everything the computer will try to determine what type of columns they are.Problem is that if we leave ID as general the computer will think it’s a number and remove the leading zeros.Let’s click on the second column and change that to text which will keep the data exactly as it isLet’s go ahead and change Zip and Cell to text files alsoWe don’t have any dates in this example, but if we did we could highlight the column and show how we want the date to display.We can also skip columns in this section. Why would we want to skip a column? Because many times when you get data that you’re going to import, the person sending data will just give you everything and you might not need it.Let’s scroll all the way over to the right and you can see a field called Transfer and maybe we don’t care if they’re transfer students so let’s highlight that column and select Do not import column.You will see the column change to Skip Column and this data will not be imported into Excel.Finally we click finish and a message box pops up asking us where we want to put this new dataLet’s go ahead and insert it into the existing worksheet since it’s blank, or you could create a new worksheet if you want and click OKRename Sheet1 to Athletics by right clicking tab and selecting rename or double click on Sheet1 tabInsert a new Column in front of Sport by right clicking A and selecting Insert ColumnHighlight the ID column (column C) and hit Ctrl+C, to copy the data, and then place your cursor in cell A1 and hit Ctrl+V to paste the data inVLookupClick the + sign on the bottom to create a new pageIn the first column type BannerID: and tabRight click field B1, click Format Cells, highlight text and hit OK. Enter a BannerID (ex. 00309993) and tabType Sport: and tabType an equals sign to indicate you are inserting a formula and then type VLOOKUP(Now the formula wants to know where it’s getting the information it’s looking up so type in B1,Now that it knows what value to look for it wants to know where it’s looking for this value.Since this data is not on this sheet we need to first indicate where it is and what rows to look inType in Athletics!A1:AC552, This goes to the Athletics page that we created from our import data step above and searches all the data we entered in from Cell A1 to AC552Once it finds the value you indicated you wanted found it wants to know what you want to know about this value.Let’s say we want to know what sport they playType in 2, since sport is the First columnFinally enter False and close the Paren “)”. This means you want an exact match. If you type in True and it can’t find the value it returns the next closest thingYour statement should now look like this:=VLOOKUP(B1,Athletics!A1:AB552,2,FALSE)****Alternate method:Type in vlookup(Select the fx button up near the ribbon barBox will come up with values to be insertedLookup_value: B1Table_array: Athletics!A2:AC552Col_index_num: 2Range_lookup: FalseCopy without updating rangesWe’ve all seen the benefits of copying a formula down without having to enter the code each timeOn a new sheet insert columns with numeric valuesIn column 3 insert the below statement:=A1+B1It should show 110Copy down by selecting the little box in the bottom right hand (turns into plus sign) and drag down to show how values being added know automatically what to add=A2+B2We don’t always want our values automatically updated so let’s switch back to Sheet2Highlight column C2, below Sport:, and type in Class:Select your VLOOKUP in cell D1, and notice the little filled in square in the bottom right cornerIf you hover over this square it turns into a plus sign and you can pull it down into cell D2Now we have that pesky #N/A because as we just learned the values all update because Excel is thinking its helping you Select #N/A in column D2 and change your values to B1 and A1:AC552, and change the 2 to be 15 before false. Now it should show the class of the BannerID you entered.=VLOOKUP(B1,Athletics!A1:AC552,15,FALSE)To solve this problem we can insert a $ before the columns and values we don’t want to changeHighlight data in D2 and in the formula bar put a $ before columns and before the numbers so it looks like this:=VLOOKUP($B$1,Athletics!$A$1:$AC$552,15,FALSE)Now when we copy down it keeps all information the sameSo if we want more information we simply change the field we’re returning from the spreadsheetLet’s say we also want their GPA. Copy down D2 to D3 and we now have class twice. Then we simply highlight D3 and in the ribbon bar change the 15 to a 25How about how many credits they’re enrolled inCopy the box to the right and change the 25 to a 26How about first and last nameHighlight boxes D3 and E3 and copy down to row 4Change 25 to a 5 and 26 to a 4What if we need the first and last name listed in one column last, first?We can merge 2 VLOOKUPS into one cell by using the & symbolCopy and paste E4 into D5At the end of False insert the following statement &”, “Beware of copying double quotes into excel, they often aren’t recognizedHit enter and go up to column D4 and highlight the ribbon bar without the “=” hit Ctrl+C to copyGo back down to D5 and at the end type in & and hit Ctrl+V to paste =VLOOKUP($B$1,Athletics!$A$1:$AB$552,4,FALSE)&", "&VLOOKUP($B$1,Athletics!$A$1:$AB$552,5,FALSE)Or if we’re already displaying the first and last name we can simply merge those columnsIn Column F4 type:=E4&”, “&D4If and ISNAWhat happens when someone types in an ID that isn’t valid?A Big list of #N/A’s show on the screen Instead let’s write a more useful error to display to the userCopy D4 and paste into D8, it should display same value as what you copied fromNow we’ll setup an IF statementType the following at the beginning just after the equals sign: IF(ISNA(Now go to the end and type: ),”BannerID Not in List”,””)Should look like the below statement:=IF(ISNA(VLOOKUP($B$1,Athletics!$A$1:$AC$552,5,FALSE)),"BannerID NOT FOUND","")Let’s Change the font and color by right clicking D8 and clicking Format Cells and selecting the Font tabChange Color to Red, make Bold and increase size to 14Change the value in B1 to be an invalid ID (ex: change 3 to 4 at end)Now we have a “BannerID Not Found” message in D8 to let the user know what happenedSwitch back to a valid IDINDEX and MATCHAnother way to search for data is to do an INDEX and MATCHLet’s click the plus sign at the bottom and create a new SheetRename Sheet to IndexSuppose we have student’s email addresses but we need to find their Banner ID’sCreate a new sheet on the bottomIn field A1 type Email: and then tabGo back to Athletics page and select the first students email address, then copy (Ctrl+C) and paste (Ctrl+V) that into field B1 on your Index sheet k2austin@bridgew.eduIn C1 insert this statement: =INDEX(Athletics!A2:A552, MATCH(B1, Athletics!F2:F552, 0))Let’s say we have a list of emails and we want to look up their Banner ID’s Go back to the Athletics tab and select values F3 to F10, then copy (Ctrl+C) and paste (Ctrl+V) that into field B2 on your Index sheetCan we just copy down the formula since we DO want the lookup value to adjust to the email address listed next to it?NO – while we want the email values to adjust, we don’t want the Range of where it’s looking up the data to move with it. Let’s update our data so excel automatically updates which field in B it’s looking for, while sticking with the same range:=INDEX(Athletics!$A$2:$A$552, MATCH(B1, Athletics!$F$2:$F$552, 0))This statement is saying go to the Athletics page and return the Value in fields A2 through A552 where it matches the value (on the current page Index) to the value on the Athletics tab in fields F2 through F552Now when we drag that plus sign down you can see it’s increasing the B value, while still looking at the same rangePivot TableGo back to Athletics and click on the INSERT tab and Pivot Table buttonSelect the range you want to include by clicking the icon at the end of Table/Range boxChose New WorksheetDo not click Add this to the Data Model, this is for when you are pivoting on multiple tables or data sources.Click OKNote if you have any columns without a title will get an error because every column needs a label. Check the empty box next to sport and it will show up in your rows below and list the available sports on the screenThere’s a drop down arrow next to sport as its highlighted and you can sort, filter, unselect values etc.Let’s say you want to see how many students play each sportHover over ID and drag it down to the VALUES It should default to Count, but if it doesn’t select the drop down arrow and click Value field settingsPull LEVEL into FiltersNow we can select if we want to see Undergrad, Grad or both in the tablePull CUM_GPA into values, note it counts this time so I change it to see the averagesPull class down into columns, now we can see how many students from each are playingWe don’t want the overall GPA let’s see if some classes are doing betterGrab class and pull it above Values in the columnsLet’s assume the Honors students are pulling everyone else’s GPA’s up and skewing the results, so let’s filter them outClick on drop down arrow next to Class up in your Pivot Table FieldsUnselect the Honors and Graduate studentsCount of ID isn’t aesthetic, let’s click on the drop down under VALUES on the bottom and select Value Field SettingsCustom name: Num of StudentsSame for GPA, let’s click on the drop down under VALUES on the bottom and select Value Field SettingsCustom name: Avg GPALet’s pull in the Max GPA by adding another GPA Hover over CUM_GPA in the Pivot Table Fields on the top right, you will see it highlightDrag that down to the bottom of your VALUES section on the bottomClick on the drop down next to CUM_GPA and select Value Field SettingsSelect Max under Summarize value field byMax GPA isn’t much help, lets drag it back up top to the Pivot Table Fields and that removes it from the tableHover over Max GPA to highlight it and click to drag it back up to Pivot Table FieldsThis looks a little cluttered so let’s take Class and move it down below Sport in our rowsHover over CLASS in the COLUMNS section to highlight it and click to drag it down below SPORT in our ROWS sectionNow this is more readable and we can see the number of students with the teams average GPAIf we click on the Plus boxes in front of the sports we’ll see that the data expands for that section to show the number of students from each class along with their average GPA’s. ................
................

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

Google Online Preview   Download