# Task SP1: EarthDigital

In this first task you will learn some of the fundamental skills you need to get started with spreadsheets, including:

- Opening a CSV file & saving as a spreadsheet
- Resizing & formatting columns, rows & cells
- Using formulas & functions to create models
- Using relative and absolute cell references, including named ranges
- Using complex functions like LOOKUPs to make decisions based on your data
- Replicating formulae & functions across many cells
- Preparing a spreadsheet to print
- Adding data to the header & footer

Follow the instructions step-by-step, and expand the **“Find out how”** sections for more guidance if you need it.

EarthDigital is a Taiwanese technology company that sells laptops to schools in four countries; Taiwan, Malaysia, Hong Kong & Thailand. The spreadsheet for this task (**earthdigital.csv**) contains their sales figures for last year. Follow the instructions carefully, step-by-step.

## Instructions

1. Open the file **earthdigital.csv** in an appropriate spreadsheet package. Make all columns **wide enough** to see the data inside them (1).

##### Find out how

2. **Merge & center** cells **A1:H1** (1) & format as **serif**, **26pt** **bold** (1).

##### Find out how

3. Select cells **A3:H3** & format as **bold**, with an **orange** background (1).

##### Find out how

4. Format cells **A4:A46** as **short date** (1).

##### Find out how

5. Use a function in cell **C4** to look up the **representative** for the region based on the region in **B4**, from the range **K4:L7** (2). Make this range **absolute** (1) & **replicate** down the column (1).

##### Find out how

6. Select cells** J12:K15**. Name this range ‘**laptops**’ (1).

##### Find out how

7. Use a function in cell **F4** to **look up** the price of the laptop from cell **D4** from the **‘laptops’** range you created in step 5 (2). **Replicate** this function down the column (1).

##### Find out how

8. In cell **G4** enter a formula to calculate the **total cost in NTD**. (1) **Replicate** this down the column. (1)

##### Find out how

9. Select cells F4:G46 & format as accounting, currency NT$ Chinese (Traditional, Taiwan) (1).

##### Find out how

10. In cell **G47**, use a **function** to calculate the** total value of sales** (1).

##### Find out how

11. In cell **F47**, write **‘Total’** (1). Format both cells as **bold** (1).

##### Find out how

12. In cell **H4**, use a **function** to look up the exchange rate from the table **K4:N7** & **multiply the total price** in cell **G4** by this value (3).

##### Find out how

13. Select cells A3:H46 & format this as a table in any readable style (1)

##### Find out how

14. **Sort** the table in **ascending order of Region** (1).

##### Find out how

15. In the column **Total (local currency)** format each cell with the appropriate currency formats. You can find these under ‘Other accounting formats’. Use the following currencies (4):

**Taiwan** **NT$** or **NTD** Chinese (Traditional, Taiwan)**Thailand ** **฿ Thai** or **THB **(Thailand)**Hong Kong** **HK$ Chinese** or **HKD **(Traditional, Hong Kong S.A.R.)**Malaysia** **RM Malay** or **MYR **(Malaysia)

##### Find out how

16. Select cells **A1:N47** & **set print area** (1). Make the spreadsheet** fit on one page** (1), formatted** landscape** (1).

##### Find out how

17. Add your name to the header, **left aligned** (1), the centre number **centre aligned** (1) and your candidate number **right aligned** (1).

##### Find out how

18. **Print** (1), ensuring all text is readable (1).

##### Find out how

19. **Show the formulas** (1), making all columns only **wide enough** to fit their contents (1) and **print cells A1:H47**, ensuring all text is readable (1).

##### Find out how

20. **Save** the file as **earthdigital.xlsx** (1)

##### Find out how

**Total marks: 40**