Advanced Excel - Quick Guide

Advanced Excel - Quick Guide - The Charts Group on the Ribbon in MS Excel 2013 looks as follows −

1. Advanced Excel - Quick Guide

Advanced Excel - Chart Recommendations

Change in Charts Group

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

Change in Charts Group

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

Insert Column Chart

When you click on the Insert Column chart, types of 2-D Column Charts, and 3-D Column Charts are displayed. You can also see the option of More Column Charts.

Step 3 − If you are sure of which chart you have to use, you can choose a Chart and proceed.

If you find that the one you pick is not working well for your data, the new Recommended Charts command on the Insert tab helps you to create a chart quickly that is just right for your data.

Recommended Charts

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 − Click on Recommended Charts.

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

All Recommended Charts

Step 3 − As you browse through the Recommended Charts, you will see the preview on the right side.

Step 4 − If you find the chart you like, click on it.

Step 5 − Click on the OK button. If you do not see a chart you like, click on All Charts to see all the available chart types.

Step 6 − The chart will be displayed in your worksheet.

Step 7 − Give a Title to the chart.

Charts Title

Fine Tune Charts Quickly

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

  • Chart Elements
  • Chart Styles and Colors, and
  • Chart Filters

You can use these buttons −

  • To add chart elements like axis titles or data labels
  • To customize the look of the chart, or
  • To change the data that’s shown in the chart

Fine Tune Charts

Select / De-select Chart Elements

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

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

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.

Select Chart Elements

Format Style

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

Step 2 − Click on the second button Chart Styles. A small window opens with different options of STYLE and COLOR as shown in the image given below.

Step 3 − Click on STYLE. Different options of Style will be displayed.

Chart Styles

Step 4 − Scroll down the gallery. The live preview will show you how your chart data will look with the currently selected style.

Step 5 − Choose the Style option you want. The Chart will be displayed with the selected Style as shown in the image given below.

Selected Chart Styles

Format Color

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

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

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

Color Schemes

Step 4 − Scroll down the options. The live preview will show you how your chart data will look with the currently selected color scheme.

Step 5 − Pick the color scheme you want. Your Chart will be displayed with the selected Style and Color scheme as shown in the image given below.

Selected Color Schemes

You can change color schemes from Page Layout Tab also.

Step 1 − Click the tab Page Layout.

Step 2 − Click on the Colors button.

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

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 − Click on the Chart. Three Buttons will appear at the upper-right corner of the chart.

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

Chart Filters

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

Values

Step 4 − Select / De-select the options given under Series and Categories. The chart changes dynamically.

Step 5 − After, you decide on the final Series and Categories, click on Apply. You can see that the chart is displayed with the selected data.

Apply Series Categories

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

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

Step 3 − Right-click the chart element.

Step 4 − Click Format <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-click the chart axis.

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

Format Axis

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

Task Pane Options

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

Pane More Options

Step 4 − Click on Axis Options.

Axis Options

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

Step 6 − Select the Chart Title.

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

Combo Charts

The following steps will show how to make a combo chart.

Step 1 − Select the Data.

Step 2 − Click on Combo Charts. As you scroll on the available Combo Charts, you will see the live preview of the chart. In addition, Excel displays guidance on the usage of that particular type of Combo Chart as shown in the image given below.

Guidance Of Combo Charts

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

Display Combo Charts

Advanced Excel - Chart Design

Ribbon of Chart Tools

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

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

Ribbon of Chart Tools

Let us understand the functions of the DESIGN tab.

Step 1 − Click on the chart.

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

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

Ribbon Add Chart Element

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 − Click on Quick Layout. Different possible layouts will be displayed.

Quick Layout

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.

Chart Layout Changes

Step 3 − Click on the layout you like. 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.

Change Colors

Chart Styles

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

Chart Styles Option

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.

Switch Row / Column

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

Switch Row / Column

Select Data

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

Select Data

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

Step 2 − Select the Chart Data Range.

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.

Chart Filters VALUES

Change Chart Type

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

Different Chart Type

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

Diffrent Chart Type

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.

Move Chart

Click on Move Chart. The Move Chart window appears.

Move Chart Appear

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 earlier 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 − Click on the Insert Scatter or the Bubble Chart.

Bubble Chart

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

Bubble Chart Appear

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

3-D Bubble Chart

Step 4 − Click on the chart and then click 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.

Select Data Labels

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

Data Labels Appear

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

Format Data Label Options

The Format Data Label Task Pane appears.

Format Data Labels Appear

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, click on Clone Current Label.

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, like changing the Fill color of the Data Label for emphasis.

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

Step 2 − Click Fill → Solid Fill. Choose the Color you want and then make the changes.

Step 3 − Click 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, click on Clone Current Label. All the other data labels will acquire the same effect.

Data Label Effects

Shape of a Data Label

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

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

Step 2 − Click on Change Data Label Shapes.

Change Data Label Shapes

Step 3 − Choose the shape you want.

Choose Data Label Shapes

Resize a Data Label

Step 1 − Click on the data label.

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

Resize Data Label

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-click on a data label. A list of options will appear.

Add Field Data Label

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

Insert Data Label Field

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

Data Label Reference

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

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

Resize Data Label Fields

Advanced Excel - Leader Lines

A Leader Line is a line 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 earlier versions of Excel, only the pie charts had this functionality. Now, all the chart types with data label have this feature.

Add a Leader Line

Step 1 − Click on the data label.

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

Four-Headed Line

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

Adjusts Leader Line

Format Leader Lines

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

Format Leader Line

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

Format Leader Line Pane Appear

Step 3 − Click on the icon Fill & Line.

Step 4 − Click on LINE.

Step 5 − Make the changes that you want. The leader lines 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 functionality. If you know the category of the function that you are looking for, you can click that category.

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

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

Function Categories

Step 3 − Click 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.

Function Categories

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 hyperbolic cosine of a complex number.

  • IMCOT − Returns the cotangent of a complex number.

  • IMCSC − Returns the cosecant of a complex number.

  • IMCSCH − Returns the hyperbolic cosecant of a complex number.

  • IMSEC − Returns the secant of a complex number.

  • IMSECH − Returns the hyperbolic secant of a complex number.

  • IMSIN − Returns the sine of a complex number.

  • IMSINH − Returns the hyperbolic 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 hyperbolic 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 hyperbolic cotangent of a number.

  • CSC − Returns the cosecant of an angle.

  • CSCH − Returns the hyperbolic 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 hyperbolic secant of an angle.

Statistical Functions

  • BINOM.DIST.RANGE − Returns the probability 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) English 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.

User Define Function in Add-ins

  • CALL − Calls a procedure in a dynamic link library 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 link library (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 highlight the parts of your data by adding things like 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.

Sparklines

Sparklines are like 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.

Analysis Data

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

Analysis Data Appear

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

Quick Analysis Toolbar

Conditional Formatting

Conditional formatting uses the rules to highlight 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 − Click on the FORMATTING button.

Step 2 − Click on Data Bars.

Formatting Data Bar

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

Step 3 − Click on Color Scale.

Formatting Color Scale

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

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

Formatting Icon Set

Step 5 − Click on the option - Greater than.

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.

Option-Greater Than Appear

Step 6 − Click on Top 10%.

Conditional Formating

Values that are in top 10% will be colored.

Step 7 − Click on Clear Formatting.

Clear Formatting

Whatever formatting is applied 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 visualize your Data.

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

Recommended Charts

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

Preview Recommended Charts

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

More Button

More Recommended Charts are displayed.

More Recommended Charts

Totals

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

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

Additional Options

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

Sum icon

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

Average Of Columns

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

Count Number Of Values in Columns

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

Percent Of Columns

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

Running Total Of Columns

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

Sum Of Rows

Step 8 − Click on the symbol Dispaly More Button. This displays more options to the right.

Display Button

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

Average Of Rows

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

Count Of Rows

Step 11 − Click on %Total.

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

%Total Of Rows

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

Running Total Of Each Rows

Tables

Tables help you sort, filter and summarize the data.

Tables

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

Step 1 − Click on TABLES.

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

Tables Icon

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

Tables Tools

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

Create Pivot Tables

Sparklines

SPARKLINES are like 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 − Click on SPARKLINES. The chart options displayed are based on the data and may vary.

Sparklines

Step 2 − Click on Line. A line chart for each row is displayed.

Sparklines Line Chart

Step 3 − Click on the Column icon.

Sparklines Column Chart

A line 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 − Click on the DATA tab.

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

Sort Data

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

Sort By

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.

Sort On

Step 5 − Click on the option Cell Color.

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

Define Order

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.

Under Order

Advanced Excel - Slicers

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

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

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

Table Tools

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

Step 3 − Click on Insert Slicer. A Insert Slicers dialog box appears.

Step 4 − Check the boxes for which you want the slicers. Click on Genre.

Step 5 − Click OK.

Insert Slicer

The slicer appears. Slicer tools appear on the ribbon. Clicking the OPTIONS button, provides various Slicer Options.

Slicer Tool Options

Step 6 − In the slicer, click 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.

Slicer Selected Items

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.

Column Containing Full Names

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

Enter First Names

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

List of Suggested Names

Step 4 − Press Enter to accept the list.

Accept List

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

Enter Last Name

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

Data Fileld With Flash-fil

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.

Enter Middle Name

Flash Fill works with any data you need to split 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.

Recommended PivotTables

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 − Click on the Table.

Step 4 − Click on Insert tab.

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

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

PivotTable Layout

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

PivotTable Created

Create a PivotTable to analyze external data

Create a PivotTable by using an existing external data connection.

Step 1 − Click any cell in the Table.

Step 2 − Click on the Insert tab.

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

PivotTable Created Appears

Step

What's Your Reaction?

like
0
dislike
0
love
0
funny
0
angry
0
sad
0
wow
0