Import Data

-Open Excel and select a Blank workbook

-Click on the Data tab and then select From Text (or Get External Data) depending on version.

-Select a .csv file to import

  • If 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 guide

-Delimited or fixed width

  • I can see that the first row contains titles so let’s check My data has headers
  • The data is delimited but let’s look at fixed, click Next
  • Please note this document isn’t fixed width, but I just wanted to reference in case anyone has a fixed width file to import
  • Drag 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 breaks
  • Select back and change to Delimited then click Next
  • Looking 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 aligned
  • To the right you see a box called Text qualifier with a double quote
  • If 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 delimiter
  • Next and Next
  • By 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 is
  • Let’s go ahead and change Zip and Cell to text files also
  • We 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 data
  • Let’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 OK
  • Rename Sheet1 to Athletics by right clicking tab and selecting rename or double click on Sheet1 tab
  • Insert a new Column in front of Sport by right clicking A and selecting Insert Column
  • Highlight 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 in

VLookup

-Click the + sign on the bottom to create a new page

-In the first column type BannerID: and tab

-Right click field B1, click Format Cells, highlight text and hit OK. Enter a BannerID (ex. 00309993) and tab

-Type Sport: and tab

-Type 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 in
  • Type 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 AC552

-Once 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 play
  • Type in 2, since sport is the First column

-Finally 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 thing

-Your 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 bar
  • Box will come up with values to be inserted
  • Lookup_value: B1
  • Table_array: Athletics!A2:AC552
  • Col_index_num: 2
  • Range_lookup: False

Copy without updating ranges

-We’ve all seen the benefits of copying a formula down without having to enter the code each time

  • On a new sheet insert columns with numeric values
  • In column 3 insert the below statement:
  • =A1+B1
  • It should show 110
  • Copy 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+B2

-We don’t always want our values automatically updated so let’s switch back to Sheet2

-Highlight 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 corner

-If you hover over this square it turns into a plus sign and you can pull it down into cell D2

-Now 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 change

-Highlight data in D2 and in the formula bar put a $ before columns andbefore 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 same

-So if we want more information we simply change the field we’re returning from the spreadsheet

-Let’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 25

-How about how many credits they’re enrolled in

  • Copy the box to the right and change the 25 to a 26

-How about first and last name

  • Highlight boxes D3 and E3 and copy down to row 4
  • Change 25 to a 5 and 26 to a 4

-What 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 & symbol
  • Copy and paste E4 into D5
  • At the end of False insert the following statement &”, “
  • Beware of copying double quotes into excel, they often aren’t recognized
  • Hit enter and go up to column D4 and highlight the ribbon bar without the “=” hit Ctrl+C to copy
  • Go 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 columns
  • In Column F4 type:
  • =E4&”, “&D4

If and ISNA

-What 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 user

  • Copy D4 and paste into D8, it should display same value as what you copied from
  • Now we’ll setup an IF statement
  • Type 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 tab
  • Change Color to Red, make Bold and increase size to 14
  • Change 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 happened
  • Switch back to a valid ID

INDEX and MATCH

-Another way to search for data is to do an INDEX and MATCH

-Let’s click the plus sign at the bottom and create a new Sheet

-Rename Sheet to Index

-Suppose we have student’s email addresses but we need to find their Banner ID’s

  • Create a new sheet on the bottom
  • In field A1 type Email: and then tab
  • Go back to Athletics page and selectthe first students email address, then copy (Ctrl+C) and paste (Ctrl+V) that into field B1 on your Index sheet
  • In 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 sheet
  • Can 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 F552
  • Now when we drag that plus sign down you can see it’s increasing the B value, while still looking at the same range

Pivot Table

-Go back to Athletics and click on the INSERT tab and Pivot Table button

-Select the range you want to include by clicking the icon at the end of Table/Range box

-Chose New Worksheet

-Do not click Add this to the Data Model, this is for when you are pivoting on multiple tables or data sources.

-Click OK

  • Note 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 screen

-There’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 sport

  • Hover 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 settings

-Pull LEVEL into Filters

  • Now we can select if we want to see Undergrad, Grad or both in the table

-Pull CUM_GPA into values, note it counts this time so I change it to see the averages

-Pull class down into columns, now we can see how many students from each are playing

-We don’t want the overall GPA let’s see if some classes are doing better

  • Grab class and pull it above Values in the columns

-Let’s assume the Honors students are pulling everyone else’s GPA’s up and skewing the results, so let’s filter them out

  • Click on drop down arrow next to Class up in your Pivot Table Fields
  • Unselect the Honors and Graduate students

-Count of ID isn’t aesthetic, let’s click on the drop down under VALUES on the bottom and select Value Field Settings

  • Custom name:Num of Students

-Same for GPA, let’s click on the drop down under VALUES on the bottom and select Value Field Settings

  • Custom name:Avg GPA

-Let’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 highlight
  • Drag that down to the bottom of your VALUES section on the bottom
  • Click on the drop down next to CUM_GPA and select Value Field Settings
  • Select Max under Summarize value field by

-Max GPA isn’t much help, lets drag it back up top to the Pivot Table Fields and that removes it from the table

  • Hover over Max GPA to highlight it and click to drag it back up to Pivot Table Fields

-This looks a little cluttered so let’s take Class and move it down below Sport in our rows

  • Hover over CLASS in the COLUMNS section to highlight it and click to drag it down below SPORT in our ROWS section
  • Now this is more readable and we can see the number of students with the teams average GPA
  • If 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.