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 cancel4) 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 right5) Select from keyboard:Shift+Arrows
select many areas: Ctrl6) Note that most of the workstations have been installed for Finnish settings:
- , comma instead of decimal point
- date options: p päivä = day
- k kuukausi = month
- v vuosi = year
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.
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.
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.
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 € |
Created by: Jaana Holvikivi Updated: 4.2.2015 |