Introduction to Spreadsheets

| HOME | SYLLABUS | CALENDAR AND EXERCISES |

Spreadsheet software

EXCEL USE, general instructions:

1) Always watch what is going on: follow the information on formula row, title bars and on bottom of the screen.
2) Mouse buttons: left for selections
right for Quick Menu
3) Edit / Undo, Ctrl+Z or (Esc) to cancel

4) Hot keys: Ctrl+Home go to A1
Ctrl+End go to the lowest right corner
PgUp, PgDn
Ctrl+PgUp screen to the left
Ctrl+PgDn screen to the right

5) Select from keyboard:Shift+Arrows
select many areas: Ctrl

6) Note that most of the workstations have been installed for Finnish settings:

7) Save all your work in the same book on different sheets. You can select the sheet from the tab on the bottom. Hand in the work by demonstrating your file to the teacher and by posting it on your pages.

PRACTICE 1

1) Start Excel. In this practice, follow the instructions step by step, unless you use OpenOffice or other spreadsheet software.

2) Enter table 1 starting from upper left corner (cell A1). Type the text "PC Sales worldwide in Q3/2009-10 " in A1 and press (Enter). Then type the text to the cell below and so on, move down again and enter all texts in column A.

Move to A1. Format it by selecting boldface. Change the typeface in A2 into Italics. Widen the column:

Or choose Wrap text.

Fill in the titles in row 3. For D3, enter the text and align it to the right by selecting "right" button from toolbar. Fill the table B4..C10.

  A B C D E
1 PC Sales worldwide in Q3/2009-10        
2 Vendor unit shipments  Sales Sales    
3 Millions of units Q3/2009 Q3/2010 Market
share
2010
Growth
4 HP 15,53 15,45    
5 Acer 11,77 11,57    
6 Dell 9,87 10,77    
7 Lenovo 6,90 9,18    
8 Asus 3,89 4,77    
9 Toshiba 4,00 4,68    
10 Others 30,60 32,41    
11 Total units        

When finished, save your workbook. Call it Learninge.xlsx. Give a name to the Sheet1: On the Sheet tab bar, right-click the sheet tab that you want to rename, and then click Rename Sheet. Rename pcsales. Continue with the same sheet.

Enter a formula for totals. Move to B11. Click S (SUM) from ribbon with mouse. You should get SUM(B4:B10). Copy the function to C11: in B11 select Copy, point C11, press (Enter).

3) Enter functions: move to D4 and type:

=C4/C11
Format number: 0,00% from the ribbon buttons.

Extend the function: select D4:D10 with mouse. From the ribbon, select Autofill (below the sum symbol) Fill down (Enter). You should see the percentages in D5 and D10, but there is on error. Fix D4: move to D4, press F2, point to C11 and press F4. Press enter and repeat the Autofill.

Move to E4 and type:

= and point C4 with mouse and click, type /, point B4 and  type -1 (Enter).

Format number: 0,00% from the ribbon buttons.

Extend the function: select E4:E11 with mouse. Fill down (Enter).

Now your table should look somewhat similar to the table below.

  A B C D E
1 PC Sales worldwide in Q3/2009-10        
2 Vendor unit shipments  Sales Sales    
3 Millions of units Q3/2009 Q3/2010 Market
share
2010
Growth
4 HP 15,53 15,45 17%  -0,50% 
5 Acer 11,77 11,57 13%  -1,70% 
6 Dell 9,87 10,77 12%  9,20% 
7 Lenovo 6,90 9,18 10%  33,00% 
8 Asus 3,89 4,77 5%  22,50% 
9 Toshiba 4,00 4,68 5%  17,00% 
10 Others 30,60 32,41 36%  5,90% 
11 Total units 82,56  88,83    8,65% 

5) Make a graph from your table. First, select only the sales of Q3/2009 and express them as a histogram (column chart).
Select area B4:B10.

When your chart is finished check that it looks meaningful. Try also other chart types to the data. Remember to save your work.

6) Make another graph which includes sales of all computers in two years. Name Source data Series from column A, and include a legend.
Make a pie chart showing market shares in 2010. Save your workbook.

7) Continue working with sheet pcsales.

Produce some statistics out of the data of 2010. Add the following lines under the table: average sales by six largest suppliers, median sales, maximum and minimum value. Use Formulas Insert Function, Statistical.

2) Signal analysis

Add a new worksheet to your assignment workbook. Rename it Signals.

Enter text to upper left corner (cell A1): "Spectral Analysis of Signals"

Enter column headings to cells A3 to F3. The signal consists of four components. Format headings by selecting boldface.

t sec DC 0 Hz 15 Hz 30 Hz 45 Hz SUM

Enter time points to column A. A4 is 0, and A5 is A4+0,002, A6 is A5+0,002 and so on until A48.
The signal components are sinusoids v(t) = 4 + 3cos( ......) that are calculated as follows:

and F4 is the sum of component values from B4 to E4. Extend the formulas down to row 48.

Produce a chart of your data. Select the area A4..F48, and Insert Line Chart. Select for the chart type a continuous line. Add titles and make the chart look neat. Save your work.

Practice 3: Mortgage payment

Start Excel. Open your workbook (the same as in the previous practice) and open another sheet. Give a name to it: Sheet Rename loan. Read all instructions carefully before starting!
You will make a table to calculate annuity payments for a loan. Start from the upper left corner (cell A1) entering the basic data about payment conditions like in the table below. Enter only values, and format cells later to currency format and percentage. Adjust the columns so that they are wide enough.

A B C D E F
Loan amount 12 000,00 €     Yearly annuity payment  
Interest rate 7 %     =PMT(B2;B3;-B1)  
Repayment period 11        
           
Year Initial Interest Annual Remaining Cumulative
  balance   installment balance interest

Fill in the years: enter 1 to A7, and 2 to A8. Select A7:A8, grab the handle in lower right corner and drag it until A17. Release mouse button.

To cell E2, enter the formula for annuity as above, starting with an 'equal to' sign =.

Plan the formulas for cells B7;F17 so that the table can be recalculated for any amount of loan, and any interest rate. Don't enter any numbers, only cell references and formulas. Begin formulas with = or +. Also notice that you need to enter formulas only once and then copy them to the remaining part. To cell B7, make a reference which picks up the initial loan from B1.

When you copy the formulas which include the interest rate (B2) or annuity payment (E2), references to those cells must remain same everywhere in the table. You make them absolute references by pressing key F4, so that they look like $B$2 and $E$2. For example, cell D7 has the formula =$E$2-C7
The alternative way to pick a permanent reference is to name the cell: select E2 and from the tab Formulas, select Define Name, add name Annuity. Now you can write in cell D7 =Annuity-C7 . Finally, your table should look like the one below.

Loan amount 12 000,00 €     Yearly annuity payment  
Interest rate 7 %     1 600,28 €  
Repayment period 11        
           
Year Initial Interest Annual Remaining Cumulative
  balance   installment balance interest
1 12 000,00 € 840,00 € 760,28€ 11 239,72 € 840,00 €
2 11 239,72 € 786,78 € 813,50 € 10 426,21 € 1 626,78 €
3 10 426,21 € 729,84 € 870,45 € 9 555,77 € 2 356,62 €
4 9 555,77 € 668,90 € 931,38 € 8 624,39 € 3 025,52 €
5 8 624,39 € 603,71 € 996,58 € 7 627,81 € 3 629,23 €
6 7 627,81 € 533,95 € 1 066,34 € 6 561,48 € 4 163,17 €
7 6 561,48 € 459,30 € 1 140,98 € 5 420,50 € 4 622,48 €
8 5 420,50 € 379,43 € 1 220,85 € 4 199,65 € 5 001,91 €
9 4 199,65 € 293,98 € 1 306,31 € 2 893,34 € 5 295,89 €
10 2 893,34 € 202,53 € 1 397,75 € 1 495,59 € 5 498,42 €
11 1 495,59 € 104,69 € 1 495,59€ 0,00 € 5 603,11 €

 

| HOME | SYLLABUS | CALENDAR AND EXERCISES |

Created by: Jaana Holvikivi
Updated: 4.2.2015