Category: excel Data Analysis

What-If Analysis with Data Tables ”; Previous Next With a Data Table in Excel, you can easily vary one or two inputs and perform What-if analysis. A Data Table is a range of cells in which you can change values in some of the cells and come up with different answers to a problem. There are two types of Data Tables − One-variable Data Tables Two-variable Data Tables If you have more than two variables in your analysis problem, you need to use Scenario Manager Tool of Excel. For details, refer to the chapter – What-If Analysis with Scenario Manager in this tutorial. One-variable Data Tables A one-variable Data Table can be used if you want to see how different values of one variable in one or more formulas will change the results of those formulas. In other words, with a one-variable Data Table, you can determine how changing one input changes any number of outputs. You will understand this with the help of an example. Example There is a loan of 5,000,000 for a tenure of 30 years. You want to know the monthly payments (EMI) for varied interest rates. You also might be interested in knowing the amount of interest and Principal that is paid in the second year. Analysis with One-variable Data Table Analysis with one-variable Data Table needs to be done in three steps − Step 1 − Set the required background. Step 2 − Create the Data Table. Step 3 − Perform the Analysis. Let us understand these steps in detail − Step 1: Set the required background Assume that the interest rate is 12%. List all the required values. Name the cells containing the values, so that the formulas will have names instead of cell references. Set the calculations for EMI, Cumulative Interest and Cumulative Principal with the Excel functions – PMT, CUMIPMT and CUMPRINC respectively. Your worksheet should look as follows − You can see that the cells in column C are named as given in the corresponding cells in column D. Step 2: Create the Data Table Type the list of values i.e. interest rates that you want to substitute in the input cell down the column E as follows − As you observe, there is an empty row above the Interest Rate values. This row is for the formulas that you want to use. Type the first function (PMT) in the cell one row above and one cell to the right of the column of values. Type the other functions (CUMIPMT and CUMPRINC) in the cells to the right of the first function. Now, the two rows above the Interest Rate values look as follows − The Data Table looks as given below − Step 3: Do the analysis with the What-If Analysis Data Table Tool Select the range of cells that contains the formulas and values that you want to substitute, i.e. select the range – E2:H13. Click the DATA tab on the Ribbon. Click What-if Analysis in the Data Tools group. Select Data Table in the dropdown list. Data Table dialog box appears. Click the icon in the Column input cell box. Click the cell Interest_Rate, which is C2. You can see that the Column input cell is taken as $C$2. Click OK. The Data Table is filled with the calculated results for each of the input values as shown below − If you can pay an EMI of 54,000, you can observe that the interest rate of 12.6% is suitable for you. Two-variable Data Tables A two-variable Data Table can be used if you want to see how different values of two variables in a formula will change the results of that formula. In other words, with a twovariable Data Table, you can determine how changing two inputs changes a single output. You will understand this with the help of an example. Example There is a loan of 50,000,000. You want to know how different combinations of interest rates and loan tenures will affect the monthly payment (EMI). Analysis with Two-variable Data Table Analysis with two-variable Data Table needs to be done in three steps − Step 1 − Set the required background. Step 2 − Create the Data Table. Step 3 − Perform the Analysis. Step 1: Set the required background Assume that the interest rate is 12%. List all the required values. Name the cells containing the values, so that the formula will have names instead of cell references. Set the calculation for EMI with the Excel function – PMT. Your worksheet should look as follows − You can see that the cells in the column C are named as given in the corresponding cells in the column D. Step 2: Create the Data Table Type =EMI in cell F2. Type the first list of input values, i.e. interest rates down the column F, starting with the cell below the formula, i.e. F3. Type the second list of input values, i.e. number of payments across row 2, starting with the cell to the right of the formula, i.e. G2. The Data Table looks as follows − Do the analysis with the What-If Analysis Tool Data Table Select the range of cells that contains the formula and the two sets of values that you want to substitute, i.e. select the range – F2:L13. Click the DATA tab on the Ribbon. Click What-if Analysis in the Data Tools group. Select Data Table from the dropdown list. Data Table dialog box appears. Click the icon in the Row input cell box. Click the cell NPER, which is C3. Again, click the icon in the Row input cell box. Next, click the icon in the Column input cell box. Click the cell Interest_Rate, which is C2. Again, click the icon in the Column input cell box. You will see that the Row input cell is taken as $C$3 and the Column input cell is taken as $C$2. Click OK. The Data Table gets filled with the

Advanced Data Analysis – Overview ”; Previous Next Excel provides several commands, functions and tools that make your complex data analysis tasks easy. Excel lets you perform various complex calculations with ease. In this tutorial, you will understand the versatile data analysis tools of Excel. You will understand data analysis with relevant examples, step by step instructions and screen shots at every step. Data Consolidation You might have to consolidate the data from various sources and present a report. The data could be in the worksheets of the same workbook or in different workbooks. With Excel data tool Consolidate, you can perform this in a few easy steps. What-If Analysis What-If Analysis provides you tools to handle the following data analysis situations − Find the input values that result in a specified value. The result could be set up as a formula with the input values as variables. By varying the values of the input variables, Excel provides the solution with the Goal Seek Tool. Find the possible output values by varying the values of one or two variables. The result could be set up as a formula with one or two input values as variables. By varying the values for the input variables, Excel provides the solution with the Data Table Tool. Find the possible output values that are a result of varying the values of more than two variables. The result could be set up as a formula with the input values as variables. By varying the values for the input variables, Excel provides the solution with the Scenario Manager Tool. Optimizing with Excel Solver Add-in Solver is used to handle complex goal seek situations. In such cases, in addition to the inputs and outputs, there will be defined constraints or limits imposed on the possible input values. Further, Solver is used to result in an optimal solution. Excel has a Solver Add-in that helps you solve such complex problems. Importing Data into Excel Your data analysis might depend on various external data sources. In Excel, you can import data from different data sources, such as Microsoft Access Database, Web Pages, Text Files, SQL Server Table, SQL Server Analysis Cube, XML File, etc. You can import any number of data tables simultaneously from a database. When you are importing multiple tables from a relational database such as Access, the existing relationships among the tables will be retained in Excel also. While importing the data, you can also optionally create a PivotTable or PivotChart or Power View report based on that data. You can just create a data connection with a data source, or import the data into Excel. If you import the data into Excel, the data tables are added to the Data Model in Excel. Data Model Data Model in Excel is used to integrate data from multiple tables in the current workbook and / or from the imported data and / or from the data sources connected to the workbook through data connections. Data model is used transparently in PivotTable, PivotChart, PowerPivot and Power View reports. You can create a Data Model while importing data, or from the Excel tables in the workbook. The data tables in the Data Model can be viewed either in Data View or Diagram View. With a Data Model, you can create relationships among the data tables. You can either use the Create Relationship command or just click and drag and connect the fields in the two tables that define the relationship in the diagram view of the Data Model. Exploring Data with PivotTable As you can integrate the Data Model with a PivotTable, you can do extensive data analysis by collating, connecting, summarizing and reporting data from several different sources. As you can import tables from external data sources and create a PivotTable, it is possible to have automatic updates of the values in the PivotTable whenever the data in the connected data sources is updated. You can create a PivotTable with the fields from multiple tables, provided the tables have relationships defined. If a relationship does not exist, Excel prompts you to create one and you can do so from the PivotTable itself. The relationship that you so define is reflected in the Data Model. Exploring Data with PowerPivot You can use PowerPivot to access, analyze and report data from various data sources. PowerPivot can help you handle large data with ease and produce fascinating analysis reports. PowerPivot provides you commands to manage the Data Model, add Excel tables to Data Model, to add calculated fields in the Data Tables, to define KPIs, etc. Exploring Data with Power View Power View provides interactive exploration, visualization and analysis of large data. Owing to its versatile visualization options, you can definitely find the one that gives your data the perfect platform wherein you can explore the data, summarize and report. Ranging from Tables to Maps, it is just a play for you to visualize your data, filter it, analyze it, and report it interactively. Moreover, you can have multiple visualizations on the same Power View sheet that reflect and highlight values, when you click on a data point in any one of them. You can explore data in Power View with a table, a matrix, a card, different chart types, multiples, maps and tiles. You will get fascinated with the versatility of these different views once you get hands-on experience. This is because it is easy to produce interactive reports highlighting significant values and dynamically switching across the views. Exploring Data with Hierarchies If your data has hierarchies, they can be either defined in the Data Model that is reflected in the Power View or build the hierarchies in Power View itself. Once a hierarchy is defined, you can drill-up and drill-down the hierarchy, displaying the required data. Aesthetic Power View Reports You can arrive at a report layout based on what you want to present in Power View. You can add a background image that reflects

What-If Analysis with Scenario Manager ”; Previous Next Scenario Manager is useful in the cases where you have more than two variables in sensitivity analysis. Scenario Manager creates scenarios for each set of the input values for the variables under consideration. Scenarios help you to explore a set of possible outcomes, supporting the following − Varying as many as 32 input sets. Merging the scenarios from several different worksheets or workbooks. If you want to analyze more than 32 input sets, and the values represent only one or two variables, you can use Data Tables. Although it is limited to only one or two variables, a Data Table can include as many different input values as you want. Refer to What-If Analysis with Data Tables in this tutorial. Scenarios A scenario is a set of values that Excel saves and can substitute automatically on your worksheet. You can create and save different groups of values as scenarios on a worksheet and then switch between these scenarios to view the different results. For example, you can have several different budget scenarios that compare various possible income levels and expenses. You can also have different loan scenarios from different sources that compare various possible interest rates and loan tenures. If the information that you want to use in scenarios is from different sources, you can collect the information in separate workbooks, and then merge the scenarios from the different workbooks into one. After you have all the scenarios you need, you can create a scenario summary report − That incorporates information from all the scenarios. That lets you compare the scenarios side-by-side. Scenario Manager Scenario Manager is one of the What-if Analysis tools in Excel. To create an analysis report with Scenario Manager, you have to follow these steps − Step 1 − Define the set of initial values and identify the input cells that you want to vary, called the changing cells. Step 2 − Create each scenario, name the scenario and enter the value for each changing input cell for that scenario. Step 3 − Select the output cells, called the result cells that you want to track. These cells contain formulas in the initial set of values. The formulas use the changing input cells. The Scenario Manager creates a report containing the input and the output values for each scenario. Initial Values for Scenarios Before you create several different scenarios, you need to define a set of initial values on which the scenarios will be based. The steps for setting up the initial values for Scenarios are − Define the cells that contain the input values. Name the input cells appropriately. Identify the input cells with constant values. Specify the values for the constant inputs. Identify the input cells with changing values. Specify the initial values for the changing inputs. Define the cells that contain the results. The result cells contain formulas. Name the result cells appropriately. Place the formulas in the result cells. Consider the previous example of loan. Now, proceed as follows − Define a cell for Loan Amount. This input value is constant for all the scenarios. Name the cell Loan_Amount. Specify the value as 5,000,000. Define the cells for Interest Rate, No. of payments and Type (Payment at the beginning or end of the month). These input values will be changing across the scenarios. Name the cells Interest_Rate, NPER and Type. Specify the initial values for the analysis in these cells as 12%, 360 and 0 respectively. Define the cell for the EMI. This is the result value. Name the cell EMI. Place the formula in this cell as − =PMT (Interest_Rate/12, NPER, Loan_Amount, 0, Type) Your worksheet looks as shown below − As you can see that the input cells and the result cells are in column C with the names as given in column D. Creating Scenarios After setting up the initial values for the Scenarios, you can create the scenarios using Scenario Manager as follows − Click the DATA tab on the Ribbon. Click What-if Analysis in the Data Tools group. Select Scenario Manager from the dropdown list. The Scenario Manager Dialog box appears. You can observe that it contains a message − “No Scenarios defined. Choose Add to.” You need to create scenarios for each set of changing values in the Scenario Manager. It is good to have the first scenario defined with initial values, as it enables you to switch back to initial values whenever you want while displaying different scenarios. Create the first scenario with the initial values as follows − Click the Add button in the Scenario Manager Dialog box. The Add Scenario dialog box appears. Under Scenario Name, type Scenario 1. Under Changing Cells, enter the references for the cells i.e. C3, C4 and C5 with the Ctrl key pressed. The name of the dialog box changes to Edit Scenario. Edit the text in the Comment as – Initial Values box. Select the option Prevent changes under Protection and then click OK. The Scenario Values dialog box appears. The initial values that you have defined appear in each of the changing cells boxes. Scenario 1 with the initial values is created. Create three more scenarios with varying values in the changing cells as follows − Click the Add button in the Scenario Values dialog box. Add Scenario dialog box appears. Note that C3, C4, C5 appear in the Changing cells box. In the Scenario Name box, type Scenario 2. Edit the text in the Comment as – Different Interest Rate. Select Prevent changes under Protection and click OK. The Scenario Values dialog box appears. The initial values appear in the changing cells. Change the value of Interest_Rate to 0.13 and click Add. The Add Scenario dialog box appears. Note that C3, C4, C5 appear in the box under changing cells. In the Scenario Name box, type Scenario 3. Edit the text in the Comment box as – Different no. of Payments. Select Prevent

Data Analysis – Process ”; Previous Next Data Analysis is a process of collecting, transforming, cleaning, and modeling data with the goal of discovering the required information. The results so obtained are communicated, suggesting conclusions, and supporting decision-making. Data visualization is at times used to portray the data for the ease of discovering the useful patterns in the data. The terms Data Modeling and Data Analysis mean the same. Data Analysis Process consists of the following phases that are iterative in nature − Data Requirements Specification Data Collection Data Processing Data Cleaning Data Analysis Communication Data Requirements Specification The data required for analysis is based on a question or an experiment. Based on the requirements of those directing the analysis, the data necessary as inputs to the analysis is identified (e.g., Population of people). Specific variables regarding a population (e.g., Age and Income) may be specified and obtained. Data may be numerical or categorical. Data Collection Data Collection is the process of gathering information on targeted variables identified as data requirements. The emphasis is on ensuring accurate and honest collection of data. Data Collection ensures that data gathered is accurate such that the related decisions are valid. Data Collection provides both a baseline to measure and a target to improve. Data is collected from various sources ranging from organizational databases to the information in web pages. The data thus obtained, may not be structured and may contain irrelevant information. Hence, the collected data is required to be subjected to Data Processing and Data Cleaning. Data Processing The data that is collected must be processed or organized for analysis. This includes structuring the data as required for the relevant Analysis Tools. For example, the data might have to be placed into rows and columns in a table within a Spreadsheet or Statistical Application. A Data Model might have to be created. Data Cleaning The processed and organized data may be incomplete, contain duplicates, or contain errors. Data Cleaning is the process of preventing and correcting these errors. There are several types of Data Cleaning that depend on the type of data. For example, while cleaning the financial data, certain totals might be compared against reliable published numbers or defined thresholds. Likewise, quantitative data methods can be used for outlier detection that would be subsequently excluded in analysis. Data Analysis Data that is processed, organized and cleaned would be ready for the analysis. Various data analysis techniques are available to understand, interpret, and derive conclusions based on the requirements. Data Visualization may also be used to examine the data in graphical format, to obtain additional insight regarding the messages within the data. Statistical Data Models such as Correlation, Regression Analysis can be used to identify the relations among the data variables. These models that are descriptive of the data are helpful in simplifying analysis and communicate results. The process might require additional Data Cleaning or additional Data Collection, and hence these activities are iterative in nature. Communication The results of the data analysis are to be reported in a format as required by the users to support their decisions and further action. The feedback from the users might result in additional analysis. The data analysts can choose data visualization techniques, such as tables and charts, which help in communicating the message clearly and efficiently to the users. The analysis tools provide facility to highlight the required information with color codes and formatting in tables and charts. Print Page Previous Next Advertisements ”;

Excel Data Analysis – Tables ”; Previous Next A Table is a rectangular range of structured data. The key features are − Each row in the table corresponds to a single record of the data. Example – Employee information. Each column contains a specific piece of information. Exmaple – The columns can contain data such as name, employee number, hire date, salary, department, etc. The top row describes the information contained in each column and is referred to as header row. Each entry in the top row is referred to as column header. You can create and use an Excel table to manage and analyze data easily. Further, with Excel Tables you get built-in Filtering, Sorting, and Row Shading that ease your reporting activities. Further, Excel responds to the actions performed on a table intelligently. For example, you have a formula in a column or you have created a chart based on the data in the table. When you add more data to the table (i.e., more rows), Excel extends the formula to the new data and the chart expands automatically. Difference between Tables and Ranges Following are the differences between a table and range − A table is a more structured way of working with data than a range. You can convert a range into a table and Excel automatically provides − a Table Name Column Header Names Formatting to the Data (Cell Color and Font Color) for better Visualization Tables provide additional features that are not available for ranges. These are − Excel provides table tools in the ribbon ranging from properties to styles. Excel automatically provides a Filter button in each column header to sort the data or filter the table such that only rows that meet your defined criteria are displayed. If you have multiple rows in a table, and you scroll down the sheet so that the header row disappears, the column letters in the worksheet are replaced by the table headers. When you place a formula in any cell in a column of the table, it gets propagated to all the cells in that column. You can use table name and column header names in the formulas, without having to use cell references or creating range names. You can extend the table size by adding more rows or more columns by clicking and dragging the small triangular control at the lower-right corner of the lower-right cell. You can create and use slicers for a table for filtering data. You will learn about all these Features in this Chapter. Create Table To create a table from the data you have on the worksheet, follow the given steps − Step 1 − Select the Range of Cells that you want to include in the Table. Cells can contain data or can be empty. The following Range has 290 rows of employee data. The top row of the data has headers. Step 2 − Under the Insert tab, in the Tables group, click Tables. The Create Table dialog box appears. Check that the data range selected in the Where is the data for your table? Box is correct. Step 3 − Check the My table has headers box if the top row of the selected Range contains data that you want to use as the Table Headers. Note − If you do not check this box, your table will have Headers – Column1, Column2, … Step 4 − Click OK. Range is converted to Table with the default Style. Step 5 − You can also convert a range to a table by clicking anywhere on the range and pressing Ctrl+T. A Create Table dialog box appears and then you can repeat the steps as given above. Table Name Excel assigns a name to every table that is created. Step 1 − To look at the name of the table you just created, click table, click on table tools – design tab on the Ribbon. Step 2 − In the Properties group, in the Table Name box, your Table Name will be displayed. Step 3 − You can edit this Table Name to make it more meaningful to your data. Step 4 − Click the Table Name box. Clear the Name and type Emp_Data. Note − The syntax rules of range names are applicable to table names. Managing Names in a Table You can manage table names just similar to how you manage range names with Name Manager. Click the Table. Click Name Manager in the Defined Names group on Formulas tab. The Name Manager dialog box appears and you can find the Table Names in your workbook. You can Edit a Table Name or add a comment with New option in the Name Manager dialog box. However, you cannot change the range in Refers to. You can Create Names with column headers to use them in formulas, charts, etc. Click the Column Header EmployeeID in the Table. Click Name Manager. Click New in the Name Manager dialog box. The New Name dialog box appears. In the Name box, you can find the Column Header, and in the Refers to box,you will find Emp_Data[[#Headers],[EmployeeID]]. As you observe, this is a quick way of defining Names in a Table. Table Headers replacing Column Letters When you are working with more number of rows of data in a table, you may have to scroll down to look at the data in those rows. However, while doing so, you also require the table headers to identify which value belongs to which column. Excel automatically provides a smooth way of doing this. As you scroll down your data, the column letters of the worksheet themselves get converted to table headers. In the worksheet given below, the column letters are appearing as they are and the table headers are in row 2. 21 rows of 290 rows of data are visible. Scroll down to see the table rows 25 – 35. The table headers will replace the column letters

Excel Data Analysis Tutorial PDF Version Quick Guide Resources Job Search Discussion Data Analysis with Excel is a comprehensive tutorial that provides a good insight into the latest and advanced features available in Microsoft Excel. It explains in detail how to perform various data analysis functions using the features available in MS-Excel. The tutorial has plenty of screenshots that explain how to use a particular feature, in a step-by-step manner. Audience This tutorial has been designed for all those readers who depend heavily on MS-Excel to prepare charts, tables, and professional reports that involve complex data. It will help all those readers who use MS-Excel regularly to analyze data. Prerequisites The readers of this tutorial are expected to have a good prior understanding of the basic features available in Microsoft Excel. Print Page Previous Next Advertisements ”;

Excel Data Analysis – Lookup Functions ”; Previous Next You can use Excel functions to − Find values in a range of data – VLOOKUP and HLOOKUP Obtain a value or the reference to a value from within a table or range – INDEX Obtain the relative position of a specified item in a range of cells – MATCH You can also combine these functions to get the required results based on the inputs you have. Using VLOOKUP Function The syntax of the VLOOKUP function is VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) Where lookup_value − is the value you want to look up. Lookup_value can be a value or a reference to a cell. Lookup_value must be in the first column of the range of cells you specify in table_array table_array − is the range of cells in which the VLOOKUP will search for the lookup_value and the return value. table_array must contain the lookup_value in the first column, and the return value you want to find Note − The first column containing the lookup_value can either be sorted in ascending order or not. However, the result will be based on the order of this column. col_index_num − is the column number in the table_array that contains the return value. The numbers start with 1 for the left-most column of table-array range_lookup − is an optional logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. range_lookup can be omitted, in which case it is assumed to be TRUE and VLOOKUP tries to find an approximate match TRUE, in which case VLOOKUP tries to find an approximate match. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned FALSE, in which case VLOOKUP tries to find an exact match 1, in which case it is assumed to be TRUE and VLOOKUP tries to find an approximate match 0, in which case it is assumed to be FALSE and VLOOKUP tries to find an exact match Note − If range_lookup is omitted or TRUE or 1, VLOOKUP works correctly only when the first column in table_array is sorted in ascending order. Otherwise, it may result in incorrect values. In such a case, use FALSE for range_lookup. Using VLOOKUP Function with range_lookup TRUE Consider a list of student marks. You can obtain the corresponding grades with VLOOKUP from an array containing the marks intervals and pass category. table_array − Note that the first column marks based on which the grades are obtained is sorted in ascending order. Hence, using TRUE for range_lookup argument you can get approximate match that is what is required. Name this array as Grades. It is a good practice to name arrays in this way so that you need not remember the cell ranges. Now, you are ready to look up the grade for the list of marks you have as follows − As you can observe, col_index_num − indicates the column of the return value in table_array is 2 the range_lookup is TRUE The first column containing the lookup value in the table_array grades is in ascending order. Hence, the results will be correct. You can get the return value for approximate matches also. i.e. VLOOKUP computes as follows − Marks Pass Category < 35 Fail >= 35 and < 50 Third Class >= 50 and < 60 Second Class >=60 and < 75 First Class >= 75 First Class with Distinction You will get the following results − Using VLOOKUP Function with range_lookup FALSE Consider a list of products containing the Product ID and price for each of the products. The product ID and price will be added to the end of the list whenever a new product is launched. This would mean that the product IDs need not be in ascending order. The product list might be as shown below − table_array − Name this array as ProductInfo. You can obtain the price of a product given the product ID with the VLOOKUP function as the product ID is in the first column. The price is in column 3 and hence col_index_ num should be 3. Use VLOOKUP Function with range_lookup as TRUE Use VLOOKUP Function with range_lookup as FALSE The correct answer is from the ProductInfo array is 171.65. You can check the results. You observe that you got − The correct result when range_lookup is FALSE, and A wrong result when range_lookup is TRUE. This is because, the first column in the ProductInfo array is not sorted in ascending order. Hence, remember to use FALSE whenever the data is not sorted. Using HLOOKUP Function You can use HLOOKUP function if the data is in rows rather than columns. Example Let us take the example of product information. Suppose the array looks as follows − Name this Array ProductRange. You can find the price of a product given the product ID with HLOOKUP function. The Syntax of HLOOKUP function is HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup]) Where lookup_value − is the value to be found in the first row of the table table_array − is a table of information in which data is looked up row_index_num − is the row number in table_array from which the matching value will be returned range_lookup − is a logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match range_lookup can be omitted, in which case it is assumed to be TRUE and HLOOKUP tries to find an approximate match TRUE, in which case HLOOKUP tries to find an approximate match. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned FALSE, in which case HLOOKUP tries to find an exact match 1, in which case it is assumed to be TRUE and HLOOKUP tries to find an approximate match 0, in which case it is assumed to be FALSE

Working with Range Names ”; Previous Next While doing Data Analysis, referring to various data will be more meaningful and easy if the reference is by Names rather than cell references – either a single cell or a range of cells. For example, if you are calculating Net Present Value based on a Discount Rate and a series of Cash Flows, the formula Net_Present_Value = NPV (Discount_Rate, Cash_Flows) is more meaningful than C10 = NPV (C2, C6:C8) With Excel, you can create and use meaningful names to various parts of your data. The advantages of using range names include − A meaningful Range name (such as Cash_Flows) is much easier to remember than a Range address (such as C6:C8). Entering a name is less error prone than entering a cell or range address. If you type a name incorrectly in a formula, Excel will display a #NAME? error. You can quickly move to areas of your worksheet by using the defined names. With Names, your formulas will be more understandable and easier to use. For example, a formula Net_Income = Gross_Income – Deductions is more intuitive than C40 = C20 – B18. Creating formulas with range names is easier than with cell or range addresses. You can copy a cell or range name into a formula by using formula Autocomplete. In this chapter, you will learn − Syntax rules for names. Creating names for cell references. Creating names for constants. Managing the names. Scope of your defined names. Editing names. Filtering names. Deleting names. Applying names. Using names in a formula. Viewing names in a workbook. Using paste names and paste list. Using names for range intersections. Copying formulas with names. Copying Name using Formula Autocomplete Type the first letter of the name in the formula. A drop-down box appears with function names and range names. Select the required name. It is copied into your formula. Range Name Syntax Rules Excel has the following syntax rules for names − You can use any combination of letters, numbers and the symbols – underscores, backslashes, and periods. Other symbols are not allowed. A name can begin with a character, underscore or backslash. A name cannot begin with a number (example – 1stQuarter) or resemble a cell address (example – QTR1). If you prefer to use such names, precede the name with an underscore or a backslash (example – 1stQuarter, _QTR1). Names cannot contain spaces. If you want to distinguish two words in a name, you can use underscore (example- Cash_Flows instead of Cash Flows) Your defined names should not clash with Excel’s internally defined names, such as Print_Area, Print_Titles, Consolidate_Area, and Sheet_Title. If you define the same names, they will override the Excel’s internal names and you will not get any error message. However, it is advised not to do so. Keep the names short but understandable, though you can use up to 255 characters Creating Range Names You can create Range Names in two ways − Using the Name box. Using the New Name dialog box. Using the Selection dialog box. Create a Range Name using the Name Box To create a Range name, using the Name box that is to the left of formula bar is the fastest way. Follow the steps given below − Step 1 − Select the range for which you want to define a Name. Step 2 − Click on the Name box. Step 3 − Type the name and press Enter to create the Name. Create a Range Name using the New Name dialog box You can also create Range Names using the New Name dialog box from Formulas tab. Step 1 − Select the range for which you want to define a name. Step 2 − Click the Formulas tab. Step 3 − Click Define Name in the Defined Names group. The New Name dialog box appears. Step 4 − Type the name in the box next to Name Step 5 − Check that the range that is selected and displayed in the Refers box is correct. Click OK. Create a Range Name using the Create Names from Selection dialog box You can also create Range names using the Create Names from the Selection dialog box from Formulas tab, when you have Text values that are adjacent to your range. Step 1 − Select the range for which you want to define a name along with the row / column that contains the name. Step 2 − Click the Formulas tab. Step 3 − Click Create from Selection in the Defined Names group. The Create Names from Selection dialog box appears. Step 4 − Select top row as the Text appears in the top row of the selection. Step 5 − Check the range that got selected and displayed in the box next to Refers to be correct. Click OK. Now, you can find the largest value in the range with =Sum(Student Name), as shown below − You can create names with multiple selection also. In the example given below, you can name the row of marks of each student with the student’s name. Now, you can find the total marks for each student with =Sum (student name), as shown below. Creating Names for Constants Suppose you have a constant that will be used throughout your workbook. You can assign a name to it directly, without placing it in a cell. In the example below, Savings Bank Interest Rate is set to 5%. Click Define Name. In the New Name dialog box, type Savings_Bank_Interest_Rate in the Name box. In Scope, select Workbook. In Refers to box, clear the contents and type 5%. Click OK. The Name Savings_Bank_Interest_Rate is set to a constant 5%. You can verify this in Name Manager. You can see that the value is set to 0.05 and in the Refers to =0.05 is placed. Managing Names An Excel Workbook can have any number of named cells and ranges. You can manage these names with the Name

Excel Data Analysis – Filtering ”; Previous Next Filtering allows you to extract data that meets the defined criteria from a given Range or table. This is a quick way to display only the information that is needed by you. You can Filter data in a Range, table or PivotTable. You can filter data by − Selected values Text filters if the column you selected contains text Date filters if the column you selected contains dates Number filters if the column you selected contains numbers Number filters if the column you selected contains numbers Font color if the column you selected contains font with color Cell icon if the column you selected contains cell icons Advanced filter Using slicers In a table, the column headers are automatically tagged to filters, known as AutoFilters. AutoFilter is represented by the arrow next to column header. Each AutoFilter has filter options based on the type of data you have in that column. For example, if the column contains numbers, when you click on the arrow next to the column header, Number Filter Options appear. When you click a Filter option or when you click on Custom Filter that appears at the end of the Filter options, Custom AutoFilter dialog box appears, wherein you can customize your filtering options. In case of a Range, you can provide the column headers in the first row of the range and click on filter in the Editing group on Home tab. This will make the AutoFilter on for the Range. You can remove the filters that you have in your data. You can also reapply the filters when data changes occur. Filter by Selected Values You can choose what data is to be displayed by clicking the arrow next to a column header and selecting the Values in the column. Only those rows containing the selected values in the chosen column will be displayed. Consider the following data − If you want to display the data only for Position = High, click the arrow next to Position. A drop-down box appears with all the values in the position column. By default, all the values will be selected. Click Select All. All the boxes are cleared. Select High as shown in the following screen shot. Click OK. Only those Rows, which have the value High as Position, will be displayed. Filter by Text Consider the following data − You can filter this data such that only those Rows wherein the Title is “Manager” will be displayed. Click the arrow next to the column header Title. From the drop-down list, click Text Filters. Text filter options appear. Select Contains from the available options. The Custom AutoFilter dialog box opens. Type Manager in the Box next to Contains. Click OK. Only the Rows where Title contains Manager will be displayed. Filter by Date You can filter this data further such that only those Rows wherein the Title is “Manager” and HireDate is prior to 2011 can be displayed. That means you will display the Employee information for all the managers who have been with the organization from before 2011. Click the arrow next to the column header HireDate. From the drop-down list, click Date Filters. The Date filter options appear. Select Before from the drop-down list. Custom AutoFilter dialog box opens. Type 1/1/2011 in the box next to is before. You can also select the date from the date picker next to the box. Click OK. Only the rows where Title contains Manager and HireDate is prior to 1/1/2011 will be displayed. Filter by Numbers Consider the following data − You can filter this data such that only those rows where Total Marks > 850 can be displayed. Click the arrow next to the column header Total Marks. From the drop-down list, click Number Filters. The Number Filter options appear. Click Greater Than. Custom AutoFilter dialog box opens. Type 850 in the box next to Greater Than. Click OK. Only the rows wherein the total marks are greater than 850 will be displayed. Filter by Cell Color If the data has different cell colors or is conditionally formatted, you can filter by the colors that are displayed in your table. Consider the following data. The column Total Marks has conditional formatting with different cell colors. Click the arrow in the header Total Marks. From the drop-down list, click Filter by Color. The Filter by Cell Color options appear. Select the green color and click OK. Only the rows wherein the total marks column has green color cells will be displayed. Filter by Font Color If the data has different font colors or is conditionally formatted, you can filter by the colors that are displayed in your table. Consider the following data. The column – Total Marks has conditional formatting with font color applied. Click the arrow in the header Total Marks. From the Drop-Down List, click Filter by Color. Filter by Font Color options appear. Select the green color and click OK. Only the rows wherein the Total Marks column has green color font will be displayed. Filter by Cell Icon If the data has different icons or a conditional format, you can filter by the icons that are shown in your table. Consider the following data. The column Total Marks has conditional formatting with icons applied. Click the arrow in the header Total Marks. From the drop-down list, select Filter by Color. The Filter by Cell Icon options appear. Select the icon and click OK. Only the rows wherein the Total Marks column has the icon will be displayed. Clear Filter Removing filters is termed as Clear Filter in Excel. You can remove A filter from a specific column, or All of the filters in the worksheet at once. To remove a filter from a specific column, click the arrow in the table header of that column. From the drop-drown menu, click Clear Filter From “<specific Column Name>”. The filter in the column is removed.

Excel Data Analysis – Conditional Formatting ”; Previous Next In Microsoft Excel, you can use Conditional Formatting for data visualization. You have to specify formatting for a cell range based on the contents of the cell range. The cells that meet the specified conditions would be formatted as you have defined. Example In a range containing the sales figures of the past quarter for a set of salespersons, you can highlight those cells representing who have met the defined target, say, &dollar;2500. You can set the condition as total sales of the person >= &dollar;2500 and specify a color code green. Excel checks each cell in the range to determine whether the condition you specified, i.e., total sales of the person >= &dollar;2500 is satisfied. Excel applies the format you chose, i.e. the green color to all the cells that satisfy the condition. If the content of a cell does not satisfy the condition, the formatting of the cell remains unchanged. The result is as expected, only for the salespersons who have met the target, the cells are highlighted in green – a quick visualization of the analysis results. You can specify any number of conditions for formatting by specifying Rules. You can pick up the rules that match your conditions from Highlight cells rules Top / Bottom rules You can also define your own rules. You can − Add a rule Clear an existing rule Manage the defined rules Further, you have several formatting options in Excel to choose the ones that are appropriate for your Data Visualization − Data Bars Color Scales Icon Sets Conditional formatting has been promoted over the versions Excel 2007, Excel 2010, Excel 2013. The examples you find in this chapter are from Excel 2013. In the following sections, you will understand the conditional formatting rules, formatting options and how to work with rules. Highlight Cells Rules You can use Highlight Cells rule to assign a format to cells whose contents meet any of the following criteria − Numbers within a given numerical range − Greater Than Less Than Between Equal To Text that contains a given text string. Date occurring within a given range of dates relative to the current date − Yesterday Today Tomorrow In the last 7 days Last week This week Next week Last month This Month Next month Values that are duplicate or unique. Follow the steps to conditionally format cells − Select the range to be conditionally formatted. Click Conditional Formatting in the Styles group under Home tab. Click Highlight Cells Rules from the drop-down menu. Click Greater Than and specify >750. Choose green color. Click Less Than and specify < 500. Choose red color. Click Between and specify 500 and 750. Choose yellow color. The data will be highlighted based on the given conditions and the corresponding formatting. Top / Bottom Rules You can use Top / Bottom Rules to assign a format to cells whose contents meet any of the following criteria − Top 10 items − Cells that rank in the top N, where 1 <= N <= 1000. Top 10% − Cells that rank in the top n%, where 1 <= n <= 100. Bottom 10 items − Cells that rank in the bottom N, where 1 <= N <= 1000. Bottom 10% − Cells that rank in the bottom n%, where 1 <= n <= 100. Above average − Cells that are above average for the selected range. Below average − Cells that are below average for the selected range. Follow the steps given below to assign the Top/Bottom rules. Select the range to be conditionally formatted. Click Conditional Formatting in the Styles group under Home tab. Click Top/Bottom Rules from the drop-down menu. Top/Bottom rules options appear. Click Top Ten Items and specify 5. Choose green color. Click Bottom Ten Items and specify 5. Choose red color. The data will be highlighted based on the given conditions and the corresponding formatting. Repeat the first three steps given above. Click Top Ten% and specify 5. Choose green color. Click Bottom Ten% and specify 5. Choose red color. The data will be highlighted based on the given conditions and the corresponding formatting. Repeat the first three steps given above. Click Above Average. Choose green color. Click Below Average. Choose red color. The data will be highlighted based on the given conditions and the corresponding formatting. Data Bars You can use colored Data Bars to see the value in a cell relative to the values in the other cells. The length of the data bar represents the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value. You have six solid colors to choose from for the data bars – blue, green, red, yellow, light blue and purple. Data bars are helpful in visualizing the higher, lower and intermediate values when you have large amounts of data. Example – Day temperatures across regions in a particular month. You can use gradient fill color bars to visualize the value in a cell relative to the values in other cells. You have six Gradient Colors to choose from for the Data Bars – Blue, Green, Red, Yellow, Light Blue and Purple. Select the range to be formatted conditionally. Click Conditional Formatting in the Styles group under Home tab. Click Data Bars from the drop-down menu. The Gradient Fill options and Fill options appear. Click the blue data bar in the Gradient Fill options. Repeat the first three steps. Click the blue data bar in the Solid Fill options. You can also format data bars such that the data bar starts in the middle of the cell, and stretches to the left for negative values and stretches to the right for positive values. Color Scales You can use Color Scales to see the value in a cell relative to the values in the other cells in a given range. As in the case of Highlight