Category: Advanced Excel

Advanced Excel – Manage Passwords ”; Previous Next If you are using Workbook Analysis or Compare Files commands for Workbooks that are password-protected, you can avoid having to type the password each time those files are opened. Excel 2013 has a Password Manager, which can be accessed through the Workbook Passwords Command. Step 1 − Click on the INQUIRE tab on the ribbon. Step 2 − Click on Workbook Passwords in the Miscellaneous Group. The Password Manager window opens. Step 3 − Add passwords of your Workbooks. Add password descriptions also. Embed Worksheet Data in a Web Page To share a part of your worksheet on the web, you can simply embed it on your web page. Other people can then work with the data in Excel Online or open the embedded data in Excel. Share an Excel worksheet in an online meeting You can present your Workbook online if you have the Lync installed. Step 1 − Close all the Workbooks that you do not want to share and keep only the Workbook you want to share Open. Step 2 − Click on the File menu. Step 3 − Click on the option – Share. Step 4 − Click on Present Online. Step 5 − Under the option – Present Online, click Present. Step 6 − In the Share Workbook Window, pick a scheduled meeting or click Start a new Lync meeting, and then click OK. Step 7 − To stop sharing, click Stop Sharing at the top of the screen. You can present your Workbook online if you have a Skype Account also. Microsoft is introducing Skype for Business. Print Page Previous Next Advertisements ”;

Advanced Excel – Sorting Data by Color ”; Previous Next If you have formatted a table column, manually or conditionally, with the cell color or font color, you can also sort by these colors. Step 1 − Click on the DATA tab. Step 2 − Click on Sort in the Sort & Filter group. The Sort dialog box appears. Step 3 − Under the Column option, in the Sort by box, select the column that you want to sort. For example, click on Exam 2 as shown in the image given below. Step 4 − Under the topic Sort On, select the type of sort. To sort by cell color, select Cell Color. To sort by font color, select Font Color. Step 5 − Click on the option Cell Color. Step 6 − Under Order, click the arrow next to the button. The colors in that column are displayed. Step 7 − You must define the order that you want for each sort operation because there is no default sort order. To move the cell color to the top or to the left, select On Top for column sorting and On Left for row sorting. To move the cell color to the bottom or to the right, select On Bottom for column sorting and On Right for row sorting. Print Page Previous Next Advertisements ”;

Advanced Excel – Data Model ”; Previous Next Excel 2013 has powerful data analysis features. You can build a data model, then create amazing interactive reports using Power View. You can also make use of the Microsoft Business Intelligence features and capabilities in Excel, PivotTables, Power Pivot, and Power View. Data Model is used for building a model where data from various sources can be combined by creating relationships among the data sources. A Data Model integrates the tables, enabling extensive analysis using PivotTables, Power Pivot, and Power View. A Data Model is created automatically when you import two or more tables simultaneously from a database. The existing database relationships between those tables is used to create the Data Model in Excel. Step 1 − Open a new blank Workbook in Excel. Step 2 − Click on the DATA tab. Step 3 − In the Get External Data group, click on the option From Access. The Select Data Source dialog box opens. Step 4 − Select Events.accdb, Events Access Database file. Step 5 − The Select Table window, displaying all the tables found in the database, appears. Step 6 − Tables in a database are similar to the tables in Excel. Check the ‘Enable selection of multiple tables’ box, and select all the tables. Then click OK. Step 7 − The Import Data window appears. Select the PivotTable Report option. This option imports the tables into Excel and prepares a PivotTable for analyzing the imported tables. Notice that the checkbox at the bottom of the window – ‘Add this data to the Data Model’ is selected and disabled. Step 8 − The data is imported, and a PivotTable is created using the imported tables. You have imported the data into Excel and the Data Model is created automatically. Now, you can explore data in the five tables, which have relationships defined among them. Explore Data Using PivotTable Step 1 − 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 best-suited report. In PivotTable Fields, click on the arrow beside the table – Medals to expand it to show the fields in that table. Drag the NOC_CountryRegion field in the Medals table to the COLUMNS area. Step 2 − Drag Discipline from the Disciplines table to the ROWS area. Step 3 − Filter Discipline to display only five sports: Archery, Diving, Fencing, Figure Skating, and Speed Skating. This can be done either in PivotTable Fields area, or from the Row Labels filter in the PivotTable itself. Step 4 − In PivotTable Fields, from the Medals table, drag Medal to the VALUES area. Step 5 − From the Medals table, select Medal again and drag it into the FILTERS area. Step 6 − Click the dropdown list button to the right of the Column labels. Step 7 − Select Value Filters and then select Greater Than… Step 8 − Click OK. The Value Filters dialog box for the count of Medals is greater than appears. Step 9 − Type 80 in the Right Field. Step 10 − Click OK. The PivotTable displays only those regions, which has more than total 80 medals. You could analyze your data from the different tables and arrive at the specific report you want in just a few steps. This was 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 recreated the relationships in its Data Model. If you do not import the tables at the same time, or 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. Create Relationship between Tables Relationships let you analyze your collections of the data in Excel, and create interesting and aesthetic reports from the data you import. Step 1 − Insert a new Worksheet. Step 2 − Create a new table with new data. Name the new table as Sports. Step 3 − Now you can create relationship between this new table and the other tables that already exist in the Data Model in Excel. Rename the Sheet1 as Medals and Sheet2 as Sports. On the Medals sheet, in the PivotTable Fields List, click All. A complete list of available tables will be displayed. The newly added table – Sports will also be displayed. Step 4 − Click on Sports. In the expanded list of fields, select Sports. Excel messages you to create a relationship between tables. Step 5 − Click on CREATE. The Create Relationship dialog box opens. Step 6 − To create the relationship, one of the tables must have a column of unique, non-repeated, values. In the Disciplines table, SportID column has such values. The table Sports that we have created also has the SportID column. In Table, select Disciplines. Step 7 − In Column (Foreign), select SportID. Step 8 − In Related Table, select Sports. Step 9 − In Related Column (Primary), SportID gets selected automatically. Click OK. Step 10 − The PivotTable is modified to reflect the addition of the new Data Field Sport. Adjust the order of the fields in the Rows area to maintain the Hierarchy. In this case, Sport should be first and Discipline should be the next, as Discipline will be nested in Sport as a sub-category. Print Page Previous Next Advertisements ”;

Advanced Excel – Workbook Analysis ”; Previous Next Workbook Analysis command creates an interactive report showing detailed information about the Workbook and its Structure, Formulas, Cells, Ranges, and Warnings. Step 1 − Click on the Inquire tab on the ribbon. Step 2 − Click on Workbook Analysis in the Report group. The Report will be displayed after the Workbook Analysis is completed. The Report has six categories − Summary − General information about the structure and content of the Workbook. Workbook (with subcategories) − General Workbook Statistics. Formulas (with subcategories) − Specific information about formulas in the Workbook. Cells (with subcategories) − Specific information about the cells in the Workbook. Ranges (with subcategories) − Specific information about the ranges in the Workbook. Warnings − Several types of warnings about the Workbook structure and content. Selecting a Category gives you more information about that Category. Step 3 − Click on the Formulas category. Sub-Categories of the formulas will be displayed. For example, All formulas are 224 in number. With numeric values are 224 in number. Step 4 − Click on a Sub-Category. For example, click “With numeric values”. Step 5 − Click on the Excel Export button. The Save As Dialog Box opens. Step 6 − Save the Report as an Excel Workbook. A Load Export File button appears next to Excel Export button. Step 7 − Click on the Load Export File button. The saved Report Excel Workbook opens. Diagrams In the Diagram group, under the INQUIRE tab, three types of diagrams are available. Workbook Relationship Worksheet Relationship Cell Relationship They are interactive diagrams created by links. The links show the dependencies between the nodes in the diagram. You can drag the links or nodes to arrange them and align them to view whatever you are looking for. Workbook Relationship You can use the Workbook Relationship diagram to create an interactive, graphical map of Workbook dependencies created by connections (links) between files. The types of links in the diagram can include other Workbooks, Access databases, text files, HTML pages, SQL Server databases, and other data sources. Step 1 − Click on the INQUIRE tab on the ribbon. Step 2 − Click on Workbook Relationship in the Diagram group. The Workbook Relationship Diagram appears, showing its links with different Data Sources. Worksheet Relationship You can use Worksheet Relationship Diagram to create an interactive, graphical map of connections (links) between Worksheets in the same Workbook and /or Worksheets in other Workbooks. Step 1 − Click on the INQUIRE tab on the ribbon. Step 2 − Click on Worksheet Relationship in the Diagram Group. The Worksheet Relationship Diagram appears, showing the links between the Worksheets in the same Workbook and in other Workbooks. Cell Relationship You can use the Cell Relationship Diagram to get a detailed, interactive diagram of all links from a selected cell to cells in other worksheets or even other workbooks. Step 1 − Click on the INQUIRE tab on the ribbon. Step 2 − Click on Cell Relationship in the Diagram group. The Cell Relationship Diagram options window appears. Step 3 − Select the options. Step 4 − Click OK. The Cell Relationship Diagram appears, showing links between the Selected Cell and the Cells in the same Worksheet, same Workbook and in other Workbooks, based on the options you have chosen. Step 5 − Click on Zoom. You can view the nodes clearly. Clean Excess Cell Formatting When a Workbook loads slowly, or has become huge, it might have formatting applied to rows or columns you are not aware of. You can use the Clean Excess Cell Formatting command to remove the excess formatting and greatly reduce the file size. This reduces file size and improves your Excel”s speed. But, you may want to make a backup copy of your file before cleaning the excess cell formatting, because there are certain cases where this process may increase your file size, and there is no way to undo the change. Step 1 − Click on the INQUIRE tab on the ribbon. Step 2 − Click on Clean Excess Cell Formatting in the Miscellaneous Group. The Clean Excess Cell Formatting Dialog Box appears. Step 3 − Choose All Sheets in the Apply to box. You will get a message about saving the changes made. Step 4 − Click OK. Print Page Previous Next Advertisements ”;

Excel – PivotTable Recommendations ”; Previous Next Excel 2013 has a new feature Recommended PivotTables under the Insert tab. This command helps you to create PivotTables automatically. Step 1 − Your data should have column headers. If you have data in the form of a table, the table should have Table Header. Make sure of the Headers. Step 2 − There should not be blank rows in the Data. Make sure No Rows are blank. Step 3 − Click on the Table. Step 4 − Click on Insert tab. Step 5 − Click on Recommended PivotTables. The Recommended PivotTables dialog box appears. Step 6 − Click on a PivotTable Layout that is recommended. A preview of that pivot table appears on the right–side. Step 7 − Double-click on the PivotTable that shows the data the way you want and Click OK. The PivotTable is created automatically for you on a new worksheet. Create a PivotTable to analyze external data Create a PivotTable by using an existing external data connection. Step 1 − Click any cell in the Table. Step 2 − Click on the Insert tab. Step 3 − Click on the PivotTable button. A Create PivotTable dialog box appears. Step 4 − Click on the option Use an external data source. The button below that, ‘Choose Connection’ gets enabled. Step 5 − Select the Choose Connection option. A window appears showing all the Existing Connections. Step 6 − In the Show Box, select All Connections. All the available data connections can be used to obtain the data for analysis. The option Connections in this Workbook option in the Show Box is to reuse or share an existing connection. Connect to a new external data source You can create a new external data connection to the SQL Server and import the data into Excel as a table or PivotTable. Step 1 − Click on the Data tab. Step 2 − Click on the From Other Sources button, in the Get External Data Group. The options of External Data Sources appear as shown in the image below. Step 3 − Click the option From SQL Server to create a connection to an SQL Server table. A Data Connection Wizard dialog box appears. Step 4 − Establish the connection in three steps given below. Enter the database server and specify how you want to log on to the server. Enter the database, table, or query that contains the data you want. Enter the connection file you want to create. Using the Field List option In Excel 2013, it is possible to arrange the fields in a PivotTable. Step 1 − Select the data table. Step 2 − Click the Insert Tab. Step 3 − Click on the PivotTable button. The Create PivotTable dialog box opens. Step 4 − Fill the data and then click OK. The PivotTable appears on a New Worksheet. Step 5 − Choose the PivotTable Fields from the field list. The fields are added to the default areas. The Default areas of the Field List are − Nonnumeric fields are added to the Rows area Numeric fields are added to the Values area, and Time hierarchies are added to the Columns area You can rearrange the fields in the PivotTable by dragging the fields in the areas. Step 6 − Drag Region Field from Rows area to Filters area. The Filters area fields are shown as top-level report filters above the PivotTable. Step 7 − The Rows area fields are shown as Row Labels on the left side of the PivotTable. The order in which the Fields are placed in the Rows area, defines the hierarchy of the Row Fields. Depending on the hierarchy of the fields, rows will be nested inside rows that are higher in position. In the PivotTable above, Month Field Rows are nested inside Salesperson Field Rows. This is because in the Rows area, the field Salesperson appears first and the field Month appears next, defining the hierarchy. Step 8 − Drag the field – Month to the first position in the Rows area. You have changed the hierarchy, putting Month in the highest position. Now, in the PivotTable, the field – Salesperson will nest under Month fields. In a similar way, you can drag Fields in the Columns area also. The Columns area fields are shown as Column Labels at the top of the PivotTable. PivotTables based on Multiple Tables In Excel 2013, it is possible to create a PivotTable from multiple tables. In this example, the table ‘Sales’ is on one worksheet and table – ‘Products’ is on another worksheet. Step 1 − Select the Sales sheet from the worksheet tabs. Step 2 − Click the Insert tab. Step 3 − Click on the PivotTable button on the ribbon. The Create PivotTable dialog box, Step 4 − Select the sales table. Step 5 − Under “choose whether you want to analyze multiple tables”, Click Add this Data to the Data Model. Step 6 − Click OK. Under the PivotTable Fields, you will see the options, ACTIVE and ALL. Step 7 − Click on ALL. You will see both the tables and the fields in both the tables. Step 8 − Select the fields to add to the PivotTable. You will see a message, “Relationships between tables may be needed”. Step 9 − Click on the CREATE button. After a few steps for creation of Relationship, the selected fields from the two tables are added to the PivotTable. Print Page Previous Next Advertisements ”;

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 – Format Reports ”; Previous Next In Excel 2013, Power View has 39 additional themes with more varied chart palettes as well as fonts and background colors. When you change the theme, the new theme applies to all the Power View Views in the Report or Sheets in the Workbook. You can also change the text size for all of your Report Elements. You can add Background Images, choose Background Formatting, choose a Theme, change the Font Size for One Visualization, change the Font or Font Size for the whole sheet and Format numbers in a Table, Card, or Matrix. Step 1 − Click on the Power View tab on the ribbon. Step 2 − Click on Set Image in the Background Image group. Step 3 − Click on Set Image in the drop-down menu. The File Browser opens. Step 4 − Browse to the Image File you want to use as Background and click open. The image appears as background in the Power View. Step 5 − Click on Image Position in the Background Image group. Step 6 − Click on Stretch in the Drop down menu as shown in the image given below. The Image stretches to the full size of Power View. Step 7 − Click on Transparency in the Background Image group. Step 8 − Click on 80% in the Drop down box. The higher the percentage, the more transparent (less visible) the image. Instead of images, you can also set different backgrounds to Power View. Step 9 − Click on Power View tab on the ribbon. Step 10 − Click on Set Image in the Background Image group. Step 11 − Click on Remove Image. Now, Power View is with White Background. Step 12 − Click on Background in the Themes Group. You have different backgrounds, from solids to a variety of gradients. Step 13 − Click on Dark1 Center Gradient. The background changes to Dark1 Center Gradient. As the background is darker, the text turns into white color. Step 14 − Click on the Power View tab on the ribbon. Step 15 − Click on Font in the Themes group. All the available fonts will be displayed in the Drop down list. Step 16 − Click on Tahoma. The font of the text changes to Tahoma. Step 17 − Click on Text Size in the Themes group. The percentages of the font sizes will be displayed. The default font size 100% is highlighted. Step 18 − Select 150%. The font size changes from 100% to 150%. Step 19 − Switch Stacked Bar Chart Visualization to Table Visualization. The changed font and font size are retained in the Table Visualization. When you change the font in one Visualization, the same font is applied to all visualizations except for the font in a Map Visualization. You cannot have different fonts for different Visualizations. However, you can change the font size for individual visualizations. Step 20 − Click on a Cell in the Column containing Numbers. Step 21 − Click on Number in the Number Group. Step 22 − Click on Percentage in the Drop down menu. The entire column containing the selected cell gets converted to the selected format. You can format numbers in Card and Matrix Visualizations also. Hyperlinks You can add a Hyperlink to a text box in Power View. If Data Model has a field that contains a Hyperlink, add that field to the Power View. It can link to any URL or email address. This is how you could get the sport images in Tiles in Tiles Visualization in the previous section. Printing You can print Power View sheets in Excel 2013. What you print is what you see on the sheet when you send it to the printer. If the sheet or view contains a region with a scroll bar, the printed page contains the part of the region that is visible on the screen. If a sheet or view contains a region with tiles, then whichever tile is selected is the one that prints. Support for right-to-left languages Power View in Excel 2013 supports right-to-left languages. Power View takes the setting for default direction from Excel. You can change those settings. Step 1 − Click on File. Step 2 − Click on Options. The Excel Options window appears. Step 3 − By default, the direction has two options Right-to-left and Left-to-right. Step 4 − Set the default direction to Left-to-right. Step 5 − Click OK. Step 6 − Change the default direction to Right-to-left. Step 7 − Click OK. You can see that the columns are now starting from the right side of the screen as shown in the image given below. Microsoft Office supports right-to-left functionality and features for languages that work in a right-to-left or a combined right-to-left, left-to-right environment for entering, editing, and displaying text. In this context, “right-to-left languages” refers to any writing system that is written from right to left and includes languages that require contextual shaping, such as Arabic, and languages that do not. You can change your display to read right-to-left or change individual files so their contents read from right to left. If your computer does not have a right-to-left language version of Office installed, you will need to install the appropriate language pack. You must also be running a Microsoft Windows operating system that has right-to-left support — for example, the Arabic version of Windows Vista Service Pack 2 — and enable the keyboard language for the right-to-left language that you want to use. Print Page Previous Next Advertisements ”;

Advanced Excel – Pie Charts ”; Previous Next You can have simple Pie Chart Visualizations in Power View. Step 1 − Click on the Table Visualization as shown below. Step 2 − Click on Other Chart in the Switch Visualization group. Step 3 − Click on Pie as shown in the image given below. The Table Visualization converts into Pie Chart Visualization. You now have a Simple Pie Chart Visualization wherein the count of Medals are shown by the Pie Size, and Countries by Colors. You can also make your Pie Chart Visualization sophisticated by adding more features. One such example is SLICES. Step 1 − Add Field Gender to the Table above. Step 2 − Click on Pie Chart Visualization. Step 3 − Drag Field Gender in the Power View Fields List to the SLICES Box as shown below. Now, with SLICES, you can visualize the count of Medals for men and for women in each country. Step 4 − Click on a SLICE in the Pie Chart Visualization. Step 5 − Only the specific row containing the data specific to the SLICE will be displayed in the TABLE VISUALIZATION above. Bubble and Scatter Charts You can use the Bubble and Scatter charts to display many related data in one chart. In Scatter charts, the x-axis displays one numeric field and the y-axis displays another, making it easy to see the relationship between the two values for all the items in the chart. In a Bubble Chart, a third numeric field controls the size of the data points. Step 1 − Add one Category Field and one Numeric Field to the Table. Step 2 − Click on Other Chart in the Switch Visualization group. Step 3 − Click on Scatter. The Table Visualization converts into Scatter Chart Visualization. The Data points are little circles and all are of same size and same color. Category is in DETAILS Box. Step 4 − Drag Medal to Size. Step 5 − Drag field NOC_CountryRegion to Σ X VALUE. The Scatter Chart Visualization converts into Bubble Chart Visualization. The data points are circles of the size represented by the values of Data points. The color of the circles is the X VALUE and given in the Legend. The data labels are the Category Values. Step 6 − Drag the field NOC_CountryRegion to the COLOR Box. The bubbles will be colored by the values of the field in the COLOR box. Step 7 − Drag the Year field to PLAY AXIS. A Time Line with Play button will be displayed below the Bubble Chart Visualization. Step 8 − Click on the Play button. The bubbles travel, grow, and shrink to show how the values change based on the PLAY AXIS. You can pause at any point to study the data in more detail. Step 9 − Click any color on the Legend. All the bubbles of that color will be highlighted and other bubbles will be grayed out. Maps 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 the content. Adding locations and fields 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. Step 1 − Drag a Geographic Field such as Country/Region, State/Province, or City from Power View Fields List to the table. Step 2 − Drag a numeric field such as Count to the table. Step 3 − Click on DESIGN tab on the ribbon. Step 4 − Click on Map in the Switch Visualization group. The Table Visualization converts into Map Visualization. Power View creates a map with a dot for every geographic location. The size of the dot is the value of the corresponding numeric field. Step 5 − Click on a dot. The data, viz., the geographic location and the numeric information relating to the size of the dot will be displayed. Step 6 − You can also verify that below the Power View Fields List, the Geographic field is in the Locations Box and Numeric Field is in the Σ SIZE Box. Step 7 − Drag Medal to COLOR Box. The Dots are converted into Pie Charts. Each Color in the Pie representing the category of the Medals. Step 8 − Place the cursor on one of the Dots. The Dot gets highlighted and zoomed. The details of the Pie Slice are displayed. Step 9 − Place the cursor on one of the Dots and click on it. That Pie Slice is highlighted. The other Slices in the Pie and all other Pie Dots will gray out. Multiples: A Set of Charts with the Same Axes Multiples are a series of charts with identical X and Y axes. You can have Multiples arranged side by side, making it easy to compare many different values at the same time. Multiples are also called Trellis Charts. Step 1 − Start with a Pie Chart. Click on the Pie Chart. Step 2 − Drag a Field to Vertical Multiples. Step 3 − Click on the LAYOUT tab on the ribbon. Step 4 − Click on Grid Height and select a number. Step 5 − Click on Grid Width and select a number. Vertical Multiples expand across the available page width and then wrap down the page into the space available. If all the multiples do not fit in the available space, you get a vertical scroll bar. Step 6 − Drag the field in VERTICAL MULTIPLES to HORIZONTAL MULTIPLES. The horizontal multiples expand across the page. If all the multiples do not fit in the

Advanced Excel – Visualizations ”; Previous Next You can quickly create a number of different data visualizations that suit your data using Power View. The visualizations possible are Tables, Matrices, Cards, Tiles, Maps, Charts such as Bar, Column, Scatter, Line, Pie and Bubble Charts, and sets of multiple charts (charts with same axis). Create Charts and other Visualizations For every visualization you want to create, you start on a Power View sheet by creating a table, which you then easily convert to other visualizations, to find one that best illustrates your Data. Step 1 − Under the Power View Fields, select the fields you want to visualize. Step 2 − By default, the Table View will be displayed. As you move across the Table, on the top-right corner, you find two symbols – Filters and Pop out. Step 3 − Click on the Filters symbol. The filters will be displayed on the right side. Filters has two tabs. View tab to filter all visualizations in this View and Table tab to filter the specific values in this table only. Visualization – Matrix A Matrix is made up of rows and columns like a Table. However, a Matrix has the following capabilities that a Table does not have − Display data without repeating values. Display totals and subtotals by row and column. With a hierarchy, you can drill up/drill down. Collapse and Expand the Display Step 1 − Click on the DESIGN tab. Step 2 − Click on Table in the Switch Visualization Group. Step 3 − Click on Matrix. The Matrix Visualization appears. Visualization – Card You can convert a Table to a series of Cards that display the data from each row in the table laid out in a Card format, like an index Card. Step 1 − Click on the DESIGN tab. Step 2 − Click on Table in the Switch Visualization Group. Step 3 − Click on Card. The Card Visualization appears. Visualization – Charts In Power View, you have a number of Chart options: Pie, Column, Bar, Line, Scatter, and Bubble. You can use several design options in a chart such as showing and hiding labels, legends, and titles. Charts are interactive. If you click on a Value in one Chart − the Value in that chart is highlighted. All the Tables, Matrices, and Tiles in the report are filtered to that Value. That Value in all the other Charts in the report is highlighted. The charts are interactive in a presentation setting also. Step 1 − Create a Table Visualization from Medals data. You can use Line, Bar and Column Charts for comparing data points in one or more data series. In these Charts, the x-axis displays one field and the y-axis displays another, making it easy to see the relationship between the two values for all the items in the Chart. Line Charts distribute category data evenly along a horizontal (category) axis, and all numerical value data along a vertical (value) axis. Step 2 − Create a Table Visualization for two Columns, NOC_CountryRegion and Count of Medal. Step 3 − Create the same Table Visualization below. Step 4 − Click on the Table Visualization below. Step 5 − Click on Other Chart in the Switch Visualization group. Step 6 − Click on Line. The Table Visualization converts into Line Chart Visualization. In a Bar Chart, categories are organized along the vertical axis and values along the horizontal axis. In Power View, there are three subtypes of the Bar Chart: Stacked, 100% stacked, and Clustered. Step 7 − Click on the Line Chart Visualization. Step 8 − Click on Bar Chart in the Switch Visualization Group. Step 9 − Click on the Stacked Bar option. The Line Chart Visualization converts into Stacked Bar Chart Visualization. Step 10 − In the Power View Fields, in the Medals Table, select the Field Gender also. Step 11 − Click on one of the bars. That portion of the bar is highlighted. Only the row containing the Data specific to the selected bar is displayed in the table above. You can use the column charts for showing data changes over a period of time or for illustrating comparison among different items. In a Column Chart, the categories are along the horizontal axis and values are along the vertical axis. In Power View, there are three Column Chart subtypes: Stacked, 100% stacked, and Clustered. Step 12 − Click on the Stacked Bar Chart Visualization. Step 13 − Click on Column Chart in the Switch Visualization group. Step 14 − Click on Stacked Column. The Stacked Bar Chart Visualization converts into Stacked Column Chart Visualization. Print Page Previous Next Advertisements ”;

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 ”;