Advanced Excel for Finance Professionals

1. Advanced Excel for Finance Professionals A self study material from South Asian Management Technologies Foundation

2. Welcome Back to Session 3

3. Pivot Table • A PivotTable is an interactive table that summarizes a data source such as a list or another table.

4. Creating a PivotTable Report

5. Creating a PivotTable • Start a PivotTable from Insert menu and provide the required data

6. Creating a PivotTable • The initial steps include: • Specifying the data source • Specifying the location of the report • a new worksheet • the existing worksheet – if you chose this, be careful to select a blank area in the worksheet

7. Creating a PivotTable • To complete the design of the PivotTable, specify whether you want to: • use a Layout dialog box • Make selections directly on the worksheet • You can easily set up a PivotTable directly on the worksheet by dragging the names of fields listed in the PivotTable toolbar to the appropriate areas of a PivotTable shell. • Allows you to view the data while you arrange the fields

8. Creating a PivotTable Report

9. Specify Data for PivotTable • Create a summary report for expenses against each expense head and purpose. You filter the items online

10. Have Multiple Dimension • Drag “Mode” to row label and get a granulated report

11. Multiple Dimensions • Add Date in the row label and further dimension. • You can filter to get different summary

12. Month Summary • Date fields allow you to group by month • You can also show values other than total in summary – viz. average

13. PivotTable Management • If we select anywhere on the PivotTable we will get a special menu o the ribbon. • This can be used for many purpose including those we will describe now.

14. Changing Order of Summary Data • The order in which the field buttons appear in the Row, Column, and Data boxes of the Layout Dialog box determines the order in which data displays in the PivotTable. • To change the order: • Open the Layout dialog box • Drag a field button to the desired location.

15. Removing, Hiding, and Showing Data • As your information needs change, you may want to display more or less summary data in a PivotTable. • The process for removing a field is opposite that of adding a field. • Drag the field away from the row, column, or Data area in the Layout dialog box. • To show or hide detail in a field just pull down a list of items in a field on the PivotTable and select or deselct the field.

16. Refreshing a PivotTable • Excel does not automatically update PivotTable. • After making changes to the worksheet, you must refresh (recalculate) the PivotTable. • Right click anywhere on the PivotTable and refresh on the menu. • In complex PivotTables, errors in summary amounts are difficult to detect visually. • It is important to acquire the habit of refreshing PivotTables after any change in worksheet data.

17. Deleting a PivotTable • Before you can delete a PivotTable, you must select it using a three-step process: • Select Options under PivotTable menu • Choose Select from the shortcut menu • Click Entire Table. • After selecting the table you may clear the table through menu or by ‘delete’ key • When you delete a PivotTable, the source data is not affected.

18. Pivot Chart • PivotChart needs the same set of operations to be followed. • Instead of the summary report, a chart comes up.

19. Protecting Your Work • If you are building worksheets that will be used by other people, you may need to protect the work using protection features. • At the file level, you can password-protect a workbook so that only authorized users (people given the password) can open, view, and modify its contents.

20. Protecting Your Workbook & Worksheet • At the workbook level, you can protect and hide individual tabs containing worksheets, charts, and modules. • At the sheet level, you can protect cells and objects from being modified or deleted.

21. Protecting Cells, Worksheets, & Workbooks • There are two options under the Review option and Changes group of ribbon • Protect sheet • Protect workbook

22. Protect Sheet • Protect Sheet option enables the protection feature specifying what is to be protected • Right click on any cell and select format cell. Select Protection tab therein to protect/hide

23. Warning Dialog Box

24. Hide Worksheet • Right click on worksheet tab and hide it • To unhide follow the same principle

25. The Protect Workbook Dialog Box Protect the individual sheets in a workbook and/or the document window display Enter a password to prevent users from removing the protection

26. Protect Workbook • You can protect workbook in two manner: • Protect the structure of a workbook, by selecting Structure check box. • Keep workbook windows in the same size and position whenever the workbook is opened, by selecting Windows check box.

27. Password-Protecting a Workbook • Select Save As from Files menu • Click on Tools and select General Options to get password dialog box.

28. Managing Security and Privacy

29. Signing a Worksheet • From Insert menu, select insert signature in text group and click on Microsoft office signature line. Enter the necessary data

30. Signing the Worksheet • A signature pane will appear once you sign the document. Right click the pane and select Sign option.

31. Signing the Worksheet • If you have a digital signature, use the same else use an image

32. Complete Signing • Once you complete signing, there will be a message and a signature icon at the bottom. • Any further editing will deactivate the button

33. Conditional Formatting • A great way of formatting depending on the content of the cell.

34. Define Format Conditions • Highlight all sales above 2000 and below 500 • Select area / sheet and get conditional formatting in Home menu.

35. Define New Formatting Rule • Select Highlight Cell Rules and Greater Than and Less Than options

36. Formatted • You can remove formatting from the same menu by selecting Clear Rules option

37. Get Ready for Session 4