data analysis and security 11 session version 1.0 © 2011 aptech limited

Data Analysis and Security11Session

Version 1.0 © 2011 Aptech Limited.

Version 1.0 © 2011 Aptech Limited. 2

At the end of this session, students will be able to: Describe sorting and filtering of dataExplain the methods to present the data graphically using chartsExplain the steps to create and format the chartsExplain the methods of securing and protecting a Workbook

Objectives

Data Analysis and Security / Session 11


Introduction

Excel allows users to organize data entries by sorting and filtering

Sorting: Reorganizes rows in the table based on the contents of a particular column Sorts the data numerically or alphabetically Help users to understand and identify the required data

Filtering: Used for viewing only certain items When users apply filter to a column, they can decide what they want to see

In addition, the users can use charts to display the graphical representation of the data

Users can also secure their workbooks when the data is highly confidential



Sorting Data

Means arranging data entries based on the specified conditions

Applied on data using pre-defined sorting methods, or by selecting the range of cells and apply the rules using Custom Sort



Using basic sorting, users can sort the data in the following ways:

Sort A to Z or Sort Smallest to Largest

Sort Z to A or Sort Largest to Smallest

Put Selected Cell Color On Top

Put Selected Font Color On Top

Put Selected Cell Icon On Top

5

Basic Sorting [1-2]



Basic Sorting [2-2]

Sort Sub-menu


To apply basic sorting, perform the following steps:

Open Microsoft Excel 2010 Assign the heading as Name in cell

D5 Type names of people from cell D6

to D10 Assign the heading as Age in cell E5 Type age in cells from E6 to E10 Select the cells from D5 to E10 Right-click the highlighted cells Select Sort from the context menu Select Sort > Sort A to Z Excel rearranges data entries in the

column D along with the corresponding data in the column E in ascending order


Custom Sorting [1-2]

Enables a user to sort data as per their requirements

Allows a user to add multiple columns for arrangement

Users can decide which column needs to be sorted first and in what order



Open Microsoft Excel 2010 Assign the heading as Name in D5 Type names of people from cell D6

to D10 Assign the heading as Age in cell

E5 Type age in cells from E6 to E10 Select the cells from D5 to E10 Right-click the highlighted cells Select Sort > Custom Sort Select Name Click Add Level Select Age Click OK Excel first sorts the entire range of

data alphabetically based on Name column and further on Age column


Sort Dialog Box

To apply custom sorting, perform the following steps:

Sort Dialog Box After Selection of Rows

Custom Sorting [2-2]


Filtering Data

Displaying only those row that meet the specific criteria and hiding non-specific rows in the table

Allows users to filter data entries manually or by applying rules



Basic Filtering [1-2]

Involves displaying of data entries manually

Users can decide what data they want to display in the spreadsheet

Users can filter data in the following ways: Filter by Selected Cell’s Value Filter by Selected Cell’s Color Filter by Selected Cell’s Font Color Filter by Selected Cell’s Icon



To apply basic filter, perform the following steps: Open Microsoft Excel 2010 Assign the heading as Name in D5 Type names of people from cell D6

to D10 Assign the heading as Age in cell E5 Type age in cells from E6 to E10 Select the cells from D5 to E10 Right-click the highlighted cells Select Filter from the context menu Select Filter > Filter by Selected Cell’s

Value Click the small arrow next to column

headings and select the data entries to be displayed on the spreadsheet


Filter Options

Basic Filtering [2-2]


Users can utilize this option to search data after applying a filter Helps to search for a specific data entry when there are numerous data entries To use filtering by search, perform the following steps: Open Microsoft Excel 2010 Assign the heading as Name in D5 Type names of people from cell D6 to D10 Assign the heading as Age in cell E5 Type age in cells from E6 to E10 Select the cells from D5 to E10 Right-click highlighted cells Select Filter > Filter by Selected Cell’s Value Click icon in Name heading Type the data to search in the Search box Click OK

12Data Analysis and Security / Session 11

Filtering using Search


To apply advanced date filter, perform the following steps: Open Microsoft Excel 2010 Assign the heading as Date in C5 Type dates from cell C6 to C10 Assign the heading as Name in D5 Type names of people from cell D6

to D10 Assign the heading as Age in cell E5 Type age in cells from E6 to E10 Select the cells from C5 to E10 Right-click highlighted cells Select Filter > Filter by Selected

Cell’s Value Click icon in Date heading Select Date Filters > Custom Filter Set the required criteria to apply Click OK


Using Date Filters

Custom AutoFilter Dialog Box for Date


To apply advanced text filter, perform the following steps: Open Microsoft Excel 2010 Assign the heading as Date in C5 Type dates from cell C6 to C10 Assign heading as Name in D5 Type names of people from cell D6

to D10 Assign the heading as Age in cell E5 Type age in cells from E6 to E10 Select the cells from C5 to E10 Right-click highlighted cells Select Filter > Filter by Selected

Cell’s Value Click icon in Name heading Select Text Filters > Custom Filter Select the required rules Click OK


Custom AutoFilter Dialog Box for Text

Using Text Filters


To apply advanced number filter, perform the following steps: Open Microsoft Excel 2010 Assign the heading as Date in C5 Type dates from cell C6 to C10 Assign heading as Name in D5 Type names of people from cell D6 to

D10 Assign the heading as Age in cell E5 Type age in cells from E6 to E10 Select the cells from C5 to E10 Right-click highlighted cells Select Filter > Filter by Selected Cell’s

Value Click icon in Age heading Select Number Filters > Custom Filter Select the required rules Click OK


Custom AutoFilter Dialog Box for Numbers

Using Number Filters


Provide the visual aids to the data present in tables Make the data entries more comprehensible and clear The table lists different types of Charts provided in Microsoft Excel


Tab DescriptionColumn Compares the values across categoriesLine Displays the graph over a periodPie Displays the contribution of each value to the totalBar Compares multiple valuesArea Highlights the differences between numerous sets of data over a periodScatter Compares pair of values and are also known as XY chartsStock Displays trends of the stock marketSurface Shows trends in values across two dimensions in a continuous curveDoughnut Displays contribution of each value to a series like Pie chart but it displays

multiple seriesBubble Resembles a scatter chart but it compares sets of three values instead of twoRadar Displays values relative to a centre point

Working with Charts


To create a chart in Excel, perform the following steps: Open Microsoft Excel 2010 Assign the heading as Name in D5 Type the names of the people from

cell D6 to D10 Assign Math as heading in E5 Type two-digit numbers from cell

E6 to E10 Assign Art as heading in F5 Type two-digit numbers from cell

F6 to F10 Select the cells from D5 to F10 Click Column from the Charts

group in the Insert tab


Column Sub-Menu

Creating a Chart [1-2]


Select the first chart from 2-D Column


Chart in Excel

Creating a Chart [2-2]


When the chart is selected, following tabs appears in Chart Tools group of the ribbon: Design Layout Format

These tabs lets the user to: Change overall chart arrangement Modify the appearance Layout and style of the chart


Modifying the Chart Layout and Chart Style [1-3]


To change the chart type, layouts, and style, perform the following steps: Select the chart Click Change Chart Type from

Type group of the Design tab Select the required chart type Click OK Select the chart Click the Design tab Click icon from the Charts

Layouts group Select the required layout


Changing Chart Type

Chart Layouts Gallery



Select the chart Click the Design tab Click icon from the Charts Styles group Select the required style to apply


Chart Styles Gallery



Helps the user to convey the details of horizontal and vertical axis, assign a title to the chart, display data labels, and so forth

The table lists different Chart Labels available in Excel


Specifying Chart Labels [1-2]

Chart Labels

Label DescriptionChart Title Specifies a title to the chart generated and enables to select its positionAxis Titles Provides name to horizontal and vertical axis of the chart and enables to

select its positionLegend States what each of the data series stands for in the chartData Labels Displays details of each of the data series on the chart and enables to

select the position of the valuesData Table Provide details of each of the data series below the chart


To specify chart labels, perform the following steps: Select the chart Click Chart Title from the Labels group of the Layout tab Select the option to display the chart title Double-click the Chart Title name and assign a new name Similarly, change the Axis Titles, Legend, Data Labels, and Data Table from the

Labels group


Specifying Chart Labels [2-2]


Involves changing the outline, fill color, and shape effects to the chart

The figure shows the Shape Styles group

Allows users to change the text fill, text outline, and text effects The figure displays the WordArt Styles group


Formatting a Chart [1-2]

Shape Styles Group

WordArt Styles Group


To format a chart, perform the following steps: Select the chart Click Shape Fill from the Shape Styles group of the Format tab Select the required fill color Similarly, select the options from Shape Outline, and Shape Effects from the

Shape Styles group Click Text Fill from the WordArt Styles group of the Format tab and select the

required text fill color Similarly, select the options from Text Outline, and Text Effects from the

WordArt Styles group


Formatting a Chart [2-2]


Are Charts that are displayed in a single cell based on the range of numeric data selected to show the trend of value

Usually compares the data entries from different columns and display the graph in the cell

The figure displays the Sparklines group


Working with Sparklines [1-2]

Sparklines Group


To use the Sparklines, perform the following steps: Open Microsoft Excel 2010 Assign the heading as Name in A1 Type names of people from cell A2 to

A8 Assign the heading as Math in cell B1 Type numbers from cell B2 to B8 Assign the heading as Language in C1 Type numbers from cell C2 to C8 Assign the heading as Art in cell D1 Type numbers from cell D2 to D8 Select the cells from B2 to D8 Click Line from the Sparklines group

of the Insert tab Type E2:E8 in the Location Range box

to place the Sparklines in the Excel sheet

Click OK


Create Sparklines Dialog Box

Example of Sparklines in Excel

Working with Sparklines [2-2]


Protects the workbook by assigning a password to the workbook or a worksheet

Users cannot view the contents or edit the file until they provide the password

Excel allows users to remove a password from a password-protected file

However, if the user forgets the password after protecting it, Excel cannot recover the contents of the file or provide the details of the password


Securing and Protecting a Workbook


Protecting the workbook by providing the password

User need to enter the password to re-open the protected file

Even the worksheets will not open in read mode without the password

To encrypt a workbook, perform the following steps: Open Microsoft Excel 2010 Click the File tab Click Info Click Protect Workbook


Encrypting a Workbook [1-2]

Information Pane

Protect Workbook Sub-Menu


Select Encrypt with Password Type the password in the Password box Click OK The workbook is protected

To decrypt a workbook, perform the following steps: Open an encrypted document in Microsoft

Excel 2010 Enter the password Click the File tab Click Info Click Protect Workbook Select Encrypt with Password Clear the password from the Password box Click OK


Encrypt Document Dialog Box

Encrypting a Workbook [2-2]


To protect the workbook structure or windows, perform the following steps: Open a file in Microsoft Excel 2010 Click Protect Workbook from the

Changes group of the Review tab Type the password in the Password

(optional) box Click OK Re-enter the password in the Reenter

password to proceed box Click OK This will prevent the users from

adding, deleting, or displaying hidden worksheets


Protect Workbook Dialog Box

Protecting a Workbook


To unprotect the workbook, perform the following steps: Open a protected workbook in Microsoft Excel 2010 Click Protect Workbook from the Changes group of the Review tab Type the password that was used to protect the sheet Click OK


Unprotect Workbook Dialog Box

Unprotecting a Workbook


To protect the worksheet, perform the following steps: Open Microsoft Excel 2010 Save the file Click Protect Sheet from the

Changes group of the Review tab Type the password in the

Password to unprotect sheet box Click OK Re-enter the password in the

Reenter password to proceed box

Click OK


Protect Sheet Dialog Box

Confirm Password Dialog Box

Protecting a Worksheet


To unprotect a worksheet, perform the following steps: Open the protected worksheet in Microsoft Excel 2010 Click Unprotect Sheet from the Changes group of the Review tab Type the password Click OK to unprotect the sheet


Unprotect Sheet Dialog Box

UnProtecting a Worksheet


Sorting arranges data in ascending or descending order. Filtering data means displaying only those rows that meet the specific criteria

and hiding non-specific rows in the table. Excel allows users to filter data entries manually or by applying rules. Charts provide the visual aids to the data present in tables. Sparklines compare the data entries from different column and display the

graph in the cell. Protecting the workbook helps users to keep the contents safe. When the sheet is protected, the workbook will open in reading mode, but to

make changes in the file, the user must enter the password.

Summary


data analysis and security 11 session version 1.0 © 2011 aptech limited

Documents