Excel 2014-Formulae,Functions,& Formatting Excercises Instructions, Exercises of MS Microsoft Excel skills

Download Excel 2014-Formulae,Functions,& Formatting Excercises Instructions and more Exercises MS Microsoft Excel skills in PDF only on Docsity!

Excel 2014

Formulae, Functions

& Formatting

Exercise Instructions

LSHTM Online Software Training

How to use this Excel 2013 Formulae, Functions & Formatting Video

Course

 Watch the Introductory Video first, making notes if you wish. Note the videos have sound, so you will need to use headphones / speakers  Each of the Exercise Videos cover the skills needed to do a specific exercise , so watch a video, then do the exercise that goes with it. Some exercises build on previously used skills, so working through the exercises in the order they are in this document will work best.  The exercise instructions are listed in this book.  The files to use the exercises are: Excel FFF disability_and_employment.xlsx, Excel FFF Exercise 1.xlsx, Excel FFF Exercise 2.xlsx, Excel FFF Exercise 3.xlsx, Excel FFF Exercise 4.xlsx

Skills Covered in each Video / Exercise pair

Introduction Video (No Exercise to do)

 What is Excel used for?  Columns / Rows / Cells  Workbooks and Worksheets  Cell addresses  Entering Text / Numbers / Formulae / Functions  Alignment  Column widths  F2 to Edit cells  Selecting and formatting cells

Exercise 1

 Rename and colour tabs  Add / delete / move / copy worksheets  Delete / insert / change widths columns  Change decimal places shown  Word wrap / Merge and centre  Autofill

Exercise 2

 Transpose  Simple formulae / Functions  Percentages

Exercise 3a

 Absolute Cell Addresses  BEDMAS  Percentages

Exercise 3b  Naming cells and ranges Exercise 3c  Text to Columns Exercise 3d  Mixed referencing Exercise 4a  Trim  Paste Special – Values  Remove Duplicates Exercise 4b  Concatenate Exercise 4c  Counta  Countif Exercise 4d  If Exercise 4e  Rank

12. Merge and centre cells A1:C
13. On the sheet Employment by Impairment add the text Main impairment in cell A
14. Delete the comment in cell D
15. Adjust column A so all text is visible
16. Insert a new column to the left of column A
17. Starting in cell A5 use the autofill command to fill the column 1 to 15 as shown below
18. Save and close the workbook

EXERCISE 2

1. Open the workbook Excel FFF Exercise 2.xlsx. On the England and Wales worksheet select the data range B2:G15. Transpose the date (copy – paste – transpose) to start in the cell B17.
2. Copy the new data range to cells A6:N11 on the Number of deaths worksheet
3. On the Number of deaths worksheet enter a formula in cell B12 to calculate the total mentions for 1999.
4. In cell B13 calculate the percentage male, and in cell B14 calculate the percentage female, for 1999.
5. Use the fill handle to copy the formulae in B12:B14 across for all years
6. Delete the formula from cell C12:C14 as there are no figures for that year.
7. In cells O10 and O11 calculate the total for all years
8. Go to the More functions worksheet and add the correct functions to the shaded cells (TIP: the name of each function is underlined ).

EXERCISE 4

All these exercise sections are in the workbook Excel FFF Exercise 4.xlsx

It is easier to do each of these exercise sections separately. Watch the skills video for each part and then do the corresponding exercise section before going on to the next video.

EXERCISE 4a TRIM

Go to the Trim sheet and follow the instructions

EXERCISE 4b CONCATENATE

Go to the Concatenate sheet and follow the instructions

EXERCISE 4c COUNTA / COUNTIF

1. On the CountA worksheet insert a function in cell B36 to calculate the total number of students in attendance at the Excel class
2. On the CountIf worksheet insert a function in cell B which will calculate the number of female students. Repeat in cell B37 to calculate the number of male students.

EXERCISE 4d IF

1. On the If worksheet add a function in cell E2 which will return the value ‘pass’ in the figure in cell D2 is greater than or equal to 50 and ‘fail’ if the figure is below 50.
2. Copy the function down the column.
3. Use an appropriate function in E50 and E51 to count the number of passes and fails.

EXERCISE 4e RANK

1. Go to the Rank sheet
2. Select cells D2:D36, name the range as ExamResults
3. In cell E2 use the Rank function to rank D2 compared with the other students’ exam results
4. Hint: =Rank(cell to rank, range)
5. Use the fill handle to copy the formula and rank all the other students’ results.