Interviewing data with Excel

1. Freeze Panes to maintain headings (top row) while scrolling down

Click on Cell A2
Go to ‘Windows’ and click on ‘Freeze Panes’

2. Move around on a spreadsheet using Command + Arrow Keys

Hold the Command key and tap the up or down arrow or left or right to move around without scrolling

3. Format numbers to make them easier to read

Click on the column, row, or cell you want to format
Go to ‘Format’ and click on ‘Cells’
Choose ‘Number’ as the category you want
Select what you want then click ‘OK’
To add commas check “Use 1000 Separator” box

4. Sort data

Put the cursor in the Column you wish to sort
Click on ‘Sort and Filter’ (AZ button)
Click on ‘Descending’ and the data is sorted
Click on ‘Ascending’ to sort the other way around
It shows that the variable varies, different numbers in different places

5. Sort data by multiple variables

Go to ‘Data’ and click on ‘Sort’
Change the Column in the ‘Sort by’ row to the variable you want to sort first
Adjust the Order you want the data in
Click on the plus (+) sign
Change the Column in the newly ‘Then by’ row to the variable you want to sort now
Adjust the Order you want the data in and click ‘OK’
You can do this with several different variables.

6. Filter data

Go to ‘Data’
Click on ‘Filter’ and buttons will appear on your variable names
Click on the button related to the variable you want to filter
Unselect all by un-ticking ‘(Select All)’
Tick the variable you want
The data now shows the data you filtered. Also note: the row number the filtered data is on, is highlighted.
You can filter for several items within one variable can be done by ticking more items in the filter.

7. Filter data by multiple variables

Filter for several variables in different columns by filtering them in the same way
Filter for several numbers by clicking ‘Choose One’ or ‘Number Filters’
Choose the type of filter you need
Add the number you wish the filter to use and click ‘OK’

8. Create subtotals

Go to ‘Sort’ and click the variable you want to have subtotals by
Go to ‘Data’ and click ‘Subtotals’
Tick the variable you wish to have a subtotal of
Make sure ‘At each change in’ matches the variable you sorted earlier
Set ‘Use function’ to the kind of subtotals you want and click ‘OK’
You can add other subtotals in the same way

9. Total rows and columns

Every Excel formula starts with an ‘equal’ sign or =
In the cell for the new variable add =cell name+cell name

To add multiple variables

=SUM(first cell name:last cell name) allows the variable from the first to the last cell in the formula to be added up
For example: =SUM(D2:G2)

10. Copy formula to entire row or cell

Place the cursor on the bottom right of the cell with the new formula and double click or drag it down or across.
For big data set, place cursor (the big white cross) on corner of cell with formula until it turns into a skinny black cross.

To be continued… More will be added to this as we go.

This entry was posted in Excel Tips, In-class Activity, Lecture. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s