Introduction

1. Course Title: MS Excel Tips & TricksTrack: Microsoft Office(Foundation)Course Tutor: Rehan Ahmadrehan.ahmad@thetrainingsquad.co.ukhttp://www.thetrainingsquad.co.uk

2. Introduction • Welcome • About me • Who’se Who? • Why this course? • What to expect • Rules – Oooh!

3. Overview • This course will cover some quick tips and tricks you can use to make your use of Microsoft Excel more speedy and efficient. • Many people already know how to use Microsoft Excel, but perhaps you need some timesaving techniques to help you work faster. • This short course will do just that: give you tips and tricks for working with Excel 2010.

4. Topics we’ll cover • Quick overview of Excel ribbons and environment • Adding numbers (+) - done • Creating a chart - done • Creating a workbook in Excel - done • Conditional Formatting – done - jerrys • Freezing and locking panes need to do • Adding and subtracting time – done jerrys • VLOOKUP, HLOOKUP

5. 1 - Overview of Excel environment Column Functions/Formulas Cell reference Row

6. ‘Run’ shortcuts • Run Calculator calc • Run Command Prompt cmd • Run Firefox (if installed) firefox • Run Internet Explorer iexplore • Run Microsoft Word (if installed) winword • Run Microsoft Excel (if installed) excel • Run Microsoft Outlook (if installed) outlook • Run Notepad notepad • Run Wordpadwordpad

7. 2 – Formulas, Buttons and Functions • Can add data into cells using formulas, buttons or functions • =123 + 234 • SUM(A1,B1,C1) • Or by using the SUM button on the top of the Excel program. • Let’s have a look at this in our Excel workbook.

8. 2 – Lets add a few cells together • Type a list of 10 numbers in a single column in Excel • Add a total field at the end of your column • To the field at the right of the total column enter • = • The with the mouse select your first cell • Hit ‘+’ • Then add the second cell • Repeat and hit enter once you have all your numbers • If you change the number in a cell, the total value will automatically update.

9. 2 – Adding cells and literal numbers • We can add cells by choosing them but also use the + sign to add in literal number such as • Adding cell b1+b2+10 • Let’s try it and see what result we get • Start by typing ‘=‘ into an empty cell • Then add two cells • Finally enter the + signfollowed by 10

10. 2 - AutoSum • Lets create a sheet which looks like this: • We want to use the auto sum button to do the sums for us • Use where you want to add totalled values • Or press alt and = • Give it a go

11. 2– Bulk auto sum • Lets select the columns and rows together and an extra column and row, then click auto sum. These extra cells we have highlighted will now hold the totals of the rows and columns, like so

12. 2 – A look at the SUM Dropdown We’ve seen how we can add/sum numbers and cells, but the AutoSum dropdown has more to offer: • Average • Count • Max • Min Lets play with these on our spreadsheet

13. 2 – Copying cells and formulas • To copy a cells formula we can simply drag the formulas down or across • Or we can just double click the crosshair at the bottom right of a cell – I’ll show you how.

14. 2 – SUM Vs AUTOSUM • I can only use auto sum for adjacent values • To sum or total non adjacent values I need to use the =SUM function • I can use it to select any cells in the sheet or even across multiple worksheet • Let’s take a look

15. 2 – SUMIF? • Allows you to sum a set of cells only if a criteria is matched • Have a play with this in your own time 

16. 3 – Creating a Column Chart • Enter data for monthly sales into your sheet • Select the two columns and all cells • Select insert - > chart • Job done!

17. 3 – Creating a Pie Chart • Enter data for monthly sales into your sheet • Select the two columns and all cells • Select insert - > chart • Job done!

18. 3 – Customising the Pie Chart • Right click on the ‘Sales’ label • Here you can modify the label style and size • Right click on the legend • Here again you can modify font styles/size • Right click on the chart and select 3D rotation • Also right click the chart and select add data labels • I can quickly change the chart type too • Right click, change chart type – Easy!

19. 4 – Insert / removing columns or rows • Right click and add rows or columns • You can remove column or rows in the same fashion • Double click to the right of a column header if your text overflow • You can also look at wrapping your text if needed

20. 5– Sample workbook • Jerry owns a cake shop. • He’s on the ball when it comes to keeping track of sales and orders • He uses Excel to do this – let take a look at how he does it • www.thetrainingsquad.co.uk/excelsheet.xlsx

21. 6– VLOOKUP • What is a VLOOKUP? • Too technical? • No, it’s an easy to use tool that can save time and hassle • Essentially the same principle as a phone book • Using information you know(name of person), to find information you don’t know(number)

22. 6– VLOOKUP • Used when you have a large list of codes and description and want to use one to find the other etc. • I’ve created an example you can find at www.thetrainingsquad.co.uk/vlookup.xlsx

23. 6– VLOOKUP - Examined • 4 bits on information for VLOOKUP The lookup The Table Column reference – the number of the column in the table – in this case 3 Item 4 is FALSE as we don’t want a partial match

24. 6– HLOOKUP • Used when you have a large list of codes and description and want to use one to find the other etc. • Same as VLOOKUP but horizontal

25. 7– Protecting workbooks • Create your workbook, save it, then go to • File, permissions then encrypt with password When you try to open your Protected workbook you will See the following prompt

26. 7– Protecting workbooks • Save your password in a safe place, if you forget it, there is no way to recover it and you won’t be able to open your workbook!