Category: excel Data Analysis

Exploring Data with Hierarchies ”; Previous Next If your data more number of levels, it would be easy for you to explore and present it with Hierarchies. For any data value in your Hierarchy, you can drill down to display more details or drill up to have a holistic view. If your data model has a hierarchy, you can use it in Power View. Otherwise, you can create a hierarchy in the Power View itself with few easy steps. Creating a Hierarchy in Power View In Power View a Hierarchy is best depicted in Matrix visualization. Create a Hierarchy in Matrix visualization as follows − Drag the fields NOC_CountryRegion, Sport, Discipline, Year and Medal – in that order to ROWS area. Drag the field Medal Count to ∑ VALUES. The order of the fields in the ROWS area defines the Hierarchy in Power View. If you are not sure about the order of fields, start with any order and then rearrange them in the ROWS area by dragging them up and down, while looking at the display in the Power View. You will get the following Matrix visualization showing the five Hierarchy levels − As you observe, the levels are nested. Drilling Up and Drilling Down the Hierarchy You can drill up and drill down the hierarchy such that you can show just one level at a time. You can drill down for details and drill up for summary. To enable drill up and drill down, first set up show levels option as follows − Click the DESIGN tab on the Ribbon. Click Show Levels in the Options group. Select Rows – Enable Drill Down One Level at a Time from the drop-down list. The Matrix collapses to display only Level 1 data. As you observe, when you click on each of the data items, a downward arrow appears on the right side for that item indicating drill down. Now, you can drill down the data one level at a time as follows − Click Level 1 data item for which you want further details. For example, click AUS. As you observe, Medal Count for AUS is 1079. Click on the arrow to the right side of it. Alternatively, you can double click on the data item. Level 2 data pertaining to AUS will be displayed. As you observe, an upward arrow appears on the left side of the first item, indicating drill up and when you click on each of the data items, a downward arrow appears on the right side for that item indicating drill down. Now, you can drill down the data one more level as follows − Click Level 2 data item for which you want further details. For example, click Aquatics. As you observe, the medal count for Aquatics is 354. Click on the arrow to the right side of it. Level 3 data pertaining to Aquatics will be displayed. As you observe, an upward arrow appears on the left side of the first item, indicating drill up and when you click on each of the data items, a downward arrow appears on the right side for that item indicating drill down. Now, you can drill down the data one more level as follows − Click Level 3 data item for which you want further details. For e.g. click on Diving. As you observe, the medal count for Diving is 17. Click on the arrow to the right side of it. Level 4 data pertaining to Diving will be displayed. As you observe, an upward arrow appears on the left side of the first item, indicating drill up and when you click on each of the data items, a downward arrow appears on the right side for that item indicating drill down. Now, you can drill down the data one more level as follows − Click on the Level 4 data item for which you want further details. For example, click on 2008. As you observe, the medal count for 2008 is 3. Click on the arrow to the right side of it. Level 5 data pertaining to 2008 will be displayed. As you observe, for the first Level 5 data item an upward arrow appears on the left side indicating drill up. Drill down arrows do not appear on the right side for the data items as there are further levels to drill down. Now, you can drill up the data one level at a time by clicking on the upward arrow to the left side of the first item at each Level. Exploring a Hierarchy in Stacked Bar Chart You might want to highlight certain values in a hierarchy in a significant way. In such a case, you can use a chart visualization, such as Stacked Bar chart as follows − Click the Matrix visualization and switch to Stacked Bar Chart. Drag the field Medal to LEGEND area. You will get the Stacked Bar chart visualization as follows − In this case, you need to double-click on a Bar to drill down. Double-click on Bar representing AUS. The chart is drilled down to display the next level of data in the hierarchy. As you can observe, an upward arrow indicating drill up appears at the top-right corner of the chart. Drill down one more level by double-clicking on Aquatics Bar. The chart is drilled down to display the next level of data in the hierarchy. You can drill down by double-clicking on a Bar or drill up by clicking on the drill up arrow on the top-right corner of the chart. This gives you an interactive exploration of data during presentation also. Print Page Previous Next Advertisements ”;

Key Performance Indicators ”; Previous Next Key Performance Indicators (KPI) are a set of quantifiable measures that an organization uses to measure its performance over time. KPIs are normally used to evaluate the success of an organization as a whole or department-wise (e.g. sales, finance, etc.). You need to define the KPIs based on the organization objectives and monitor them from time to time to track the progress. There are a number of KPI types to choose from based on your requirements. Examples include − Income and Expenses Rate of Return Average Purchase Value Customer Lifetime Value Working Capital Note that KPIs are a form of communication involving the following activities − Identifying the KPIs based on the organization’s objectives. Monitoring and reporting the KPIs. Altering the KPIs as the organization progresses and / or the organization’s goals change. Identifying the KPIs The first and the most crucial step in KPI analysis is to identify the KPIs that effectively monitor the required trends in the organization. This requires complete understanding of the objectives and requires proper communication channels between the analysts and those who are responsible for fulfilling the objectives. There are a number of KPIs to choose from, but the success in monitoring relies on the right choice of those that are relevant to the objectives. The KPIs differ from organization to organization and from department to department and will be effective only when they lead to improvement in the performance. You can evaluate the relevance of a KPI using the SMART criteria – i.e. the KPI should be Specific, Measurable, Attainable, Relevant and Time-bound. In other words, the KPI chosen should meet the following criteria − The KPI reflects your Specific objective. The KPI enables you to Measure progress towards that goal. The goal for which the KPI is being defined is realistically Attainable. The goal that the KPI is targeting is Relevant to the organization. You can set a time-frame for achieving the goal so that the KPI reveals how near the goal is as compared to the time that is left. The defined KPIs are to be evaluated from time to time to find their relevance as the time progresses. If required, different KPIs need to be defined and monitored. Only then, your KPI monitoring will be relating to the current organization needs. Based on the analysis needs, you have to choose the relevant KPIs, and examples include the following − Sales department might use a KPI to measure monthly gross profit against projected gross profit. Accounting department might measure monthly expenditures against revenue to evaluate costs. Human resources department might measure quarterly employee turnover. Business professionals frequently use KPIs that are grouped together in a business scorecard to obtain a quick and accurate historical summary of business success or to identify trends or to identify performance improvement opportunities. The examples used in this chapter are indicative so as to help you in understanding how you can define and monitor KPIs in Excel. The sole discretion of identifying the KPIs lies with you based on the objectives so as to reflect the current scenario as compared to the targets. KPIs in Excel You can analyze performance against the set target with PowerPivot. For e.g., a PowerPivot KPI could be used to determine for each year and salesperson how his actual sales compared to his sales target. You can explore and visualize the same KPI with Power View. You can also define new KPIs and /or edit them in Power View. You can produce aesthetic reports with KPIs in Power View. Defining a KPI in Excel The first step in KPI analysis is to define the identified KPI. This requires defining the three parameters for the KPI as follows − Base Value A Base Value is defined by a calculated field that resolves to a value. The calculated field represents the current value for the item in that row of the table. E.g., aggregate of sales, profit for a given period, etc. Target Value/ Goal A Target Value (or Goal) is defined by a calculated field that resolves to a value, or by an absolute value. It is the value against which the current value is evaluated. This could be a fixed number, for example, average number of sick-leave days that is applicable to all the employees, or a calculated field, which results in a different goal for each row, for example, budget of each department in the organization. Status Status is the indicator of the value. It would be striking if you set it as visual indicator. In Power View in Excel, you can edit the KPI, choosing which indicators to use and what values to trigger each indicator. For example, suppose you want to monitor the Sales targets of the Salespersons in an organization who are selling a product. The objective of the analysis is to identify the best performers who are meeting the target Sales Amount. You can proceed to define the KPI as follows − Base Value − Current Value of the Sales Amount for each salesperson. Target Value / Goal − This is fixed for all the salespersons so as to enable comparison between the salespersons. Assume that the Target Sales Amount is 3500. Note that for a different analysis you could vary the target values for the salespersons. Status − The Status is to be displayed with a graphic to easily determine the status of the Base Value compared to the Target Value. KPIs in PowerPivot You can define KPIs in PowerPivot as follows − Start with two tables SalesPerson and Sales. SalesPerson table contains SalesPerson ID and SalesPerson Name. Sales table contains the sales information salesperson-wise and month-wise. Add the two tables to Data Model. Create a relationship between the two tables using the field SalesPerson ID. To set the Base Value, you need a calculated field for Sales Amount. Add the calculated field in the Sales table for Sales Amount column in the Data Model

Exploring Data with PivotTables ”; Previous Next You can do extensive data analysis using PivotTables and produce desired reports. The integration of Data Model with PivotTable enhances the way the data is collated, connected, summarized and reported. You can import tables from external data sources and create a PivotTable with the imported tables. This facilitates automatic updations of the values in the PivotTable whenever the data in the connected data sources is updated. Creating a PivotTable to analyze External Data To create a PivotTable to analyze external data − Open a new blank workbook in Excel. Click the DATA tab on the Ribbon. Click From Access in the Get External Data group. The Select Data Source dialog box appears. Select the Access database file. Click the Open button. The Select Table dialog box appears, displaying the tables in the database. Access database is a relational database and the tables will be similar to Excel tables, with the exception that relationships exist among those tables. Check the box Enable selection of multiple tables. Select all the tables. Click OK. The Import Data dialog box appears. Select PivotTable Report. This option imports the tables into your Excel workbook and creates a PivotTable for analyzing the imported tables. As you observe, the checkbox Add this data to the Data Model is selected and disabled, indicating that the tables will be added to the Data Model automatically. The data will be imported and an empty PivotTable will be created. The imported tables appear in the PivotTable Fields list. Exploring Data in Multiple Tables You can analyze the data from the imported multiple tables with PivotTable and arrive at the specific report you want in just few steps. This is possible because of the pre-existing relationships among the tables in the source database. As you imported all the tables from the database together at the same time, Excel recreates the relationships in its Data Model. In the PivotTable Fields list, you will find all the tables that you imported and the fields in each of them. If the fields are not visible for any table, Click on the arrow next to that table in the PivotTable Fields list. The fields in that table will be displayed. Exploring Data using PivotTable You know how to add fields to PivotTable and drag fields across areas. Even if you are not sure of the final report that you want, you can play with the data and choose the appropriate report. Suppose you want to have a report displaying the following − Data for five disciplines – Archery, Diving, Fencing, Figure Skating and Speed Skating. Regions that scored more than 80 medals in these 5 disciplines. The count of medals in each of the five disciplines in each of these regions. Total count of medals for the five disciplines in each of these regions. You can see how easily you can create this report in few steps. To start with, create a PivotTable displaying the count of medals in all the regions for the selected five disciplines as follows − Drag the NOC_CountryRegion field from the Medals table to the COLUMNS area. Drag Discipline from the Disciplines table to the ROWS area. Filter Discipline to display only the five disciplines for which you wanted the report. This can be done either in the PivotTable Fields area, or from the Row Labels filter in the PivotTable itself. Drag Medal from the Medals table to the VALUES area. Drag Medal from the Medals table to the FILTERS area. You will get the following PivotTable − As you observe, Count of Medals is displayed for all the regions and for the five disciplines that you selected. Next, you have to fine-tune this report so that only those regions with total count of medals greater than 80 will be displayed. Click the arrow button to the right of Column Labels. Click Value Filters in the drop-down list that appears. Select Greater Than… from the drop-down list that appears. Value Filters dialog box appears. As you observe, Count of Medals and is greater than are displayed in the boxes below Show items for which. Type 80 in the box next to the box containing is greater than and click OK. Now, the PivotTable displays only those regions with total count of medals in the selected five disciplines greater than 80. Creating a Relationship between Tables with PivotTable Fields If you do not import the tables at the same time, if the data is from different sources, or if you add new tables to your workbook, you have to create the relationships among the tables by yourself. Add a new worksheet with a table that contains Sport and SportID fields to your workbook. Name the table – Sports. Click ALL in the PivotTable Fields list in the PivotTable worksheet. You can see that the newly added table- Sports is also visible in the PivotTable Fields list. Next, add the field Sport also to the PivotTable as follows − Drag the field Sport from the table Sports to ROWS area. The Sport values appear as Row labels in the PivotTable. A message will appear in the PivotTable Fields list that Relationships between tables may be needed. A CREATE button appears next to the message. Click the CREATE button. The Create Relationship dialog box appears. Select Medals under Table. Select Sport under Column. Select Sports under Related Table. Sport appears under related column. Click OK. Drag Discipline under Sport in ROWS. This is to define the hierarchy in the PivotTable. The PivotTable displays the Sport and the corresponding group of disciplines for that sport. Print Page Previous Next Advertisements ”;

Exploring Data with Power View Maps ”; Previous Next You can use maps to display your data in the context of geography. Maps in Power View use Bing map tiles, so you can zoom and pan as you would with any other Bing map. To make maps work, Power View has to send the data to Bing through a secured web connection for geocoding. So, it asks you to enable content. Adding locations and values places dots on the map. The larger the value, the bigger the dot. When you add a multivalue series, you get pie charts on the map, with the size of the pie chart showing the size of the total. Exploring Data with Geographic Fields You can create a Power View Map Visualization if your data has a geographic field such as Country/Region, State/Province, or City. To create a map visualization for the medal count, country wise proceed as follows − Create a new Power View sheet from INSERT tab on the Ribbon. Drag the fields NOC_CountryRegion and Medal Count in the Power View Fields list to the Power View. A Table with these two fields are created. So, you have a geographic field and a numeric field. Click the DESIGN tab on the Ribbon. Click Map in the Switch Visualization group. The Table Visualization converts into Map Visualization. As you observe, Power View creates a map with a dot representing each geographic location. The size of the dot is the value of the corresponding numeric field, which is the medal count in this case. Further, in the Power View Fields list, the Geographic field will be in the Locations area and the Numeric field will be in the ∑ SIZE area. To display more information about a data point, you can do one of the following − If you place the cursor on a dot on the map, a box appears displaying the geographic location name and the corresponding numeric value. If you click on a dot on the map, that particular dot gets highlighted. Pie Charts as Data Points Suppose you want to add another field also to the map visualization. For example, you might want to display the details about the medals, viz. the medal types – Gold, Silver and Bronze. You can do it as follows − Drag the field Medal from the Power View Fields list to the COLOR area. The dots are converted to Pie charts. A Legend for Medal appears displaying the types of medals and respective colors. i.e., each color in the Pie chart represents the type of the medal. You can observe that the size of a Pie chart corresponds to the medal count and the size of each slice in the Pie chart corresponds to the count of that medal type. Highlighting a Data Point Now, you can filter your data and highlight a significant data point as follows − In the Filters area, set the Medal Count to display only the values greater than or equal to 300. Apply the filter. The map zooms and displays only the filtered values. Place the cursor on the dot representing Great Britain. The dot gets highlighted and zoomed. The details of the Pie chart will be displayed. As you can see, the medal count for gold for Great Britain is 514. You can find the medal counts for silver and bronze by placing the cursor on those slices. Highlighting a Pie Slice in a Data Point Next, you might want to highlight the gold medal count of Great Britain. Place the cursor on the slice with red color (as you can make out that red represents gold in the Legend). Click it. That Pie Slice will be highlighted. The other slices in that Pie chart and all the other Pie charts will be deactivated. Place the cursor on the dot again. The dot is zoomed. The slice representing gold will be highlighted. The details of the slice will be displayed. Print Page Previous Next Advertisements ”;

Exploring Data with Power View ”; Previous Next Power View enables interactive data exploration, visualization and presentation that encourages intuitive ad-hoc reporting. Large data sets can be analyzed on the fly using the versatile visualizations. The data visualizations can also be made dynamic facilitating ease of presentation of the data with a single Power View report. Power View is introduced in Microsoft Excel 2013. Before you start your data analysis with Power View, make sure that the Power View add-in enabled and available on the Ribbon. Click the INSERT tab on the Ribbon. Power View should be visible in the Reports group. Creating a Power View Report You can create a Power View report from the tables in the Data Model. Click the INSERT tab on the Ribbon. Click Power View in the Reports group. Opening Power View message box appears with a horizontal scrolling green status bar. This might take a little while. Power View sheet is created as a worksheet in your Excel workbook. It contains an empty Power View report, Filters space holder and the Power View Fields list displaying the tables in the Data Model. Power View appears as a tab on the Ribbon in the Power View sheet. Power View with Calculated Fields In the Data Model of your workbook, you have the following data tables − Disciplines Events Medals Suppose you want to display the number of medals that each country has won. Select the fields NOC_CountryRegion and Medal in the table Medals. These two fields appear under FIELDS in the Areas. Power View will be displayed as a table with the two selected fields as columns. The Power View is displaying what medals each country has won. To display the number of medals won by each country, the medals need to be counted. To get the medal count field, you need to do a calculation in the Data Model. Click PowerPivot tab on the Ribbon. Click Manage in the Data Model group. The tables in the Data Model will be displayed. Click the Medals tab. In the Medals table, in the calculation area, in the cell below the Medal column, type the following DAX formula Medal Count:=COUNTA([Medal]) You can observe that the medal count formula appears in the formula bar and to the left of the formula bar, the column name Medal is displayed. You will get a Power View message that the Data Model is changed and if you click OK, the changes will be reflected in your Power View. Click OK. In the Power View Sheet, in the Power View Fields list, you can observe the following − A new field Medal Count is added in the Medals table. A calculator icon appears adjacent to the field Medal Count, indicating that it is a calculated field. Deselect the Medal field and select the Medal Count field. Your Power View table displays the medal count country wise. Filtering Power View You can filter the values displayed in Power View by defining the filter criteria. Click the TABLE tab in the Filters. Click Medal Count. Click the icon Range file mode that is to the right of Medal Count. Select is greater than or equal to from the drop-down list in the box below Show items for which the value. Type 1000 in the box below that. Click apply filter. Below the field name – Medal Count, is greater than or equal to 1000 appears. Power View will display only those records with Medal Count >= 1000. Power View Visualizations In the Power View sheet, two tabs – POWER VIEW and DESIGN appear on the Ribbon. Click the DESIGN tab.You will find several visualization commands in the Switch Visualization group on the Ribbon. You can quickly create a number of different data visualizations that suit your data using Power View. The visualizations possible are Table, Matrix, Card, Map, Chart types such as Bar, Column, Scatter, Line, Pie and Bubble Charts, and sets of multiple charts (charts with same axis). To explore the data using these visualizations, you can start on the Power View sheet by creating a table, which is the default visualization and then easily convert it to other visualizations, to find the one that best illustrates your Data. You can convert one Power View visualization to another, by selecting a visualization from the Switch Visualization group on the Ribbon. It is also possible to have multiple visualizations on the same Power View sheet, so that you can highlight the significant fields. In the sections below, you will understand how you can explore data in two visualizations – Matrix and Card. You will get to know about exploring data with other Power View visualizations in later chapters. Exploring Data with Matrix Visualization Matrix Visualization is similar to a Table Visualization in that it also contains rows and columns of data. However, a matrix has additional features − It can be collapsed and expanded by rows and/or columns. If it contains a hierarchy, you can drill down/drill up. It can display totals and subtotals by columns and/or rows. It can display the data without repeating values. You can see these the differences in the views by having a Table Visualization and a Matrix Visualization of the same data side by side in the Power View. Choose the fields – Sport, Discipline and Event. A Table representing these fields appears in Power View. As you observe, there are multiple disciplines for every sport and multiple events for every discipline. Now, create another Power View visualization on the right side of this Table visualization as follows − Click the Power View sheet in the space to the right of the Table. Choose the fields – Sport, Discipline and Event. Another Table representing these fields appears in Power View, to the right of the earlier Table. Click the right Table. Click the DESIGN tab on the Ribbon. Click Table in the Switch Visualization group. Select Matrix from the drop-down list. The Table

Exploring Data with Powerpivot ”; Previous Next PowerPivot is an easy to use Data Analysis tool that can be used from within Excel. You can use PowerPivot to access and mashup data from virtually any data source. You can create your own fascinating reports with PowerPivot. You can access the PowerPivot commands from PowerPivot tab on the Ribbon. Click the PowerPivot tab on the Ribbon. The PowerPivot commands will be displayed on the Ribbon. You can observe that the commands related to Data Model also appear here. Adding Tables to Data Model If you have imported tables, they are added to the Data Model. You can manage the Data Model from PowerPivot Ribbon. You can add tables to Data Model with PowerPivot as follows − Insert a new worksheet in your workbook. Copy data from the web page and paste it on the worksheet. Create a table with the pasted data. Name the table as Hosts. Rename the worksheet as Hosts. Click the table Hosts. Click the POWERPIVOT tab on the Ribbon. Click Add to Data Model in the Tables group. The table is added to the Data Model. The PowerPivot window appears. You will find the table Hosts in the Data Model tables. Viewing Tables in the Data Model Click on the POWERPIVOT tab on the Ribbon. Click on Manage in the Data Model group. PowerPivot window appears, in Data View. PowerPivot has two views − Data View − It displays all the tables in the Data Model with fields displayed in columns and data as records in the rows, with a calculation area below each table. The table tabs look similar to the Excel worksheet tabs with names. You can move from table to table by clicking on the tabs. Diagram View − It displays all the tables as boxes with table name as caption and the fields listed in the box. You can drag the tables to align them, resize them to make all the fields visible, and create relationships by just clicking on the fields and connecting them with lines. You will understand the Diagram View and the Relationships in detail in the later sections. Here, observe that all the tables in the Data Model are visible in the PowerPivot window, irrespective of whether they are present as worksheets in the workbook or not. Viewing Relationships between Tables You can use the data from different tables for analysis and reporting only when relationships exist among them. You can view the relationships between tables from the diagram view in the PowerPivot window. Click Diagram View in the View group. Resize the diagram using the scroll bar so that you can see all the tables in the Data Model in the diagram. All the tables in the Data Model appear with their fields lists. The relationships among the tables are denoted by the lines connecting them. Creating Relationships between Tables You might want to create a relationship between the tables – Medals and Events. In order to do this, there should be a field that is common in both the tables and contains unique values in one of the tables. First, you need to verify this. Click the Data View in the View group. Click the Events tab to view the Events table. You can observe that the field DisciplineEvent in the Events table has unique values (no duplicate values). Click the Medals tab to view the Medals table. The field DisciplineEvent is available in the Medals table also. Hence, you can create a relationship using the field DisciplineEvent as follows − Click Diagram View in the View group. Rearrange the tables in the view by dragging them so that Events table and Medals table are close to each other. Resize the tables so that all the fields are visible. Click the field DisciplineEvent in the Events table and Drag to the field DisciplineEvent in the Medals table. A line appears between the Events table and the Medals table, indicating that a relationship has been established. Viewing the Field defining a Relationship You can view the field that is used to create the relationship between two tables. Click the relationship line connecting the two tables. The relationship line and the field defining the relationship between the two tables get highlighted. Print Page Previous Next Advertisements ”;

Importing Data into Excel ”; Previous Next You might have to use data from various sources for analysis. In Excel, you can import data from different data sources. Some of the data sources are as follows − Microsoft Access Database Web Page Text File SQL Server Table SQL Server Analysis Cube XML File You can import any number of tables simultaneously from a database. Importing Data from Microsoft Access Database We will learn how to import data from MS Access database. Follow the steps given below − Step 1 − Open a new blank workbook in Excel. Step 2 − Click the DATA tab on the Ribbon. Step 3 − Click From Access in the Get External Data group. The Select Data Source dialog box appears. Step 4 − Select the Access database file that you want to import. Access database files will have the extension .accdb. The Select Table dialog box appears displaying the tables found in the Access database. You can either import all the tables in the database at once or import only the selected tables based on your data analysis needs. Step 5 − Select the Enable selection of multiple tables box and select all the tables. Step 6 − Click OK. The Import Data dialog box appears. As you observe, you have the following options to view the data you are importing in your workbook − Table PivotTable Report PivotChart Power View Report You also have an option – only create connection. Further, PivotTable Report is selected by default. Excel also gives you the options to put the data in your workbook − Existing worksheet New worksheet You will find another check box that is selected and disabled – Add this data to the Data Model. Whenever you import data tables into your workbook, they are automatically added to the Data Model in your workbook. You will learn more about the Data Model in later chapters. You can try each one of the options to view the data you are importing, and check how the data appears in your workbook − If you select Table, Existing worksheet option gets disabled, New worksheet option gets selected and Excel creates as many worksheets as the number of tables you are importing from the database. The Excel tables appear in these worksheets. If you select PivotTable Report, Excel imports the tables into the workbook and creates an empty PivotTable for analyzing the data in the imported tables. You have an option to create the PivotTable in an existing worksheet or a new worksheet. Excel tables for the imported data tables will not appear in the workbook. However, you will find all the data tables in the PivotTable fields list, along with the fields in each table. If you select PivotChart, Excel imports the tables into the workbook and creates an empty PivotChart for displaying the data in the imported tables. You have an option to create the PivotChart in an existing worksheet or a new worksheet. Excel tables for the imported data tables will not appear in the workbook. However, you will find all the data tables in the PivotChart fields list, along with the fields in each table. If you select Power View Report, Excel imports the tables into the workbook and creates a Power View Report in a new worksheet. You will learn how to use Power View Reports for analyzing data in later chapters. Excel tables for the imported data tables will not appear in the workbook. However, you will find all the data tables in the Power View Report fields list, along with the fields in each table. If you select the option – Only Create Connection, a data connection will be established between the database and your workbook. No tables or reports appear in the workbook. However, the imported tables are added to the Data Model in your workbook by default. You need to choose any of these options, based on your intent of importing data for data analysis. As you observed above, irrespective of the option you have chosen, the data is imported and added to the Data Model in your workbook. Importing Data from a Web Page Sometimes, you might have to use the data that is refreshed on a web site. You can import data from a table on a website into Excel. Step 1 − Open a new blank workbook in Excel. Step 2 − Click the DATA tab on the Ribbon. Step 3 − Click From Web in the Get External Data group. The New Web Query dialog box appears. Step 4 − Enter the URL of the web site from where you want to import data, in the box next to Address and click Go. Step 5 − The data on the website appears. There will be yellow arrow icons next to the table data that can be imported. Step 6 − Click the yellow icons to select the data you want to import. This turns the yellow icons to green boxes with a checkmark as shown in the following screen shot. Step 7 − Click the Import button after you have selected what you want. The Import Data dialog box appears. Step 8 − Specify where you want to put the data and click Ok. Step 9 − Arrange the data for further analysis and/or presentation. Copy-pasting data from web Another way of getting data from a web page is by copying and pasting the required data. Step 1 − Insert a new worksheet. Step 2 − Copy the data from the web page and paste it on the worksheet. Step 3 − Create a table with the pasted data. Importing Data from a Text File If you have data in .txt or .csv or .prn files, you can import data from those files treating them as text files. Follow the steps given below − Step 1 − Open a new worksheet in Excel. Step 2 − Click the DATA tab on

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