Microsoft Excel Training

1. Microsoft Excel Training September 16, 2006 CHART Hinche, Haiti

2. Agenda • Introduction to excel functions • Formatting • Useful tips • Formulas • Charts • Importing data • Printing excel worksheets • Practicing with reports

3. Intro to Excel Excel is used for budgeting, reporting, and data analysis Each workbook is made up of worksheets Each worksheet is made up of a grid with cells

4. Opening Excel Click on start, programs Microsoft Excel Click on open new document A new workbook will appear Within each workbook is a worksheet or worksheets

5. Basics of Excel • An excel worksheet is made up of a grid of cells • Each cell has distinct coordinates • the columns are indicated by letters • the rows are indicated by numbers • One entry per cell • each cell should contain either numbers or text, not both • Example:

6. Saving your workbook • Create templates that are saved before entering data so that you don’t have to create a new one each time you need it • Ensure version control by naming each spreadsheet with name, date, version number • Ex. Rapportmensual_06Jun_v1.xls

7. Cell Formatting • Number • Alignment • Fonts • Borders

8. Inserting comments • Insert comments into a cell to provide supplementary information and explanations • Ex. 1 CD4 machine broken, 2 staff hired in May • Right click on the cell in which you want to insert a comment • Click on Insert Comment

9. Formatting

10. Worksheet Formatting • Gridlines • New Worksheet

11. Standard Toolbar

12. Standard Toolbar Explanations Copy Email Permission Sort Save Drawing Paste Spell Check Help Paste Special Print Preview View (Zoom) Auto Sum Cut Chart Research Print open New document

13. Formatting Toolbar

14. Formatting Toolbar Comma Style Align text right Align text Left Font Size More options Currency Style Borders Decrease Decimal Italics Align text center Percent Style Indent Font color Bold Underline Font Type Merge Cells Background color Increase Decimal

15. Conditional Formatting • The Conditional Formatting dialog box is big, with enough boxes and buttons to be intimidating, because it's built to take in a lot of possible conditions. But once you know which boxes and buttons to pay attention to, it's a snap • First you choose the cell value conditions to trigger the conditional formatting by selecting less than and typing 15. • Then you click the Format button to pick bold red format for any values less than 15. • Now Excel knows that the condition that triggers bold red formatting is any value that is less than 15 in column C. See? It's a snap. • Note    You can add up to three conditions to a cell or a given range of cells.

16. Inserting Headers and Footers • To create headers and footers, if you're looking at the worksheet in print preview, click Setup. Or in normal view, click the File menu and then click Page Setup. In the Page Setup dialog box, click the Header/Footer tab. • Click the arrow next to the Header box or the Footer box and choose from the list you see. You could select Page 1 and the name of the worksheet. Or you could enter your name, the page number, and the date. (If you decide later that you don't want a header or a footer, go back and select None.) If you want both a header and a footer, click the other arrow and choose from that list.

17. Useful tips

18. Freezing Panes • Column titles    Select the first row below the titles. • Row titles    Select the first column to the right (for example, to keep supplier names in sight as you scroll across the worksheet). • Both column and row titles    Click the cell that is both just below the column titles and just to the right of the row titles. • If you don't get it right the first time, it's easy to unfreeze and try again. Just click Unfreeze Panes on the Window menu.

19. Comparing 2 worksheets at one time • Create a new workbook that you can compare with another file that's already open by clicking New on the File menu, and then clicking Blank workbook in the New Workbook task pane. A new workbook, called "Book1", opens. • On the Window menu, click the Compare Side by Side with Compare side by side1 command. • Scroll in the workbook at the top of the window. See how the workbook at the bottom of the window scrolls along with you. • Scrolling to the left or right works just the same as scrolling down or up in the worksheet. Both workbooks scroll together.

20. Viewing 2 worksheets at one time • Tips • The worksheet at the top of the window is the one that's in view when you click the Side by Side command. • You can navigate from either the top or bottom worksheet. Just click in the worksheet you want to navigate in to activate the scroll bars in that sheet. • You can see data up close by zooming in on both worksheets at the same time by clicking Zoom on the View window.

21. Summing • Tip    The numbers you select don't have to be lined up together or in the same row or column. Add up numbers anywhere on the worksheet by pressing CTRL and then selecting each number.

22. Averages, Minimum, Maximum • Need an average? Select the numbers, right-click the status bar, and then click Average on the shortcut menu, which gives you the arithmetic mean. The answer in the status bar changes from a sum to Average=$39.23. • If you want to do even more, just click one of the other options on the shortcut menu, such as Max or Min to find the maximum or minimum in a range.

23. Type less, get more • Pretend that you're typing the first six months of the year for the umpteenth time. Only this time you'll do it the easy way. • Type "January" so that Excel knows what you want. • Select the January cell, and then position the mouse pointer over the lower-right corner of the cell until the black cross (+) appears. • Drag the fill handle over the range you want to fill. As you drag, the ScreenTip tells you what will be filled in. • Release the mouse button to fill the series in. Tips • For some lists you need to type two entries to establish a pattern. For example, to fill in a series of numbers such as 3, 6, 9, type two numbers, select both cells, and then drag the fill handle. • You can also drag up or to the left as well as drag down or to the right. • Ever need to type the same word many times, such as Complete in 10 consecutive rows? Just type the word once, and then drag the fill handle down rows or across columns to enter the same text without typing.

24. Formulas

25. How to create formulas Entering cell references lets Excel automatically update formula results if cell values are changed. For example: • Type=C4+C7 in a cell. • Or type the equal sign (=), click cell C4, then type the plus sign (+), and finally click cell C7.

26. Add, Divide, Multiply, and Subtract • Type an equal sign (=), use math operators, and then press ENTER. • =10+5 to add • =10-5 to subtract • =10*5 to multiply • =10/5 to divide • Formulas are visible in the formula bar when you select a cell that contains a result. If the formula bar is not visible, on the Tools menu, click Options. Click the View tab, and select the Formula bar check box.

27. Sum values in a row or column • Use the SUM function, which is a prewritten formula, to add all the values in a row or column: • Click a cell below the column of values or to the right of the row of values. • Click the AutoSum button on the Standard toolbar, and then press ENTER. • To add some of the values in a column or row: • Type an equal sign, type SUM, then type an opening parenthesis. • Type or select the cell references you want to add. A comma (,) separates individual arguments that tell the function what to calculate. • Type a closing parenthesis, and then press ENTER. • For example: =SUM(B2:B4,B6) and =SUM(B2,B5,B7)

28. Checking summation formulas to make sure you have not missed any cells • For example, if you are working with a column that has subtotals by section, • in addition to selecting subtotal 1 + subtotal 2 + subtotal 3 + subtotal 4, you can do a formula for the sum the entire column (the individual line items and the subtotals) and divide by 2. 

29. Copy a formula instead of creating a new one • Note    You can drag the fill handle to copy formulas only into cells that are next to each other, either horizontally or vertically. Drag the black cross from the cell containing the formula to the cell where the formula will be copied, then release the fill handle. Auto Fill Options button appears but requires no actions.

30. References Relative references change as they are copied.        Absolute references stay the same as they are copied.

31. Linking cells Create a link between cells in the same worksheet or workbook • Click the cell that contains the data you want to link to, and then click Copy. • Click the cell you want to link from, and then click Paste . • Click Paste Options and then click Link Cells. Create a link between cells in different worksheets • Open both the workbook that will contain the link (called the destination (destination file: The file that a linked or embedded object is inserted into. The source file contains the information that is used to create the object. When you change information in a destination file, the information is not updated in the source file.) workbook), and the workbook that contains the data you want to link to (called the source (source file: The file that contains information that was used to create a linked or embedded object. When you update the information in the source file, you can also update the linked object in the destination file.) workbook). • In the destination workbook, click Save . • Select a cell or cells you want to link from. • If you are creating a new formula, type = (an equal sign). • If you are entering the link elsewhere in the formula, type the operator (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.) or function that you want to precede the link. • On the Window menu, click the name of the source workbook, and then click the worksheet that contains the cells you want to link to. • Select the cells you want to link to. • Complete the formula. When you finish entering the formula, press ENTER.

32. Break Links • Break a link to a source • Important When you break a link to a source, all formulas that use the source are converted to their current value. For example, the link =SUM([Budget.xls]Annual!C10:C25) would be converted to =45. Because this action cannot be undone, you may want to save a version of the file before you start. • On the Edit menu, click Links. • In the Source list, click the link you want to break. To select multiple linked objects, hold down CTRL and click each linked object. • To select all links, press CTRL+A. • Click Break Link.

33. Replace a formula with its calculated value • Caution  When you replace a formula with its value, Excel permanently removes the formula. If you accidentally replace a formula with a value and want to restore the formula, click Undo immediately after you enter or paste the value. • Click Copy . • Click Paste • Click the arrow next to Paste Options , and then click Values Only.

34. Understand error values • #####    The column is not wide enough to display the content. Increase column width, shrink contents to fit the column, or apply a different number format. • #REF!    A cell reference is not valid. Cells may have been deleted or pasted over. • #NAME?    You may have misspelled a function name. • Cells with errors such as #NAME? may display a color triangle. If you click the cell, an error button appears to give you some error correction options. How to use the button is not covered in this course.

35. Creating Charts

36. Importing data

37. Printing Worksheets

38. Print Preview What print preview gives you • A view of how your worksheet will look when printed. • Next and Previous buttons to see all the pages. • A Zoom button to switch between a full-page view and a magnified partial view. • A Print button to select options and to start printing. • A Setup button to set up the appearance of the page. • A Margins button to adjust page margins, header and footer margins, and column widths. • A Page Break Preview button to adjust page breaks. (Whether you see Page Break Preview or Normal depends on which view you were in when you clicked Print Preview.) • A Close button to close print preview.

39. Print Cell Gridlines • Excel automatically prints worksheets without cell gridlines. However, it may be easier for some readers to view data on paper with the cell gridlines in place. • Click on the File menu, click Page Setup. Click the Sheet tab. Under Print, select the Gridlines check box.

40. Viewing or Printing Formulas • It's easy to print formulas instead of formula results. On the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. That's all you have to do to see the formulas in the worksheet. Then print as you normally would.

41. Page Setup • You can also get more columns on the page by using the Fit to option. This will temporarily reduce the data on the printed page to a smaller size. • Note    This option does not change the size of the data on your worksheet. Only the printed data is smaller. • From print preview, click Setup (or in normal view, on the File menu, click Page Setup). On the Page tab, select the Fit to option. • In the pages(s) wide by box, 1 is already entered. This means that the printed data will be one page wide. • In the tall box, 1 means that the printed data will be one page long. • Click OK to go back to print preview. See if the text is readable. If you're not sure, click Setup again. On the Page tab, look at the number in the Adjust to box. Depending on your audience, 50% and above should be readable. In the picture, the data is adjusted to 89%. • Tip    The Zoom command will not affect how a worksheet is printed. It changes only what you see on your computer. For example, changing the magnification to 75% or 150% will not make the worksheet print at a smaller or larger percentage. The Fit to option is another way to print your data on one page.        Leave 1 in the page(s) wide by box.        Leave 1 in the tall box.

42. Defining Number of Pages • Imagine that you have a big worksheet with a lot of data. It definitely will not fit on one printed page, but you'd like to fit the data onto a specific number of pages. Six pages, say. • Start by clicking Setup in print preview (or in normal view, on the File menu, click Page Setup). On the Page tab, Click Fit to. Because you want to have all the columns on each page, you leave 1 in the pages(s) wide by box. • In the tall box, you enter 6. Click OK to go back to print preview. Now you see that you have six pages, and how readable they are. Click Setup to go back to the Page Setup dialog box to see the number in the % normal size box. Any number larger than 50% means the pages will probably be readable.

43. Adjust page breaks to control page contents • When a worksheet prints on several pages, Excel inserts automatic page breaks that divide the worksheet into separate pages for printing. These page breaks appear as dotted lines, which you can see by looking at the worksheet in page break preview. You can control what appears on a page by changing those page breaks or creating your own. • To do that, in print preview, click Page Break Preview (or in normal view, click Page Break Preview on the View menu). • If you don't like the page breaks you create, right-click the worksheet and select Reset All Page Breaks. Or you can remove a page break by dragging it outside the print area. In page break preview, a big number identifies the page.        An automatic page break appears as a dotted line.        A manual page break appears as a solid blue line.

44. Choose what data to print • Say you want to print just part of your data, not the whole worksheet. Perhaps you want to print cells C7 through C16, or the results of one calendar quarter, or the products from one supplier. How do you do that? • Select the area you want to print. Then, on the File menu, click Print. Under Print what, click Selection. Then click OK. • If you expect to print a particular area of a worksheet frequently, it's convenient to define and save it as a print area. • To do that, on the View menu, click Page Break Preview. Select the area that you expect to print often. Next, on the File menu, point to Print Area, and then click Set Print Area. When you save the workbook, your defined print area is also saved. You can save only one defined print area at a time on a worksheet. • When you're ready to print, on the File menu, click Print. Only the defined print area will be printed.

45. Printing row and column titles on each page You can print the column titles (months) and the row titles (company names) on every page. You can also print the alphabetical column headings and numerical row headings on every page. • On the File menu, select Page Setup. In the Page Setup dialog box, click the Sheet tab and enter the row titles and column titles you want Excel to print on every page.