Category: Big Data & Analytics

Advanced Excel Charts – Introduction ”; Previous Next You are aware that charts are the efficient data visualization means to convey the results. In addition to the chart types that are available in Excel, some widely used application charts are popular. In this tutorial, you will learn about these advanced charts and how you can create them in Excel. Types of Advanced Charts Following are the advanced charts that you will learn in this tutorial − Waterfall Chart Band Chart Gantt Chart Thermometer Chart Gauge Chart Bullet Chart Funnel Chart Waffle Chart Heat Map Step Chart Box and Whisker Chart Histogram Pareto Chart Organization Chart Advanced Charts in Brief We will see all the advanced charts briefly. Waterfall Chart A Waterfall chart is a form of data visualization that helps in understanding the cumulative effect of sequentially introduced positive or negative values. Band Chart A Band chart is a Line chart with added shaded areas to display the upper and lower boundaries of the defined data ranges. Gantt Chart A Gantt chart is a chart in which a series of horizontal lines depicting tasks, task duration and task hierarchy are used planning and tracking projects. Thermometer Chart A Thermometer chart keeps track of a single task, for e.g. completion of work, representing the current status as compared to a Target. It displays the percentage of the task completed, taking Target as 100%. Gauge Chart Gauge charts, also referred to as Dial charts or Speedometer charts, use a pointer or a needle to show information as a reading on a dial. Bullet Chart Bullet charts support the comparison of a measure to one or more related measures with a linear design. Funnel Chart Funnel chart is used to visualize the progressive reduction of data as it passes from one phase to another in a process. Waffle Chart Waffle chart is a 10 × 10 cell grid with the cells colored as per conditional formatting to portray a percent value such % work complete. Heat Map A Heat Map is a visual representation of data in a Table to highlight the data points of significance. Step Chart A Step chart is a Line chart that uses vertical and horizontal lines to connect the data points in a series, forming a step-like progression. Box and Whisker Chart Box and Whisker charts, also referred to as Box Plots are commonly used in statistical analysis. In a Box and Whisker chart, numerical data is divided into quartiles and a box is drawn between the first and third quartiles, with an additional line drawn along the second quartile to mark the median. The minimums and maximums outside the first and third quartiles are depicted with lines, which are called whiskers. Whiskers indicate variability outside the upper and lower quartiles, and any point outside the whiskers is considered as an outlier. Histogram A Histogram is a graphical representation of the distribution of numerical data and is widely used in Statistical Analysis. A Histogram is represented by rectangles with lengths corresponding to the number of occurrences of a variable in successive numerical intervals. Pareto Chart Pareto chart is widely used in Statistical Analysis for decision-making. It represents the Pareto principle, also called 80/20 Rule, which states that 80% of the results are due to 20% of the causes. Organization Chart An Organization chart graphically represents the management structure of an organization. Though some of these charts are included in Excel 2016, Excel 2013 and earlier versions do not have them as built-in charts. In this tutorial, you will learn how to create these charts from the built-in chart types in Excel. Methodology for Creating Advanced Excel Charts For each of the advanced charts mentioned above, you will learn how to create them in Excel with the following steps − Prepare data for the chart − Your input data might have to be put in a format that can be used to create the chart at hand. Hence, for each of the charts you will learn how to prepare the data. Create the chart − You will learn step by step how you can arrive at the chart, with illustrations. Print Page Previous Next Advertisements ”;

Advanced Excel – Organization Chart ”; Previous Next You can illustrate the reporting relationships in your team or organization using an organization chart. In Excel, you can use a SmartArt graphic that uses an organization chart layout. What is an Organization Chart? An Organization chart graphically represents the management structure of an organization, such as department managers and the corresponding reporting employees within the organization. Further, there can be assistants for the top managers and they are also depicted in the Organization chart. An Organization chart in Excel will be as shown below. Preparation of Data Following are steps to prepare the data for an Organization chart − Step 1 − Collate the information about the different roles in the organization as given below. Step 2 − Identify the reporting relationships in the hierarchy. You will use this information to create the Organization chart. Creating an Organization Chart Following are the steps to create the Organization chart. Step 1 − Click the INSERT tab on the Ribbon. Step 2 − Click the SmartArt Graphic icon in the Illustrations group. Step 3 − Choose a SmartArt Graphic dialog box appears. Step 4 − Select Hierarchy from the left pane. Step 5 − Click on an Organization Chart. Step 6 − A preview of the Organization Chart appears. Click OK. The Organization chart template appears in your worksheet. As you can observe, you can enter the text in the left pane and it appears immediately on the chart on the right. The box that has a bullet with line attached in the left pane indicates that it is Assistant box in the chart. The boxes with bullets in the left pane indicate they are part of hanging layout in the chart. Step 7 − Enter the information in the Text pane. Step 8 − Demote if there is reporting relationship. Step 9 − Click outside the chart. Your Organization chart is ready. Formatting the Organization Chart You can format the Organization chart to give it a designer look. Follow the steps given below − Click on the chart. Click the DESIGN tab on the Ribbon. Select Polished from the SmartArt Styles group. Change the colors of the boxes. Change the font color. Adjust the size of the boxes. Adjust the widths of the lines. Your Organization chart is ready. Print Page Previous Next Advertisements ”;

Advanced Excel Charts – Quick Guide ”; Previous Next Advanced Excel Charts – Introduction You are aware that charts are the efficient data visualization means to convey the results. In addition to the chart types that are available in Excel, some widely used application charts are popular. In this tutorial, you will learn about these advanced charts and how you can create them in Excel. Types of Advanced Charts Following are the advanced charts that you will learn in this tutorial − Waterfall Chart Band Chart Gantt Chart Thermometer Chart Gauge Chart Bullet Chart Funnel Chart Waffle Chart Heat Map Step Chart Box and Whisker Chart Histogram Pareto Chart Organization Chart Advanced Charts in Brief We will see all the advanced charts briefly. Waterfall Chart A Waterfall chart is a form of data visualization that helps in understanding the cumulative effect of sequentially introduced positive or negative values. Band Chart A Band chart is a Line chart with added shaded areas to display the upper and lower boundaries of the defined data ranges. Gantt Chart A Gantt chart is a chart in which a series of horizontal lines depicting tasks, task duration and task hierarchy are used planning and tracking projects. Thermometer Chart A Thermometer chart keeps track of a single task, for e.g. completion of work, representing the current status as compared to a Target. It displays the percentage of the task completed, taking Target as 100%. Gauge Chart Gauge charts, also referred to as Dial charts or Speedometer charts, use a pointer or a needle to show information as a reading on a dial. Bullet Chart Bullet charts support the comparison of a measure to one or more related measures with a linear design. Funnel Chart Funnel chart is used to visualize the progressive reduction of data as it passes from one phase to another in a process. Waffle Chart Waffle chart is a 10 × 10 cell grid with the cells colored as per conditional formatting to portray a percent value such % work complete. Heat Map A Heat Map is a visual representation of data in a Table to highlight the data points of significance. Step Chart A Step chart is a Line chart that uses vertical and horizontal lines to connect the data points in a series, forming a step-like progression. Box and Whisker Chart Box and Whisker charts, also referred to as Box Plots are commonly used in statistical analysis. In a Box and Whisker chart, numerical data is divided into quartiles and a box is drawn between the first and third quartiles, with an additional line drawn along the second quartile to mark the median. The minimums and maximums outside the first and third quartiles are depicted with lines, which are called whiskers. Whiskers indicate variability outside the upper and lower quartiles, and any point outside the whiskers is considered as an outlier. Histogram A Histogram is a graphical representation of the distribution of numerical data and is widely used in Statistical Analysis. A Histogram is represented by rectangles with lengths corresponding to the number of occurrences of a variable in successive numerical intervals. Pareto Chart Pareto chart is widely used in Statistical Analysis for decision-making. It represents the Pareto principle, also called 80/20 Rule, which states that 80% of the results are due to 20% of the causes. Organization Chart An Organization chart graphically represents the management structure of an organization. Though some of these charts are included in Excel 2016, Excel 2013 and earlier versions do not have them as built-in charts. In this tutorial, you will learn how to create these charts from the built-in chart types in Excel. Methodology for Creating Advanced Excel Charts For each of the advanced charts mentioned above, you will learn how to create them in Excel with the following steps − Prepare data for the chart − Your input data might have to be put in a format that can be used to create the chart at hand. Hence, for each of the charts you will learn how to prepare the data. Create the chart − You will learn step by step how you can arrive at the chart, with illustrations. Advanced Excel – Waterfall Chart Waterfall chart is one of the most popular visualization tools used in small and large businesses, especially in Finance. Waterfall charts are ideal for showing how you have arrived at a net value such as net income, by breaking down the cumulative effect of positive and negative contributions. What is a Waterfall Chart? A Waterfall chart is a form of data visualization that helps in understanding the cumulative effect of sequentially introduced positive or negative values. A typical Waterfall chart is used to show how an initial value is increased and decreased by a series of intermediate values, leading to a final value. In a Waterfall chart, the columns are color coded so that you can quickly tell positive from negative numbers. The initial and the final value columns start on the horizontal axis, while the intermediate values are floating columns. Because of this look, Waterfall charts are also called Bridge charts, Flying Bricks charts or Cascade charts. Advantages of Waterfall Charts A Waterfall chart has the following advantages − Analytical purposes − Used especially for understanding or explaining, the gradual transition in the quantitative value of an entity, which is subjected to increment or decrement. Quantitative analysis − Used in quantitative analysis ranging from inventory analysis to performance analysis. Tracking contracts − Starting with the number of contracts at hand at the beginning of the year, taking into account − The new contracts that are added The contracts that got cancelled The contracts that are finished, and Finally ending with the number of contracts at hand at the end of the year. Tracking performance of company over a given number of years. In general, if you have an initial value, and changes (positive and negative) occur to that value over a period of

Advanced Excel – Band Chart ”; Previous Next Suppose you have data across a time period to represent graphically, confiding each data point to a defined interval. For example, you might have to present customer survey results of a product from different regions. Band chart is suitable for this purpose. What is Band Chart? A Band chart is a Line chart with added shaded areas to display the upper and lower boundaries of the defined data ranges. The shaded areas are the Bands. Band chart is also referred to as Range chart, High-Low Line chart or Corridor chart. Advantages of Band Charts Band chart is used in the following scenarios − Monitoring a metric within standard defined bands. Profit % for each of the regions (represented by Line chart) and bands with defined intervals in the range 0% – 100%. Performance measurements of an employee or company responses to client’s complaints. Monitoring Service Tickets- Responded service tickets as line and the throughput time as bands. Preparation of Data You need to prepare the data that can be used to create a Band chart from the given input data. Step 1 − Consider the following data that you have from the customer survey for two regions – East and West across the financial year April – March. Suppose you want to compare this data across three intervals − Low (<50%). Medium (50% – 80%). High (>80%). Step 2 − Add three columns to the above table as shown below. As you can observe, the values in the column Low are 50%, denoting the band 0% – 50% and the values in the column Medium are 30%, denoting the bandwidth of Medium above the band Low. Similarly the values in the column High are 20%, denoting the band width of High above the band Low. Use this data to create a Band chart. Creating a Band Chart Follow the steps given below to create a Band chart − Step 1 − Select the data in the above table. Step 2 − Insert a Combo chart. Step 3 − Click on Change Chart Type. Change the chart types for the data series as follows − Line with Markers for East series and West series. Stacked Column for the series – Low, Medium and High. Your chart looks as shown below. Step 4 − Click one of the Columns. Step 5 − Change Gap Width to 0% in the Format Data Series pane. You will get Bands instead of Columns. Step 6 − Make the chart appealing − Add Chart Title. Adjust Vertical Axis range. Change the colors of the bands to Green-Yellow-Red. Add Data Labels to bands. The result is a Band chart with defined boundaries depicted by bands. The survey results are represented across the bands. One can quickly and clearly make out from the chart whether the survey results are satisfactory or they need attention. Your Band chart is ready. Print Page Previous Next Advertisements ”;

Advanced Excel Charts – Histogram ”; Previous Next A Histogram is a graphical representation of the distribution of numerical data. It is widely used in Statistical Analysis. Karl Pearson introduced histogram. In Excel, you can create a Histogram from the Analysis ToolPak that comes as an add-in with Excel. However, in such a case, when the data is updated, Histogram will not reflect the changed data unless it is modified through Analysis ToolPak again. In this chapter, you will learn how to create a Histogram from a Column chart. In this case, when the source data is updated the chart also gets refreshed. What is a Histogram? A Histogram is represented by rectangles with lengths corresponding to the number of occurrences of a variable in successive numerical intervals. The numerical intervals are called bins and the number of occurrences is called frequency. The bins are usually specified as consecutive, non-overlapping intervals of the variable. The bins must be adjacent and are of equal size. A rectangle over a bin with height proportional to the frequency of the bin depicts the number of cases in that bin. Thus, the horizontal axis represents the bins whereas the vertical axis represents the frequency. The rectangles are colored or shaded. A Histogram will be as shown below. Advantages of Histograms Histogram is used to inspect the data for its underlying distribution, outliers, skewness, etc. For example, Histogram can be used in statistical analysis in the following scenarios − A census of a country to obtain the people of various age groups. A survey focused on the demography of a country to obtain the literacy levels. A study on the effect of tropical diseases during a season across different regions in a state. Preparation of Data Consider the data given below. Create bins and calculate the number of values in each bin from the above data as shown below − The number of values in a bin is referred to as the frequency of that bin. This table is called a Frequency table and we will use it to create the Histogram. Creating a Histogram Following are the steps to create a Histogram. Step 1 − Select the data in the Frequency table. Step 2 − Insert a Clustered Column chart. Step 3 − Right click on the Columns and select Format Data Series from the dropdown list. Step 4 − Click SERIES OPTIONS and change the Gap Width to 0 under SERIES OPTIONS. Step 5 − Format the chart as follows. Click on Fill & Line. Click on Solid Line under Border. Select black for color. Type 1.5 for Width. Step 6 − Adjust the size of the chart. Your Histogram is ready. As you can observe, the length of each column corresponds to the frequency of that particular bin. Print Page Previous Next Advertisements ”;

Advanced Excel – Waffle Chart ”; Previous Next Waffle chart adds beauty to your data visualization, if you want to display work progress as percentage of completion, goal achieved vs Target, etc. It gives a quick visual cue of what you want to portray. Waffle chart is also known as Square Pie chart or Matrix chart. What is a Waffle Chart? Waffle chart is a 10 × 10 cell grid with the cells colored as per conditional formatting. The grid represents values in the range 1% – 100% and the cells will be highlighted with the conditional formatting applied to the % values they contain. For example, if the percentage of completion of work is 85%, it is portrayed by formatting all the cells that contain values <= 85% with a specific color, say green. Waffle chart looks as shown below. Advantages of Waffle Chart Waffle chart has the following advantages − It is visually interesting. It is very readable. It is discoverable. It does not distort the data. It provides visual communication beyond simple data visualization. Uses of Waffle Chart The Waffle chart is used for completely flat data that adds up to 100%. The percentage of a variable is highlighted to give the depiction by the number of cells that are highlighted. It can be used for various purposes, including the following − To display the percentage of work that is complete. To display the percentage of progress that is made. To depict the expenses incurred as against the budget. To display Profit %. To portray the actual value achieved as against the set target, say in sales. To visualize the company progress as against the goals that are set. To display the pass percentage in an exam in a college / city/ state. Creating a Waffle Chart Grid For the Waffle Chart, you need to first create the 10 × 10 Grid of square cells such that the Grid itself will be a square. Step 1 − Create a 10 × 10 square grid on an Excel sheet by adjusting the cell widths. Step 2 − Fill the cells with % values, starting with 1% in the left-bottom cell and ending with 100% in the right-top cell. Step 3 − Decrease the font size such that all the values are visible but do not change the shape of the grid. This is the grid that you will use for the Waffle chart. Creating a Waffle Chart Suppose you have the following data − Step 1 − Create a Waffle chart that displays the Profit% for the Region East by applying Conditional Formatting to the Grid you have created as follows − Select the Grid. Click Conditional Formatting on the Ribbon. Select New Rule from the drop down list. Define the Rule to format values <= 85 % (give the cell reference of the Profit %) with fill color and font color as dark green. Step 2 − Define another rule to format values > 85 % (give the cell reference of the Profit %) with fill color and font color as light green. Step 3 − Give the Chart Title by giving reference to the cell B3. As you can see, choosing the same color for both Fill and Font enable you not to display the %values. Step 4 − Give a Label to the chart as follows. Insert a Text box in the chart. Give the reference to the cell C3 in the Text box. Step 5 − Color the cell borders white. Your Waffle chart for the Region East is ready. Create Waffle charts for the Regions, i.e. North, South and West as follows − Create the Grids for North, South and West as given in the previous section. For each Grid, apply conditional formatting as given above based on the corresponding Profit % value. You can also make Waffle charts for different regions distinctly, by choosing a variation in the colors for Conditional Formatting. As you can see, the colors chosen for the Waffle charts on the right are varying from the colors chosen for the Waffle charts on the left. Print Page Previous Next Advertisements ”;

Advanced Excel – Funnel Chart ”; Previous Next Funnel chart is used to visualize the progressive reduction of data as it passes from one phase to another. Data in each of these phases is represented as different portions of 100% (the whole). Like the Pie chart, the Funnel chart does not use any axes either. For example, in a sales pipeline, there will be stages as shown below. Prospects → Qualified Prospects → Proposals → Negotiations → Final Sales. Typically, the values decrease gradually. Many prospects are identified, but a part of them are validated and even lesser qualify for Proposals. A still lesser number come for negotiations and in the end, there is only a handful of deals that are won. This will make the bars resemble a funnel. What is a Funnel Chart? The Funnel chart shows a process that starts at the initial state and ends with a final state, where it is noticeable in what stages the fall out happens and by what magnitude. If the chart is also combined with research data, meaning quantified measurements of just how many items are lost at each step of the sales or order fulfillment process, then the Funnel chart illustrates where the biggest bottlenecks are in the process. Unlike a real funnel, not everything that is poured in at the top flows through to the bottom. The name only refers to the shape of the chart, the purpose of which is illustrative. Another variant of Funnel chart is where the data in each of these phases is represented as different portions of 100% (the whole), to show at what rate the changes occur along the Funnel. Like the Pie chart, the Funnel chart does not use any axes either. Advantages of Funnel Charts Funnel chart can be used in various scenarios, including the following − To allow executives to see how effective the sales team is in turning a sales lead into a closed deal. A Funnel chart can be used to display Web site visitor trends. It can display visitor page hits to the home page at the top, and the other areas, for e.g. the web site downloads or the people interested in buying the product will be proportionally smaller. Order fulfillment funnel chart with the initiated orders on top and down to the bottom the orders delivered to satisfied customers. It shows how many there are still in the process and the percentage cancelled and returned. Another use of Funnel chart is to display sales by each salesperson. Funnel chart can also be used to evaluate Recruitment process. Funnel chart can also be used to analyze the order fulfillment process. Preparation of Data Place the data values in a table. Step 1 − Insert a column in the table as shown below. You will get the following data. You will use this table to create the Funnel chart. Creating a Funnel Chart Following are the steps to create the Funnel chart − Step 1 − Select the data and insert a Stacked Bar chart. Step 2 − Right click on the Shift Bars (blue color in the above chart) and change Fill color to No Fill. The chart looks as shown below. Step 3 − Design the chart as follows. Right click on the Vertical Axis. Select Format Axis from the dropdown list. Click AXIS OPTIONS in the Format Axis pane. Check the box – Categories in reverse order. Step 4 − Fine tune the chart as follows. Deselect Legend in Chart Elements. Change the Chart Title. Right click on the orange Bars. Select Format Data Series. Change the Gap Width to 0% in the SERIES OPTIONS. Step 5 − Select Data Labels in Chart Elements. Your Sales Pipeline Funnel chart is ready. Formatting a Funnel Chart Step 6 − Make the chart more appealing as follows Click on the Chart Elements. Deselect Horizontal Axis. Deselect Gridlines. Right click on the Bars. Select Format Data Series from the dropdown list. Click on Fill & Line under SERIES OPTIONS in the Format Data Series pane. Click on Gradient fill under the Fill option. Set Gradient stops. Step 7 − Click on Solid line under BORDER. Select Color as black. Your formatted Funnel chart is ready. Aesthetic Funnel Chart Now that you understood the fundamentals of Funnel chart, you can proceed to create an aesthetic Funnel chart that actually looks like a Funnel as follows − Step 1 − Start with the original table of data. Step 2 − Select the data and insert a 3-D Stacked Column chart. Step 3 − Design the chart as follows. Right click on the Columns. Select Format Data Series. Click on SERIES OPTIONS in the Format Data Series pane. Select Full Cone under Column shape. Step 4 − Fine tune the chart as follows. Click the Design tab on the Ribbon. Click Switch Row / Column. Right click on the Vertical Axis and select Format Axis from the dropdown list. Click AXIS OPTIONS in the Format Axis pane. Check the box – Values in reverse order. Step 5 − Deselect all the Chart Elements Step 6 − Right click on the Bars and select Format Data Series from the dropdown list. Step 7 − Click on SERIES OPTIONS in the Format Data Series pane and type 50% for Gap Depth under SERIES OPTIONS. Step 8 − Format your chart with details as follows. Select Data Labels in Chart Elements. Place them and format them. You can choose Call Out option for the Data Label of the bottom part. Give a Chart Title. Your Funnel chart is ready. Print Page Previous Next Advertisements ”;