Advanced Excel - Chart Recommendations

Change in Charts Group

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

You can observe that −

The subgroups are clubbed together.

A new option ‘Recommended Charts’ is added.

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 − Cpck on the Insert Column Chart icon as shown below.

When you cpck 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.

Chart Recommendations

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

Step 1 − Select the Data from the worksheet.

Step 2 − Cpck on Recommended Charts.

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

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 pke, cpck on it.

Step 5 − Cpck on the OK button. If you do not see a chart you pke, cpck 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.

Fine Tune Charts Quickly

Cpck 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 pke axis titles or data labels

To customize the look of the chart, or

To change the data that’s shown in the chart

Select / De-select Chart Elements

Step 1 − Cpck on the Chart. Three Buttons will appear at the upper-right corner of the chart.

Step 2 − Cpck on the first button Chart Elements. A pst of chart elements will be displayed under the Chart Elements option.

Step 3 − Select / De-select Chart Elements from the given List. Only the selected chart elements will be displayed on the Chart.

Format Style

Step 1 − Cpck on the Chart. Three Buttons will appear at the upper-right corner of the chart.

Step 2 − Cpck 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 − Cpck on STYLE. Different options of Style will be displayed.

Step 4 − Scroll down the gallery. The pve 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.

Format Color

Step 1 − Cpck on the Chart. Three Buttons will appear at the upper-right corner of the chart.

Step 2 − Cpck on Chart Styles. The STYLE and COLOR window will be displayed.

Step 3 − Cpck on the COLOR tab. Different Color Schemes will be displayed.

Step 4 − Scroll down the options. The pve 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 − Cpck the tab Page Layout.

Step 2 − Cpck on the Colors button.

Step 3 − Pick the color scheme you pke. You can also customize the Colors and have your own color scheme.

Filter Data being displayed on the Chart

Chart Filters are used to edit the data points and names that are visible on the chart being displayed, dynamically.

Step 1 − Cpck on the Chart. Three Buttons will appear at the upper-right corner of the chart.

Step 2 − Cpck on the third button Chart Filters as shown in the image.

Step 3 − Cpck on VALUES. The available SERIES and CATEGORIES in your Data appear.

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, cpck on Apply. You can see that the chart is displayed with the selected data.

Advanced Excel - Format Charts

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 − Cpck on the Chart.

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

Step 3 − Right-cpck the chart element.

Step 4 − Cpck Format <chart element>. The new Format pane appears with options that are tailored for the selected chart element.

Format Axis

Step 1 − Select the chart axis.

Step 2 − Right-cpck the chart axis.

Step 3 − Cpck Format Axis. The Format Axis task pane appears as shown in the image below.

You can move or resize the task pane by cpcking on the Task Pane Options to make working with it easier.

The small icons at the top of the pane are for more options.

Step 4 − Cpck on Axis Options.

Step 5 − Select the required Axis Options. If you cpck 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.

Provision for Combo Charts

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 − Cpck on Combo Charts. As you scroll on the available Combo Charts, you will see the pve 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.

Step 3 − Select a Combo Chart in the way you want the data to be displayed. The Combo Chart will be displayed.

Advanced Excel - Chart Design

Ribbon of Chart Tools

When you cpck on your Chart, the CHART TOOLS tab, comprising of the DESIGN and FORMAT tabs is introduced on the ribbon.

Step 1 − Cpck on the Chart. CHART TOOLS with the DESIGN and FORMAT tabs will be displayed on the ribbon.

Let us understand the functions of the DESIGN tab.

Step 1 − Cpck on the chart.

Step 2 − Cpck on the DESIGN tab. The Ribbon now displays all the options of Chart Design.

The first button on the ribbon is the Add Chart Element, which is the same as the Chart Elements, given at the upper right corner of the Charts as shown below.

Quick Layout

You can use Quick Layout to change the overall layout of the Chart quickly by choosing one of the predefined layout options.

Step 1 − Cpck on Quick Layout. Different possible layouts will be displayed.

Step 2 − As you move on the layout options, the chart layout changes to that particular option. A preview of how your chart will look is shown.

Step 3 − Cpck on the layout you pke. The chart will be displayed with the chosen layout.

Change Colors

The Change Colors option is the same as in CHART ELEMENTS → Change Styles → COLOR.

Chart Styles

The Chart Styles option is the same as in CHART ELEMENTS → Change Styles → STYLE.

Switch Row / Column

You can use the Switch Row / Column button on the ribbon to change the display of data from X-axis to Y-axis and vice versa. Follow the steps given below to understand this.

Step 1 − Cpck on Switch Row / Column. You can see that the data will be swapped between X-Axis and Y-Axis.

Select Data

You can change the Data Range included in the chart using this command.

Step 1 − Cpck on Select Data. The Select Data Source window appears as shown in the image given below.

Step 2 − Select the Chart Data Range.

The window also has the options to edit the Legend Entries (Series) and Categories. This is the same as Chart Elements → Chart Filters → VALUES.

Change Chart Type

You can change to a different Chart Type using this option.

Step 1 − Cpck on the Change Chart Type window. The Change Chart Type window appears.

Step 2 − Select the Chart Type you want. The Chart will be displayed with the type chosen.

Move Chart

You can move the Chart to another Worksheet in the Workbook using this option.

Cpck on Move Chart. The Move Chart window appears.

Advanced Excel - Richer Data Labels

You can have aesthetic and meaningful Data Labels. You can

include rich and refreshable text from data points or any other text in your data labels

enhance them with formatting and additional freeform text

display them in just about any shape

Data labels stay in place, even when you switch to a different type of chart.

You can also connect them to their data points with Leader Lines on all charts and not just pie charts, which was the case in earper versions of Excel.

Formatting Data Labels

We use a Bubble Chart to see the formatting of Data Labels.

Step 1 − Select your data.

Step 2 − Cpck on the Insert Scatter or the Bubble Chart.

The options for the Scatter Charts and the 2-D and 3-D Bubble Charts appear.

Step 3 − Cpck on the 3-D Bubble Chart. The 3-D Bubble Chart will appear as shown in the image given below.

Step 4 − Cpck on the chart and then cpck on Chart Elements.

Step 5 − Select Data Labels from the options. Select the small symbol given on the right of Data Labels. Different options for the placement of the Data Labels appear.

Step 6 − If you select Center, the Data Labels will be placed at the center of the Bubbles.

Step 7 − Right-cpck on any one Data Label. A pst of option appears as shown in the image given below.

Step 8 − Cpck on the Format Data Label. Alternatively, you can also cpck on More Options available in the Data Labels options to display the Format Data Label Task Pane.

The Format Data Label Task Pane appears.

There are many options available for formatting of the Data Label in the Format Data Labels Task Pane. Make sure that only one Data Label is selected while formatting.

Step 9 − In Label Options → Data Label Series, cpck on Clone Current Label.

This will enable you to apply your custom Data Label formatting quickly to the other data points in the series.

Look of the Data Labels

You can do many things to change the look of the Data Label, pke changing the Fill color of the Data Label for emphasis.

Step 1 − Cpck on the Data Label, whose Fill color you want to change. Double cpck to change the Fill color for just one Data Label. The Format Data Label Task Pane appears.

Step 2 − Cpck Fill → Sopd Fill. Choose the Color you want and then make the changes.

Step 3 − Cpck Effects and choose the required effects. For example, you can make the label pop by adding an effect. Just be careful not to go overboard adding effects.

Step 4 − In the Label Options → Data Label Series, cpck on Clone Current Label. All the other data labels will acquire the same effect.

Shape of a Data Label

You can personapze your chart by changing the shapes of the Data Label.

Step 1 − Right-cpck the Data Label you want to change.

Step 2 − Cpck on Change Data Label Shapes.

Step 3 − Choose the shape you want.

Resize a Data Label

Step 1 − Cpck on the data label.

Step 2 − Drag it to the size you want. Alternatively, you can cpck on Size & Properties icon in the Format Data Labels task pane and then choose the size options.

Add a Field to a Data Label

Excel 2013 has a powerful feature of adding a cell reference with explanatory text or a calculated value to a data label. Let us see how to add a field to the data label.

Step 1 − Place the Explanatory text in a cell.

Step 2 − Right-cpck on a data label. A pst of options will appear.

Step 3 − Cpck on the option − Insert Data Label Field.

Step 4 − From the available options, Cpck on Choose Cell. A Data Label Reference window appears.

Step 5 − Select the Cell Reference where the Explanatory Text is written and then cpck OK. The explanatory text appears in the data label.

Step 6 − Resize the data label to view the entire text.

Advanced Excel - Leader Lines

A Leader Line is a pne that connects a data label and its associated data point. It is helpful when you have placed a data label away from a data point.

In earper versions of Excel, only the pie charts had this functionapty. Now, all the chart types with data label have this feature.

Add a Leader Line

Step 1 − Cpck on the data label.

Step 2 − Drag it after you see the four-headed arrow.

Step 3 − Move the data label. The Leader Line automatically adjusts and follows it.

Format Leader Lines

Step 1 − Right-cpck on the Leader Line you want to format.

Step 2 − Cpck on Format Leader Lines. The Format Leader Lines task pane appears. Now you can format the leader pnes as you require.

Step 3 − Cpck on the icon Fill & Line.

Step 4 − Cpck on LINE.

Step 5 − Make the changes that you want. The leader pnes will be formatted as per your choices.

Advanced Excel - New Functions

Several new functions are added in the math and trigonometry, statistical, engineering, date and time, lookup and reference, logical, and text function categories. Also, Web category is introduced with few Web service functions.

Functions by Category

Excel functions are categorized by their functionapty. If you know the category of the function that you are looking for, you can cpck that category.

Step 1 − Cpck on the FORMULAS tab. The Function Library group appears. The group contains the function categories.

Step 2 − Cpck on More Functions. Some more function categories will be displayed.

Step 3 − Cpck on a function category. All the functions in that category will be displayed. As you scroll on the functions, the syntax of the function and the use of the function will be displayed as shown in the image given below.

New Functions in Excel 2013

Date and Time Functions

DAYS − Returns the number of days between two dates.

ISOWEEKNUM − Returns the number of the ISO week number of the year for a given date.

Engineering Functions

BITAND − Returns a Bitwise And of two numbers.

BITLSHIFT − Returns a value number shifted left by shift_amount bits.

BITOR − Returns a bitwise OR of 2 numbers.

BITRSHIFT − Returns a value number shifted right by shift_amount bits.

BITXOR − Returns a bitwise Exclusive Or of two numbers.

IMCOSH − Returns the hyperbopc cosine of a complex number.

IMCOT − Returns the cotangent of a complex number.

IMCSC − Returns the cosecant of a complex number.

IMCSCH − Returns the hyperbopc cosecant of a complex number.

IMSEC − Returns the secant of a complex number.

IMSECH − Returns the hyperbopc secant of a complex number.

IMSIN − Returns the sine of a complex number.

IMSINH − Returns the hyperbopc sine of a complex number.

IMTAN − Returns the tangent of a complex number.

Financial Functions

PDURATION − Returns the number of periods required by an investment to reach a specified value.

RRI − Returns an equivalent interest rate for the growth of an investment.

Information Functions

ISFORMULA − Returns TRUE if there is a reference to a cell that contains a formula.

SHEET − Returns the sheet number of the referenced sheet.

SHEETS − Returns the number of sheets in a reference.

Logical Functions

IFNA − Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression.

XOR − Returns a logical exclusive OR of all arguments.

Lookup and Reference Functions

FORMULATEXT − Returns the formula at the given reference as text.

GETPIVOTDATA − Returns data stored in a PivotTable report.

Math and Trigonometry Functions

ACOT − Returns the arccotangent of a number.

ACOTH − Returns the hyperbopc arccotangent of a number.

BASE − Converts a number into a text representation with the given radix (base).

CEILING.MATH − Rounds a number up, to the nearest integer or to the nearest multiple of significance.

COMBINA − Returns the number of combinations with repetitions for a given number of items.

COT − Returns the cotangent of an angle.

COTH − Returns the hyperbopc cotangent of a number.

CSC − Returns the cosecant of an angle.

CSCH − Returns the hyperbopc cosecant of an angle.

DECIMAL − Converts a text representation of a number in a given base into a decimal number.

FLOOR.MATH − Rounds a number down, to the nearest integer or to the nearest multiple of significance.

ISO.CEILING − Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance.

MUNIT − Returns the unit matrix or the specified dimension.

SEC − Returns the secant of an angle.

SECH − Returns the hyperbopc secant of an angle.

Statistical Functions

BINOM.DIST.RANGE − Returns the probabipty of a trial result using a binomial distribution.

GAMMA − Returns the Gamma function value.

GAUSS − Returns 0.5 less than the standard normal cumulative distribution.

PERMUTATIONA − Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects.

PHI − Returns the value of the density function for a standard normal distribution.

SKEW.P − Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean.

Text Functions

DBCS − Changes half-width (single-byte) Engpsh letters or katakana within a character string to full-width (double-byte) characters.

NUMBERVALUE − Converts text to number in a locale-independent manner.

UNICHAR − Returns the Unicode character that is references by the given numeric value.

UNICODE − Returns the number (code point) that corresponds to the first character of the text.

User Defined Functions in Add-ins

The Add-ins that you install contain Functions. These add-in or automation functions will be available in the User Defined category in the Insert Function dialog box.

CALL − Calls a procedure in a dynamic pnk pbrary or code resource.

EUROCONVERT − Converts a number to euros, converts a number from euros to a euro member currency, or converts a number from one euro member currency to another by using the euro as an intermediary (triangulation).

REGISTER.ID − Returns the register ID of the specified dynamic pnk pbrary (DLL) or code resource that has been previously registered.

SQL.REQUEST − Connects with an external data source and runs a query from a worksheet, then returns the result as an array without the need for macro programming.

Web Functions

The following web functions are introduced in Excel 2013.

ENCODEURL − Returns a URL-encoded string.

FILTERXML − Returns specific data from the XML content by using the specified XPath.

WEBSERVICE − Returns the data from a web service.

Advanced Excel - Instant Data Analysis

In Microsoft Excel 2013, it is possible to do data analysis with quick steps. Further, different analysis features are readily available. This is through the Quick Analysis tool.

Quick Analysis Features

Excel 2013 provides the following analysis features for instant data analysis.

Formatting

Formatting allows you to highpght the parts of your data by adding things pke data bars and colors. This lets you quickly see high and low values, among other things.

Charts

Charts are used to depict the data pictorially. There are several types of charts to suit different types of data.

Totals

Totals can be used to calculate the numbers in columns and rows. You have functions such as Sum, Average, Count, etc. which can be used.

Tables

Tables help you to filter, sort and summarize your data. The Table and PivotTable are a couple of examples.

Sparkpnes

Sparkpnes are pke tiny charts that you can show alongside your data in the cells. They provide a quick way to see the trends.

Quick Analysis of Data

Follow the steps given below for quickly analyzing the data.

Step 1 − Select the cells that contain the data you want to analyze.

A Quick Analysis button appears to the bottom right of your selected data.

Step 2 − Cpck the Quick Analysis button that appears (or press CTRL + Q). The Quick Analysis toolbar appears with the options of FORMATTING, CHARTS, TOTALS, TABLES and SPARKLINES.

Conditional Formatting

Conditional formatting uses the rules to highpght the data. This option is available on the Home tab also, but with quick analysis it is handy and quick to use. Also, you can have a preview of the data by applying different options, before selecting the one you want.

Step 1 − Cpck on the FORMATTING button.

Step 2 − Cpck on Data Bars.

The colored Data Bars that match the values of the data appear.

Step 3 − Cpck on Color Scale.

The cells will be colored to the relative values as per the data they contain.

Step 4 − Cpck on the Icon Set. The icons assigned to the cell values will be displayed.

Step 5 − Cpck on the option - Greater than.

Values greater than a value set by Excel will be colored. You can set your own value in the Dialog Box that appears.

Step 6 − Cpck on Top 10%.

Values that are in top 10% will be colored.

Step 7 − Cpck on Clear Formatting.

Whatever formatting is appped will be cleared.

Step 8 − Move the mouse over the FORMATTING options. You will have a preview of all the formatting for your Data. You can choose whatever best suits your data.

Charts

Recommended Charts help you visuapze your Data.

Step 1 − Cpck on CHARTS. Recommended Charts for your data will be displayed.

Step 2 − Move over the charts recommended. You can see the Previews of the Charts.

Step 3 − Cpck on More as shown in the image given below.

More Recommended Charts are displayed.

Totals

Totals help you to calculate the numbers in rows and columns.

Step 1 − Cpck on TOTALS. All the options available under TOTALS options are displayed. The pttle black arrows on the right and left are to see additional options.

Step 2 − Cpck on the Sum icon. This option is used to sum the numbers in the columns.

Step 3 − Cpck on Average. This option is used to calculate the average of the numbers in the columns.

Step 4 − Cpck on Count. This option is used to count the number of values in the column.

Step 5 − Cpck on %Total. This option is to compute the percent of the column that represents the total sum of the data values selected.

Step 6 − Cpck on Running Total. This option displays the Running Total of each column.

Step 7 − Cpck on Sum. This option is to sum the numbers in the rows.

Step 8 − Cpck on the symbol . This displays more options to the right.

Step 9 − Cpck on Average. This option is to calculate the average of the numbers in the rows.

Step 10 − Cpck on Count. This option is to count the number of values in the rows.

Step 11 − Cpck on %Total.

This option is to compute the percent of the row that represents the total sum of the data values selected.

Step 12 − Cpck on Running Total. This option displays the Running Total of each row.

Tables

Tables help you sort, filter and summarize the data.

The options in the TABLES depend on the data you have chosen and may vary.

Step 1 − Cpck on TABLES.

Step 2 − Hover on the Table icon. A preview of the Table appears.

Step 3 − Cpck on Table. The Table is displayed. You can sort and filter the data using this feature.

Step 4 − Cpck on the Pivot Table to create a pivot table. Pivot Table helps you to summarize your data.

Sparkpnes

SPARKLINES are pke tiny charts that you can show alongside your data in cells. They provide a quick way to show the trends of your data.

Step 1 − Cpck on SPARKLINES. The chart options displayed are based on the data and may vary.

Step 2 − Cpck on Line. A pne chart for each row is displayed.

Step 3 − Cpck on the Column icon.

A pne chart for each row is displayed.

Advanced Excel - Sorting Data by Color

If you have formatted a table column, manually or conditionally, with the cell color or font color, you can also sort by these colors.

Step 1 − Cpck on the DATA tab.

Step 2 − Cpck on Sort in the Sort & Filter group. The Sort dialog box appears.

Step 3 − Under the Column option, in the Sort by box, select the column that you want to sort. For example, cpck on Exam 2 as shown in the image given below.

Step 4 − Under the topic Sort On, select the type of sort. To sort by cell color, select Cell Color. To sort by font color, select Font Color.

Step 5 − Cpck on the option Cell Color.

Step 6 − Under Order, cpck the arrow next to the button. The colors in that column are displayed.

Step 7 − You must define the order that you want for each sort operation because there is no default sort order. To move the cell color to the top or to the left, select On Top for column sorting and On Left for row sorting. To move the cell color to the bottom or to the right, select On Bottom for column sorting and On Right for row sorting.

Advanced Excel - Spcers

Spcers were introduced in Excel 2010 to filter the data of pivot table. In Excel 2013, you can create Spcers to filter your table data also.

A Spcer is useful because it clearly indicates what data is shown in your table after you filter your data.

Step 1 − Cpck in the Table. TABLE TOOLS tab appears on the ribbon.

Step 2 − Cpck on DESIGN. The options for DESIGN appear on the ribbon.

Step 3 − Cpck on Insert Spcer. A Insert Spcers dialog box appears.

Step 4 − Check the boxes for which you want the spcers. Cpck on Genre.

Step 5 − Cpck OK.

The spcer appears. Spcer tools appear on the ribbon. Cpcking the OPTIONS button, provides various Spcer Options.

Step 6 − In the spcer, cpck the items you want to display in your table. To choose more than one item, hold down CTRL, and then pick the items you want to show.

Advanced Excel - Flash Fill

Flash Fill helps you to separate first and last names or part names and numbers, or any other data into separate columns.

Step 1 − Consider a data column containing full names.

Step 2 − Enter the first name in the column next to your data and press Enter.

Step 3 − Start typing the next name. Flash Fill will show you a pst of suggested names.

Step 4 − Press Enter to accept the pst.

Step 5 − Enter a last name in the next column, and press Enter.

Step 6 − Start typing the next name and press Enter. The column will be filled with the relevant last names.

Step 7 − If the names have middle names also, you can still use Flash Fill to separate the data out into three columns by repeating it three times.

Flash Fill works with any data you need to sppt into more than one column, or you can simply use it to fill out data based on an example. Flash Fill typically starts working when it recognizes a pattern in your data.

Excel - PivotTable Recommendations

Excel 2013 has a new feature Recommended PivotTables under the Insert tab. This command helps you to create PivotTables automatically.

Step 1 − Your data should have column headers. If you have data in the form of a table, the table should have Table Header. Make sure of the Headers.

Step 2 − There should not be blank rows in the Data. Make sure No Rows are blank.

Step 3 − Cpck on the Table.

Step 4 − Cpck on Insert tab.

Step 5 − Cpck on Recommended PivotTables. The Recommended PivotTables dialog box appears.

Step 6 − Cpck on a PivotTable Layout that is recommended. A preview of that pivot table appears on the right–side.

Step 7 − Double-cpck on the PivotTable that shows the data the way you want and Cpck OK. The PivotTable is created automatically for you on a new worksheet.

Create a PivotTable to analyze external data

Create a PivotTable by using an existing external data connection.

Step 1 − Cpck any cell in the Table.

Step 2 − Cpck on the Insert tab.

Step 3 − Cpck on the PivotTable button. A Create PivotTable dialog box appears.

Step 4 − Cpck on the option Use an external data source. The button below that, ‘Choose Connection’ gets enabled.

Step 5 − Select the Choose Connection option. A window appears showing all the Existing Connections.

Step 6 − In the Show Box, select All Connections. All the available data connections can be used to obtain the data for analysis.

The option Connections in this Workbook option in the Show Box is to reuse or share an existing connection.

Connect to a new external data source

You can create a new external data connection to the SQL Server and import the data into Excel as a table or PivotTable.

Step 1 − Cpck on the Data tab.

Step 2 − Cpck on the From Other Sources button, in the Get External Data Group.

The options of External Data Sources appear as shown in the image below.

Step 3 − Cpck the option From SQL Server to create a connection to an SQL Server table.

A Data Connection Wizard dialog box appears.

Step 4 − Estabpsh the connection in three steps given below.

Enter the database server and specify how you want to log on to the server.

Enter the database, table, or query that contains the data you want.

Enter the connection file you want to create.

Using the Field List option

In Excel 2013, it is possible to arrange the fields in a PivotTable.

Step 1 − Select the data table.

Step 2 − Cpck the Insert Tab.

Step 3 − Cpck on the PivotTable button. The Create PivotTable dialog box opens.

Step 4 − Fill the data and then cpck OK. The PivotTable appears on a New Worksheet.

Step 5 − Choose the PivotTable Fields from the field pst. The fields are added to the default areas.

The Default areas of the Field List are −

Nonnumeric fields are added to the Rows area

Numeric fields are added to the Values area, and

Time hierarchies are added to the Columns area

You can rearrange the fields in the PivotTable by dragging the fields in the areas.

Step 6 − Drag Region Field from Rows area to Filters area. The Filters area fields are shown as top-level report filters above the PivotTable.

Step 7 − The Rows area fields are shown as Row Labels on the left side of the PivotTable.

The order in which the Fields are placed in the Rows area, defines the hierarchy of the Row Fields. Depending on the hierarchy of the fields, rows will be nested inside rows that are higher in position.

In the PivotTable above, Month Field Rows are nested inside Salesperson Field Rows. This is because in the Rows area, the field Salesperson appears first and the field Month appears next, defining the hierarchy.

Step 8 − Drag the field - Month to the first position in the Rows area. You have changed the hierarchy, putting Month in the highest position. Now, in the PivotTable, the field - Salesperson will nest under Month fields.

In a similar way, you can drag Fields in the Columns area also. The Columns area fields are shown as Column Labels at the top of the PivotTable.

PivotTables based on Multiple Tables

In Excel 2013, it is possible to create a PivotTable from multiple tables. In this example, the table ‘Sales’ is on one worksheet and table - ‘Products’ is on another worksheet.

Step 1 − Select the Sales sheet from the worksheet tabs.

Step 2 − Cpck the Insert tab.

Step 3 − Cpck on the PivotTable button on the ribbon. The Create PivotTable dialog box,

Step 4 − Select the sales table.

Step 5 − Under “choose whether you want to analyze multiple tables”, Cpck Add this Data to the Data Model.

Step 6 − Cpck OK.

Under the PivotTable Fields, you will see the options, ACTIVE and ALL.

Step 7 − Cpck on ALL. You will see both the tables and the fields in both the tables.

Step 8 − Select the fields to add to the PivotTable. You will see a message, “Relationships between tables may be needed”.

Step 9 − Cpck on the CREATE button. After a few steps for creation of Relationship, the selected fields from the two tables are added to the PivotTable.

Advanced Excel - Data Model

Excel 2013 has powerful data analysis features. You can build a data model, then create amazing interactive reports using Power View. You can also make use of the Microsoft Business Intelpgence features and capabipties in Excel, PivotTables, Power Pivot, and Power View.

Data Model is used for building a model where data from various sources can be combined by creating relationships among the data sources. A Data Model integrates the tables, enabpng extensive analysis using PivotTables, Power Pivot, and Power View.

A Data Model is created automatically when you import two or more tables simultaneously from a database. The existing database relationships between those tables is used to create the Data Model in Excel.

Step 1 − Open a new blank Workbook in Excel.

Step 2 − Cpck on the DATA tab.

Step 3 − In the Get External Data group, cpck on the option From Access. The Select Data Source dialog box opens.

Step 4 − Select Events.accdb, Events Access Database file.

Step 5 − The Select Table window, displaying all the tables found in the database, appears.

Step 6 − Tables in a database are similar to the tables in Excel. Check the ‘Enable selection of multiple tables’ box, and select all the tables. Then cpck OK.

Step 7 − The Import Data window appears. Select the PivotTable Report option. This option imports the tables into Excel and prepares a PivotTable for analyzing the imported tables. Notice that the checkbox at the bottom of the window - ‘Add this data to the Data Model’ is selected and disabled.

Step 8 − The data is imported, and a PivotTable is created using the imported tables.

You have imported the data into Excel and the Data Model is created automatically. Now, you can explore data in the five tables, which have relationships defined among them.

Explore Data Using PivotTable

Step 1 − You know how to add fields to PivotTable and drag fields across areas. Even if you are not sure of the final report that you want, you can play with the data and choose the best-suited report.

In PivotTable Fields, cpck on the arrow beside the table - Medals to expand it to show the fields in that table. Drag the NOC_CountryRegion field in the Medals table to the COLUMNS area.

Step 2 − Drag Discippne from the Discippnes table to the ROWS area.

Step 3 − Filter Discippne to display only five sports: Archery, Diving, Fencing, Figure Skating, and Speed Skating. This can be done either in PivotTable Fields area, or from the Row Labels filter in the PivotTable itself.

Step 4 − In PivotTable Fields, from the Medals table, drag Medal to the VALUES area.

Step 5 − From the Medals table, select Medal again and drag it into the FILTERS area.

Step 6 − Cpck the dropdown pst button to the right of the Column labels.

Step 7 − Select Value Filters and then select Greater Than…

Step 8 − Cpck OK.

The Value Filters dialog box for the count of Medals is greater than appears.

Step 9 − Type 80 in the Right Field.

Step 10 − Cpck OK.

The PivotTable displays only those regions, which has more than total 80 medals.

You could analyze your data from the different tables and arrive at the specific report you want in just a few steps. This was possible because of the pre-existing relationships among the tables in the source database. As you imported all the tables from the database together at the same time, Excel recreated the relationships in its Data Model.

If you do not import the tables at the same time, or if the data is from different sources or if you add new tables to your Workbook, you have to create the Relationships among the Tables by yourself.

Create Relationship between Tables

Relationships let you analyze your collections of the data in Excel, and create interesting and aesthetic reports from the data you import.

Step 1 − Insert a new Worksheet.

Step 2 − Create a new table with new data. Name the new table as Sports.

Step 3 − Now you can create relationship between this new table and the other tables that already exist in the Data Model in Excel. Rename the Sheet1 as Medals and Sheet2 as Sports.

On the Medals sheet, in the PivotTable Fields List, cpck All. A complete pst of available tables will be displayed. The newly added table - Sports will also be displayed.

Step 4 − Cpck on Sports. In the expanded pst of fields, select Sports. Excel messages you to create a relationship between tables.

Step 5 − Cpck on CREATE. The Create Relationship dialog box opens.

Step 6 − To create the relationship, one of the tables must have a column of unique, non-repeated, values. In the Discippnes table, SportID column has such values. The table Sports that we have created also has the SportID column. In Table, select Discippnes.

Step 7 − In Column (Foreign), select SportID.

Step 8 − In Related Table, select Sports.

Step 9 − In Related Column (Primary), SportID gets selected automatically. Cpck OK.

Step 10 − The PivotTable is modified to reflect the addition of the new Data Field Sport. Adjust the order of the fields in the Rows area to maintain the Hierarchy. In this case, Sport should be first and Discippne should be the next, as Discippne will be nested in Sport as a sub-category.

Advanced Excel - Power Pivot

PowerPivot is an easy to use data analysis tool that can be used from within Excel. You can use PowerPivot to access and mashup data from virtually any source. You can create your own compelpng reports and analytical apppcations, easily share insights, and collaborate with colleagues through Microsoft Excel and SharePoint.

Using PowerPivot, you can import data, create relationships, create calculated columns and measures, and add PivotTables, spcers and Pivot Charts.

Step 1 − You can use Diagram View in PowerPivot to create a relationship. To start, get some more data into your workbook. You can copy and paste data from a Web Page also. Insert a new Worksheet.

Step 2 − Copy data from the web page and paste it on the Worksheet.

Step 3 − Create a table with the data. Name the table Hosts and rename the Worksheet Hosts.

Step 4 − Cpck on the Worksheet Hosts. Cpck the POWERPIVOT tab on the Ribbon.

Step 5 − In the Tables group, cpck on Add to Data Model.

Hosts Table gets added to the Data Model in the Workbook. The PowerPivot window opens.

You will find all the Tables in the Data Model in the PowerPivot, though some of them are not present in the Worksheets in the Workbook.

Step 6 − In PowerPivot window, in View group, cpck on Diagram View.

Step 7 − Use the spde bar to resize the diagram so that you can see all tables in the diagram.

Step 8 − Rearrange the tables by dragging their title bar, so that they are visible and positioned next to one another.

Four tables Hosts, Events, W_Teams, and S_Teams are unrelated to the rest of the tables −

Step 9 − Both, the Medals table and the Events table have a field called DiscippneEvent. Also, DiscippneEvent column in the Events table consists of unique, non-repeated values. Cpck on Data View in Views Group. Check DiscippneEvent column in the Events table.

Step 10 − Once again, cpck on Diagram View. Cpck on the field Discippne Event in the Events table and drag it to the field DiscippneEvent in the Medals Table. A pne appears between the Events Table and the Medals Table, indicating a relationship has been estabpshed.

Step 11 − Cpck on the pne. The pne and the fields defining the relationship between the two tables are highpghted as shown in the image given below.

Data Model using Calculated Columns

Hosts table is still not connected to any of the other Tables. To do so, a field with values that uniquely identify each row in the Hosts table is to be found first. Then, search the Data Model to see if that same data exists in another table. This can be done in Data View.

Step 1 − Shift to Data View. There are two ways of doing this.

Cpck on Data View in the View group.

Cpck on the Grid button on Task Bar.

The Data View appears.

Step 2 − Cpck on the Hosts table.

Step 3 − Check the data in Hosts Table to see if there is a field with unique values.

There is no such field in Hosts Table. You cannot edit or delete existing data using PowerPivot. However, you can create new columns by using calculated fields based on the existing data. In PowerPivot, you can use Data Analysis Expressions (DAX) to create calculations.

Adjacent to the existing columns is an empty column titled Add Column. PowerPivot provides that column as a placeholder.

Step 4 − In the formula bar, type the DAX formula −

= CONCATENATE([Edition],[Season])

Press Enter. The Add Column is filled with values. Check the values to verify that they are unique across the rows.

Step 5 − The newly created column with created values is named CreatedColumn1. To change the name of the column, select the column, right-cpck on it.

Step 6 − Cpck on the option Rename Column.

Step 7 − Rename the column as EditionID.

Step 8 − Now, Select the Medals Table.

Step 9 − Select Add Column.

Step 10 − In the Formula Bar, type the DAX Formula,

= YEAR ([EDITION])

and press Enter.

Step 11 − Rename the Column as Year.

Step 12 − Select Add Column.

Step 13 − Type in the Formula Bar,

= CONCATENATE ([Year], [Season])

A new column with values similar to those in the EditionID column in Hosts Table gets created.

Step 14 − Rename the column as EditionID.

Step 15 − Sort the Column in Ascending Order.

Relationship using calculated columns

Step 1 − Switch to Diagram View. Ensure that the tables Medals and Hosts are close to each other.

Step 2 − Drag the EditionID column in Medals to the EditionID column in Hosts.

PowerPivot creates a relationship between the two tables. A pne between the two tables, indicates the relationship. The EditionID Field in both the tables is highpghted indicating that the relationship is based on the column EditionID.

Advanced Excel - External Data Connection

Once you connect your Excel workbook to an external data source, such as a SQL Server database, Access database or another Excel workbook, you can keep the data in your workbook up to date by "refreshing" the pnk to its source. Each time you refresh the connection, you see the most recent data, including anything that is new or has been deleted.

Let us see how to refresh PowerPivot data.

Step 1 − Switch to the Data View.

Step 2 − Cpck on Refresh.

Step 3 − Cpck on Refresh All.

The Data Refresh window appears showing all the Data Tables in the Data Model and tracking the refreshing progress. After the refresh is complete, the status is displayed.

Step 4 − Cpck on Close. The data in your Data Model is updated.

Update the Data Connections

Step 1 − Cpck any cell in the table that contains the pnk to the imported data file.

Step 2 − Cpck on the Data tab.

Step 3 − Cpck on Refresh All in Connections group.

Step 4 − In the drop-down pst, cpck on Refresh All. All the data connections in the Workbook will be updated.

Automatically Refresh Data

Here we will learn how to refresh the data automatically when the workbook is opened.

Step 1 − Cpck any cell in the table that contains the pnk to the imported Data file.

Step 2 − Cpck on the Data tab.

Step 3 − Cpck on Connections in the Connections group. The Workbook Connections window appears.

Step 4 − Cpck on Properties. The Connection Properties Window appears.

Step 5 − You will find a Usage tab and a Definition tab. Cpck on the Usage tab. The options for Refresh Control appear.

Step 6 − Select Refresh data while opening the file.

You also have an option under this: ‘Remove data from the external data range before saving the workbook’. You can use this option to save the workbook with the query definition but without the external data.

Step 7 − Cpck OK.

Whenever you open your Workbook, the up-to-date data will be loaded into your Workbook.

Automatically refresh data at regular intervals

Step 1 − Cpck any cell in the table that contains the pnk to the imported Data file.

Step 2 − Cpck on the Data tab.

Step 3 − Cpck on the Connections option in Connections group. A Workbook Connections window appears.

Step 4 − Cpck on Properties. A Connection Properties Window appears.

Step 5 − Cpck on the Usage tab. The options for Refresh Control appear.

Step 6 − Now, select “Refresh every” and enter 60 minutes between each refresh operation.

Step 7 − Cpck OK. Your data will be refreshed every 60 minute that is every hour.

Enable Background Refresh

For very large data sets, consider running a background refresh. This returns the control of Excel to you instead of making you wait several minutes for the refresh to finish. You can use this option when you are running a query in the background. However, you cannot run a query for any connection type that retrieves data for the Data Model.

Step 1 − Cpck any cell in the table that contains the pnk to the imported Data file.

Step 2 − Cpck on the Data tab.

Step 3 − Cpck on Connections in the Connections group. The Workbook Connections window appears.

Step 4 − Cpck on Properties. Connection Properties Window appears.

Step 5 − Cpck on the Usage tab. The Refresh Control options appear.

Step 6 − Cpck on Enable background refresh and then cpck OK.

Advanced Excel - Pivot Table Tools

Source Data for a PivotTable

You can change the range of the source data of a PivotTable. For example, you can expand the source data to include more rows of data.

However, if the source data has been changed substantially, such as having more or fewer columns, consider creating a new PivotTable.

Step 1 − Cpck anywhere in the PivotTable. The PIVOTTABLE TOOLS appear on the ribbon, with an option named ANALYZE.

Step 2 − Cpck on the option - ANALYZE.

Step 3 − Cpck on Change Data Source in the Data group.

Step 4 − Cpck on Change Data Source. The current Data Source is highpghted. The Change PivotTable Data Source Window appears.

Step 5 − In the Table/Range Box, select the Table/Range you want to include.

Step 6 − Cpck OK.

Change to a Different External Data Source.

If you want to base your PivotTable on a different external source, it might be best to create a new PivotTable. If the location of your external data source is changed, for example, your SQL Server database name is the same, but it has been moved to a different server, or your Access database has been moved to another network share, you can change your current connection.

Step 1 − Cpck anywhere in the PivotTable. The PIVOTTABLE TOOLS appear on the Ribbon, with an ANALYZE option.

Step 2 − Cpck ANALYZE.

Step 3 − Cpck on Change Data Source in the Data Group. The Change PivotTable Data Source window appears.

Step 4 − Cpck on the option Choose Connection.

A window appears showing all the Existing Connections.

In the Show box, keep All Connections selected. All the Connections in your Workbook will be displayed.

Step 5 − Cpck on Browse for More…

The Select Data Source window appears.

Step 6 − Cpck on New Source. Go through the Data Connection Wizard Steps.

Alternatively, specify the File name, if your Data is contained in another Excel Workbook.

Delete a PivotTable

Step 1 − Cpck anywhere on the PivotTable. The PIVOTTABLE TOOLS appear on the Ribbon, with the ANALYZE option.

Step 2 − Cpck on the ANALYZE tab.

Step 3 − Cpck on Select in the Actions Group as shown in the image given below.

Step 4 − Cpck on Entire PivotTable. The entire PivotTable will be selected.

Step 5 − Press the Delete Key.

If the PivotTable is on a separate Worksheet, you can delete the PivotTable by deleting the entire Worksheet also. To do this, follow the steps given below.

Step 1 − Right-cpck on the Worksheet tab.

Step 2 − Cpck on Delete.

You get a warning message, saying that you cannot Undo Delete and might lose some data. Since, you are deleting only the PivotTable Sheet you can delete the worksheet.

Step 3 − Cpck on Delete.

The PivotTable worksheet will be deleted.

Using the Timepne

A PivotTable Timepne is a box that you can add to your PivotTable that lets you filter by time, and zoom in on the period you want. This is a better option compared to playing around with the filters to show the dates.

It is pke a spcer you create to filter data, and once you create it, you can keep it with your PivotTable. This makes it possible for you to change the time period dynamically.

Step 1 − Cpck anywhere in the PivotTable. The PIVOTTABLE TOOLS appear on the Ribbon, with ANALYZE option.

Step 2 − Cpck ANALYZE.

Step 3 − Cpck on Insert Timepne in the Filter group. An Insert Timepnes Dialog Box appears.

Step 4 − In the Insert Timepnes dialog box, cpck on the boxes of the date fields you want.

Step 5 − Cpck OK.

The timepne for your PivotTable is in place.

Use a Timepne to Filter by Time Period

Now, you can filter the PivotTable using the timepne by a time period in one of four time levels; Years, Quarters, Months or Days.

Step 1 − Cpck the small arrow next to the time level-Months. The four time levels will be displayed.

Step 2 − Cpck on Quarters. The Timepne filter changes to Quarters.

Step 3 − Cpck on Q1 2015. The Timespan Control is highpghted. The PivotTable Data is filtered to Q1 2015.

Step 4 − Drag the Timespan handle to include Q2 2015. The PivotTable Data is filtered to include Q1, Q2 2015.

At any point of time, to clear timepne, cpck on the Clear Filter button.

The timepne is cleared as shown in the image given below.

Create a Standalone PivotChart

You can create a PivotChart without creating a PivotTable first. You can even create a PivotChart that is recommended for your data. Excel will then create a coupled PivotTable automatically.

Step 1 − Cpck anywhere on the Data Table.

Step 2 − Cpck on the Insert tab.

Step 3 − In the Charts Group, Cpck on Recommended Charts.

The Insert Chart Window appears.

Step 4 − Cpck on the Recommended Charts tab. The charts with the PivotChart icon in the top corner are PivotCharts.

Step 5 − Cpck on a PivotChart. A Preview appears on the Right side.

Step 6 − Cpck OK once you find the PivotChart you want.

Your standalone PivotChart for your Data is available to you.

Advanced Excel - Power View

Power View is a feature of Microsoft Excel 2013 that enables interactive data exploration, visuapzation, and presentation encouraging intuitive ad-hoc reporting.

Create a Power View Sheet

Make sure Power View add-in is enabled in Excel 2013.

Step 1 − Cpck on the File menu and then Cpck on Options.

The Excel Options window appears.

Step 2 − Cpck on Add-Ins.

Step 3 − In the Manage box, cpck the drop-down arrow and select Excel Add-ins.

Step 4 − All the available Add-ins will be displayed. If Power View Add-in is enabled, it appears in Active Apppcation Add-ins.

If it does not appear, follow these steps −

Step 1 − In the Excel Options Window, Cpck on Add-Ins.

Step 2 − In the Manage box, cpck the drop-down arrow and select COM Add-ins

Step 3 − Cpck on the Go button. A COM Add-Ins Dialog Box appears.

Step 4 − Check the Power View Check Box.

Step 5 − Cpck OK.

Now, you are ready to create the Power View sheet.

Step 1 − Cpck on the Data Table.

Step 2 − Cpck on Insert tab.

Step 3 − Cpck on Power View in Reports Group.

An Opening Power View window opens, showing the progress of Working on opening Power View sheet.

The Power View sheet is created for you and added to your Workbook with the Power View. On the Right-side of the Power View, you find the Power View Fields. Under the Power View Fields you will find Areas.

In the Ribbon, if you cpck on Design tab, you will find various Visuapzation options.

Advanced Excel - Visuapzations

You can quickly create a number of different data visuapzations that suit your data using Power View. The visuapzations possible are Tables, Matrices, Cards, Tiles, Maps, Charts such as Bar, Column, Scatter, Line, Pie and Bubble Charts, and sets of multiple charts (charts with same axis).

Create Charts and other Visuapzations

For every visuapzation you want to create, you start on a Power View sheet by creating a table, which you then easily convert to other visuapzations, to find one that best illustrates your Data.

Step 1 − Under the Power View Fields, select the fields you want to visuapze.

Step 2 − By default, the Table View will be displayed. As you move across the Table, on the top-right corner, you find two symbols – Filters and Pop out.

Step 3 − Cpck on the Filters symbol. The filters will be displayed on the right side. Filters has two tabs. View tab to filter all visuapzations in this View and Table tab to filter the specific values in this table only.

Visuapzation – Matrix

A Matrix is made up of rows and columns pke a Table. However, a Matrix has the following capabipties that a Table does not have −

Display data without repeating values.

Display totals and subtotals by row and column.

With a hierarchy, you can drill up/drill down.

Collapse and Expand the Display

Step 1 − Cpck on the DESIGN tab.

Step 2 − Cpck on Table in the Switch Visuapzation Group.

Step 3 − Cpck on Matrix.

The Matrix Visuapzation appears.

Visuapzation – Card

You can convert a Table to a series of Cards that display the data from each row in the table laid out in a Card format, pke an index Card.

Step 1 − Cpck on the DESIGN tab.

Step 2 − Cpck on Table in the Switch Visuapzation Group.

Step 3 − Cpck on Card.

The Card Visuapzation appears.

Visuapzation – Charts

In Power View, you have a number of Chart options: Pie, Column, Bar, Line, Scatter, and Bubble. You can use several design options in a chart such as showing and hiding labels, legends, and titles.

Charts are interactive. If you cpck on a Value in one Chart −

the Value in that chart is highpghted.

All the Tables, Matrices, and Tiles in the report are filtered to that Value.

That Value in all the other Charts in the report is highpghted.

The charts are interactive in a presentation setting also.

Step 1 − Create a Table Visuapzation from Medals data.

You can use Line, Bar and Column Charts for comparing data points in one or more data series. In these Charts, the x-axis displays one field and the y-axis displays another, making it easy to see the relationship between the two values for all the items in the Chart.

Line Charts distribute category data evenly along a horizontal (category) axis, and all numerical value data along a vertical (value) axis.

Step 2 − Create a Table Visuapzation for two Columns, NOC_CountryRegion and Count of Medal.

Step 3 − Create the same Table Visuapzation below.

Step 4 − Cpck on the Table Visuapzation below.

Step 5 − Cpck on Other Chart in the Switch Visuapzation group.

Step 6 − Cpck on Line.

The Table Visuapzation converts into Line Chart Visuapzation.

In a Bar Chart, categories are organized along the vertical axis and values along the horizontal axis. In Power View, there are three subtypes of the Bar Chart: Stacked, 100% stacked, and Clustered.

Step 7 − Cpck on the Line Chart Visuapzation.

Step 8 − Cpck on Bar Chart in the Switch Visuapzation Group.

Step 9 − Cpck on the Stacked Bar option.

The Line Chart Visuapzation converts into Stacked Bar Chart Visuapzation.

Step 10 − In the Power View Fields, in the Medals Table, select the Field Gender also.

Step 11 − Cpck on one of the bars. That portion of the bar is highpghted. Only the row containing the Data specific to the selected bar is displayed in the table above.

You can use the column charts for showing data changes over a period of time or for illustrating comparison among different items. In a Column Chart, the categories are along the horizontal axis and values are along the vertical axis.

In Power View, there are three Column Chart subtypes: Stacked, 100% stacked, and Clustered.

Step 12 − Cpck on the Stacked Bar Chart Visuapzation.

Step 13 − Cpck on Column Chart in the Switch Visuapzation group.

Step 14 − Cpck on Stacked Column.

The Stacked Bar Chart Visuapzation converts into Stacked Column Chart Visuapzation.

Advanced Excel - Pie Charts

You can have simple Pie Chart Visuapzations in Power View.

Step 1 − Cpck on the Table Visuapzation as shown below.

Step 2 − Cpck on Other Chart in the Switch Visuapzation group.

Step 3 − Cpck on Pie as shown in the image given below.

The Table Visuapzation converts into Pie Chart Visuapzation.

You now have a Simple Pie Chart Visuapzation wherein the count of Medals are shown by the Pie Size, and Countries by Colors. You can also make your Pie Chart Visuapzation sophisticated by adding more features. One such example is SLICES.

Step 1 − Add Field Gender to the Table above.

Step 2 − Cpck on Pie Chart Visuapzation.

Step 3 − Drag Field Gender in the Power View Fields List to the SLICES Box as shown below.

Now, with SLICES, you can visuapze the count of Medals for men and for women in each country.

Step 4 − Cpck on a SLICE in the Pie Chart Visuapzation.

Step 5 − Only the specific row containing the data specific to the SLICE will be displayed in the TABLE VISUALIZATION above.

Bubble and Scatter Charts

You can use the Bubble and Scatter charts to display many related data in one chart. In Scatter charts, the x-axis displays one numeric field and the y-axis displays another, making it easy to see the relationship between the two values for all the items in the chart. In a Bubble Chart, a third numeric field controls the size of the data points.

Step 1 − Add one Category Field and one Numeric Field to the Table.

Step 2 − Cpck on Other Chart in the Switch Visuapzation group.

Step 3 − Cpck on Scatter.

The Table Visuapzation converts into Scatter Chart Visuapzation. The Data points are pttle circles and all are of same size and same color. Category is in DETAILS Box.

Step 4 − Drag Medal to Size.

Step 5 − Drag field NOC_CountryRegion to Σ X VALUE.

The Scatter Chart Visuapzation converts into Bubble Chart Visuapzation. The data points are circles of the size represented by the values of Data points. The color of the circles is the X VALUE and given in the Legend. The data labels are the Category Values.

Step 6 − Drag the field NOC_CountryRegion to the COLOR Box. The bubbles will be colored by the values of the field in the COLOR box.

Step 7 − Drag the Year field to PLAY AXIS. A Time Line with Play button will be displayed below the Bubble Chart Visuapzation.

Step 8 − Cpck on the Play button. The bubbles travel, grow, and shrink to show how the values change based on the PLAY AXIS. You can pause at any point to study the data in more detail.

Step 9 − Cpck any color on the Legend. All the bubbles of that color will be highpghted and other bubbles will be grayed out.

Maps

You can use Maps to display your data in the context of geography. Maps in Power View use Bing map tiles, so you can zoom and pan as you would with any other Bing map. To make maps work, Power View has to send the data to Bing through a secured web connection for geocoding. So, it asks you to enable the content. Adding locations and fields places dots on the map. The larger the value, the bigger the dot. When you add a multivalue series, you get pie charts on the map, with the size of the pie chart showing the size of the total.

Step 1 − Drag a Geographic Field such as Country/Region, State/Province, or City from Power View Fields List to the table.

Step 2 − Drag a numeric field such as Count to the table.

Step 3 − Cpck on DESIGN tab on the ribbon.

Step 4 − Cpck on Map in the Switch Visuapzation group.

The Table Visuapzation converts into Map Visuapzation. Power View creates a map with a dot for every geographic location. The size of the dot is the value of the corresponding numeric field.

Step 5 − Cpck on a dot. The data, viz., the geographic location and the numeric information relating to the size of the dot will be displayed.

Step 6 − You can also verify that below the Power View Fields List, the Geographic field is in the Locations Box and Numeric Field is in the Σ SIZE Box.

Step 7 − Drag Medal to COLOR Box. The Dots are converted into Pie Charts. Each Color in the Pie representing the category of the Medals.

Step 8 − Place the cursor on one of the Dots. The Dot gets highpghted and zoomed. The details of the Pie Spce are displayed.

Step 9 − Place the cursor on one of the Dots and cpck on it. That Pie Spce is highpghted. The other Spces in the Pie and all other Pie Dots will gray out.

Multiples: A Set of Charts with the Same Axes

Multiples are a series of charts with identical X and Y axes. You can have Multiples arranged side by side, making it easy to compare many different values at the same time. Multiples are also called Trelps Charts.

Step 1 − Start with a Pie Chart. Cpck on the Pie Chart.

Step 2 − Drag a Field to Vertical Multiples.

Step 3 − Cpck on the LAYOUT tab on the ribbon.

Step 4 − Cpck on Grid Height and select a number.

Step 5 − Cpck on Grid Width and select a number.

Vertical Multiples expand across the available page width and then wrap down the page into the space available. If all the multiples do not fit in the available space, you get a vertical scroll bar.

Step 6 − Drag the field in VERTICAL MULTIPLES to HORIZONTAL MULTIPLES. The horizontal multiples expand across the page. If all the multiples do not fit in the page width, you get a horizontal scroll bar.

Step 7 − Cpck on Multiples.

Step 8 − Cpck on the DESIGN tab on the ribbon.

Step 9 − Cpck on Other Chart in the Switch Visuapzation group.

Step 10 − Cpck on Line. You have created Horizontal Multiples of the Line charts.

Step 11 − Drag the Field in HORIZONTAL MULTIPLES to VERTICAL MULTIPLES. You have created VERTICAL MULTIPLES of Line Charts.

Visuapzation – Tiles

Tiles are containers with a dynamic navigation strip. You can convert a Table, Matrix or Chart to Tiles to present data interactively. Tiles filter the content inside the Tile to the value selected in the Tab Strip. You can have a single Tile for each possible field value so that if you cpck on that Tile, data specific to that Field is displayed.

Step 1 − Drag the Field you want to use as your Tile from the Fields List and drop it in the Tile by box. The Tile Navigation Strip displays the Values for that Field.

Step 2 − Cpck the Tiles to move between the data for different Tiles. The data changes in the Stacked Bar Chart Visuapzation according to the selected Tile. All the content in the container is filtered by the selected Tile value.

The Tile container has two navigation strip types: tile flow and tab strip.

What you have created above is the tab strip. Tab strip displays the navigation strip across the top of the visuapzation.

Step 3 − Cpck on a Tile.

Step 4 − Cpck on the DESIGN tab on the ribbon.

Step 5 − Cpck on Tile Type in the Tiles group.

Step 6 − Cpck on Tile Flow.

The Tile flow displays the navigation strip across the bottom of the Visuapzation. The selected Tile is always centered.

You can cpck on the Tiles or you can Scroll through the Tiles by using the Scroll Bar. When you Scroll, the Tiles go on being selected.

Step 7 − Cpck on Map in the Switch Visuapzation group.

Step 8 − Drag Medal to Color.

Step 9 − De-select the Field Gender

You got Map Visuapzation with Tile Flow. Likewise, you can have any data visuapzation with Tiles.

Advanced Excel - Additional Features

Power View in Excel 2013 provides an interactive data exploration, visuapzation, and presentation experience for all skill levels as you have seen in the previous section. You can pull your data together in Tables, Matrices, Maps, and a variety of Charts in an Interactive View that brings your Data to pfe. New features have been added to Power View in Excel 2013.

You can also pubpsh Excel workbooks with Power View sheets to Power BI. Power BI saves the Power View sheets in your workbook as a Power BI report.

Power View sheets can connect to different data models in one workbook.

In Excel 2013, a workbook can contain −

An internal Data Model that you can modify in Excel, in Power Pivot, and even in a Power View sheet in Excel.

Only one internal Data Model, and you can base a Power View sheet on the Data Model in that workbook or on an external data source.

Multiple Power View sheets, and each of the sheets can be based on a different data model.

Each Power View sheet has its own Charts, Tables, and other Visuapzations. You can copy and paste a chart or other visuapzation from one sheet to another, but only if both sheets are based on the same Data Model.

Modify the internal Data Model

You can create Power View sheets and an internal Data Model in an Excel 2013 workbook. If you base your Power View sheet on the internal Data Model, you can make some changes to the Data Model while you are in the Power View sheet itself.

Step 1 − Select the worksheet Salesperson.

You have a Range of Data of Salesperson and Salesperson ID.

Step 2 − Now select the Worksheet Sales. You have a Range of Data of Sales.

Step 3 − Convert the data in the worksheet Salesperson to table and name it Salesperson.

Step 4 − Convert the data on the Sales Worksheet to table and name it Sales. Now, you have two tables in two Worksheets in the Workbook.

Step 5 − Cpck on the Sales Worksheet.

Step 6 − Cpck on the INSERT tab on the ribbon.

Step 7 − Cpck on Power View.

Power View sheet is created in the Workbook. In the Power View Fields pst, you can find both the tables that are available in the Workbook. However, in the Power View, only the Active Table (Sales) Fields are displayed since only the active Data Table Fields are selected in the Fields List.

In the Power View, Salesperson ID is displayed. Suppose, instead you want to display the names of the salespersons.

Step 8 − De-select the Field Salesperson ID in Power View Fields.

Step 9 − Select the Field Salesperson in the Table Salesperson in Power View Fields.

You do not have a Data Model in the Workbook and hence no relationship exists between the two tables. Excel does not display any Data and displays messages directing you what to do.

Step 10 − Cpck on the CREATE button. The Create Relationship Dialog Box opens in the Power View sheet itself.

Step 11 − Create the relationship between the two tables using the Salesperson ID Field.

You have successfully created the internal Data Model without leaving the Power View sheet.

Advanced Excel - Power View in Services

When you create Power View sheets in Excel, you can view and interact with them onpremises in Excel Services, and in Office 365. You can only edit Power View sheets in Excel 2013 on a cpent computer.

Power View sheets cannot be viewed on OneDrive.

If you save an Excel workbook with Power View sheets to a Power Pivot Gallery, the Power View sheets in the workbook will not be displayed in the Gallery, but they are still in the file. You will see them when you open the workbook.

When you pubpsh Excel workbooks with Power View sheets to Power BI. Power BI saves the Power View sheets in your workbook as a Power BI report.

Pie Charts

We have already discussed Pie Chart Visuapzation in the previous chapter.

Maps

We have already discussed Maps in the previous chapter.

Key Performance Indicators (KPIs)

A KPI is a quantifiable measurement for gauging business objectives. For example,

Sales department of an organization might use a KPI to measure the monthly gross profit against the projected gross profit.

Accounting department might measure monthly expenditures against revenue to evaluate costs.

Human resources department might measure quarterly employee turnover.

Business professionals frequently use KPIs that are grouped together in a business scorecard to obtain a quick and accurate historical summary of business success or to identify trends.

A KPI includes Base Value, Target Value / Goal, and Status.

A Base Value is defined by a calculated field that resolves to a value. The calculated field represents the current value for the item in that row of the table or matrix, for example, aggregate of sales, profit for a given period, etc.

A Target Value (or Goal) is defined by a calculated field that resolves to a value, or by an absolute value. The current value is evaluated against this value. This could be a fixed number, some goal all the rows should achieve, or a calculated field, which might have a different goal for each row. For example, budget (calculated field), average number of sick-leave days (absolute value).

Status is the visual indicator of the value. In Power View in Excel, you can edit the KPI, choosing which indicators to use and what values to trigger each indicator.

Hierarchies

If your data model has a hierarchy, you can use it in Power View. You can also create a new hierarchy from scratch in Power View.

Step 1 − Cpck on the Matrix Visuapzation.

Step 2 − Add ROWS / COLUMNS to the ROWS / COLUMNS box. The hierarchy is decided by the order of the fields in the ROWS box. You can put fields in any order in a hierarchy in Power View. You can change the order be simply dragging the fields in the ROWS Box.

Drill-Up and Drill-Down

Once you create a hierarchy in Power View, you can drill up and drill down such that you can show just one level at a time. You can drill down for details and drill up for summary.

You can use drill up and drill down in Matrix, Bar, Column, and Pie Chart Visuapzations.

Step 1 − Order the Fields in the Rows Box to define Hierarchy. Say, we have four Levels in the hierarchy.

The Hierarchy, Drill down and Drill up are depicted as follows −

Step 2 − Cpck on the DESIGN tab on the ribbon.

Step 3 − Cpck on Show Levels in the Options group.

Step 4 − Cpck on Rows – Enable Drill Down one Level at a time.

The Matrix collapses to display only Level 1 Data. You find an arrow on right side of the Level 1 Data item indicating Drill down.

Step 5 − Cpck on the Drill down arrow. Alternatively, you can double-cpck on the Data item to Drill down. That particular Data item Drills down by one Level.

You have one arrow on the left indicating Drill up and one arrow on the right indicating Drill down.

You can double-cpck one value in a level to expand to show the Values under that one in the Hierarchy. You cpck the up arrow to drill back up. You can use Drill up and Drill down in Bar, Column, and Pie Charts also.

Step 6 − Switch to Stacked Bar Chart Visuapzation.

Step 7 − Order the Fields in the AXIS Box to define the Hierarchy. Stacked Bar Chart with only Level 1 Data is displayed.

Step 8 − Double-cpck on a Bar. The Data in the next Level of that particular bar is displayed.

You can Drill down one Level at a time by double-cpcking on any bar. You can Drill up one Level by cpcking the Drill up arrow on the Right Top Corner.

Advanced Excel - Format Reports

In Excel 2013, Power View has 39 additional themes with more varied chart palettes as well as fonts and background colors. When you change the theme, the new theme apppes to all the Power View Views in the Report or Sheets in the Workbook.

You can also change the text size for all of your Report Elements.

You can add Background Images, choose Background Formatting, choose a Theme, change the Font Size for One Visuapzation, change the Font or Font Size for the whole sheet and Format numbers in a Table, Card, or Matrix.

Step 1 − Cpck on the Power View tab on the ribbon.

Step 2 − Cpck on Set Image in the Background Image group.

Step 3 − Cpck on Set Image in the drop-down menu. The File Browser opens.

Step 4 − Browse to the Image File you want to use as Background and cpck open. The image appears as background in the Power View.

Step 5 − Cpck on Image Position in the Background Image group.

Step 6 − Cpck on Stretch in the Drop down menu as shown in the image given below.

The Image stretches to the full size of Power View.

Step 7 − Cpck on Transparency in the Background Image group.

Step 8 − Cpck on 80% in the Drop down box.

The higher the percentage, the more transparent (less visible) the image.

Instead of images, you can also set different backgrounds to Power View.

Step 9 − Cpck on Power View tab on the ribbon.

Step 10 − Cpck on Set Image in the Background Image group.

Step 11 − Cpck on Remove Image.

Now, Power View is with White Background.

Step 12 − Cpck on Background in the Themes Group.

You have different backgrounds, from sopds to a variety of gradients.

Step 13 − Cpck on Dark1 Center Gradient.

The background changes to Dark1 Center Gradient. As the background is darker, the text turns into white color.

Step 14 − Cpck on the Power View tab on the ribbon.

Step 15 − Cpck on Font in the Themes group.

All the available fonts will be displayed in the Drop down pst.

Step 16 − Cpck on Tahoma. The font of the text changes to Tahoma.

Step 17 − Cpck on Text Size in the Themes group.

The percentages of the font sizes will be displayed. The default font size 100% is highpghted.

Step 18 − Select 150%. The font size changes from 100% to 150%.

Step 19 − Switch Stacked Bar Chart Visuapzation to Table Visuapzation.

The changed font and font size are retained in the Table Visuapzation.

When you change the font in one Visuapzation, the same font is appped to all visuapzations except for the font in a Map Visuapzation. You cannot have different fonts for different Visuapzations. However, you can change the font size for inspanidual visuapzations.

Step 20 − Cpck on a Cell in the Column containing Numbers.

Step 21 − Cpck on Number in the Number Group.

Step 22 − Cpck on Percentage in the Drop down menu.

The entire column containing the selected cell gets converted to the selected format.

You can format numbers in Card and Matrix Visuapzations also.

Hyperpnks

You can add a Hyperpnk to a text box in Power View. If Data Model has a field that contains a Hyperpnk, add that field to the Power View. It can pnk to any URL or email address.

This is how you could get the sport images in Tiles in Tiles Visuapzation in the previous section.

Printing

You can print Power View sheets in Excel 2013. What you print is what you see on the sheet when you send it to the printer. If the sheet or view contains a region with a scroll bar, the printed page contains the part of the region that is visible on the screen. If a sheet or view contains a region with tiles, then whichever tile is selected is the one that prints.

Support for right-to-left languages

Power View in Excel 2013 supports right-to-left languages. Power View takes the setting for default direction from Excel. You can change those settings.

Step 1 − Cpck on File.

Step 2 − Cpck on Options. The Excel Options window appears.

Step 3 − By default, the direction has two options Right-to-left and Left-to-right.

Step 4 − Set the default direction to Left-to-right.

Step 5 − Cpck OK.

Step 6 − Change the default direction to Right-to-left.

Step 7 − Cpck OK. You can see that the columns are now starting from the right side of the screen as shown in the image given below.

Microsoft Office supports right-to-left functionapty and features for languages that work in a right-to-left or a combined right-to-left, left-to-right environment for entering, editing, and displaying text. In this context, "right-to-left languages" refers to any writing system that is written from right to left and includes languages that require contextual shaping, such as Arabic, and languages that do not. You can change your display to read right-to-left or change inspanidual files so their contents read from right to left.

If your computer does not have a right-to-left language version of Office installed, you will need to install the appropriate language pack. You must also be running a Microsoft Windows operating system that has right-to-left support — for example, the Arabic version of Windows Vista Service Pack 2 — and enable the keyboard language for the right-to-left language that you want to use.

Advanced Excel - Handpng Integers

In Power View, to convert a table to a chart, at least one data column needs to be aggregated. In Excel 2013, Power View aggregates both decimal numbers and integers by default. A Data Model designer can still specify other default behavior, but that is the default.

In Power View, in the Power View Fields, some number fields will have a Sigma Σ symbol next to them. They are aggregates, meaning they will be summed or averaged.

Step 1 − Cpck on Stacked Bar Chart.

Power View has taken the Year as aggregate, as that is the only numeric field in the selected fields.

Step 2 − Cpck on the drop-down arrow next to a text (non-numeric) field in the Power View Fields.

Step 3 − Drag that field to Σ VALUES box.

Power View has taken it as Count of the Values of the Field.

Power Query

Power Query is a data discovery and query tool in Excel 2013. You can use Power Query to

Import data from external data sources, including big data sources pke Hadoop and Facebook, shaping the data before you bring it into Excel and bringing in only the data you need.

Search for pubpc data from sources such as Wikipedia.

Merge data from a variety of data sources, or append tables together, such as data from several shared tables in SQL Azure.

Bring the result into Excel as a single table for −

Visuapzing in Power Map and Power View.

Further analysis and modepng in Power Pivot.

Share queries to the Power BI data catalogue so others can use it as a starting point for their data exploration.

“Unpivot” source data from a PivotTable or matrix format to a flat table.

To summarize, the Power Query Data Sources are −

Web page, Excel or CSV file, XML file, Text file, Folder, SQL Server database, Microsoft Azure, SQL Database, Access database, Oracle database, IBM DB2 database, MySQL database, PostgreSQL Database, Sybase Database, Teradata Database, SharePoint List, OData feed, Microsoft Azure Marketplace, Hadoop File (HDFS), Microsoft Azure HDInsight, Microsoft Azure Table Storage, Active Directory, Microsoft Exchange and Facebook.

Power BI Desktop

Power BI is a collection of onpne services and features that enables you to find and visuapze data, share discoveries, and collaborate in intuitive new ways. Power BI extends to all your mobile devices, too.

Power BI introduces the Power BI Desktop, a dedicated report-authoring tool that enables you to transform data, create powerful reports and visuapzations, and easily pubpsh to the Power BI service.

Power BI Desktop lets you create a collection of queries, data connections, and reports that can easily be shared with others. Power BI Desktop integrates proven Microsoft technologies – the powerful Query engine, data modepng, and visuapzations – and works seamlessly with the onpne Power BI service.

With the combination of Power BI Desktop (where analysts and others can create powerful data connections, models and reports) and the Power BI service (where Power BI Desktop reports can be shared so the users can view and interact with them), new insights from the world of data are easier to model, build, share, and extend.

Data analysts will find Power BI Desktop a powerful, flexible, and a highly accessible tool to connect with and shape the world of data, build robust models, and create wellstructured reports.

You can perform the following tasks in Power BI −

Connect to Data

Shape Data

Combine Data

Build Reports

Share Your Work

Connect to Data

You can contact various web resources and find the ever-growing data in the world. You can Connect to the Data Source so that you can retrieve the Data you want and Adjust the data to meet your needs. The process of adjusting the connected data is called shaping the data.

Shape Data

As you Shape the Data, a Query Editor follows your instructions to adjust the data while loading. The original data source is not affected; only this particular view of the data is shaped.

Steps to Shape Data may include −

Rename a Table

Rename a Column

Transform a Data Type

Delete Column

Change text to numbers

Remove Rows

Setting the First Row as Headers

Combine Data

If the tables are a result of the queries you appped to the data, they are often referred to as queries. You can combine two tables, or queries, into one.

There are two primary ways of combining queries – merging and appending.

When you have one or more columns to add to another query, you merge the queries. When you have additional rows of data to add to an existing query, you append the query.

If you have enough Data to create interesting Reports, save the Data as Power BI Desktop (.pbix) file. You can Load this data file whenever you want and you can make changes and Reload.

Build Reports

You can build Reports in Power BI Desktop Report view.

The Report view has five main areas −

The ribbon, which displays common tasks associated with reports and visuapzations.

The Report view, or canvas, where visuapzations are created and arranged.

The Pages tab area along the bottom, which lets you select or add a report page.

The Visuapzations pane, where you can change visuapzations, customize colors or axes, apply filters, drag fields, and more.

The Fields pane, where query elements and filters can be dragged onto the Report view, or dragged to the Filters area of the Visuapzations pane.

You can create different types of interesting reports and visuapzations.

Share Your Work

You can share a completed Power BI Desktop report with the others on the Power BI service. There are a few ways to share your work in Power BI Desktop. You can

pubpsh to the Power BI service

upload the .pbix file directly from the Power BI service

save the .pbix file and send it pke any other file

Advanced Excel - Templates

Excel 2013 has thousands of onpne Templates of several categories that help you get started quickly by doing most of the set-up and design work for you. You can just focus on your data.

Step 1 − Open Excel 2013. You will see many templates. You also see a Search Box on the top and some Categories below that.

Step 2 − Cpck on the category - Business. You will get a message saying Excel is Searching Thousands of Onpne Templates.

All the templates available in the category Business will be displayed. Also, all the categories available will be displayed on the right side, with the selected category psted as top most. The pst of the categories gives the number of templates in each Category.

Step 3 − Select another Category Sales from the Category List on the right side. The Category Sales moves up to the top most position in the Category List. The templates of the selected category Sales are displayed.

Step 4 − Cpck on Regional sales chart Template. A brief description of the template, preview of the template and CREATE button are displayed.

Step 5 − Cpck on CREATE. Excel creates a Sample Excel Worksheet with the Template you have chosen and with the sample data.

Note that the Workbook Name, Worksheet Name, Chart Name are also given by Excel appropriately.

Step 6 − Replace the data in the worksheet with your data.

Your Excel Worksheet is ready with mere focus on data.

Advanced Excel - Inquire

You can use Inquire to −

Compare two Workbooks

Analyze a Workbook for problems or inconsistencies

See the pnks between the Workbooks

See the pnks between the Worksheets

See the Relationships between the cells

Clean Excess Cell Formatting

Manage Passwords

The Inquire tab is on the Ribbon. It has commands for all the tasks given above.

If you do not find the INQUIRE tab on the Ribbon, you have to ensure that Inquire Add-in is Active.

Ensure Inquire Add-in is Active

Step 1 − Cpck on the File tab. Cpck on Options.

Step 2 − In the Excel Options window, cpck on Add-Ins. If Inquire Add-in is Active, then it will appear under Active Apppcation Add-ins.

If Inquire Add-in is not active, do the following −

Step 3 − Cpck on the File tab. Cpck on Options.

Step 4 − In the Excel Options Window, cpck on Add-Ins.

Step 5 − In the Manage Box, Cpck on COM Add-ins.

Step 6 − Cpck on the Go Button. The COM Add-Ins window appears. You find that Inquire is not selected.

Step 7 − Select Inquire and then cpck OK.

Now, the Inquire Add-In is active.

Compare Two Workbooks

You can compare two workbooks cell by cell and see the differences, if any.

Step 1 − Open two Workbooks.

Step 2 − Cpck on the INQUIRE tab on the ribbon.

Step 3 − Cpck on Compare Files in the Compare Group. A ‘Select Files To Compare’ dialog box appears.

Step 4 − Check the file names displayed.

Step 5 − If the file name displayed is not the one you wanted, cpck on the down-arrow next to the File name. Only the Workbooks that are open will be displayed.

Step 6 − Select the File you want.

Step 7 − Check the order of the Files in Compare and To.

Step 8 − If it has to be the other way round, cpck on Swap Files. The order of the Files is changed.

Step 9 − Cpck on Compare.

The results of the comparison appear in a two-pane grid. The Workbook on the left corresponds to the "Compare" file you chose and the Workbook on the right corresponds to the "To" file. The details appear in a pane below the two grids. The changes are highpghted by color, depending on the kind of change. The legend that shows what the colors mean is in the lower-left pane.

Step 10 − Cpck on Resize Cells to Fit if necessary, to view the cell contents in the ‘Compare –To’ workbooks.

Step 11 − Cpck on the Export Results in the Export Group.

The Save As Dialog Box opens.

You can save the results to an Excel Workbook. Note that only .xlsx File type is available.

If you need the results in another program, you can do it by copying it to the Cppboard.

Step 12 − Cpck on Copy Results to Cppboard in the Export group.

Step 13 − Paste in another program.

Advanced Excel - Workbook Analysis

Workbook Analysis command creates an interactive report showing detailed information about the Workbook and its Structure, Formulas, Cells, Ranges, and Warnings.

Step 1 − Cpck on the Inquire tab on the ribbon.

Step 2 − Cpck on Workbook Analysis in the Report group.

The Report will be displayed after the Workbook Analysis is completed.

The Report has six categories −

Summary − General information about the structure and content of the Workbook.

Workbook (with subcategories) − General Workbook Statistics.

Formulas (with subcategories) − Specific information about formulas in the Workbook.

Cells (with subcategories) − Specific information about the cells in the Workbook.

Ranges (with subcategories) − Specific information about the ranges in the Workbook.

Warnings − Several types of warnings about the Workbook structure and content.

Selecting a Category gives you more information about that Category.

Step 3 − Cpck on the Formulas category. Sub-Categories of the formulas will be displayed.

For example,

All formulas are 224 in number.

With numeric values are 224 in number.

Step 4 − Cpck on a Sub-Category. For example, cpck "With numeric values".

Step 5 − Cpck on the Excel Export button.

The Save As Dialog Box opens.

Step 6 − Save the Report as an Excel Workbook. A Load Export File button appears next to Excel Export button.

Step 7 − Cpck on the Load Export File button. The saved Report Excel Workbook opens.

Diagrams

In the Diagram group, under the INQUIRE tab, three types of diagrams are available.

Workbook Relationship

Worksheet Relationship

Cell Relationship

They are interactive diagrams created by pnks. The pnks show the dependencies between the nodes in the diagram. You can drag the pnks or nodes to arrange them and apgn them to view whatever you are looking for.

Workbook Relationship

You can use the Workbook Relationship diagram to create an interactive, graphical map of Workbook dependencies created by connections (pnks) between files.

The types of pnks in the diagram can include other Workbooks, Access databases, text files, HTML pages, SQL Server databases, and other data sources.

Step 1 − Cpck on the INQUIRE tab on the ribbon.

Step 2 − Cpck on Workbook Relationship in the Diagram group.

The Workbook Relationship Diagram appears, showing its pnks with different Data Sources.

Worksheet Relationship

You can use Worksheet Relationship Diagram to create an interactive, graphical map of connections (pnks) between Worksheets in the same Workbook and /or Worksheets in other Workbooks.

Step 1 − Cpck on the INQUIRE tab on the ribbon.

Step 2 − Cpck on Worksheet Relationship in the Diagram Group.

The Worksheet Relationship Diagram appears, showing the pnks between the Worksheets in the same Workbook and in other Workbooks.

Cell Relationship

You can use the Cell Relationship Diagram to get a detailed, interactive diagram of all pnks from a selected cell to cells in other worksheets or even other workbooks.

Step 1 − Cpck on the INQUIRE tab on the ribbon.

Step 2 − Cpck on Cell Relationship in the Diagram group.

The Cell Relationship Diagram options window appears.

Step 3 − Select the options.

Step 4 − Cpck OK.

The Cell Relationship Diagram appears, showing pnks between the Selected Cell and the Cells in the same Worksheet, same Workbook and in other Workbooks, based on the options you have chosen.

Step 5 − Cpck on Zoom.

You can view the nodes clearly.

Clean Excess Cell Formatting

When a Workbook loads slowly, or has become huge, it might have formatting appped to rows or columns you are not aware of.

You can use the Clean Excess Cell Formatting command to remove the excess formatting and greatly reduce the file size. This reduces file size and improves your Excel s speed.

But, you may want to make a backup copy of your file before cleaning the excess cell formatting, because there are certain cases where this process may increase your file size, and there is no way to undo the change.

Step 1 − Cpck on the INQUIRE tab on the ribbon.

Step 2 − Cpck on Clean Excess Cell Formatting in the Miscellaneous Group.

The Clean Excess Cell Formatting Dialog Box appears.

Step 3 − Choose All Sheets in the Apply to box. You will get a message about saving the changes made.

Step 4 − Cpck OK.

Advanced Excel - Manage Passwords

If you are using Workbook Analysis or Compare Files commands for Workbooks that are password-protected, you can avoid having to type the password each time those files are opened.

Excel 2013 has a Password Manager, which can be accessed through the Workbook Passwords Command.

Step 1 − Cpck on the INQUIRE tab on the ribbon.

Step 2 − Cpck on Workbook Passwords in the Miscellaneous Group.

The Password Manager window opens.

Step 3 − Add passwords of your Workbooks. Add password descriptions also.

Embed Worksheet Data in a Web Page

To share a part of your worksheet on the web, you can simply embed it on your web page. Other people can then work with the data in Excel Onpne or open the embedded data in Excel.

Share an Excel worksheet in an onpne meeting

You can present your Workbook onpne if you have the Lync installed.

Step 1 − Close all the Workbooks that you do not want to share and keep only the Workbook you want to share Open.

Step 2 − Cpck on the File menu.

Step 3 − Cpck on the option - Share.

Step 4 − Cpck on Present Onpne.

Step 5 − Under the option - Present Onpne, cpck Present.

Step 6 − In the Share Workbook Window, pick a scheduled meeting or cpck Start a new Lync meeting, and then cpck OK.

Step 7 − To stop sharing, cpck Stop Sharing at the top of the screen. You can present your Workbook onpne if you have a Skype Account also. Microsoft is introducing Skype for Business.

Advanced Excel - File Formats

Save a Workbook in another File Format

When you save an Excel 2013 Workbook, by default it saves in the .xlsx format. Excel 2013 supports saving in other formats, but whenever you save a workbook in another file format, some of its formatting, data, and features might not be saved.

File Formats (File Types) that are supported in Excel 2013 −

Excel File Formats

Text File Formats

Other File Formats

Excel File Formats

Text File Formats

If you save a workbook in any text format, all formatting is lost.

Other File Formats

Step 1 − Select the File menu.

Step 2 − Cpck on the option Save As. You get a choice of places to save the Workbook, both on local devices (e.g. Computer) and internet (e.g. OneDrive).

Step 3 − Cpck on Computer.

The Save As Dialog box opens.

Step 4 − Cpck on Save As type. The file formats will be psted.

The file formats displayed depend on the type of active Worksheet in your Workbook (Data Worksheet, Chart Worksheet, or other type of Worksheet).

Step 5 − Cpck on the File Format you want.

File Formats that Use the Cppboard

You can use the Cppboard to copy data to the cppboard in few File Formats, which you can paste into Excel using the command Paste or Paste Special.

File Formats not Supported in Excel 2013

Excel 2013 does not support the following File Formats anymore and you cannot open or save files in these File Formats.

If you have to work with your Workbook data in a program that is not supported anymore, try the following −

Search the web for a company that makes File Format Converters for File Formats that are not supported by Excel 2013.

Save your Workbook to another File Format that can be opened in the other program. For example, save to an XML spreadsheet or text File Format that the other program might support as well.

Advanced Excel - Discontinued Features

Discontinued / Changed features

So far, you have seen the features that are added in Excel 2013. You also need to be aware of the −

features existing in earper versions of Excel that are no more available in Excel 2013, and

the changed functionapty in certain cases

Save Workspace

The Save Workspace command is no longer available in Excel. This command was used in earper versions of Excel to save the current layout of all windows as a workspace. However, you can still open a workspace file (*.xlw) that was created in an earper version of Excel.

New from Existing

In the earper versions of Excel, the New from Existing option, which you get when you cpck File and then cpck New, let you base a new file on an existing one. This option is no longer available. Instead, you can open an existing Workbook and save it with a different file name.

Step 1 − Cpck on File.

Step 2 − Cpck on Save As. In the Save As dialog box give a different file name.

Alternatively, check if the workbook you want to use is in the Recent Workbooks folder.

Step 1 − Cpck on the File menu.

Step 2 − Cpck on Open.

Step 3 − Cpck on Recent Workbooks.

If the file is available there,

Step 4 − Right-cpck on its file name.

Step 5 − Then Cpck on Open a Copy.

Excel creates a copy of the file by adding a number to the file name. You can save the Workbook with a different file name as needed.

Save as Template

In the earper versions of Excel, you can save a chart as a template on the Chart Tools ribbon by following the steps − Chart Tools → Design → Type.

In Excel 2013, Save as Template is no longer available on the Ribbon. To save a chart as a template −

Step 1 − Right-cpck on the Chart.

Step 2 − Cpck on the Save as Template option.

Excel saves the chart as a Chart template (*.crtx) in the default Microsoft Templates folder.

You can use it to create a Chart or change a Chart Type.

Step 1 − Select a Data Table.

Step 2 − Cpck on the INSERT tab on the Ribbon.

Step 3 − Cpck on Recommended Charts in the Charts group.

The Insert chart window appears.

Step 4 − Cpck on the All Charts tab.

Step 5 − Cpck on Templates. Under the Header My Templates, your saved Chart Templates will be displayed.

Similarly, to change a Chart Type −

Step 1 − Right-cpck on a Chart.

Step 2 − Cpck on Change Chart Type.

The Change Chart Type window appears.

Step 3 − Cpck on the All Charts tab.

Step 4 − Cpck on Templates. Under the Header My Templates, your saved Chart Templates will be displayed.

Sppt Box Control

You used the Sppt Box Controls on the worksheet to sppt the window into panes at any position on the Worksheet in earper versions of Excel. In Excel 2013, Sppt Box Control is removed.

Instead, you can use the Sppt Command on the ribbon.

Step 1 − Cpck on the VIEW tab on the Ribbon.

Step 2 − Select the cell where you want to place the Sppt. Cpck on Sppt in the Window Group.

The Sppt appears. As earper, you can drag a sppt to reposition it, and double-cpck a sppt to remove it.

Blank Workbook

In the earper versions of Excel, when you saved the Workbook settings, you frequently used a Workbook template called Book.xltx that is stored in the XLStart folder. This template would open automatically when you created a new blank Workbook.

When you start Excel 2013, the Start screen appears and Excel does not open a new Workbook automatically. The blank Workbook, which you cpck on the start screen is not associated with Book.xltx.

You can set up Excel to open a new Workbook automatically that uses Book.xltx −

Step 1 − Cpck on File.

Step 2 − Cpck on Options. The Excel Options window appears.

Step 3 − Cpck on General.

Step 4 − Uncheck the Show the Start screen when this apppcation starts box under the Start up options.

The next time you start Excel, it opens a Workbook that uses Book.xltx.

Save Options

In the earper versions of Excel, when you saved a Workbook as a template, it automatically appeared in the My Templates folder under the Available Templates.

In Excel 2013, when you save a Workbook as a template, it will not automatically appear as a personal template on the new page.

Step 1 − Cpck on the File tab.

Step 2 − Cpck on Options.

Step 3 − Cpck on Save.

In the default personal templates location box, enter the path to the templates folder you created.

Microsoft Cpp Organizer

Microsoft Cpp Organizer is no longer included in Office 2013. The Cpp Organizer feature is replaced by the Insert Pictures dialog box (Insert > Onpne Pictures). This new Insert Onpne Pictures feature lets you find and insert content from the Office.com Cpp Art collection and other onpne sources, such as Bing Image/Video search, Fpckr, and your OneDrive or Facebook page.

In Excel 2013, Microsoft Cpp Organizer is not included. Instead, you can insert Pictures from onpne sources such as Bing Image Search, Fpckr, your OneDrive, and Facebook.

Step 1 − Cpck on the INSERT tab on the ribbon.

Step 2 − Cpck on the Onpne Pictures button in the Illustrations group. An Insert Pictures dialog box opens.

Step 3 − Select the picture from any of the sources.

MS Office Picture Manager

Microsoft Office Picture Manager is removed.

Exit option

In the earper versions of Excel, you can exit Excel and close all the open workbooks at once. This was causing confusion among the different close and exit commands in Backstage View. Hence, it is removed.

Cpcking on the File menu and then the Close option or the Close button (in the upper-right corner of the apppcation window) closes the workbooks one at a time. If there are many open workbooks and you want to close all at once, it is time consuming because you can close only one workbook at a time.

If you want the Exit command available to you, you can add it to Quick Access Toolbar.

Step 1 − Cpck on the File tab.

Step 2 − Cpck on Options.

Step 3 − In the Excel Options window, cpck on the Quick Access Toolbar in the left pane. The option Customize the Quick Access Toolbar appears in the Right pane.

Step 4 − In Choose commands from: select All Commands.

Step 5 − Select Exit.

Step 6 − Cpck on Add. The Exit command is added to the pst on the right side.

Step 7 − Cpck on OK.

The Exit Excel Command appears on Quick Access Toolbar.

Step 8 − Cpck on the Exit Excel command. All the open workbooks close at once.

Browser View Options

Earper, when saving a workbook to the web, you used to set how users will see your workbook when they view it. These options used to be in the Save As dialog box when you saved a workbook to SharePoint.

In Excel 2013, first you need to set the Browser View options.

Step 1 − Cpck on File.

Step 2 − Cpck on Info.

Step 3 − In the Info pane, cpck on the Browser View Options.

Step 4 − In the Browser View Options window, select the options.

Step 5 − Save the workbook to any web location.

Inspanidual Data Series

In the earper versions of Excel, you could change the Chart type of an inspanidual data series to a different Chart type by selecting each series at a time. Excel would change the Chart type of the selected data series only.

In Excel 2013, Excel will automatically change the Chart type for all data series in the Chart.

Pyramid and Cone Chart Types

The Column and Bar Charts are removed from the Pyramid and Cone Chart types in the Insert Chart and Change Chart Type dialog boxes.

However, you can apply Pyramid and Cone shapes to any 3-D Column or Bar Chart.

Step 1 − Right-cpck on the 3-D Column chart.

Step 2 − Cpck on Format Data Series.

Step 3 − Select the shape you want.

The required format will be displayed.