Exploring Excel

Charts

Charts is one of the easiest and effective way to project your important data visually so that an end user can quickly understand what your data is all about and what it depicts. Charts are basic components of reporting. Categories: -Are plotted on the horizontal axis Data Series: A data series is a set of related data points. -Are plotted on the vertical axis In the context of charts, a sat point represents a mark on a chart.

INDEX

Syntax: INDEX(array, row_num, [column_num]) -Returns the value of an element in a table or an array, selected by the row_num and column_num. row_num & column_num are relative row number and column number in array. -If array contains only one row or column, the corresponding row_num or column_num argument is optional.

Bivariate Analysis

When a pair of variables is considered together, they are referred as bivariate data. For bivariate data, you often want to know the relationship between the two variables. Some important values studied for bivariate data are: -Causality relationship between the 2 variables ~Intercept, Slope, R^2 -Statistical information between the 2 variables ~Covariance, Correlation EXCEL Function: Slope (known_y, known_x) Intercept (known_y, known_x)

Univariate Analysis

Analysis that involves a single variable is called univariate analysis. 3 Most Commonly used types of characteristics with the statistical values involved one ache characteristic of this analysis are: 1.) Central Tendency: Statistical Values: Mean, Median, Mode 2.) Dispersion: Statistical Values: Standard Deviation, Variance, Coefficient of Variation 3.) Distribution (Detail discussion on distributions is NOT under scope of this course)

Conditional Computation:

Is a technique where you carry out logical and mathematical operations based on specific conditions. Some of the conditional computations can be carried out in Excel with the help of functions like: -COUNTIF, COUNTIFS -AVERGAEIF, AVERAGEIFS -SUMIF, SUMIFS

Data Transformation:

The visual transformation of data helps the user understand, compare and focus on the selected part of data. In excel, some techniques to project data visually are: Highlighting data with below mentioned formatting techniques: -Top-Bottom rules with simple background fill -Data Bars -Icon Sets Creating Charts: -Column Chart -Line Chart -Pie Chart Creating Pivot table & Pivot chart

Roadmap

6 Sections: -Conditional Computation: Compute conditional aggregations by using functions -Data Lookup: Perform data lookups with variants of Lookup functions -Data Filters: Restrict display of records by implementing data filters -Data Transformation: Transform data with formats, charts and Pivot table -Data Protection: Protect and validate data by applying security and validation rules -Statistics EXCEL 2016

Advanced Filters

Advanced Filter allows us to filter data according to multiple criteria concurrently. However, you need to follow few rules. Some of the important ones are: 1.) Criteria range must be above data list range. 2.) There must be at least 1 empty row separating them. 3.) Both must have at least one matching field headers. 4.) Same field headers need not reside directly above each other, since matching is done by field header name. Multiple field headers selected int he criteria range implies "AND" condition, while selecting more than one row of criteria values imply "OR" condition. More complex filtering criterion like 'names beginning with H OR salary greater than 1000', 'second highest salary', 'salaries above average salary' etc. cannot be applied using these custom filters. Advance filters are used to implement such complex filters.

Conditional Formatting

Conditional formatting helps the user understand, compare and focus on selected data in a quick glance. Conditional formatting is used to highlight and project data that satisfy certain business conditions by applying different formats configured in various rules. Ex. Highlight top three unsold products i.e. three products having highest stocks in inventory with ed color. Step 1: Select data in the column Step 2: 1.) got to HOME tab 2.) Click on Conditional Formatting option Step 3: In Conditional Formatting: 1.) Select Top/Bottom Rules option 2.) Click on Top 10 Items Step 4: A dialogue box appears, in this: 1.) Change the value 10 to 3 in Top 10 Items 2.) Select formatting option as Green Fill with Dark Green Text from the drop down to format cells. Step 5: Click OK to observe the output. You can see that products with top 3 stocks are highlighted with Green Fill with Dark Green text. A conditional formatting rule is created and applied to a range. What happens when any cell within the range is modified? ANSWER: The rule automatically gets applied to the new modified value.

Data Lookup:

Data lookup is a technique where the data is to be matched in given data set. In Excel, data lookup can be carried out using functions like: -VLOOKUP -HLOOKUP -MATCH -INDEX

Data Validation

Data validation is a process to ensure that data entered is valid and is implemented with the help of validation rules. In Excel data validation can be used in 2 ways. 1.) Restricting user from invalid data entires: Validation rules are configured for the selected cells. When a user enters a value in such a cell then he gets an error/warning/ information message based on the configurations if the value doesn't match the rule. 2.) Highlighting invalid data if it's already entered: Validation rules are configured for already entered data. Invalid entries will be encircled but no error message displayed. Error alert can be of 3 types: 1.) Stop Alert: This alert type doesn't allow end user to proceed until the data entered is valid. Suitable for high critical data. 2.) Warning Alert: This alert warns the user of invalid data and gives him a chance to either replace with a valid value or to continue with invalid one. Suitable for medium critical value. 3.) Information Alert: This type of alert is only to keep the user informed of invalid entry but doesn't really expect him to act upon it. Most suitable for very low critical data.

Removing Filters

In order to remove a filter from any particular column, select column header and clock on Clear filter option available for the column menu. 1.) Click the filter drop-down on column header 2.) Select 'Clear filter from Name' option from the drop-down REMOVE ALL FILTERS AT ONE GO: To remove filters from all columns. 1.) Click SORT & FILTER drop-down 2.) Select CLEAR option from the drop-down menu.

Data Filters:

Is a technique to display data that meet certain criteria and hide the rest. Data filtering is useful to restrict unwanted data, instead of working on entire data set you would like to do analysis on required data set that makes analysis easier, faster and accurate. Some of the techniques used in Excel are: -Custom filters with the help of following: ~Range Filter ~Pattern Filter ~Top N Filter -Advance Filters

Data Protection:

Is a technique to safeguard the important data by applying various kinds of securities and validation checks. Some of the ways to apply data protection in Excel are: - Applying cell level security - Protecting worksheet - Managing access permissions for multiple users - Tracking changes made by multiple users - Preventing invalide entries - Identifying invalid entries in existing data

Statistics:

Is the collection, analysis and interpretation of data, along with presentation of results relying on data. In this section you will learn to gather statistics in Excel by: -Computing various kinds of statistical values for univariate data ~ Mean, Mode, Median ~ Standard Deviation, Variance -Computing various kinds of statistical values for bivariate data ~Covariance, Correlation, Coefficient of determination -Identifying the type of linear relation btwn two variables -Computing Slope and Intercept parameters of linear relation -Predicting Y value for a given X value using Linear Regression

Pivot Table

Pivot Tables are used to summarize, analyze, explore and present huge amount of data, in a considerable manner. -A Pivot table has to be manually updated.

Pivot Chart

Similar to a Pivot table, a Pivot chart can be used to summarize, analyze, explore and present huge amount of data, in a concise manner using charts available in excel. Step 1: Select the data set. 1.) Go to Insert tab 2.) select PivotChart option 3.) Click on PivotChart in drop-down Step 2: A dialogue box appears, click on OK to insert a new pivot chart in a new worksheet. Step 3: A new worksheet opens with pivot table and chart. Step 4: Now do the following operations; - Select and drag Product Name to Rows - Select and drag Quantity to Values - Select and drag Quarter to Legends

HLOOKUP

Sometimes you have data in a horizontal table format. In such a case you must use HLOOKUP function instead of VLOOKUP. The H in HLOOKUP stands for Horizontal. This function searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Syntax: HLOOKUP(lookup value, table/array, row index number, range lookup) -Lookup Value: The value you want to look up -Range/Table: Where the lookup value is located. Remember that the lookup value should always be looked for in the first row of the range for HLOOKUP to work correctly. -Row Index Number: The range that contains the return value. Ex. If you specify B2:D11 as the range, you should count 2 as the first row, 3 as the second and so on. -Optionally, you can specify TRUE if you want an approximate match or FALSE if you want an exact match of the return value. If you don't specify anything, the default value will always be TRUE or approximate match. Ex. =HLOOKUP(B8,$B$3:$K$4,2,FALSE) *Note: HLOOKUP and VLOOKUP can be used for approximate match but in that case the data in lookup table must be sorted in ascending order of lookup row and column respectively.

Range Filters

Step 1: 1.) Select the column including column header. To do this, clock on column header and less CTRL+ Down Key Step 2: 1.) Go to HOME tab. 2.) Click on SORT and FILTER drop-down in Editing group. Step 3: 1.) Select the FILTER option from the drop-down means or press "Ctrl+Shift+L" Step 4: A drop down means appears on the column header. 1.) Click the drop down menu on column header for filtering 2.) Select NUMBER FILTERS 3.) Click on 'Greater Than...' option from drop down Step 5: 1.) Select "Greater Than" & "Less Than" options for the 2 drop-down menus and connect them with logical AND radio button 2.) Type in values BLANK and BLANK in the text boxes 3.) Click OK to apply the filter Step 6: Observe the display after the filter on the column has been applied.

TOP N Filters

Step 1: Click on column header and press Ctrl+ down key to select all contents of column, including column header Step 2: 1.) Go to HOME tab 2.) Click on Sort &Filter Step 3: Select the Filter option. This enables filter option on column header. Step 4: A drop down menu appears on the column header. 1.) Click on the drop-down. 2.) Select NUMBER FILTERS from the drop-down menu 3.) Click on TOP 10 option. Step 5: A dialogue box appears, that asks you to set values. 1.) First selection is to specify the ranking criterion 2.) Second selection is to specify how may entries (N) you want to filter. 3.) Third selection is to specify if N represents number of items (values) or percentage.

COUNTIFS

This function is used to count the number of cells specified by a set of conditions or criteria in selected ranges. Syntax: COUNTIFS (Criteria Range 1, Criteria 1 [, criteria range 2, criteria 2]...) -Criteria Range 1, Criteria Range 2,...: Criteria range 1 is required, subsequent criteria_ranges are optional. Maximum 127 ranges can be specified. -Criteria 1, Criteria 2,...: Criteria 1 is required, subsequent criteria are optional. maximum 127 ranges can be sppeicifetd in the form of a number, expression, cell reference, or text that define which cells will be counted. An expression may include wild cards (* or ?). *NOTE: Each range is associated with a criteria and these criteria are applied to respective ranges ONE CELL AT A TIME. If ALL THE FIRST CELLS IN EVERY RANGE meet their associated criteria, the COUNT INCREASES by 1. If all the seconds cells in every range meet their associated criteria, the count increases by 1 again, and so on until the cells are evaluated. If any cell among fails to meet its criterion then there's NO increment in the counter. Ex. Continuing to the next cell values in the criteria ranges, when E4 & C4 are evaluated and BOTH found to be TRUE for their respective criteria, finally the count becomes 1. This continues for every pair of cells in E & C column ranges until it reaches E18 & C18 i.e. the LAST CELLS OF THE RANGES specified. Each range must have the SAME number of ROWS AND COLUMNS. The ranges DO NOT have to be ADJACENT to each other.

COUNTIF

This function is used to count the number of cells that meet a given criterion in selected range. Syntax: COUNTIF (criteria range, criteria) -Criteria Range: The range in which the criteria is applied -Criteria: The criteria in the form of a number, expression, cell referencer text that define which cells will be counted. *NOTE: The COUNTIF function uses only a single criteria for its operation. But if you want to use multiple criteria according to your requirements, you can use COUNTIFS function.

AVERAGEIFS

This function returns the average (arithmetic mean) of all cells that meet multiple criteria. Syntax: AVERAGEIFS (average range, criteria range 1, criteria 1 [criteria range 2, criteria 2]...) -Average Range: One or more cells to average, including numbers or names, arrays or references that contain numbers. -Criteria Range 1, Criteria Range 2,...: Criteria range 1 is required, subsequent criteria_ranges are optional. 1 to 127 ranges in which to evaluate the associated criteria. -Criteria1, Criteria2,...: Criteria 1 is required, subsequent criteria are optional 1 to 127 criteria in the form of a number, expression, cell reference or text that defined which cells will be averages. Expression may include wild cards (* and ?). Ex. =AVERAGEIFS($D$2:$D$18,$E$2:$E$18,">75",$B$2:$B$18,"Food") *NOTES: -In the average_range argument, empty cells, Boolean values of TRUE/FALSE and text values are IGNORED. -In the criteria_range argument, empty cells are treated as ZERO values (0). -If average_range contains only blank cells or text values, the function returns the #DIV0! error. -If there are NO CELLS that meet all the criteria or if cells in average range cannot be translated into numbers, AVERAGEIFS returns the #DIV/0! error value. -Unlike the AVERAGEIF function, in AVERAGEIFS each criteria range must have the SAME NUMBER OF ROWS AND COLUMNS as in the average range. -Self explore SUMIF and SUMIFS functions too. They are very similar to AVERAGEIF and AVERAGEIFS.

AVERAGEIF

This function returns the average (arithmetic mean) of all the cells in range that meet a given criteria. Syntax: AVERAGEIF (criteria range, criteria, [average range]) -Criteria Range: One or more cells to average, including numbers or names, arrays, or references that contain numbers. -Criteria: The criteria in the form of a number, expression, cell reference, or text that defines the cells where values needs to be averaged. Expression may include wild cards (*and ?). -Average Range: Optional, the actual set of cells to average. If omitted, criteria range is used. *NOTE: -If no cell in the range meets the criteria, AVERAGEIF returns the #DIV/0! error value. -Average range does not have to be the same size and shape as criteria range. The actual cells that are averaged are determined by using the top, left cell in average range as the starting cell, and then includes cells that correspond in size and shape to criteria range. The AVERAGEIF functions uses only a single criteria along with a single range for its operation. BUT if you want to use multiple criteria on the same range, you can use AVERAGEIFS function.

VLOOKUP

This function returns the value in a table or a range by row. The V in VLOOKUP stands for Vertical. It searches for a value in the first column of a table or an array of values, and then returns a value in the same row from a column you specify in the table or array. Syntax: VLOOKUP (Lookup Value, Table/Array, Column Index Number, Range Lookup) -Lookup_Value: The value to be found in the first row of the table. Lookup_value can be a value, a reference, or a text string. -Table_Array: A table of information in which data is looked up. Use a reference to a range or a range name. ~The values in the first column of the table_array can be text, numbers or logical values. ~If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order:...-2, -1, 0, 1, 2,...,A-Z, FALSE, TRUE; otherwise, VLOOKUP may not give the correct value. If range_lookup is FALSE table_array does not need to be sorted. ~Uppercase and lowercase text are equivalent. ~Sort the values in ascending order left to right. -Row_Index_Num: The row number in table_array from which the matching value will be returned. ~A row_index_num of 1 returns the first row value in table_array, a row_index_num of 2 returns the second row value in table_array, and so on. ~If row_index_num is less than 1, VLOOKUP returns the #VALUE! error value. ~If row_index is greater than the number of rows on table_array, VLOOKUP returns the #REF! error value. -Range_Lookup: A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. ~ If TRUE or omitted; an appropriate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. ~If FALSE: VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned. Ex. =VLOOKUP(H2,$C$2:$D$18,2,FALSE) Bc we need an exact match range_lookup ...the fourth parameter of the function is kept as FALSE.

MATCH

This function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. Syntax: MATCH (lookup_value, lookup_array, match_type) -Lookup_Value: The value that you want to match in lookup_array. This argument can be a value (number, text, or logical value) for a cell reference to a number, text, or logical value. -Lookup_Array: The range of cells being searched. -Match_Type: It can be either -1, 0, or 1. The default value for this argument is 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. ~0: finds the first value exactly equal to lookup_value. Lookup_array need not be sorted. ~1: Finds the largest value that is less than or equal to lookup_value. The lookup_array must be sorted in ascending order. ~-1: Finds the smallest value that is greater than or equal to lookup_value. The lookup_array must be sorted in descending order.

Workbook Security

Use 'Protect Sheet' Option: It is essential to protect your data form accidental or intentional alteration. Excel provides wide range of options to protect the data at cell range, worksheet and workbook level -Protect Sheet: This allows you to restrict several operations on the worksheet such as editing, deleting data in a sheet. -Protect Workbook: This option allows you to prevent the structure of workbook from any change like adding, deleting, renaming a sheet etc. Steps involved in PROTECTING a WORKSHEET are: Step 1: On the Ribbon: 1.) Go to Review tab 2.) Select Protect Sheet option Step 2: On Protect Sheet dialog box: 1.) Tick Protect worksheet option. 2.) Select all operations that you want to allow to other users. 3.) Specify password to protect the sheet (Make sure that you remember the password). Step 3: Retype the password for confirmation and click OK. Step 4: When a user try to preform restricted operation without unprotected worksheet, it will give you the error message.