SkillsTo learn basic spreadsheet skills with Microsoft Excel.
- Simple Formatting: fonts, type size, bold/italics, justifying, adjust column width
- Format as Currency
- Add and Delete Sheets
- Use Formulas to Add, Subtract, Multiply, Divide)
- Use Functions: SUM, AVERAGE, MIN, MAX
- Use Ranges in Functions
- Fill Down Formulas
NOTE: For the next lab, you will build on what you do for this lab, so be careful not to lose your work.
NOTE: Some things will be filled in later, so if the instructions do not ask you to put things in cells that seem to need something, just leave them blank for now.
Make sure that money amounts are always formatted as Currency (ie. with dollar signs, and with two decimal places if cents are involved).
Be sure to use formulas with cell addresses everywhere calculations are done. Don't just type in the answers as plain numbers, even if you can do them in your head or on a calculator. The purpose of this assignment is to give you practice using formulas and cell references.
Be sure to save frequently!
Your results should be similar to Crunch the Numbers, so look there if you are not sure how something should look.
Sheet 1: Pay Me Now, Pay Me Later
In this section, you will compare median salaries for a variety of jobs in a city you choose.
-
Open Excel to create a new document. Save the document using the normal naming pattern:
John Smith Lab 5 Crunch. -
Change the tab of the first sheet to Pay Me. For all the sheets in this lab, be sure to name them as given and make sure they are in the order given so they can be graded more efficiently.
-
Type in labels as shown below. For all the sheets in this lab, put the Sheet Title in cell A1 in bold, size 14. (You can change the wording of the title and the font and size if you want to, but make it at least size 14.) Right-side labels are always bold, right-justified, size 11, and end in : . Column headers are always bold, centered, size 11.
-
In B3 and B4, enter a zip code and city you will use when searching for job salaries
-
Go to www.salary.com and use the Salary Wizard to look up job titles (or browse by job category) for the zip code you chose. Look up six different jobs and for each one enter the Job Title, 25th% Base Salary, and 75th% Base Salary in A7-C12.
To save time and reduce typing errors, copy and paste from the browser when possible. Just be sure to put the fonts, sizes, and formatting back to normal (Calibri size 11 font) after you finish pasting. You don't want a jumble of different formats on your sheet.
-
In D7, enter a formula to calculate the average salary of the 25th% and the 75th% values. Use the AVERAGE function with the cell addresses, for example: =AVERAGE(B7:C7).
-
Fill down cell D7 to get the average salaries for the other jobs in column D.
-
In F2, F3, and F4, enter formulas to find the Minimum, Maximum, and Average of the salaries in column D.
Sheet 2: Market Watch
In this section, you will examine the changes in the stock value over a period of years of a company you select.
-
Go to the second sheet in the document and change the tab to Market Watch.
-
Type in labels as shown below.
-
Go to www.marketwatch.com and use the search box at the top of the page to find the proper name and the stock symbol for a company you choose. For example if you type in disney (without hitting enter or search), you will see Walt Disney Company with symbol DIS. Note that foreign branches of a company may appear.
-
Click on the company you choose in the list that appears to look up today's stock value.
-
In your spreadsheet, enter the official company name and symbol in B2 and B3.
-
Enter today's date and the current (or closing) price (ie. the big number under the company name) in cells A10 and B10.
Click on Historical Quotes on the menu. Enter today's date for one year ago and click the arrow button.
If your date shows up as a weekend (there is no trading on weekends) then change the day to be the preceeding Friday. (Ex. 5/23/2004 appears as a Sunday, so enter 5/21/2004 to get the Friday.) For the next lookup, return to the same day (eg. 5/23/2003 in this example.)
-
Enter the date and the closing price in your spreadsheet in columns A and B, beginning with Row 11.
-
Repeat this six more times so that you have a total of 8 years of closing prices, going down the rows from most recent to oldest.
If your company is less than 8 years old, go backwards in 6 month increments (or change companies!). Some companies may be like Google, which has been around since 1998 but was a private company the first few years, and has only been trading stock since 2004.
NOTE: Some of the cells for this sheet such as the Investment, Shares, and the Value column will be filled in later, in the next lab. Just leave them blank for now.
Sheet 3: How Hot Can You Get
In this section you will compare yesterday's high and low temperatures and daylight hours for a variety of cities around the world.
-
Go to the third sheet in the document and change the tab to How Hot.
-
Type in labels as shown below. For the Temperature (°F) label, merge cells C4-E4 together by highlighting them and clicking the Merge & Center
button. Then click the Bottom Border 
button to underline the whole cell. You can get the degree (°) symbol on the Insert tab, click Symbol, scroll until you find the degree symbol, and click the Insert button.
-
Enter yesterday's date in B3.
-
Go to www.weather.com and look up yesterday's weather for a city in the United States. In your spreadsheet, record the city, country, high and low temperatures, and the sunrise and sunset times.
As you enter the time, after typing the hour and minutes, type a Space and then AM or PM. The computer will format the cell properly if you type in the Space there.
-
Look up five more cities from different countries outside the US and record the same information.
Sheet 4: What People Believe
In this section you will compare the number of believers in the world's major religions.
-
Create a new sheet in your document with a tab named Believe and move it so it is the fourth sheet.
-
Type in labels as shown below.
-
Go to the page Worldwide Adherents of All Religions (Encyclopædia Britannica - 2007).
-
Pick one of the continents listed in the table (except Oceania), or you can choose the whole World column if you like, and type in that continent in cell B3 of your spreadsheet.
-
Read the two notes below and then, for each religion listed in the rows of the table, enter the name of the religion and the number of adherents in your spreadsheet for the continent you chose, beginning in cells A6 and B6, one religion per row.
Note: that Christians are listed in subgroups in the table, so only include these rows: Roman Catholics, Independents, Protestants, Orthodox, Anglicans, Marginal Christians, and Unaffiliated Christians.
Note: Include all the other religions, but skip any religions that have less than 1 million adherents for your region. (If you chose the whole world, skip any with less than 10 million.)
-
Format the numbers so that they show whole numbers with commas at the millions and thousands places.
-
In the row after the last religion enter a right-side label Total: in column A.
-
In the same row enter a formula in column B to calculate the total number of adherents for your region.
Sheet 5: Countries of the World, Unite
In this section you will analyze demographic information from a country of the world which you choose.
-
Go to www.google.com (or another search engine of your choice) and search for the CIA World Factbook. In the Factbook, select a country (pick a real country, not just a location, territory, or barren island).
-
Create a new sheet in your document and put the name of the country on the tab for the sheet. Move the sheet so it is the fifth sheet.
-
Type in labels as shown below.
-
In cell B3 enter the name of the country.
-
In the People section, look for the population and enter it in cell B4. Put when that population figure was determined in cell C4 (eg. July 2007 est.)
-
Look for two demographic statistics in the People section where a percent breakdown is given (with at least three items each). It should be data suitable for a pie chart, that is, it is the kind that adds up to approximately 100% (ie. literacy rates do not work for this). If you choose statistics other than Reglions and Ethnic Groups, change the labels in cells A6 and E6 accordingly.
-
Enter the items and percents in columns A and B for the first statistic beginning in row 7, and in columns E and F for the second statistic. The numbers should be formatted as percents.
-
For each of the two demographics, enter a right-side label Total: under the last item.
-
For each of the two demographics, enter a formula under the last percent to calculate the total percent, formatted as percents. The total may not add up to 100% exactly, but what matters is that your formula is correct.
-
At the top of the page for the country, there is a small picture of the flag. Click on the flag to enlarge it.
-
Copy the flag and paste or place the flag on your sheet. Resize the flag so that it is about 5 columns wide. Dragging from one of the four corners instead of from the sides will keep the picture from distorting. Place the flag on the upper right-hand side of the sheet.
Sheet 6: Gas Guzzlers
In this section you will compare oil consumption for different countries around the world.
-
Create a new sheet in your document with a tab named Guzzlers and move it so it is the sixth sheet.
- Type in labels as shown below.
-
In the CIA World Factbook, go to the Home page, then Definitions and Notes, and then Oil - consumption.
-
In cell A5 enter United States and in cell B5 enter the oil consumption for the US from the Factbook. (Bbls/day means barrels of oil consumed per day.) Be sure to enter millions as as numbers, not words (eg. 15 million should be entered as 15,000,000).
-
Select 7 other countries and enter their names and oil consumption figures in the next rows. Format the numbers so that they show whole numbers with commas at the millions and thousands places.
-
Return to the Definitions and Notes page and go to the Population page. Enter the population figures for the same countries in column C. Format the numbers so that they show whole numbers with commas at the millions and thousands places.
- Save your presentation (be sure it is named according to the usual pattern: John Smith Lab 5 Crunch) and use the Drop Box to submit it.
Grading
Pay Me: all labels correct Pay Me: zip code and city entered correctly in B3-B4 Pay Me: six job titles entered in A7-A12 Pay Me: six 25th% salaries entered in B7-B12 Pay Me: six 75th% salaries entered in C7-C12 Pay Me: cell D7 has correct formula for average base salary Pay Me: cell D7 filled down to cells D8-D12 Pay Me: min, max, avg formulas correct in F2-F4 Pay Me: all money values formatted as currency Market Watch: all labels correct Market Watch: company name and symbol entered correctly Market Watch: date and closing prices entered correctly (for 8 years) Market Watch: all money values formatted as currency Market Watch: all dates formatted as dates How Hot: all labels correct How Hot: C4-E4 merged, underlined, degree symbol included How Hot: yesterday date entered and formatted as date How Hot: six cities and countries entered (5 non-US) How Hot: six city high and low temperatures entered How Hot: six city sunrise and sunset values entered How Hot: sunrise and sunset values formatted as AM/PM times How Hot: high and low temperatures formatted as integers with no units Believe: all labels correct Believe: all religion names entered, none missing Believe: all adherents entered and formatted as integers with commas Believe: total label and formula correct Country: all labels correct Country: country name (B3), population (B4), date population determined (C4) Country: flag included, of the larger size (not the thumbnail size) Country: data for first demographic complete (adds up to close to 100%) Country: data for second demographic complete (adds up to close to 100%) Country: percent data formatted as percents Country: total formulas correct and formatted as percents Guzzlers: all labels correct Guzzlers: USA and seven other countries entered Guzzlers: consumption data entered for the eight countries as integers with commas Guzzlers: population data entered for the eight countries as integers with commas sheet tabs named correctly, in correct order
