[PDF] PDF Advanced Excel Tutorial Advanced Excel is a comprehensive





Previous PDF Next PDF



Excel Advanced

An example of the formula is: VLOOKUP(E2D2:M3



Excel 2019 Level 1 - Dublin

› Closing a Workbook. Page 12. Lesson Notes. Page 13. Excel 2019: Level 1 Rel. 1.0



MO-201: Microsoft Excel Expert (Office 2019) – Skills Measured

NOTE: The bullets that appear below each of the skills measured are intended to illustrate how we are assessing that skill. This list is not definitive or 



MOS Excel

- Microsoft Excel (Microsoft 365 Apps and Office 2019): Exam MO-200. The Microsoft Office Specialist: Excel Associate Certification demonstrates competency.



Microsoft SQL Server 2019 technical white paper

All other trademarks are property of their respective owners. © 2018 Microsoft Corporation. All rights reserved. Page 3. Microsoft SQL Server 2019 preview.



Microsoft Excel 2019 Fundamentals Workshop

Sep 20 2019 This manual provides instructions with the fundamental spreadsheet features of. Microsoft Excel 2019. Topics covered in this document will ...



CFI-Excel-eBook.pdf

To see a video demonstration check out our Advanced Excel Formulas Course. • Maturity date: 6/30/2019. • Rate of interest: 10%. • Price per $100 FV: $101.



Microsoft Word 2019 Fundamentals Workshop

Jan 11 2019 Microsoft Word 2019 is a powerful word processing application. This is a hands-on workshop to help University faculty and staff become more ...



Microsoft Certification Paths 2019

one of below course & exam: · 331: Core Solutions of Microsoft SharePoint · Server 2013 · 332: Advanced Solutions of Microsoft SharePoint.



STAT 448 Advanced Data Analysis (Treatment)-Kinson-Spring 2019

Microsoft Excel. Page 3. STAT 448. Syllabus Page 3 of 6. Spring 2019. Additional Spring Break - March 16 - 24



Excel Advanced

Excel's traditional formulas do not work on filtered data since the function will be performed on both the hidden and visible cells. To perform functions on 



PDF Advanced Excel 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 



Adv Excel SYLLABUS

Adv Excel SYLLABUS. Advanced Excel Course - Overview of the Basics of Excel. ? Customizing common options in Excel. ? Absolute and relative cells.



Microsoft Office Specialist-Excel Syllabus

Microsoft Office Specialist-Excel Syllabus. 1. Manage Workbook Options and Settings. 1.1. Create Worksheets and Workbooks. ? Create a workbook.



Excel advanced

The course is also valuable as preparation for the ECDL Advanced Spreadsheet certification. At the end of the course participants will be able to: • Effectively 



Microsoft Excel for Beginners

Jan 16 2018 Microsoft Excel is a spreadsheet program. We use it to create reports that need calculations and charts. In this workshop we will learn how to ...



2019-2021 Syllabus

Also available for examination in March 2019 2020 and 2021 for India only. Cambridge Advanced. Version 2. Page 2. Cambridge International Examinations retains 



Certi icate Course in - ADVANCED BUSINESS EXCEL FOR

Oct 18 2020 BOARD OF ADVANCED STUDIES & RESEARCH ... Advanced Business Excel for Finance Professionals ... MS Excel 2013



Pearson LCCI Qualifications - January–December 2021

Dec 4 2021 Advanced Business Calculations. Wednesday. 13 January ... Business Statistics (2019 Syllabus) ... (2019 Syllabus



UNIVERSITY OF DELHI Bachelor of Management Studies (Effective

UNIVERSITY OF DELHI. Bachelor of Management Studies. (Effective from Academic Year 2019-20). Revised Syllabus as approved by. Academic Council.

Advanced Excel

i 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. This tutorial 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. The readers of this tutorial are expected to have a good prior understanding of the basic features available in Microsoft Excel.

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, retain, 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. 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 contact@tutorialspoint.com

Advanced Excel

ii

About the Tutorial ............................................................................................................................................ 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 .................................................................................................................................................... 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

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

Functions 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

Totals ............................................................................................................................................................. 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 ......................................................................................................... 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

Relationship 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 ....................................................................................................................... 111

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 ............................................................................................................................................................ 143

Multiples: A Set of Charts with the Same Axes ........................................................................................... 145

Visualization - Tiles ..................................................................................................................................... 147

19. Power View - Additional Features ........................................................................................................ 150

Modify the internal Data Model .................................................................................................................. 150

20. Excel - Power View in Services.............................................................................................................. 155

Key Performance Indicators (KPIs) .............................................................................................................. 155

Hierarchies ................................................................................................................................................... 156

Drill-Up and Drill-Down ............................................................................................................................... 157

21. Excel - Format Reports ......................................................................................................................... 161

Hyperlinks .................................................................................................................................................... 173

Printing ........................................................................................................................................................ 173

Support for right-to-left languages .............................................................................................................. 173

22. Excel - Handling Integers ...................................................................................................................... 176

Power Query ................................................................................................................................................ 177

Power BI Desktop ........................................................................................................................................ 178

PART 6: OTHER FEATURES ....................................................................................................... 181

23. Excel - Templates ................................................................................................................................. 182

24. Excel - Inquire....................................................................................................................................... 186

Ensure Inquire Add-in is Active .................................................................................................................... 186

Compare Two Workbooks ........................................................................................................................... 188

25. Excel - Workbook Analysis.................................................................................................................... 194

Diagrams ...................................................................................................................................................... 198

Workbook Relationship ............................................................................................................................... 199

Worksheet Relationship .............................................................................................................................. 200

Cell Relationship .......................................................................................................................................... 201

Clean Excess Cell Formatting ....................................................................................................................... 203

26. Excel - Manage Passwords.................................................................................................................... 205

Embed Worksheet Data in a Web Page ....................................................................................................... 207

Advanced Excel

v

27. Excel - File Formats ............................................................................................................................... 208

Save a Workbook in another File Format .................................................................................................... 208

Excel File Formats ........................................................................................................................................ 208

Text File Formats ......................................................................................................................................... 209

Other File Formats ....................................................................................................................................... 210

File Formats that Use the Clipboard ............................................................................................................ 213

File Formats not Supported in Excel 2013 ................................................................................................... 214

28. Excel - Discontinued Features ............................................................................................................... 215

Discontinued / Changed features ................................................................................................................ 215

Save Workspace .......................................................................................................................................... 215

New from Existing ....................................................................................................................................... 215

Save as Template ......................................................................................................................................... 216

Split Box Control .......................................................................................................................................... 219

Blank Workbook .......................................................................................................................................... 220

Save Options ................................................................................................................................................ 221

Microsoft Clip Organizer .............................................................................................................................. 222

MS Office Picture Manager ......................................................................................................................... 222

Exit option ................................................................................................................................................... 222

Browser View Options ................................................................................................................................. 224

Individual Data Series .................................................................................................................................. 225

Pyramid and Cone Chart Types ................................................................................................................... 225

Advanced Excel

6

Part 1: Excel New Features

Advanced Excel

7 The Charts Group on the Ribbon in MS Excel 2013 looks as follows:

You can observe that:

The subgroups are clubbed together.

Let us create a chart. Follow the steps given below. Step 1: Select the data for which you want to create a chart. Step 2: Click on the Insert Column Chart icon as shown below.

EXCEL - CHART RECOMMENDATIONS

Advanced Excel

8 When you click on the Insert Column chart, types of 2-D Column Charts, and 3-D Column Charts are displayed. You can also see the option of More Column Charts. Step 3: If you are sure of which chart you have to use, you can choose a Chart and proceed. If you find that the one you pick is not working well for your data, the new Recommended Charts command on the Insert tab helps you to create a chart quickly that is just right for your data.

Advanced Excel

9 Let us see the options available under this heading. (use another word for heading)

Step 1: Select the Data from the worksheet.

Step 2: Click on Recommended Charts.

The following window displaying the charts that suit your data will be displayed.

Advanced Excel

10 Step 3: As you browse through the Recommended Charts, you will see the preview on the right side. Step 4: If you find the chart you like, click on it. Step 5: Click on the OK button. If you do not see a chart you like, click on All Charts to see all the available chart types. Step 6: The chart will be displayed in your worksheet.

Step 7: Give a Title to the chart.

Advanced Excel

11 Click on the Chart. Three Buttons appear next to the upper-right corner of the chart. They are:

Chart Elements

Chart Styles and Colors, and

Chart Filters

You can use these buttons-

To add chart elements like axis titles or data labels

To customize the look of the chart, or

Advanced Excel

12 Step 1: Click on the Chart. Three Buttons will appear at the upper-right corner of the chart. Step 2: Click on the first button Chart Elements. A list of chart elements will be displayed under the Chart Elements option.

Advanced Excel

13 Step 3: Select / De-select Chart Elements from the given List. Only the selected chart elements will be displayed on the Chart. Step 1: Click on the Chart. Three Buttons will appear at the upper-right corner of the chart. Step 2: Click on the second button Chart Styles. A small window opens with different options of STYLE and COLOR as shown in the image given below. Step 3: Click on STYLE. Different options of Style will be displayed.

Advanced Excel

14 Step 4: Scroll down the gallery. The live preview will show you how your chart data will look with the currently selected style. Step 5: Choose the Style option you want. The Chart will be displayed with the selected Style as shown in the image given below.

Advanced Excel

15 Step 1: Click on the Chart. Three Buttons will appear at the upper-right corner of the chart. Step 2: Click on Chart Styles. The STYLE and COLOR window will be displayed. Step 3: Click on the COLOR tab. Different Color Schemes will be displayed.

Advanced Excel

16 Step 4: Scroll down the options. The live preview will show you how your chart data will look with the currently selected color scheme. Step 5: Pick the color scheme you want. Your Chart will be displayed with the selected Style and Color scheme as shown in the image given below. You can change color schemes from Page Layout Tab also.

Step 1: Click the tab Page Layout.

Step 2: Click on the Colors button.

Step 3: Pick the color scheme you like. You can also customize the Colors and have your own color scheme. Chart Filters are used to edit the data points and names that are visible on the chart being displayed, dynamically. Step 1: Click on the Chart. Three Buttons will appear at the upper-right corner of the chart. Step 2: Click on the third button Chart Filters as shown in the image.

Advanced Excel

17 Step 3: Click on VALUES. The available SERIES and CATEGORIES in your Data appear.

Advanced Excel

18 Step 4: Select / De-select the options given under Series and Categories. The chart changes dynamically. Step 5: After, you decide on the final Series and Categories, click on Apply. You can see that the chart is displayed with the selected data.

Advanced Excel

19 The Format pane is a new entry in Excel 2013. It provides advanced formatting options in clean, shiny, new task panes and it is quite handy too.

Step 1: Click on the Chart.

Step 2: Select the chart element (e.g., data series, axes, or titles).

Step 3: Right-click the chart element.

Step 4: Click Format . The new Format pane appears with options that are tailored for the selected chart element.

Step 1: Select the chart axis.

Step 2: Right-click the chart axis.

Step 3: Click Format Axis. The Format Axis task pane appears as shown in the image below.

EXCEL - FORMAT CHARTS

Advanced Excel

20 You can move or resize the task pane by clicking on the Task Pane Options to make working with it easier. The small icons at the top of the pane are for more options.

Advanced Excel

21

Step 4: Click on Axis Options.

Advanced Excel

22
Step 5: Select the required Axis Options. If you click on a different chart element, you will see that the task pane automatically updates to the new chart element.

Step 6: Select the Chart Title.

Step 7: Select the required options for the Title. You can format all the Chart Elements using the Format Task Pane as explained for Format Axis and Format Chart Title.

Advanced Excel

23
There is a new button for combo charts in Excel 2013. The following steps will show how to make a combo chart.

Step 1: Select the Data.

Step 2: Click on Combo Charts. As you scroll on the available Combo Charts, you will see

the live preview of the chart. In addition, Excel displays guidance on the usage of that

particular type of Combo Chart as shown in the image given below.

Advanced Excel

24
Step 3: Select a Combo Chart in the way you want the data to be displayed. The Comboquotesdbs_dbs22.pdfusesText_28
[PDF] advanced excel test practice

[PDF] advanced excel test questions

[PDF] advanced excel test questions and answers for interview

[PDF] advanced excel test questions and answers pdf

[PDF] advanced excel test questions and answers xls

[PDF] advanced excel tips and tricks 2016 pdf

[PDF] advanced excel tips tricks pdf

[PDF] advanced excel training ppt

[PDF] advanced excel tutorial pdf 2010

[PDF] advanced excel tutorial pdf 2013

[PDF] advanced excel tutorial pdf 2016

[PDF] advanced excel tutorial pdf download

[PDF] advanced excel tutorial pdf with examples

[PDF] advanced excel tutorial pdf with examples 2013

[PDF] advanced excel tutorial point pdf