LAB 2: Create a table in Excel, using data from the Statistical Abstract.
Note: Before doing this lab assignment, you should have read the "Good Tables" section of the Presenting Data website
Goal:
To create a table in Excel that looks like table 1, with these features:
- Country-level data for two years.
- Data sorted on the base year.
- Formulas used to calculate the change in the third column.
- Merged cells and text-wrap for the title.
- Horizontal borders under the title and headings.
- Source listed at the bottom of the table.
In this exercise, you should learn how to use the following Excel functions:
- sorting data.
- formulas (to calculate the average and net change).
- copying and pasting formulas (so you don't have to retype them).
- merging cells and wrapping text (the title and footnote for this chart is in four merged cells.
- drawing lines in cell borders.
- aligning text and setting decimals.
Instructions:
Launch the Statistical Abstract reader (this is an adobe file, enter your ulid and password to access it). (As an alternative, see the beginning of the analyzing budgets page.
Go to Section 30,Comparative International Statistics and find a table with two years of data (best if it is adjacent columns).
Open the Excel file for that table.
- Begin by deleting all the unnecessary rows and columns.
- Use formulas* to calculate net change or percentage change in the third column.
- Adjust the column widths (use the mouse to drag the widths).
- Be sure to use merge and wrap in the cell formatting:
Highlight the cells to be merged and:
use Format | Cells | Alignment -- check word wrap and merge to merge cells and wrap lines. - use Format | Cells | Border to draw lines.
- To sort the data, highlight the entire set of rows and columns to be
sorted, then select:
Data | Sort - Change the fonts from Courier to something that looks nicer.
Save the Excel file -- AS AN EXCEL WORKBOOK FILE -- on your website drive.
Save a backup copy on your H:\ drive.
Open your Welcome.htm file in Word and create a link to the excel file.
1. Formulas:
On the right, notice the
formula in the formula bar for cell E4.
To
create a formula in cell E4, enter:
=D4-C4
Then copy that cell down the whole column (this can be done by dragging the
little square on the bottom right hand side down the column). Excel will
automatically recalculate the formula for the entire column.
In some cases, you may need to calculate a percentage change rather than a net
change. For example:
=(D4-C4)/C4*100
2. Merging Cells
The Cells B2,C2, D2 and E2 have all been merged into a single cell
-- use ALT-ENTER to force line breaks (e.g., between "Net" and "Change").
3. Aligning Columns
Unfortunately, centering the column of data does not properly align the decimal points of the column if the numbers have different numbers of digits. Instead, try right justifying the numbers and using the indent functions to center the column.
4. Decimals.
It may also be necessary to adjust the number of decimal places, particularly
for the newly calculated "change" variable. Use:
Format | Cells | Number (and specify the number of decimal places)