Category: Advanced Excel

Advanced Excel – External Data Connection ”; Previous Next Once you connect your Excel workbook to an external data source, such as a SQL Server database, Access database or another Excel workbook, you can keep the data in your workbook up to date by “refreshing” the link to its source. Each time you refresh the connection, you see the most recent data, including anything that is new or has been deleted. Let us see how to refresh PowerPivot data. Step 1 − Switch to the Data View. Step 2 − Click on Refresh. Step 3 − Click on Refresh All. The Data Refresh window appears showing all the Data Tables in the Data Model and tracking the refreshing progress. After the refresh is complete, the status is displayed. Step 4 − Click on Close. The data in your Data Model is updated. Update the Data Connections Step 1 − Click any cell in the table that contains the link to the imported data file. Step 2 − Click on the Data tab. Step 3 − Click on Refresh All in Connections group. Step 4 − In the drop-down list, click on Refresh All. All the data connections in the Workbook will be updated. Automatically Refresh Data Here we will learn how to refresh the data automatically when the workbook is opened. Step 1 − Click any cell in the table that contains the link to the imported Data file. Step 2 − Click on the Data tab. Step 3 − Click on Connections in the Connections group. The Workbook Connections window appears. Step 4 − Click on Properties. The Connection Properties Window appears. Step 5 − You will find a Usage tab and a Definition tab. Click on the Usage tab. The options for Refresh Control appear. Step 6 − Select Refresh data while opening the file. You also have an option under this: ‘Remove data from the external data range before saving the workbook’. You can use this option to save the workbook with the query definition but without the external data. Step 7 − Click OK. Whenever you open your Workbook, the up-to-date data will be loaded into your Workbook. Automatically refresh data at regular intervals Step 1 − Click any cell in the table that contains the link to the imported Data file. Step 2 − Click on the Data tab. Step 3 − Click on the Connections option in Connections group. A Workbook Connections window appears. Step 4 − Click on Properties. A Connection Properties Window appears. Step 5 − Click on the Usage tab. The options for Refresh Control appear. Step 6 − Now, select “Refresh every” and enter 60 minutes between each refresh operation. Step 7 − Click OK. Your data will be refreshed every 60 minute that is every hour. Enable Background Refresh For very large data sets, consider running a background refresh. This returns the control of Excel to you instead of making you wait several minutes for the refresh to finish. You can use this option when you are running a query in the background. However, you cannot run a query for any connection type that retrieves data for the Data Model. Step 1 − Click any cell in the table that contains the link to the imported Data file. Step 2 − Click on the Data tab. Step 3 − Click on Connections in the Connections group. The Workbook Connections window appears. Step 4 − Click on Properties. Connection Properties Window appears. Step 5 − Click on the Usage tab. The Refresh Control options appear. Step 6 − Click on Enable background refresh and then click OK. 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 ”;

Advanced Excel – Leader Lines ”; Previous Next A Leader Line is a line that connects a data label and its associated data point. It is helpful when you have placed a data label away from a data point. In earlier versions of Excel, only the pie charts had this functionality. Now, all the chart types with data label have this feature. Add a Leader Line Step 1 − Click on the data label. Step 2 − Drag it after you see the four-headed arrow. Step 3 − Move the data label. The Leader Line automatically adjusts and follows it. Format Leader Lines Step 1 − Right-click on the Leader Line you want to format. Step 2 − Click on Format Leader Lines. The Format Leader Lines task pane appears. Now you can format the leader lines as you require. Step 3 − Click on the icon Fill & Line. Step 4 − Click on LINE. Step 5 − Make the changes that you want. The leader lines will be formatted as per your choices. Print Page Previous Next Advertisements ”;

Advanced Excel – Richer Data Labels ”; Previous Next You can have aesthetic and meaningful Data Labels. You can include rich and refreshable text from data points or any other text in your data labels enhance them with formatting and additional freeform text display them in just about any shape Data labels stay in place, even when you switch to a different type of chart. You can also connect them to their data points with Leader Lines on all charts and not just pie charts, which was the case in earlier versions of Excel. Formatting Data Labels We use a Bubble Chart to see the formatting of Data Labels. Step 1 − Select your data. Step 2 − Click on the Insert Scatter or the Bubble Chart. The options for the Scatter Charts and the 2-D and 3-D Bubble Charts appear. Step 3 − Click on the 3-D Bubble Chart. The 3-D Bubble Chart will appear as shown in the image given below. Step 4 − Click on the chart and then click on Chart Elements. Step 5 − Select Data Labels from the options. Select the small symbol given on the right of Data Labels. Different options for the placement of the Data Labels appear. Step 6 − If you select Center, the Data Labels will be placed at the center of the Bubbles. Step 7 − Right-click on any one Data Label. A list of option appears as shown in the image given below. Step 8 − Click on the Format Data Label. Alternatively, you can also click on More Options available in the Data Labels options to display the Format Data Label Task Pane. The Format Data Label Task Pane appears. There are many options available for formatting of the Data Label in the Format Data Labels Task Pane. Make sure that only one Data Label is selected while formatting. Step 9 − In Label Options → Data Label Series, click on Clone Current Label. This will enable you to apply your custom Data Label formatting quickly to the other data points in the series. Look of the Data Labels You can do many things to change the look of the Data Label, like changing the Fill color of the Data Label for emphasis. Step 1 − Click on the Data Label, whose Fill color you want to change. Double click to change the Fill color for just one Data Label. The Format Data Label Task Pane appears. Step 2 − Click Fill → Solid Fill. Choose the Color you want and then make the changes. Step 3 − Click Effects and choose the required effects. For example, you can make the label pop by adding an effect. Just be careful not to go overboard adding effects. Step 4 − In the Label Options → Data Label Series, click on Clone Current Label. All the other data labels will acquire the same effect. Shape of a Data Label You can personalize your chart by changing the shapes of the Data Label. Step 1 − Right-click the Data Label you want to change. Step 2 − Click on Change Data Label Shapes. Step 3 − Choose the shape you want. Resize a Data Label Step 1 − Click on the data label. Step 2 − Drag it to the size you want. Alternatively, you can click on Size & Properties icon in the Format Data Labels task pane and then choose the size options. Add a Field to a Data Label Excel 2013 has a powerful feature of adding a cell reference with explanatory text or a calculated value to a data label. Let us see how to add a field to the data label. Step 1 − Place the Explanatory text in a cell. Step 2 − Right-click on a data label. A list of options will appear. Step 3 − Click on the option − Insert Data Label Field. Step 4 − From the available options, Click on Choose Cell. A Data Label Reference window appears. Step 5 − Select the Cell Reference where the Explanatory Text is written and then click OK. The explanatory text appears in the data label. Step 6 − Resize the data label to view the entire text. 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 ”;