Advanced Excel - Instant Data Analysis


Advertisements


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.



Advertisements
E-Books Store