Category: excel Data Analysis

What-If Analysis with Goal Seek ”; Previous Next Goal Seek is a What-If Analysis tool that helps you to find the input value that results in a target value that you want. Goal Seek requires a formula that uses the input value to give result in the target value. Then, by varying the input value in the formula, Goal Seek tries to arrive at a solution for the input value. Goal Seek works only with one variable input value. If you have more than one input value to be determined, you have to use the Solver add-in. Refer to the chapter – Optimization with Excel Solver in this tutorial. Analysis with Goal Seek Suppose you want to take a loan of 5,000,000 and you want to repay in 25 years. You can pay an EMI of 50000. You want to know at what interest rate you can borrow the loan. You can use Goal Seek to find the interest rate at which you can borrow the loan as follows − Step 1 − Set up the Excel cells for Goal Seek as given below. Step 2 − Enter the values in column C corresponding to column D. The cell Interest_Rate is kept empty, as you have to retrieve that value. Further, though you know the EMI that you can pay (50000), that value is not included as you have to use the Excel PMT function to arrive at it. Goal Seek requires a formula to find the result. The PMT function is placed in the cell EMI so that it can be used by Goal Seek. Excel computes the EMI with the PMT function. The table now looks like − As the Interest_Rate cell is empty, Excel takes that value as 0 and calculates the EMI. You can ignore the result -13,888.89. Perform the Analysis with Goal Seek as follows − Step 1 − Go to DATA > What If Analysis > Goal Seek on the Ribbon. The Goal Seek dialog box appears. Step 2 − Type EMI in the Set cell box. This box is the reference for the cell that contains the formula that you want to resolve, in this case the PMT function. It is the cell C6, which you named as EMI. Step 3 − Type -50000 in the To value box. Here, you get the formula result, in this case, the EMI that you want to pay. The number is negative because it represents a payment. Step 4 − Type Interest_Rate in the By changing cell box. This box has the reference of the cell that contains the value you want to adjust, in this case the interest rate. It is cell C2, which you named as Interest_Rate. Step 5 − This cell that Goal Seek changes, must be referenced by the formula in the cell that you specified in the Set cell box. Click OK. Goal Seek produces a result, as shown below − As you can observe, Goal Seek found the solution using cell C6 (containing the formula) as 12% that is displayed in the cell C2, which is the interest rate. Click OK. Solving Story Problems You can solve story problems easily with Goal Seek. Let us understand this with the help of an example. Example Suppose there is a bookstore that has 100 books in storage. The original price of the book is 250 and certain number of books was sold at that price. Later, the bookstore announced a 10% discount on that book and cleared off the stock. You might want to know how many books are sold at the original price to obtain a total revenue of 24,500. You can use Goal Seek to find the solution. Follow the steps given below − Step 1 − Set the worksheet as given below. Step 2 − Go to DATA > What If Analysis > Goal Seek on the Ribbon. The Goal Seek dialog box appears. Step 3 − Type Revenue, 24500 and Books_OriginalPrice in the Set cell box, To Value box and By changing cell box respectively. Click OK. Goal Seek displays the status and solution. If 80 books were sold at the original price, the revenue would be 24500. Performing a Break-even Analysis In economy, break-even point is the point at which there is neither profit nor loss. This would mean − Revenue = Expenses, or Revenue – Expenses = 0 You can do break-even analysis with Goal Seek in Excel. Example Suppose there is a store that sells toys. You might want to make a break-even analysis of the store. Collect the following information from the store − Fixed cost of the store. Unit cost of the toy. Number of toys to be sold. You need to find at which price they should sell the toys to break even. Step 1 − Set the worksheet as given below. Step 2 − Go to DATA > What If Analysis > Goal Seek on the Ribbon. The Goal Seek dialog box appears. Step 3 − Type Break_even_Point, 0, and Unit_Price in the Set cell box, To value box and By changing cell box respectively. Click OK. As you can observe, Goal Seek gave the result that if the Unit Price is 35, the store will break even. Print Page Previous Next Advertisements ”;

Discuss Excel Data Analysis ”; Previous Next 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. Print Page Previous Next Advertisements ”;

Advanced Data Analysis – What-If Analysis ”; Previous Next What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. You can use several different sets of values in one or more formulas to explore all the various results. What-if analysis is useful in many situations while doing data analysis. For example − You can propose different budgets based on revenue. You can predict the future values based on the given historical values. If you are expecting a certain value as the result from a formula, you can find different sets of input values that produce the desired result. Excel provides you with the following What-if analysis tools that can be used based on your data analysis needs − Data Tables Scenario Manager Goal Seek Data Tables and Scenarios take sets of input values and project forward to determine possible results. Goal seek differs from Data Tables and Scenarios in that it takes a result and projects backwards to determine possible input values that produce that result. In this chapter, you will understand the possible situations where you can use the Whatif Analysis tools. For details on usage of these tools, refer to the later chapters in this tutorial. Data Tables 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. For example, you might want to know how much loan you can afford for a home by analyzing different loan amounts and interest rates. You can put these different values along with the PMT function in a Data Table and get the desired result. A Data Table works only with one or two variables, but it can accept many different values for those variables. For the details on Data Tables, refer to the chapter – What-If Analysis with Data Tables in this tutorial. Scenario Manager A scenario is a set of values that Excel saves and can substitute automatically in cells on a worksheet. The key features are − You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results. A scenario can have multiple variables, but it can accommodate only up to 32 values. You can also create a scenario summary report, which combines all the scenarios on one worksheet. For example, you can create several different budget scenarios that compare various possible income levels and expenses, and then create a report that lets you compare the scenarios side-by-side. Scenario Manager is a dialog box that allows you to save the values as a scenario and name the scenario. For the details on Scenarios, refer to the chapter – What-If Analysis with Scenario Manager in this tutorial. Goal Seek Goal Seek is useful if you know the result that you want from a formula, but not sure what input value the formula needs, to get that result. For example, if you want to borrow a loan and you know the loan amount, tenure of loan and the EMI that you can pay, you can use Goal Seek to find the interest rate at which you can avail the loan. Goal Seek can be used only with one variable input value. If you have more than one variable for input values, you can use the Solver add-in. For details on the usage of Goal Seek, refer to the chapter – What-If Analysis with Goal Seek in this tutorial. Solver Solver comes with Excel as an add-in. You can use Solver to find an optimal value for a formula in a cell called the target cell on a worksheet. Solver works with a group of cells that are related to the formula in the target cell. Solver adjusts the values in the adjustable cells that you specify, to produce the result that you specify, from the target cell formula. For the details on the usage of Excel Solver add-in, refer to the chapter – Optimization with Excel Solver in this tutorial. Print Page Previous Next Advertisements ”;

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

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&plus;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.