1、 Advanced Excel i About the Tutorial Advanced Excel is a comprehensive tutorial that provides a good insight into the latest and advanced features available in Microsoft Excel 2013. It has plenty of screenshots that explain how to use a particular feature, in a step-by-step manner. Audience This tut
2、orial has been designed for all those readers who depend heavily on MS-Excel to prepare charts, tables, and professional reports that involve complex data. It will help all those readers who use MS-Excel regularly to analyze data. Prerequisites The readers of this tutorial are expected to have a goo
3、d prior understanding of the basic features available in Microsoft Excel. Copyright & Disclaimer Copyright 2015 by Tutorials Point (I) Pvt. Ltd. All the content and graphics published in this e-book are the property of Tutorials Point (I) Pvt. Ltd. The user of this e-book is prohibited to reuse, ret
4、ain, copy, distribute or republish any contents or a part of contents of this e-book in any manner without written consent of the publisher. We strive to update the contents of our website and tutorials as timely and as precisely as possible, however, the contents may contain inaccuracies or errors.
5、 Tutorials Point (I) Pvt. Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of our website or its contents including this tutorial. If you discover any errors on our website or in this tutorial, please notify us at Advanced Excel ii Table of Contents About the Tutorial .
6、 i Audience . i Prerequisites . i Copyright & Disclaimer . i Table of Contents . ii PART 1: EXCEL NEW FEATURES . 1 1. Excel Chart Recommendations . 2 Change in Charts Group . 2 Chart Recommendations . 3 Fine Tune Charts Quickly . 5 Select / De-select Chart Elements . 6 Format Style . 7 Format Color
7、. 8 Filter Data being displayed on the Chart . 9 2. Excel Format Charts . 12 Format Axis . 12 Provision for Combo Charts . 15 3. Excel Chart Design . 17 Ribbon of Chart Tools . 17 Quick Layout . 19 Change Colors . 20 Chart Styles . 20 Switch Row / Column . 21 Select Data . 22 Change Chart Type . 23
8、Move Chart . 24 4. Excel Richer Data Labels . 26 Formatting Data Labels . 26 Look of the Data Labels . 30 Shape of a Data Label . 31 Resize a Data Label . 32 Add a Field to a Data Label . 32 5. Excel Leader Lines . 35 Add a Leader Line . 35 Format Leader Lines . 36 6. Excel New Functions. 38 Functio
9、ns by Category . 38 New Functions in Excel 2013 . 39 User Defined Functions in Add-ins . 42 Web Functions . 42 Advanced Excel iii PART 2: FUNDAMENTAL DATA ANALYSIS . 43 7. Instant Data Analysis . 44 Quick Analysis Features . 44 Quick Analysis of Data . 44 Conditional Formatting . 46 Charts . 50 Tota
10、ls . 52 Tables . 58 Sparklines . 60 8. Excel Sorting Data by Color . 62 9. Excel Slicers . 65 10. Excel Flash Fill . 68 PART 3: POWERFUL DATA ANALYSIS INTRODUCTION . 72 11. Excel PivotTable Recommendations . 73 Create a PivotTable to analyze external data . 74 Connect to a new external data source .
11、 76 Using the Field List option . 78 PivotTables based on Multiple Tables . 82 PART 4: POWERFUL DATA ANALYSIS 1 . 85 12. Excel Data Model in Excel . 86 Explore Data Using PivotTable . 89 Create Relationship between Tables . 91 13. Excel Power Pivot . 95 Data Model using Calculated Columns . 99 Relat
12、ionship using calculated columns . 103 14. Excel External Data Connection . 105 Update the Data Connections . 106 Automatically Refresh Data . 107 Automatically refresh data at regular intervals . 109 Enable Background Refresh . 109 15. Excel Pivot Table Tools . 111 Source Data for a PivotTable . 11
13、1 Change to a Different External Data Source. . 112 Delete a PivotTable. 114 Using the Timeline . 117 Use a Timeline to Filter by Time Period . 119 Create a Standalone PivotChart . 122 Advanced Excel iv PART 5: POWERFUL DATA ANALYSIS 2 . 124 16. Excel Power View . 125 Create a Power View Sheet . 125 17. Excel Visualizations . 129 Create Charts and other Visualizations . 129 Visualization Matrix . 130 Visualization Card . 131 Visualization Charts . 132 18. Excel Pie Charts . 138 Bubble and Scatter Charts . 140 Maps .
