Excel for UROP: Summer Edition

Excel for UROP: Summer Edition
Nicole Scholtz(nscholtz@umich.edu) (clarklibrary@umich.edu) Clark Library: Spatial and Numeric Data (SAND)

Rules of Thumb There’s more than one way to do it. Iterate! Trial and error.

The spreadsheet as… A giant piece of graph paper or ledger paper

The spreadsheet as…. A table that follows certain rules: One row of headers Individual cases / items on each line

Selection Can select cells, entire rows or entire columns To select a row/column, you must click on the number (row) or letter (column) Can select multiple at once Will know if selecting row/column when arrow shows up

Cell References A cell reference is the location of a cell and its contents that Excel can understand. Composed of column and row location: Letter-Number format. Ex: column 1, row 1 (uppermost left cell) is A1.

Columns Give your columns semantic (human readable, meaningful) names, but don’t use extra characters like commas, ampersands, percent signs, etc. Avoid writing over existing columns. Instead copy whole columns and only edit on the new column

Number: Format Data in cells is formatted to a specific type This is handled differently in other software There are numbers, text, dates, and more Number: Any number, fractional, whole, small big, negative, positive Text: Non-measurable/countable content Date: Count of days since January 1, 1900 More: Format percentages, fractions, currency

Data Formats: Number In order to format a cell as a number, right-click on the cell and click “Format Cells.” You can choose here how you want your numbers to look: - decimal places - how negative numbers should look

Format Types: Dates Date formatting can be tricky. In the formatting box, you can choose how you want your dates displayed. Watch out for dates are formatted as numbers; if this happens, the date “3/1/09” will instead appear as the number 39873.

Entering Data Use the tools Excel gives you for entering data, e.g. dragging series, copying. Don’t over-type! Pay attention to tab versus enter If entering text, Excel may suggest values – use them! Also notice if there are misspellings (may not work on ITS/Sites computers)

Dragging You can drag values or formulas from one cell to the rest of the row or column. For example (see right), if you want to find the percentage Column B in Column A and put it in Column C, you would merely have to place an “=” in your desired cell (C2) to initiate the formula and then the calculation: =B2/A2. You can then drag the small rectangle at the bottom of the cell to the rest of the cells in your column(s). Double-clicking the rectangle does the same thing

Dragging cont’d Excel automatically finds formulas for rows and columns. In order to find the percentage 200 out of 2200, the formula used was “A2/A7”. This formula carried onto the other cells changed the formula to “A3/A8” and “A4/A9” etc…

Dragging cont’d In order to fix this, a “sticky” or dollar sign ($) must be placed in front of the cell value that you do not want to change. The formula can then be dragged to the other cells without erring.

Dragging cont’d When dragging, the formats of the dragged cells will follow (color, font, etc). This can be rectified by clicking the small icon that appears when dragging and clicking “Fill Without Formatting.”

Dragging cont’d When dragging, the formats of the dragged cells will follow (color, text, etc) and Excel will guess whether you are filling a series or just copying cells. Click the small icon that appears after dragging and click to “Fill without Formatting” or “Fill Series”

Absolute Cell References Absolute vs. Regular An absolute cell reference does not change Will only ever refer to one cell How to create? (2 ways) Add a ‘$’ before the letter/number of a cell reference Or click on cell desired and press ‘F4.’ This effectively locks the cell reference in place The position of the ‘$’ matters – will hold either letter ($A1) or number (A$1) as absolute (or both: $A$1)

Exercise!FormulasSticky_Exercise.xlsx

Find Suppose you want to find a particular string of text or numbers in a spreadsheet of 30,000 records. By pressing Ctrl+F, a box will come up that will allow you to type in exactly what you want to find.

Find and Replace If you highlight a selection, when you use the Find and Replace feature it will only act on the section you highlighted Very useful for fixing errors in a particular column

CTRL + Z Is your friend Undo Use the keyboard shortcut; the save and undo buttons are next to each other in the Microsoft program window

Exercise: Dragging-Data Types_Exercise

Sorting Sort in different ways to learn about your data You may discover problems with how you cleaned your data that you will have to backtrack to solve

Editing: Sort and Filter A to Z, Ascending, Descending Custom Sort: Drill-down sorting Can add/delete levels.

Custom Sort and Lists What if you want to sort by something that is not in alphabetical order? (High/Medium/Low) Can make a “Custom List”

Filters Can help you subset data Can identify categories, problems with categories, misspellings

Editing: Filter Filters allow you to select certain values in a table/dataset. Drop-down arrows are the filters. Useful for seeing which values are in data. Can filter multiple columns at once.

A Filtering Caveat Some things that you do with filtered data will only apply to the selected cases. How to tell? Filter icon is highlighted Row numbers are blue Bottom says X of Y records found

Clearing Filters You can clear filters by turning them off, but then you have to turn them back on Go to the “Data” tab, then in the “Sort and Filter” section, click “Clear”

Navigation Ctrl+Arrowkey You can navigate through an entire row of data or an entire column by pressing “Ctrl” and then an arrow key in the direction you desire to go Excel will only move to a blank cell, so by using Ctrl+Arrow on an empty column, you will be moving down over 1 million cells. Be careful of this Ctrl+Shift+Arrow key This combination allows you to select the cells in an entire row or column. The same method applies: Press Ctrl, then Shift, and then the arrow key in the direction you desire to select Again, be careful of selecting an entire column of blank cells, as you will be selecting over 1 million cells

Exercise!SortingFiltering_Exercise.xlsx

Data Analysis ToolPak Install and use for descriptive statistics Not recommended for t-tests and other statistical calculations

Exercise: Survey_Pivot_Frequences.xlsx

What is a PivotTable? Summary of data using dynamic tabling Can choose what you wish to see in different ways Like a cross tabulation or a 2-way table Great for frequencies, summary statistics

PivotTables for Basic Statistics Create a pivot table for each variable, where you can show both counts (frequencies), min, max, mean, and percentages You can’t do a median in a pivot table

Pivot Tables Pivot tables are an even easier way to display data and view it in different ways. To make one, go to the Insert tab. Click on “PivotTable”

Pivot Tables cont’d A box like this will show up. Be sure to select your entire table, and make sure that the PivotTable is being placed in an appropriate location. **Note: Excel will select your data on its own if you have it formatted as a table.

Pivot Tables cont’d This will appear on a new sheet. This is where the PivotTable will show up when you start building it.

Pivot Tables cont’d To the right of the screen, you will see this, a PivotTable Field List. This has all of the data that you had in your original table, but there are four boxes below the list of fields.

Understanding the Table Areas Row Labels: These are the rows in your table What variables are you assessing? How do you want to group things? Column Labels: These are the columns in your table I don’t use these very frequently (messy). I prefer the row distribution and the values to the right. Good for comparing two variables, three at most. (Yes/No, High/Low, etc)

Understanding the Table Areas Report Filter: Allows you to filter by a certain variable; applies to entire table Great for filtering by dates Cannot put a field in both report filter and rows/columns Values: The actual numbers you see Counts, sums, averages, calculated fields

How to Start To put anything in the PivotTable, all you have to do is drag and drop. Alternatively, you can click the box to the left of the name in the PivotTable Field List, but then you don’t control where it goes. To remove something from the PivotTable, you can drag it out, tossing it into nowhere OR you can uncheck the box to the left of the name in the PivotTable Field List.

How to Start Let us say you want Region as your rows for your new table. Click and hold Region and drag it out of the list and into “Row Labels.”

Brief Note: PivotTable Field List Like tables and graphics in Microsoft Office 2007/2010 programs, if you are not actively selecting part of the PivotTable, the Field List will disappear (as well as Options and Designs tabs at the top) HakunaMatata! If you click on the PivotTable, the Field List will reappear. You can move the field list around as well.

Adding to Source Data Let us say we want to know how many states are in each Division. Instead of using another measure for count, let’s add one to the source data. Go back to the source table and create a count. Go back to the PivotTable.

The PivotTable has not updated. You must update the table to reflect the changes made. In the PivotTable Tools section of the Ribbon, select “Options.” Click “Refresh.” This must be done any time you modify your source table. ***Note*** This will not work if your data is not formatted as a table (range) and you add/change it. If it is not formatted as a table, you will have to “Change Data Source” instead.

Naming in Pivot Tables As you can see here, the name of the count is “Sum of Count.” You want to change the name, but “Count” is not working.

Naming in PivotTables Technically you cannot rename it the same as its name in the Field List, or the original source data. You can place a space before or after the name for a superficial fix. Or, you can modify the names however you want if you copy the PivotTable values into a new sheet.

Field Settings You may have noticed that all your row/column headings say something like “Sum of FieldName.” You must specify how you want a field to be shown.

Changing Field Settings There are a few ways to change the field settings for any field. One: right-click the field name you wish to change and click “Value Field Settings”

Value Field Settings Here, you can change the name of your field. You can change how the data is being summarized. Click on “Number Format.”

Number Format Clicking on “Number Format” brings up a very familiar box. You can change the formats here, or in the “Number” section of the Home tab.

Changing Field Settings Two: The Quickest Way You can right-click the field name and hover over “Summarize Data By.” This will bring up a list of commonly used summaries. “More options…” will bring up the box.

Changing Field Settings You can click the arrow next to the field name in the Field List and change the settings there. Click on “Value Field Settings…” Three:

Picked the Wrong Range? Let us say you selected the wrong data altogether, or want to change it. You can change the data source without having to get rid of the whole Pivot. Go to the Options tab and click on “Change Data Source”

Want to move a PivotTable? If you try to cut part of the Pivot Table you’ll get an error: You must go to PivotTable Options and use the “Move PivotTable” option. If you simply want to cut the entire table or copy part of it, you will not encounter this problem.

PivotTable Design Tab The PivotTable Design tab will allow you to format your PivotTable’s appearance. Change the colors here, whether your rows/columns are banded, and whether it shows totals or not.

Exercise: Survey_Pivot_Frequences.xlsx

Formula Construction All functions give you a guide as you type them You can expand the formula you type in the formula bar The guide is a small, white, movable box underneath the cell in which you are typing the formula

View Tab: Window This section of the View tab is very useful when working with large datasets or multiple workbooks. “Arrange All” will make windows smaller so multiple can be viewed at once.

View Tab: Window Freeze Panes Split Literally splits the screen Up to four different sections Allows you to freeze a row or column Can see headers while scrolling past them

Zooming You can zoom in and out of your spreadsheet quickly by holding down “Ctrl” and using the click-wheel on your mouse (if you have one).

Formula Creation All formulas start with an equal sign: = This is either followed by a function or a cell reference: =SUM or =A2… When a formula uses a function, an open parenthesis must follow [ =SUM( ] Note that when you type in a cell or column/row reference, it is outlined on the spreadsheet in color.

Formula Creation Operators Add (+), Subtract (-), Divide (/), Multiply (*) Greater than (>), Less than (<), Not equal to (<>) Mathematical operations in Excel work in a fairly standard way Parentheses matter! Some things cannot be calculated using only operators (ex. standard deviation)

Function Formula vs. Formula Not all formulas are function formulas, but the converse is true. A mathematical operation can be completed without using a function: =SUM(A2:F2) These would produce the same result. =(A2+B2+C2+D2+E2+F2)

Function Formulas Mathematical AVERAGE, SUM, STDEV, COUNT These can also be completed without the function Logical IF, SUMIF, COUNTIF, IFERROR Textual MID, TRIM Temporal DAY, MINUTE, HOUR

Function Formula Construction All formulas start with an equal sign: = Some formulas start with a function, or the actual name of the formula (ex. SUM vs “+”) The function is followed by an opening parenthesis =FUNCTION(

Function Formula Syntax = is the trigger to any formula Parts of the formula are separated by commas. You MUST enter text “within quotes” or Excel will err. So if you want to say blank, you would type “” empty quotes. Useful operators: < is ‘less than’, > is ‘greater than’, <> is ‘not equal to’

Using New Formulas In Excel 2007 and 2010, you can use the Formulas ribbon to create formulas When you select a formula, a dialog box will open up to help you write the formula and show you how it evaluates

Text to Columns You can subdivide a column into multiple columns based on a delimiter (comma, space, tab, etc.) or a fixed width Acts much like the Text Import wizard – but this lets you operate on a small subset of your worksheet Always copy the column you want to split to another column of your spreadsheet

Date Functions MONTH – gives the month of a date DAY – gives the day of a date YEAR – gives the year of a date TODAY – literally, today’s date (updates daily) WEEKDAY – gives a number 1-7 depending on what day of the week

Mathematical COUNT: counts the number of items in a selection (does not count blank cells; spaces count) =COUNT(value1,value2,value3…) ROUND =ROUND(number, num_digits)

Auto Calculations You can automatically find averages, sums, counts, maximums and minimums using Excel By clicking the button “AutoSum” at the top right corner, you can automatically sum a column

Exercise: TTC-Formulas_Exercise.xlsx

Logical Formulas IF =IF(logical_test, value_if_true, value_if_false) Logical test? What are you trying to show? A3 > 0, F4 <> “”, etc Value if true/false? If the test is true/false, what do you want to display? Blank cell, “text”, numbers, etc IFERROR = IFERROR(value, value_if_error) Value? Whatever your normal formula is (A3 > 0, F4 <> 0, B2/A2 etc) Value if error? What value do you want displayed in case of an error? (Text, #)

Example of IF Statement Let us say you have survey data and one question (let us say Column F) is a “Yes/No.” You have 1 for “Yes” and 0 for “No.” You want a column next to these 1s and 0s with actual text “Yes” and “No;” you can use an IF. It would look like this: =IF(F2=1,”Yes”,”No”) This says that if F2 is equal to 1, then place a “Yes” in cell G2 (assuming you are putting this formula next to the 1s and 0s) else put “No.”

IF Statement “buckets” By “buckets” I mean grouped values. Say you have the median income values but you want to group them by $5000 ranges. You can ‘nest’ IF statements (up to 72) =if(A2=0,0,if(A2<5000,”$1-$5000”, if(A2<10000, ”$5001-$9999”….)

Text Functions TRIM – gets rid of extra spaces (good for cleaning) =TRIM(text) where ‘text’ is the cell(s) you are trimming LEFT, RIGHT, MID – substrings a text field =LEFT(text, num_chars) =RIGHT(text, num_chars) =MID(text, start_num, num_chars)

Text Functions REPLACE: replaces text in a string with different text =REPLACE(old_text, start_num, num_chars, new_text) CONCATENATE: joins together text strings in multiple cells into one string/one cell =CONCATENATE(text1,text2..) where ‘text’ is cell references Same as =A1&”/”&b1&”/”&C1) More! http://office.microsoft.com/en-us/excel-help/CH010064534.aspx?CTT=97

Lookup Functions LOOKUP: searches for a value in a row or column, and returns another value from a different row or column =LOOKUP(lookup_value, lookup_vector, result_vector) Lookup value: the value you are searching on Lookup vector: the row/column you are searching for the value in Result vector: the row/column you want values from Lookup functions allow tables to draw data from one another, acting like a relational database VLOOKUP, HLOOKUP If you find yourself using these consistently, Access may be a more useful tool for your work

Exercise:TextLookup_Exercise.xlsx

Brief Note: File Extensions A file with the extension “Book1.xls” is an Excel 2003 or earlier document A file with the extension “Book1.xlsx” is an Excel 2007-2010 document When converting files from one format to the other, it is important to remember that some formulas and formatting will be lost when switching from Excel 2007-2010 (.xlsx) to Excel 2003 (.xls)

Interoperability Tables are everywhere Excel can be output to .csv, .txt (tab-separated or comma separated) and lots of other text and table based formats Data types are the stickiest point Be careful about using comma-separated values if any free text fields have commas in them

Basic guidelines, part 1 Save your progress with different names as you go along. The undo button is magical, but not all powerful. Make notes for yourself! Don’t overengineer! Do the first thing that feels right, try it out and see if it worked. Then go on to the next thing if there is still more to be done.

Basic guidelines, part 2 Refer frequently to the original version Don’t bother cleaning up something you don’t need But don’t leave anything behind that you do need, either!

Spot checking Spot check as you go Look at a few cases scattered throughout your spreadsheet – are things still lining up and making sense? Is the original order of the spreadsheet useful, perhaps alphabetical or in number order? Use this feature as you’re spot checking The software doesn’t care if something gets out of wack – only you can identify that

Preserving original order If you find yourself about to change the order of rows in your spreadsheet, but you think the order might matter to you later, make a column numbered 1 to X that you can re-sort to if needed

Finding help, Doing more Be ready to learn! The Excel help is great Find ebook Excel titles in the library. E.g. search for excel 2010 at http://mirlyn.lib.umich.edu , and then filter at left under Availability by “Available Online” You don’t need to purchase data cleaning tools for purchase on the web while you’re looking for information

Excel Help Excel Help is a great resource for Excel. Both the blue question mark in the top right corner and the fx next to the equation bar are useful.

Excel Help cont’d The blue question mark will bring up Excel Help The fx button will help you find an equation

Pleasefill out the workshop evaluation in the Ctools folder anonymous and < 5 minutes thank you 

Excel for UROP: Summer Edition