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 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 3a
Exercise 4e
Absolute Cell Addresses BEDMAS Percentages
Exercises for use with the Excel FFF Online Video Course
Rank
Page 2
EXERCISE 1 Open the file Excel FFF Exercise 1.xlsx The file contains data about disability and employment from the ONS Labour Force Survey 2011. 1. Rename Sheet 1 to Employment Rename Sheet 2 to Employment by impairment Rename Sheet 3 to Full and part time
2. Change the tab colours to any colour of your choice 3. Add a new work sheet and name the sheet Skill level 4. Enter and format the data as shown below Percentage of people employed in different skill groups of job (UK)
High
Upper-middle Lower-middle Low
DDA disabled
24.32
24.65
39.55 11.48
Not disabled
26.83
27.93
34.33 10.91
Open the workbook Excel FFF disability_and_employment.xlsx 5. Copy the worksheet Country & Region, to the Excel FFF Exercise 1.xlsx workbook (select the sheet – click Format on the Home tab – choose Move or copy sheet...). 6. Close the Excel FFF disability_and_employment.xlsx workbook 7. In the Excel FFF Exercise 1.xlsx workbook reorder the sheets as shown below
8. On the Full-time and part-time sheet delete rows 2, 3 and 4 9. Delete column C 10. Autofit column A to contents 11. Format cells B3:C4 to two decimal places
Exercises for use with the Excel FFF Online Video Course
Page 3
12. Merge and centre cells A1:C1 13. On the sheet Employment by Impairment add the text Main impairment in cell A4 14. Delete the comment in cell D5 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
Exercises for use with the Excel FFF Online Video Course
Page 4
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).
Exercises for use with the Excel FFF Online Video Course
Page 5
EXERCISE 3 All these exercise sections are in the workbook Excel FFF Exercise 3.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.
3a) ABSOLUTE CELL ADDESSES EXERCISE Go to the Absolute sheet and follow the instructions
3b) NAMING CELLS AND RANGES EXERCISE 1. Go to the Conference costs sheet and follow the instructions 2. Go to the International client’s sheet and follow the instructions
3c) TEXT TO COLUMNS EXERCISE Go to the “Text to columns” sheet and follow the instructions
3d) MIXED REFERENCING EXCERCISE Go to the Mixed referencing sheet and follow the instructions
Exercises for use with the Excel FFF Online Video Course
Page 6
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 B36 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.
Exercises for use with the Excel FFF Online Video Course
