Transcript Slide 1
Lecture 3 Advanced Excel OVERVIEW • • • • • • • Reviewing and Sharing Workbooks Templates Charts and Graphs Sparklines Conditional Formatting Pivot Tables What-If Analysis REVIEWING & SHARING WORKBOOKS • In your organization documents may require some review and comments from others before approval • Excel’s Track Changes and Comments features enable users to track changes, add comments, check spelling, compare two versions of a workbook, and prepare a final version for sharing Track Changes • Go to the Review tab. Click Track Changes, then select Highlight Changes Track Changes • Any changes you make to the worksheet will be highlighted with a unique border and indicator Turn Off Track Changes Adding Comments Adding Comments Editing a Comment Deleting a Comment Accepting/ Rejecting Changes Listing Changes Elsewhere Show/ Hide Comments Spell Checking Document Inspector Protecting Your Final Workbook • Click File – Info – Protect Workbook TEMPLATES • A template is a pre-designed spreadsheet that you can use to create new spreadsheets with the same formatting and predefined formulas Create New Workbook From Template Create New Workbook From Template CHARTS & GRAPHS • A chart is a tool you can use in Excel to communicate your data graphically Create a Chart Create a Chart Change Chart Type Switch Row/ Column Data Change Chart Layout Change the Chart Style Move Chart to Another Worksheet WORKING WITH SPARKLINES • Sparklines are miniature charts that fit into a single cell • Sparklines are ideal for situations a user wants to make the data clearer and more eyecatching • On the other hand, charts are ideal for representing the data in greater detail, and they are often better for comparing different data series Creating Sparklines Creating Sparklines Identifying Points on Sparklines Change Style of Sparklines Change Sparkline Type Change Sparkline Display range CONDITIONAL FORMATTING • Conditional formatting applies one or more rules to any cells in order to make them stand out • This is advantageous when working with large datasets Set Up Conditional Formatting Rule Preset Conditional Formatting Remove Conditional Formatting Rules PIVOT TABLES • A PivotTable summarizes the data, making it easier to manage • The PivotTable can quickly and easily be changed to see the data in a different way, making this an extremely powerful tool Create Pivot Table • Create Pivot table that determines the sales amount by each salesperson in the company Create Pivot Table Add Fields to Pivot Table Change the Row Labels Add Column Labels Add Report Filters Adding Slicers Using the Slicer Create Pivot Chart • Pivot Chart is a graph if data from a Pivot Table Create Pivot Chart WHAT-IF ANALYSIS • This tool enables the user to solve for unknowns as in mathematical equation • In this example the Goal Seek feature will be used to determine the interest based on a loan amount, monthly payment amount and loan period Goal Seek Goal Seek Scenario • Scenarios let you substitute values for multiple cells (up to 32) at the same time • It is especially well-suited to showing bestcase and worst-case scenarios Data Tables • Data Tables allow you to take one or two variables in a formula and replace them with as many different values as you want, and then view the results in a table. • This option is especially powerful because it shows multiple results at the same time References • http://office.microsoft.com/enus/support/results.aspx?av=zxl • http://www.functionx.com/excel/index.htm • www.youtube.com