Category: Advanced Excel

Advanced Excel – Pivot Table Tools ”; Previous Next Source Data for a PivotTable You can change the range of the source data of a PivotTable. For example, you can expand the source data to include more rows of data. However, if the source data has been changed substantially, such as having more or fewer columns, consider creating a new PivotTable. Step 1 − Click anywhere in the PivotTable. The PIVOTTABLE TOOLS appear on the ribbon, with an option named ANALYZE. Step 2 − Click on the option – ANALYZE. Step 3 − Click on Change Data Source in the Data group. Step 4 − Click on Change Data Source. The current Data Source is highlighted. The Change PivotTable Data Source Window appears. Step 5 − In the Table/Range Box, select the Table/Range you want to include. Step 6 − Click OK. Change to a Different External Data Source. If you want to base your PivotTable on a different external source, it might be best to create a new PivotTable. If the location of your external data source is changed, for example, your SQL Server database name is the same, but it has been moved to a different server, or your Access database has been moved to another network share, you can change your current connection. Step 1 − Click anywhere in the PivotTable. The PIVOTTABLE TOOLS appear on the Ribbon, with an ANALYZE option. Step 2 − Click ANALYZE. Step 3 − Click on Change Data Source in the Data Group. The Change PivotTable Data Source window appears. Step 4 − Click on the option Choose Connection. A window appears showing all the Existing Connections. In the Show box, keep All Connections selected. All the Connections in your Workbook will be displayed. Step 5 − Click on Browse for More… The Select Data Source window appears. Step 6 − Click on New Source. Go through the Data Connection Wizard Steps. Alternatively, specify the File name, if your Data is contained in another Excel Workbook. Delete a PivotTable Step 1 − Click anywhere on the PivotTable. The PIVOTTABLE TOOLS appear on the Ribbon, with the ANALYZE option. Step 2 − Click on the ANALYZE tab. Step 3 − Click on Select in the Actions Group as shown in the image given below. Step 4 − Click on Entire PivotTable. The entire PivotTable will be selected. Step 5 − Press the Delete Key. If the PivotTable is on a separate Worksheet, you can delete the PivotTable by deleting the entire Worksheet also. To do this, follow the steps given below. Step 1 − Right-click on the Worksheet tab. Step 2 − Click on Delete. You get a warning message, saying that you cannot Undo Delete and might lose some data. Since, you are deleting only the PivotTable Sheet you can delete the worksheet. Step 3 − Click on Delete. The PivotTable worksheet will be deleted. Using the Timeline A PivotTable Timeline is a box that you can add to your PivotTable that lets you filter by time, and zoom in on the period you want. This is a better option compared to playing around with the filters to show the dates. It is like a slicer you create to filter data, and once you create it, you can keep it with your PivotTable. This makes it possible for you to change the time period dynamically. Step 1 − Click anywhere in the PivotTable. The PIVOTTABLE TOOLS appear on the Ribbon, with ANALYZE option. Step 2 − Click ANALYZE. Step 3 − Click on Insert Timeline in the Filter group. An Insert Timelines Dialog Box appears. Step 4 − In the Insert Timelines dialog box, click on the boxes of the date fields you want. Step 5 − Click OK. The timeline for your PivotTable is in place. Use a Timeline to Filter by Time Period Now, you can filter the PivotTable using the timeline by a time period in one of four time levels; Years, Quarters, Months or Days. Step 1 − Click the small arrow next to the time level-Months. The four time levels will be displayed. Step 2 − Click on Quarters. The Timeline filter changes to Quarters. Step 3 − Click on Q1 2015. The Timespan Control is highlighted. The PivotTable Data is filtered to Q1 2015. Step 4 − Drag the Timespan handle to include Q2 2015. The PivotTable Data is filtered to include Q1, Q2 2015. At any point of time, to clear timeline, click on the Clear Filter button. The timeline is cleared as shown in the image given below. Create a Standalone PivotChart You can create a PivotChart without creating a PivotTable first. You can even create a PivotChart that is recommended for your data. Excel will then create a coupled PivotTable automatically. Step 1 − Click anywhere on the Data Table. Step 2 − Click on the Insert tab. Step 3 − In the Charts Group, Click on Recommended Charts. The Insert Chart Window appears. Step 4 − Click on the Recommended Charts tab. The charts with the PivotChart icon in the top corner are PivotCharts. Step 5 − Click on a PivotChart. A Preview appears on the Right side. Step 6 − Click OK once you find the PivotChart you want. Your standalone PivotChart for your Data is available to you. Print Page Previous Next Advertisements ”;

Advanced Excel – New Functions ”; Previous Next Several new functions are added in the math and trigonometry, statistical, engineering, date and time, lookup and reference, logical, and text function categories. Also, Web category is introduced with few Web service functions. Functions by Category Excel functions are categorized by their functionality. If you know the category of the function that you are looking for, you can click that category. Step 1 − Click on the FORMULAS tab. The Function Library group appears. The group contains the function categories. Step 2 − Click on More Functions. Some more function categories will be displayed. Step 3 − Click on a function category. All the functions in that category will be displayed. As you scroll on the functions, the syntax of the function and the use of the function will be displayed as shown in the image given below. New Functions in Excel 2013 Date and Time Functions DAYS − Returns the number of days between two dates. ISOWEEKNUM − Returns the number of the ISO week number of the year for a given date. Engineering Functions BITAND − Returns a ”Bitwise And” of two numbers. BITLSHIFT − Returns a value number shifted left by shift_amount bits. BITOR − Returns a bitwise OR of 2 numbers. BITRSHIFT − Returns a value number shifted right by shift_amount bits. BITXOR − Returns a bitwise ”Exclusive Or” of two numbers. IMCOSH − Returns the hyperbolic cosine of a complex number. IMCOT − Returns the cotangent of a complex number. IMCSC − Returns the cosecant of a complex number. IMCSCH − Returns the hyperbolic cosecant of a complex number. IMSEC − Returns the secant of a complex number. IMSECH − Returns the hyperbolic secant of a complex number. IMSIN − Returns the sine of a complex number. IMSINH − Returns the hyperbolic sine of a complex number. IMTAN − Returns the tangent of a complex number. Financial Functions PDURATION − Returns the number of periods required by an investment to reach a specified value. RRI − Returns an equivalent interest rate for the growth of an investment. Information Functions ISFORMULA − Returns TRUE if there is a reference to a cell that contains a formula. SHEET − Returns the sheet number of the referenced sheet. SHEETS − Returns the number of sheets in a reference. Logical Functions IFNA − Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression. XOR − Returns a logical exclusive OR of all arguments. Lookup and Reference Functions FORMULATEXT − Returns the formula at the given reference as text. GETPIVOTDATA − Returns data stored in a PivotTable report. Math and Trigonometry Functions ACOT − Returns the arccotangent of a number. ACOTH − Returns the hyperbolic arccotangent of a number. BASE − Converts a number into a text representation with the given radix (base). CEILING.MATH − Rounds a number up, to the nearest integer or to the nearest multiple of significance. COMBINA − Returns the number of combinations with repetitions for a given number of items. COT − Returns the cotangent of an angle. COTH − Returns the hyperbolic cotangent of a number. CSC − Returns the cosecant of an angle. CSCH − Returns the hyperbolic cosecant of an angle. DECIMAL − Converts a text representation of a number in a given base into a decimal number. FLOOR.MATH − Rounds a number down, to the nearest integer or to the nearest multiple of significance. ISO.CEILING − Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance. MUNIT − Returns the unit matrix or the specified dimension. SEC − Returns the secant of an angle. SECH − Returns the hyperbolic secant of an angle. Statistical Functions BINOM.DIST.RANGE − Returns the probability of a trial result using a binomial distribution. GAMMA − Returns the Gamma function value. GAUSS − Returns 0.5 less than the standard normal cumulative distribution. PERMUTATIONA − Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects. PHI − Returns the value of the density function for a standard normal distribution. SKEW.P − Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean. Text Functions DBCS − Changes half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters. NUMBERVALUE − Converts text to number in a locale-independent manner. UNICHAR − Returns the Unicode character that is references by the given numeric value. UNICODE − Returns the number (code point) that corresponds to the first character of the text. User Defined Functions in Add-ins The Add-ins that you install contain Functions. These add-in or automation functions will be available in the User Defined category in the Insert Function dialog box. CALL − Calls a procedure in a dynamic link library or code resource. EUROCONVERT − Converts a number to euros, converts a number from euros to a euro member currency, or converts a number from one euro member currency to another by using the euro as an intermediary (triangulation). REGISTER.ID − Returns the register ID of the specified dynamic link library (DLL) or code resource that has been previously registered. SQL.REQUEST − Connects with an external data source and runs a query from a worksheet, then returns the result as an array without the need for macro programming. Web Functions The following web functions are introduced in Excel 2013. ENCODEURL − Returns a URL-encoded string. FILTERXML − Returns specific data from the XML content by using the specified XPath. WEBSERVICE − Returns the data from a web service. Print Page Previous Next Advertisements ”;

Advanced Excel – Flash Fill ”; Previous Next Flash Fill helps you to separate first and last names or part names and numbers, or any other data into separate columns. Step 1 − Consider a data column containing full names. Step 2 − Enter the first name in the column next to your data and press Enter. Step 3 − Start typing the next name. Flash Fill will show you a list of suggested names. Step 4 − Press Enter to accept the list. Step 5 − Enter a last name in the next column, and press Enter. Step 6 − Start typing the next name and press Enter. The column will be filled with the relevant last names. Step 7 − If the names have middle names also, you can still use Flash Fill to separate the data out into three columns by repeating it three times. Flash Fill works with any data you need to split into more than one column, or you can simply use it to fill out data based on an example. Flash Fill typically starts working when it recognizes a pattern in your data. Print Page Previous Next Advertisements ”;

Advanced Excel – Format Charts ”; Previous Next The Format pane is a new entry in Excel 2013. It provides advanced formatting options in clean, shiny, new task panes and it is quite handy too. Step 1 − Click on the Chart. Step 2 − Select the chart element (e.g., data series, axes, or titles). Step 3 − Right-click the chart element. Step 4 − Click Format <chart element>. The new Format pane appears with options that are tailored for the selected chart element. Format Axis Step 1 − Select the chart axis. Step 2 − Right-click the chart axis. Step 3 − Click Format Axis. The Format Axis task pane appears as shown in the image below. You can move or resize the task pane by clicking on the Task Pane Options to make working with it easier. The small icons at the top of the pane are for more options. Step 4 − Click on Axis Options. Step 5 − Select the required Axis Options. If you click on a different chart element, you will see that the task pane automatically updates to the new chart element. Step 6 − Select the Chart Title. Step 7 − Select the required options for the Title. You can format all the Chart Elements using the Format Task Pane as explained for Format Axis and Format Chart Title. Provision for Combo Charts There is a new button for combo charts in Excel 2013. The following steps will show how to make a combo chart. Step 1 − Select the Data. Step 2 − Click on Combo Charts. As you scroll on the available Combo Charts, you will see the live preview of the chart. In addition, Excel displays guidance on the usage of that particular type of Combo Chart as shown in the image given below. Step 3 − Select a Combo Chart in the way you want the data to be displayed. The Combo Chart will be displayed. Print Page Previous Next Advertisements ”;

Advanced Excel – Slicers ”; Previous Next Slicers were introduced in Excel 2010 to filter the data of pivot table. In Excel 2013, you can create Slicers to filter your table data also. A Slicer is useful because it clearly indicates what data is shown in your table after you filter your data. Step 1 − Click in the Table. TABLE TOOLS tab appears on the ribbon. Step 2 − Click on DESIGN. The options for DESIGN appear on the ribbon. Step 3 − Click on Insert Slicer. A Insert Slicers dialog box appears. Step 4 − Check the boxes for which you want the slicers. Click on Genre. Step 5 − Click OK. The slicer appears. Slicer tools appear on the ribbon. Clicking the OPTIONS button, provides various Slicer Options. Step 6 − In the slicer, click the items you want to display in your table. To choose more than one item, hold down CTRL, and then pick the items you want to show. Print Page Previous Next Advertisements ”;

Advanced Excel – Chart Design ”; Previous Next Ribbon of Chart Tools When you click on your Chart, the CHART TOOLS tab, comprising of the DESIGN and FORMAT tabs is introduced on the ribbon. Step 1 − Click on the Chart. CHART TOOLS with the DESIGN and FORMAT tabs will be displayed on the ribbon. Let us understand the functions of the DESIGN tab. Step 1 − Click on the chart. Step 2 − Click on the DESIGN tab. The Ribbon now displays all the options of Chart Design. The first button on the ribbon is the Add Chart Element, which is the same as the Chart Elements, given at the upper right corner of the Charts as shown below. Quick Layout You can use Quick Layout to change the overall layout of the Chart quickly by choosing one of the predefined layout options. Step 1 − Click on Quick Layout. Different possible layouts will be displayed. Step 2 − As you move on the layout options, the chart layout changes to that particular option. A preview of how your chart will look is shown. Step 3 − Click on the layout you like. The chart will be displayed with the chosen layout. Change Colors The Change Colors option is the same as in CHART ELEMENTS → Change Styles → COLOR. Chart Styles The Chart Styles option is the same as in CHART ELEMENTS → Change Styles → STYLE. Switch Row / Column You can use the Switch Row / Column button on the ribbon to change the display of data from X-axis to Y-axis and vice versa. Follow the steps given below to understand this. Step 1 − Click on Switch Row / Column. You can see that the data will be swapped between X-Axis and Y-Axis. Select Data You can change the Data Range included in the chart using this command. Step 1 − Click on Select Data. The Select Data Source window appears as shown in the image given below. Step 2 − Select the Chart Data Range. The window also has the options to edit the Legend Entries (Series) and Categories. This is the same as Chart Elements → Chart Filters → VALUES. Change Chart Type You can change to a different Chart Type using this option. Step 1 − Click on the Change Chart Type window. The Change Chart Type window appears. Step 2 − Select the Chart Type you want. The Chart will be displayed with the type chosen. Move Chart You can move the Chart to another Worksheet in the Workbook using this option. Click on Move Chart. The Move Chart window appears. Print Page Previous Next Advertisements ”;

Advanced Excel – Chart Recommendations ”; Previous Next Change in Charts Group The Charts Group on the Ribbon in MS Excel 2013 looks as follows − You can observe that − The subgroups are clubbed together. A new option ‘Recommended Charts’ is added. Let us create a chart. Follow the steps given below. Step 1 − Select the data for which you want to create a chart. Step 2 − Click on the Insert Column Chart icon as shown below. When you click on the Insert Column chart, types of 2-D Column Charts, and 3-D Column Charts are displayed. You can also see the option of More Column Charts. Step 3 − If you are sure of which chart you have to use, you can choose a Chart and proceed. If you find that the one you pick is not working well for your data, the new Recommended Charts command on the Insert tab helps you to create a chart quickly that is just right for your data. Chart Recommendations Let us see the options available under this heading. (use another word for heading) Step 1 − Select the Data from the worksheet. Step 2 − Click on Recommended Charts. The following window displaying the charts that suit your data will be displayed. Step 3 − As you browse through the Recommended Charts, you will see the preview on the right side. Step 4 − If you find the chart you like, click on it. Step 5 − Click on the OK button. If you do not see a chart you like, click on All Charts to see all the available chart types. Step 6 − The chart will be displayed in your worksheet. Step 7 − Give a Title to the chart. Fine Tune Charts Quickly Click on the Chart. Three Buttons appear next to the upper-right corner of the chart. They are − Chart Elements Chart Styles and Colors, and Chart Filters You can use these buttons − To add chart elements like axis titles or data labels To customize the look of the chart, or To change the data that’s shown in the chart Select / De-select Chart Elements Step 1 − Click on the Chart. Three Buttons will appear at the upper-right corner of the chart. Step 2 − Click on the first button Chart Elements. A list of chart elements will be displayed under the Chart Elements option. Step 3 − Select / De-select Chart Elements from the given List. Only the selected chart elements will be displayed on the Chart. Format Style Step 1 − Click on the Chart. Three Buttons will appear at the upper-right corner of the chart. Step 2 − Click on the second button Chart Styles. A small window opens with different options of STYLE and COLOR as shown in the image given below. Step 3 − Click on STYLE. Different options of Style will be displayed. Step 4 − Scroll down the gallery. The live preview will show you how your chart data will look with the currently selected style. Step 5 − Choose the Style option you want. The Chart will be displayed with the selected Style as shown in the image given below. Format Color Step 1 − Click on the Chart. Three Buttons will appear at the upper-right corner of the chart. Step 2 − Click on Chart Styles. The STYLE and COLOR window will be displayed. Step 3 − Click on the COLOR tab. Different Color Schemes will be displayed. Step 4 − Scroll down the options. The live preview will show you how your chart data will look with the currently selected color scheme. Step 5 − Pick the color scheme you want. Your Chart will be displayed with the selected Style and Color scheme as shown in the image given below. You can change color schemes from Page Layout Tab also. Step 1 − Click the tab Page Layout. Step 2 − Click on the Colors button. Step 3 − Pick the color scheme you like. You can also customize the Colors and have your own color scheme. Filter Data being displayed on the Chart Chart Filters are used to edit the data points and names that are visible on the chart being displayed, dynamically. Step 1 − Click on the Chart. Three Buttons will appear at the upper-right corner of the chart. Step 2 − Click on the third button Chart Filters as shown in the image. Step 3 − Click on VALUES. The available SERIES and CATEGORIES in your Data appear. Step 4 − Select / De-select the options given under Series and Categories. The chart changes dynamically. Step 5 − After, you decide on the final Series and Categories, click on Apply. You can see that the chart is displayed with the selected data. Print Page Previous Next Advertisements ”;

Advanced Excel – Instant Data Analysis ”; Previous Next In Microsoft Excel 2013, it is possible to do data analysis with quick steps. Further, different analysis features are readily available. This is through the Quick Analysis tool. Quick Analysis Features Excel 2013 provides the following analysis features for instant data analysis. Formatting Formatting allows you to highlight the parts of your data by adding things like data bars and colors. This lets you quickly see high and low values, among other things. Charts Charts are used to depict the data pictorially. There are several types of charts to suit different types of data. Totals Totals can be used to calculate the numbers in columns and rows. You have functions such as Sum, Average, Count, etc. which can be used. Tables Tables help you to filter, sort and summarize your data. The Table and PivotTable are a couple of examples. Sparklines Sparklines are like tiny charts that you can show alongside your data in the cells. They provide a quick way to see the trends. Quick Analysis of Data Follow the steps given below for quickly analyzing the data. Step 1 − Select the cells that contain the data you want to analyze. A Quick Analysis button appears to the bottom right of your selected data. Step 2 − Click the Quick Analysis button that appears (or press CTRL + Q). The Quick Analysis toolbar appears with the options of FORMATTING, CHARTS, TOTALS, TABLES and SPARKLINES. Conditional Formatting Conditional formatting uses the rules to highlight the data. This option is available on the Home tab also, but with quick analysis it is handy and quick to use. Also, you can have a preview of the data by applying different options, before selecting the one you want. Step 1 − Click on the FORMATTING button. Step 2 − Click on Data Bars. The colored Data Bars that match the values of the data appear. Step 3 − Click on Color Scale. The cells will be colored to the relative values as per the data they contain. Step 4 − Click on the Icon Set. The icons assigned to the cell values will be displayed. Step 5 − Click on the option – Greater than. Values greater than a value set by Excel will be colored. You can set your own value in the Dialog Box that appears. Step 6 − Click on Top 10%. Values that are in top 10% will be colored. Step 7 − Click on Clear Formatting. Whatever formatting is applied will be cleared. Step 8 − Move the mouse over the FORMATTING options. You will have a preview of all the formatting for your Data. You can choose whatever best suits your data. Charts Recommended Charts help you visualize your Data. Step 1 − Click on CHARTS. Recommended Charts for your data will be displayed. Step 2 − Move over the charts recommended. You can see the Previews of the Charts. Step 3 − Click on More as shown in the image given below. More Recommended Charts are displayed. Totals Totals help you to calculate the numbers in rows and columns. Step 1 − Click on TOTALS. All the options available under TOTALS options are displayed. The little black arrows on the right and left are to see additional options. Step 2 − Click on the Sum icon. This option is used to sum the numbers in the columns. Step 3 − Click on Average. This option is used to calculate the average of the numbers in the columns. Step 4 − Click on Count. This option is used to count the number of values in the column. Step 5 − Click on %Total. This option is to compute the percent of the column that represents the total sum of the data values selected. Step 6 − Click on Running Total. This option displays the Running Total of each column. Step 7 − Click on Sum. This option is to sum the numbers in the rows. Step 8 − Click on the symbol . This displays more options to the right. Step 9 − Click on Average. This option is to calculate the average of the numbers in the rows. Step 10 − Click on Count. This option is to count the number of values in the rows. Step 11 − Click on %Total. This option is to compute the percent of the row that represents the total sum of the data values selected. Step 12 − Click on Running Total. This option displays the Running Total of each row. Tables Tables help you sort, filter and summarize the data. The options in the TABLES depend on the data you have chosen and may vary. Step 1 − Click on TABLES. Step 2 − Hover on the Table icon. A preview of the Table appears. Step 3 − Click on Table. The Table is displayed. You can sort and filter the data using this feature. Step 4 − Click on the Pivot Table to create a pivot table. Pivot Table helps you to summarize your data. Sparklines SPARKLINES are like tiny charts that you can show alongside your data in cells. They provide a quick way to show the trends of your data. Step 1 − Click on SPARKLINES. The chart options displayed are based on the data and may vary. Step 2 − Click on Line. A line chart for each row is displayed. Step 3 − Click on the Column icon. A line chart for each row is displayed. Print Page Previous Next Advertisements ”;

Advanced Excel Tutorial PDF Version Quick Guide Resources Job Search Discussion Advanced Excel is a comprehensive tutorial that provides a good insight into the latest and advanced features available in Microsoft Excel 2013. It 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 ”;

Advanced Excel – Power Pivot ”; 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 source. You can create your own compelling reports and analytical applications, easily share insights, and collaborate with colleagues through Microsoft Excel and SharePoint. Using PowerPivot, you can import data, create relationships, create calculated columns and measures, and add PivotTables, slicers and Pivot Charts. Step 1 − You can use Diagram View in PowerPivot to create a relationship. To start, get some more data into your workbook. You can copy and paste data from a Web Page also. Insert a new Worksheet. Step 2 − Copy data from the web page and paste it on the Worksheet. Step 3 − Create a table with the data. Name the table Hosts and rename the Worksheet Hosts. Step 4 − Click on the Worksheet Hosts. Click the POWERPIVOT tab on the Ribbon. Step 5 − In the Tables group, click on Add to Data Model. Hosts Table gets added to the Data Model in the Workbook. The PowerPivot window opens. You will find all the Tables in the Data Model in the PowerPivot, though some of them are not present in the Worksheets in the Workbook. Step 6 − In PowerPivot window, in View group, click on Diagram View. Step 7 − Use the slide bar to resize the diagram so that you can see all tables in the diagram. Step 8 − Rearrange the tables by dragging their title bar, so that they are visible and positioned next to one another. Four tables Hosts, Events, W_Teams, and S_Teams are unrelated to the rest of the tables − Step 9 − Both, the Medals table and the Events table have a field called DisciplineEvent. Also, DisciplineEvent column in the Events table consists of unique, non-repeated values. Click on Data View in Views Group. Check DisciplineEvent column in the Events table. Step 10 − Once again, click on Diagram View. Click on the field Discipline Event in the Events table and drag it to the field DisciplineEvent in the Medals Table. A line appears between the Events Table and the Medals Table, indicating a relationship has been established. Step 11 − Click on the line. The line and the fields defining the relationship between the two tables are highlighted as shown in the image given below. Data Model using Calculated Columns Hosts table is still not connected to any of the other Tables. To do so, a field with values that uniquely identify each row in the Hosts table is to be found first. Then, search the Data Model to see if that same data exists in another table. This can be done in Data View. Step 1 − Shift to Data View. There are two ways of doing this. Click on Data View in the View group. Click on the Grid button on Task Bar. The Data View appears. Step 2 − Click on the Hosts table. Step 3 − Check the data in Hosts Table to see if there is a field with unique values. There is no such field in Hosts Table. You cannot edit or delete existing data using PowerPivot. However, you can create new columns by using calculated fields based on the existing data. In PowerPivot, you can use Data Analysis Expressions (DAX) to create calculations. Adjacent to the existing columns is an empty column titled Add Column. PowerPivot provides that column as a placeholder. Step 4 − In the formula bar, type the DAX formula − = CONCATENATE([Edition],[Season]) Press Enter. The Add Column is filled with values. Check the values to verify that they are unique across the rows. Step 5 − The newly created column with created values is named CreatedColumn1. To change the name of the column, select the column, right-click on it. Step 6 − Click on the option Rename Column. Step 7 − Rename the column as EditionID. Step 8 − Now, Select the Medals Table. Step 9 − Select Add Column. Step 10 − In the Formula Bar, type the DAX Formula, = YEAR ([EDITION]) and press Enter. Step 11 − Rename the Column as Year. Step 12 − Select Add Column. Step 13 − Type in the Formula Bar, = CONCATENATE ([Year], [Season]) A new column with values similar to those in the EditionID column in Hosts Table gets created. Step 14 − Rename the column as EditionID. Step 15 − Sort the Column in Ascending Order. Relationship using calculated columns Step 1 − Switch to Diagram View. Ensure that the tables Medals and Hosts are close to each other. Step 2 − Drag the EditionID column in Medals to the EditionID column in Hosts. PowerPivot creates a relationship between the two tables. A line between the two tables, indicates the relationship. The EditionID Field in both the tables is highlighted indicating that the relationship is based on the column EditionID. Print Page Previous Next Advertisements ”;