The best, safest, and most consistent way to start a formula is to use the = sign, but formulas may actually start with any one of the following characters:

Visit the Excel Help Page for an Internet-based tutorial. Some interesting examples are provided in the Business Solutions section.

Microsoft has several tutorials available: Beginning, Gathering the Data, Formatting, Working with Numbers, Formulas, and Functions, Using Proofing Tools, Using Charts, and Putting it All Together.

Some more advanced Excel tutorial information may be found at the Excel Skills Guide.

1. Click on the cell which you want to copy
2. Place the large cross pointer in the ceel which you want to copy.
3. Using the large cross pointer, Click and drag from the source down to the last cell in the range you want to copy.
4. Right click on the highlighted area.

5. Select Paste.

1. Enter the first two years
2. Click in the first year's cell.
3. Click and drag to the second year's cell so that both year's cells are highlighted.
4. Move your pointer to the fill handle (the small box in the lower right corner of the highlighted cells) and click and drag the fill handle to the last cell that you want to fill.

1. Type the text.
2. Right click on the cell.
3. Click on the Alignment tab.
4. Click on Wrap Text.

---

Price Forecasting Spreadsheet

You run a computer manufacturing company. You are trying to forecast the retail price of your basic computer system for the next four years.

Construct a spreadsheet that computes the system price for projected price changes. Your research suggests that the price of electronic parts will decrease, while labor, facilities, and advertising will increase.

Set up your spreadsheet as shown. Enter and copy the formulas using absolute references, and then the numbers. Finally, format your spreadsheet.

A	B	C	D	E	F
1   ITEM	Change	1998	1999	2000	2001
2   Monitor	-3%	100
3   Sys.Unit	-7%	195
4   Keyboard	-5%	45
5   Labor	30%	50
6   Facilities	10%	30
7   Advertising	50%	25
8   Projected Price

1. Type the text.
2. Type the formula (=C2+($B2*C2) ) in D2. The $ in the $B2 reference keeps the column reference constant as you copy the formula across the columns.
3. Copy the formulas from D2 to D2:D7, then across to F7.
4. Type the formula in D9.
5. Copy the formula from D9 to E9..F9.
6. Enter the numbers.
7. Mark C7 to F7. Right-click. Select Format Cells, Border or use the Border Tool to put a border in the bottom of row 7.  (Try several different styles and colors to see which looks best.)
8. Format the spreadsheet for zero decimal places and a percent sign in column B.
9. Format the spreadsheet for zero decimal places in columns C through F.
10. Insert Row 1. Add the title Itty Bitty Machine Price Projection in cell A1.
11. Experiment using fonts or other enhancements.

 

Inserting a Row

1. Click on the Row Header Number (the number all the way on the left of the spreadsheet).
2. Right click.
3. Click on Insert.

Center Across Columns

1. Type the text.
2. Click and drag across the columns that you wish to center the text.
3. Click on the Merge and Center button on the Formatting Toolbar.

---

Bowling Spreadsheet

             A                B                 C                 D                 E                 F                 G                 H   
  1   Bowling Scorekeeper
  2   Nine Pins Bowling Team
  3   Date
  4                                             Games                          Total       Number of       Average    High
  5   Name              1                   2                 3                                Games           Game     Game
  6
  7    Don
  8    Tim
  9    Matt
10   Sue
11  June
12   Dave
13  Judy
14  Steve
15
16  TOTALS
17 Team Average................................................

Directions:

1. Enter all text.
2. Enter all formulas using the copy command.
3. You will use the following functions:

=SUM(range)

=COUNT(range)

=AVERAGE(range)

=MAX(range)

(Note: you will get an error message after you enter the AVERAGE function because division by zero is not defined.)

4. Enter the numbers (scores):

Don 190 188 210
Tim 162 200 205
Matt 145 186 181
Sue 222 209 230
June 157 171 177
Dave 167 183 250
Judy 135 183 154
Steve 159 176 182

5. Format the spreadsheet.
6. Create borders as shown.
7. Set the column width.
8. Display only whole numbers - no decimals.
9. Justify the text as necessary.

 

---

Budget Spreadsheet

           A         B            C                 D                 E                F

  1   Debit     Code Account 1 Account 2 Account 3 Account 4
  2
  3   500.00     3
  4   125.50     4
  5     22.01     4
  6     38.88     2
  7   954.19     1
  8   104.00     3
  9   424.50     4
10     84.47     1
11     96.88     3
12   840.51     2
13      22.17    1
14   TOTAL

Directions

1. Enter all text.

2. Use Borders and Shading to create an interesting heading.

3. Enter all formulas in row 3. Copy them.

4. Use the following functions as examples:

=SUM(C3..C13) =SUM(range)

=IF(B3=1,A3,0) =IF(STATMENT A IS TRUE, DO THIS, IF IT IS NOT TRUE, DO THIS)

5. Enter the numbers.

6. Format the spreadsheet.

7. Set the column width.

8. Display 2 decimal places for all dollar amounts.

9. Display 0 decimal places for the CODE number.

10. Justify the text as necessary.

11. Put borders where they are needed to separate the different sections of the spreadsheet.

12. Extra - Design the spreadsheet so you can tell how much is left in each account. The starting balances are:

Account 1 $1000.00

Account 2 $ 600.00

Account 3 $3000.00

Account 4 $ 700.00

 

---

Sales Commissions

You want to design a spreadsheet that will calculate the commission for your sales representatives. There are three different commission rates. Sales under $5000 receive a 3.5% rate. Sales over $8000 receive a 4.5% rate. Sales over $8000 receive a 6.5% rate.

Use the following data:

Johnson sold $5000. Jones sold $3500, Brown sold $6000, and Smith sold $9500 this month. Use the LOOKUP function to calculate their commission.

               A                            B                                   C                                                       D
1 Sales Commissions
2       NAME             Amount Sold          Commission Rate                                    Amount
3     Johnson                  5000.00            =VLOOKUP(B3,A$15:B$18,2)           =(B3*C3)
4
5
6
7
*
*
15         0.00           3.5%
16  5000.00          4.5%
17  8000.00          6.5%

Directions:

1. Enter all text.
2. Put a border at the bottom of row 2.
3. Enter all formulas using the copy command.

Function:

=VLOOKUP(amount sold, range of the amount sold in the lookup table, number of cells offset to return the commission rate)

NOTE: The $ before the row number in the function keeps the reference to the lookup table constant.

4. Enter the numbers.
5. Format the spreadsheet.
6. Set the column width.
7. Display 2 decimal places.
8. Display the COMMISSION using dollar signs.
9. Add borders and shading.
10. Change the font, font size, and color as needed.
11. Justify the text as necessary for the best presentation.

 

---

International Sales Comparisons

 

A	B	C	D	E
1  Comparative International Sales
2  (By City and Month)
3
4 1998
5	Jan.	Feb.	Mar.	Total
6     New York	10	50	22	82
7     Montreal	20	80	10	90
8     Paris	30	20	40	90
9     Moscow	40	15	60	115
10     Seattle	100	30	15	145
11     Lima	60	110	99	269
12     Bonn	20	33	122	175
17
18     TOTALS	280	338	348

Directions:

1. Create the spreadsheet above. Copy the formulas. Format.
2. Get the menu.
3. Select the area to graph.
4. Click on the Graph Wizard tool.
5. Mark the area on your spreadsheet where you want to place the graph.
6. Follow the Wizard's directions.

Graph the following:

1. Graph for all cities for one month.
2. Graph one city for all months.
3. Graph all of the cities for all months.
4. Graph the monthly totals.
5. Graph the monthly totals as percentages.

Label your graphs. Experiment with the different types of graphs. What type of graph would you use to:

Compare monthly sales of one city to another.

Show a trend of sales over time.

Show daily stock prices.

Show percentages of a whole.

Copy the chart to a Word document.

 

---

Hotel Customers

As a manager of a hotel, you want to develop a graphical profile of your hotel's customers for a year. First, you must enter the data into a spreadsheet.

Month	General	Business	Convention
Jan.	225	309	88
Feb.	747	303	471
Mar.	578	521	211
Apr.	370	222	287
May	519	546	837
Jun.	382	815	354
Jul.	684	228	524
Aug.	520	537	85
Sep.	700	660	921
Oct.	431	439	414
Nov.	1260	717	743
Dec.	673	991	712

Select the best type of graph to show the following:

1. A comparison of the percentage of each type of customer for the entire year.
2. The trend of the numbers of each type of customer.
3. A comparison of the number of each type of customer for each month.
4. A comparison of the total number of customers for each month.

NOTE: You will need to add another row or column of data to your spreadsheet.

---

Final Grades Spreadsheet

You are a teacher with five students. Create a spreadsheet for your test grades. There are five tests. Each test counts the same in determining the final grade. Use a nested =IF function to determine the letter grade. Your spreadsheet should contain the following information:

Student Name	Test Scores					Highest Score	Lowest Score	Average Score	Grade
S. Wozniak	66	78	95	51	87
A. Osborne	76	8	74	82	85
S. Jobs	89	74	90	77	84
M. Kapor	67	69	72	75	77
B. Gates	87	85	84	77	82

Also:
Each test score must be in a separate cell. (Note: You will need 5 columns for the test scores.
Format all cells so there are no decimal places.
Find the average score for each test.
Insert a title in Row 1.
Format the column headings so they wrap around.
Put each label in one cell.
Enhance the spreadsheet by using lines, font enhancements, etc.
Use a nested IF function

=IF function example:
=IF(AverageScore>=90, "A",=IF(AverageScore>= 80,"B"....)) etc.
Each ( must have a corresponding )

Graph the scores so that you can compare all test scores for all students.
Protect your formulas.

 

Optional:
You decide to assign a final paper that counts 50% of the grade. Change the spreadsheet to reflect a 50% test and 50% paper grade.

---

Salary Schedule Spreadsheet

Enter the following data into your spreadsheet:

Last Name	First Name	Dept.	Years	Salary
Smith	D.	Executive	7	48597
Brossmer	E.	Executive	3	20741
Stone	J.	Executive	3	36471
Braun	K.	Executive	4	17386
Ross	K.	Finance	5	44661
Gregory	L.	Finance	10	32318
Kolb	L.	Marketing	8	41907
Parent	S.	Marketing	4	23919
Hibscher	J.	Personnel	12	33647
Arnett	T.	Production	1	48229
Black	M.	Production	9	47887
Lacy	A.	Production	5	41112
Baim	M.	Production	3	24045
Skyles	G.	Production	11	11416
Gossman	M.	Production	2	27577

1. Sort the data according to NAME, YEARS, and SALARY.
2. Determine the average salary, and years of service.
3. Display the salaries in a chart form. Determine what you want to display.

---

Teachers' Salary Schedule

Use the following table to create a salary schedule that will return the correct rate of pay for the following teachers:

Name Years of Service   Degree     Salary
Brown        6                          MA
Smith      10                           BA
Jones      20                          PHD

 

                                        Degree
Years of Service        BA          MA        PHD
   0                          20000      25000    30000
   6                          25000      30000    35000
  11                         30000      35000    40000
  16                         35000      40000    45000

Use an =IF function to evaluate the degree when the degree is written BA, MA, PHD.

Hint: LOOKUP the Years in the lookup table, =IF the Degree = "BA" then go 1 cell over, =IF the Degree = "MA" then go 2 cells over, and =IF the Degree = "PHD" then go over 3 cells.